Microsoft FRx Report Design Essentials II
Course 8400: Two days; Instructor-Led
On This Page
Introduction
The two-day Report Design Essentials II course builds on the basic report design skills learned in Report Design Essentials I. Using Microsoft® Business Solutions for Analytics-FRx® you learn advanced report design including linking to external worksheet data, linking row formats, multi company consolidations, currency translation reporting and cash flow. A combination of instructor guided exercises and practice tutorials allow students to master their report design skills.
A thorough understanding of these topics allows you to master the advanced report design techniques necessary to create a more comprehensive suite of financial and management reports. You learn how to use links to extract data from a variety of sources including non-financial metrics maintained outside the general ledger. This course also teaches you how to move into cutting edge technology, using OLAP and XRBL output options. These skills enable you to take control of the financial reporting process, allowing you to be more productive and efficient in the way you create, generate and distribute financial information, which in turn will help you to enable internal and external stakeholders to make well informed decisions to positively impact your organization as a whole.
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 |
Student Materials
The student materials include a comprehensive workbook and other necessary items for this class.
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 |
After completing this chapter, students should be able to:
|
• | Understand the terminology used in class | |
• | Have a high level understanding of rows, columns, trees and how they relate to each other in the catalog. | |
• | Generate a catalog to the DrillDown Viewer and navigate to the various reports and drill down levels |
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.
Main Topics
|
• | 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 |
After completing this chapter, students should be able to:
|
• | Understand basic Row Format design | |
• | Correct design errors in an existing Row Format | |
• | Understand basic Column Layout design | |
• | Supply missing elements within a favorable/(unfavorable) Column Layout design | |
• | Understand basic Reporting Tree design | |
• | Split an account mask segment and create a tree based on a single character within the mask | |
• | Understand basic catalog settings | |
• | Build a catalog, defining the building blocks, headers and detail level |
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.
Main Topics
|
• | 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 |
After completing this chapter, students should be able to:
|
• | Understand the difference between the combined link and separate linking methods and when to use either | |
• | Add a combined link to the Row Format with related syntax | |
• | Add a separate link to the Row Format with related syntax | |
• | Understand when multiple separate links are required | |
• | Understand how and when to use /CPO or /RPO | |
• | Add conditional print controls to the Column Layout | |
• | Create a Reporting Tree with external link (separate method) information | |
• | Understand the "Use row format(s) and worksheet links from Reporting Tree" function |
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.
Main Topics
|
• | 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
After completing this chapter, students should be able to:
|
• | Modify a combined GL+Worksheet link | |
• | Modify the cell references used within a combined link | |
• | Understand the use of /RPO when linking to a worksheet | |
• | Add an account and/or book code modifier to a row in the Row Format | |
• | Add non-printing (NP) GL or worksheet rows for use in calculations | |
• | Add a complex, nested calculation to a Row Format |
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.
Main Topics
|
• | 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 |
After completing this chapter, students should be able to:
|
• | Understand how full account codes can be used in the Row Format | |
• | Understand how specific characters within the account mask can be defined in the Row Format | |
• | Create a Reporting Tree with hooks (ampersands) matching the accounts defined in the Row Format | |
• | Create a quarterly Column Layout | |
• | Define boxes in the Row Format to highlight specific report areas | |
• | Display text from the Reporting Tree in the body of the report through the Row Format | |
• | Suppress page breaks between Reporting Tree units when printing reports |
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.
Main Topics
|
• | 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 |
After completing this chapter, students should be able to:
|
• | Build a Row Format based on an account segment other than the natural account | |
• | Create a Reporting Tree to work with the Row Format | |
• | Use Account Filters in the Column Layout to define specific accounts in a side by side presentation | |
• | Work with non-printing columns when defining a Column Layout |
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.
Main Topics
|
• | 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 |
After completing this chapter, students should be able to:
|
• | Understand the use of C in the Row Format when designing a cash flow statement | |
• | Use account modifiers to define beginning and ending cash account balances | |
• | Define Row Format calculations that place data in specific columns of the Column Layout | |
• | Create a Column Layout defining current or current and year to date changes in cash | |
• | Understand the calculation priority needed when designing a 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.
Main Topics
|
• | 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 |
After completing this chapter, students should be able to:
|
• | Understand the use of C in the Row Format when designing a cash flow statement | |
• | Use a combined GL+Worksheet link for details defined in an external spreadsheet | |
• | Use account modifiers to define beginning and ending cash account balances | |
• | Define Row Format calculations that place data in specific columns of the Column Layout | |
• | Add a check row to verify worksheet data balances to GL data | |
• | Understand the calculation priority needed when designing a current and year to date 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.
Main Topics
|
• | 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 |
After completing this chapter, students should be able to:
|
• | Understand how full accounts in the Row Format work in conjunction with a Reporting Tree | |
• | Work with Book Code modifiers in the Row Format | |
• | Understand the use of column G restrictions in the Row Format | |
• | Define multiple GL Links in the Row Format and Reporting Tree | |
• | Create a two-pass report, pulling data from an external worksheet created in the first pass | |
• | Format vertical lines in a report |
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.
Main Topics
|
• | 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 |
After completing this chapter, students should be able to:
|
• | Add a non-printing WKS type column for year to date average calculations | |
• | Reference an external worksheet for year to date average calculations | |
• | Modify a report's design to accommodate year to date average calculations |
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.
Main Topics
|
• | 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 |
After completing this chapter, students should be able to:
|
• | Understand why to use text labels for row codes in the Row Format | |
• | Define a Link to Row Format and the related row codes when designing a report based on Row Linking | |
• | Create a Row Format using both GL and Row Format links | |
• | Create a Reporting Tree that references multiple Row Formats | |
• | Define a catalog using a Row Linking design |
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.
Main Topics
|
• | 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 |
After completing this chapter, students should be able to:
|
• | Design a Row Format that links to another Row Format | |
• | Add text labels as needed for the design | |
• | Create a Column Layout that dynamically pulls future budget data | |
• | Create a Reporting Tree based on Row Linking | |
• | Understand rounding options when working with a Row Linking report design |
Chapter 13: Review This chapter includes a fun way to review the report design concepts covered so far in class.
Main Topics
Lab
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.
Main Topics
|
• | 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 |
After completing this chapter, students should be able to:
|
• | Define one or more CBR (base) rows in a Row Format | |
• | Understand the proper placement of CBR rows | |
• | Enter a calculated column in the Column Layout that references the CBR rows | |
• | Suppress CBR calculations for account details when drilling down |
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.
Main Topics
|
• | 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 |
After completing this chapter, students should be able to:
|
• | Add one or more CBR format codes and related base row information to the Row Format | |
• | Define a Column Layout with calculations that refer to the CBR rows | |
• | Understand the difference between defining a Row Format based on full account codes vs. relating rows to a Reporting Tree | |
• | Understand the difference between defining a Column Layout with account filters vs. relating columns to a Reporting Tree |
Chapter 16: Additional Formatting Concepts
This chapter introduces a variety of advanced report design topics.
Main 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 |
After completing this chapter, students should be able to:
|
• | Add a conditional statement (IF/THEN) to a Row Format | |
• | Use the effective dates setting when defining a catalog | |
• | Define distinct text for each Reporting Tree unit, using the column Z of the tree | |
• | Add the additional text defined in the Reporting Tree to catalog headers or footers, or to the Row Format | |
• | Understand how building block data can be manipulated in Excel then pasted back to FRx | |
• | Generate FRx results to Excel, then use the mail merge function in Word to present financial results in a document | |
• | Define international formats and assign them to a company in FRx |
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.
Main Topics
|
• | 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 |
After completing this chapter, students should be able to:
|
• | Create a multi-company consolidation report | |
• | Add account filters to eliminate specific accounts or ranges of account | |
• | Link to an external worksheet containing elimination entries | |
• | Create a side by side, consolidating Column Layout | |
• | Define an International Format and assign it to a company | |
• | Assign an International Format to Reporting Tree units |
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.
Main Topics
|
• | 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 |
After completing this chapter, students should be able to:
|
• | Generate an FRx instant! OLAP - Microsoft Local or Server Cube | |
• | Generate an Excel Pivot Table or Excel Pivot Chart | |
• | Understand how terminology in a Local Cube (Pivot Chart or Pivot Table) relates to FRx terminology | |
• | Work with Pivot Charts or Pivot Tables 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.
Main Topics
|
• | 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 |
After completing this chapter, students should be able to:
|
• | Add an XBRL Link and related taxonomy table to the Row Format | |
• | Assign XBRL tags to all financial rows in the Row Format | |
• | Generate a catalog to the XBRL Instance Document output option | |
• | Review the related output in Internet Explorer |
Chapter 20: Currency Translation This chapter allows students to practice two methods of currency translation in reporting; using rates stored in an external worksheet and using DAX, the optional currency translation module available from FRx.
Main Topics
|
• | Using an external worksheet link to pull in translation rates | |
• | Adding CTA (cumulative translation adjustment) to the Row Format | |
• | Using column restrictions in the Column Layout to define formatting | |
• | Defining the DAX rates path | |
• | Activating DAX in the catalog and Column Layout | |
• | Assigning currency conversion format codes and related rate IDs |
Lab
|
• | Translated Balance Sheet | |
• | Translated Balance Sheet using DAX | |
• | Translated Income Statement using DAX |
After completing this chapter, students should be able to:
|
• | Create a currency conversion report pulling translation rates from an external worksheet | |
• | Define the rates database path in the company defaults | |
• | Add currency conversion format codes and related rates to the Row Format | |
• | Activate DAX in the Column Layout and select the related currency display codes | |
• | Define currency conversion options in the reporting catalog |
Appendix A: Solutions to Test Your Skills
This appendix contains solutions to quizzes and tutorials completed during class.
Appendix B: Sample Statements of Cash Flow
This appendix covers three additional cash flow reports covering a variety of advanced design concepts.
Based on student needs, this chapter is optional for classroom presentation.
Appendix C: More on OLAP This chapter is intended for students who are interested in a more detailed understanding of OLAP and available browsers.
| 
