Microsoft FRx Report Design Essentials II

Audience
This class is ideal for:

• Intermediate FRx users

• Experienced FRx users who would like to build upon their skills and master product proficiency

• FRx Software product resellers

At Course Completion
After completing this course, students should be able to:

• Have a renewed understanding of basic FRx report design concepts

• Explore ways to efficiently create and distribute many financial reports including:

• Linking to external worksheet data

• Statements of Cash Flow

• Using full accounts or multiple segments in reports

• Allocation reporting

• Linking Row Formats

• Relational reporting

• Multi company consolidations

• Currency Translation

• OLAP (Online Analytical Processing) output

• XBRL (eXtensible Business Reporting Language) output

• Advanced formatting tips and tricks

Prerequisites
Before attending this course, students must have:

• General knowledge of Microsoft® Windows®

• Basic knowledge of accounting principles

• FRx Report Design Essentials I is strongly encouraged

Course Outline
Chapter 1: Overview

This chapter gives students a general overview of the features and benefits of using FRx for financial statement design. It also introduces the demo company that will be used in the course exercises.

Main Topics

• About FRx Software products

• Terminology

• Demo company account structure

• Sample report demonstration

Chapter 2: Concept Overview

This chapter contains a detailed list of all fields within the Row Format, Column Layout, Reporting Tree and Catalog. Basic report design concepts are reviewed, while advanced functionality is introduced.

• Basic Row Format review

• Working with TOTs and CALs in the Row Format

• How C's affect TOT and CAL rows and why to use them

• Defining a sort in the Row Format

• Relating rows to other rows

• Basic Column Layout review

• Defining a favorable/(unfavorable) variance Column Layout

• Working with BASE periods and years in the Column Layout

• Using "Add Reporting Units from Chart of Accounts" to build a Reporting Tree

• Using the split function to isolate a single account mask character

• Creating a new Reporting Catalog

• Defining report headers and footers

• Working with sub-totals in the DrillDown Viewer

Labs

• Row Repair

• Column Repair

• Build a Tree

• Build a Catalog

Chapter 3: Linking to External Worksheets

This chapter allows students to complete reports that link to external worksheet data. Within FRx there are two methods for "linking" to worksheets. The combined method allows for a single link to both the general ledger and the worksheet within the Row Format. The separate method works with one or more external links in the Row Format and requires the use of a Reporting Tree. Both methods are practiced in this chapter.

• Referencing external worksheet data

• Optional row modifiers

• Determining the period of data to be reported on

• Using the combined GL+Worksheet link in the Row Format

• Calculation priority with working with intersecting row and column calculations

• Adding an external worksheet link to the Row Format

• Proper syntax in combined vs. separate linking

• Using the optional row modifiers /CPO and /RPO when referencing worksheet data

• Designing Column Layouts that report on either/or data (i.e. either Actual or Budget) depending on the report date

Labs

• Combined Worksheet Method

• Separate Worksheet Method

Chapter 4: Gross Profit per Unit - Practice

This chapter contains a practice tutorial to reinforce the students knowledge of the combined linking method covered in the previous exercises. Students modify an existing report based on changes to the worksheet and additional data required on the report.

• Defining the GL+Worksheet link

• Working with the /CPO and /RPO row modifiers

• Using account and book code modifiers

• Advanced calculations and non-printing rows

Lab

• Gross Profit per Unit

Chapter 5: Full Account Code & Multiple Segments

This chapter introduces the concept of defining the Row Format on the full account code, a technique useful when designing reports based on specific grouping of responsibility centers such as department or location. It also introduces the concept of reporting on multiple segments within the row and how to define the related Reporting Tree.

• Defining a fully qualified account in the Row Format

• How a full account in the Row Format affects units in a Reporting Tree

• Defining a range of periods in the Column Layout

• Using conditional dates in the Column Layout

• Using a range in a calculated column in the Column Layout

Lab

• Using the Full Account Code

• Using Multiple Segments

Chapter 6: Defining Departments in the Row Format - Practice

This chapter contains a practice tutorial to reinforce the students knowledge of the design concepts covered in the previous exercises. Students build a summary Income Statement where departmental data is presented in the Row Format, the Column Layout defines the key financial reporting data and the Reporting Tree is based on specific locations.

• Defining the Row Format on the Department segment

• Defining a Reporting Tree that recognizes the Department in the Row Format

• Using Account Filters in the Column Layout

Lab

• Defining Departments in the Row Format

Chapter 7: Statements of Cash Flow

This chapter looks at how simple it is to design a Statement of Cash Flow using FRx. In these exercises you work with partially created building blocks, and add the key cash flow reporting details.

• Using "C" in the normal balance to properly state sources and uses of cash

• Using the beginning balance and year to date account modifiers

• Defining a non-printing check row

• Using specific placement calculations in the Row Format

• Designing Column Layout's that display the changes in activity for the time frame being reported on

• Defining calculation priority for intersecting row and column calculations

Lab

• Monthly Statement of Cash Flow

• Current and Year to Date Statement of Cash Flow

Chapter 8: FASB Style Statement of Cash Flow - Review

This chapter contains a practice tutorial to reinforce the students knowledge of cash flow and worksheet linking design concepts covered in the previous exercises. Students modify the existing current and year to date Statement of Cash Flow to accommodate details for specific line items drawn from an external worksheet.

• Modifying an existing report design

• Using a combined GL+Worksheet link

• Using the /RPO row modifier

• Using non-printing check rows to validate report accuracy

Lab

• FASB Style Statement of Cash Flow

Chapter 9: Allocations

This chapter covers two distinct types of allocation reports. The first pulls data from the general ledger that will be used for an allocation journal entry, the second allocates financial results for reporting purposes only.

• Using specific column placement in Row Format calculation

• Column G restrictions in the Row Format

• Using Book Code modifiers in the Row Format

• How to work with full accounts in the Row Format when using a Reporting Tree

• Suppressing data from rolling up in the Reporting Tree

• Using multiple GL Links in the Row Format and Reporting Tree

• Presentation of external worksheet data in calculated columns

• Advanced calculations in the Column Layout

• Advanced formatting

Lab

• Allocation Journal Entry

• Allocation Reporting

• Two-pass Allocation Reports

Chapter 10: Modified Allocation of Corporate Expenses - Practice

This chapter contains a practice tutorial to reinforce the students knowledge of allocation and worksheet linking design concepts covered in the previous exercises.

• Modifying existing building blocks

• Working with the Combined GL+Worksheet link

• Using WKS type columns in the Column Layout

• Using non-printing rows and specific column placement calculations in the Row Format

• Proper catalog definition when linking to external worksheet data

Lab

• Modified Allocation of Corporate Expenses

Chapter 11: Row Linking

This chapter discusses reports built with the Row Linking functionality, wherein a Row Format pulls data from one or more Row Formats instead of, or in addition to, the general ledger.

• Adding a Row Format link to a Row Format

• Using text labels for row codes

• Defining a Reporting Tree that relates to multiple Row Formats

• Advanced formatting concepts

• How rounding options work with Row Linking

Lab

• Row Linking

• Row and General Ledger Linking

Chapter 12: Row Linking Income Statement - Practice

This chapter contains a practice tutorial to reinforce the students knowledge of row linking design concepts covered in the previous exercises. Using row linking, a summary and detailed Income Statement are built in a single catalog.

• Linking to another Row Format

• Defining a Reporting Tree that can access multiple Row Formats

• Using the BASE+ concept in the Column Layout

• Rounding options when using Row Linking

Lab

• Row Linking Income Statement

Chapter 13: Review

This chapter includes a fun way to review the report design concepts covered so far in class.

• Concept Review

Lab

• Crossword Puzzle

Chapter 14: Relational Reporting

This chapter introduces the concept of relational reporting, where one or more base rows and related column calculations are defined. Common uses of relational reporting include percentage of sales and percentage of total expenses, however this concept may be used for any report that requires reporting on a relationship between elements.

• Defining CBR (Base) rows in the Row Format

• Defining Column Layout calculations that work with CBR in the Row Format

• Suppressing CBR in detail reports

Lab

• Defining a Base Row

• Multiple Base Row Definitions

Chapter 15: Multiple Base Row Definitions & Using Statistical Information as a Base Row - Practice

This chapter contains two practice tutorials to reinforce the students knowledge of defining base rows and related calculations. The first tutorial assigns multiple CBR rows, while the second relates statistical results to financial data.

• Defining the CBR or Base Row

• Using Reporting Unit restrictions in the Row Format vs. full account codes to restrict the data being reported on

• Using Reporting Unit restrictions in the Column Layout vs. Account Filters to restrict the data being reported on

Lab

• Multiple Base Row Definitions

• Using Statistical Data as a Base Row

Chapter 16: Additional Formatting Concepts

This chapter introduces a variety of advanced report design topics.

• IF/THEN/ELSE calculations in the Row Format

• Defining effective dates in the catalog

• Using column Z (Additional Text) in the Reporting Tree

• Working with Microsoft® Excel or Microsoft® Word and FRx

• Defining International Formats

Lab

• Using Conditional Statements

• Using Effective Dates

• Additional Descriptive Text

Chapter 17: Consolidations

This chapter introduces the concept of reporting on multiple companies and their eliminating entries, in a single report. Two methods for reporting on elimination entries are included. In the first method, activity is stored in the general ledger in specific GL accounts. In the second method, total elimination amounts are pulled from an external worksheet.

• Overview of multiple company consolidations in FRx

• How to format amounts

• Assigning an international format to a report

• When is a Reporting Tree required

• Side-by-side (consolidating) Column Layout design

• Linking to external worksheet data

Lab

• GL Eliminations

• External Eliminations

Chapter 18: OLAP Output

This chapter introduces students to OLAP (Online Analytical Processing) output options within FRx. A brief hands-on with an OLAP cube is also included.

• FRx and OLAP

• Microsoft Local Cubes

• Microsoft Server Cubes

• Excel Pivot Chart and Excel Pivot Table output options

• Terminology in OLAP and FRx

Lab

• Working with OLAP Cubes in Excel

Chapter 19: XBRL

This chapter introduces XBRL (eXtensible Business Reporting Language) in FRx. XBRL is a standardized financial reporting format that is clear and easy for anyone to interpret and analyze.

• XBRL Overview

• Adding an XBRL link and related taxonomy file

• Assigning XBRL tags

• The XBRL Instance Document output option

Lab

• Create an XBRL Document in FRx