DURATION
2 days
Introduction
The Report Design Essentials - II course builds on the basic report design skills learned in Report Design Essentials - I. Using Microsoft 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 intended for:
Intermediate Microsoft FRx users
Experienced Microsoft FRx users who would like to build upon their skills and master product proficiency
FRx Software Corporation product resellers and consultants
At Course Completion
After completing this course, students will be able to:
Have a renewed understanding of basic Microsoft FRx report design concepts
Explore ways to efficiently create and distribute many financial report designs including:
Trial balances
Reports linking to external worksheet data
Statements of Cash Flow
Reports using full accounts or multiple GL segments
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:
Basic knowledge of accounting principles
In addition, it is recommended, but not required, that students have completed:
Microsoft FRx Report Design Essentials I
Microsoft Certified Professional Exams
This course will help the student prepare for the following Microsoft Certified Professional exam:
Exam Microsoft FRx certification exam:
Course Materials
The student kit includes a comprehensive workbook and other necessary materials for this class.
Chapter 1: Overview and Introduction
This chapter gives students a general overview of the features and benefits of using Microsoft FRx for financial statement design. It also introduces the demo company that will be used in the course exercises.
Topics
About FRx Software Corporation products
Terminology
Demo company account structure
Sample report demonstration
After completing this module, students will 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 FRx 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.
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 FRx DrillDown Viewer
Labs
Row Repair
Column Repair
Build a Tree
Build a Catalog
After completing this module, students will 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 Microsoft 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.
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 module, students will 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: Practice
This chapter contains practice tutorials to reinforce the students knowledge of linking methods covered in the previous exercises. Students modify an existing reports based on changes to the worksheet and additional data required on the reports.
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
Modifying a Column Layout to link to worksheet data
Labs
Gross Profit per Unit
Modified Favorable/(Unfavorable) Budget Variances
After completing this module, students will 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
Modify an existing Column Layout for WKS type links
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.
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
Labs
Using Full Account Codes
Using Multiple Segments
After completing this module, students will 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: Rotate the Building Blocks
This chapter introduces an alternate report design concept. 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.
Topics
Defining the Department segment in the Row Format
Defining a Reporting Tree that recognizes the Department in the Row Format
Using Account Filters in the Column Layout
Labs
Defining Departments in the Row Format
Unit Restrictions in the Row Format
After completing this module, students will 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 Microsoft FRx. In these exercises you work with partially created building blocks, and add the key cash flow reporting details.
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
Labs
Monthly Statement of Cash Flow
Current and Year to Date Statement of Cash Flow
After completing this module, students will 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: Practice
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.
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
Labs
FASB Style Statement of Cash Flow
After completing this module, students will 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.
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
Labs
Allocation Journal Entry
Allocation Reporting
Two-pass Allocation Reports
After completing this module, students will 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: Practice
This chapter contains a practice tutorial to reinforce the student's knowledge of advanced calculation concepts covered in the previous exercises.
Topics
Modifying existing building blocks
Use non-printing columns for period specific information
Using print controls to determine active columns
Using non-printing rows and specific column placement calculations in the Row Format
Labs
Modified Allocation of Corporate Expenses
After completing this module, students will be able to:
Add non-printing CALC columns, hard coded to each period
Use column print controls to determine which columns will add into a related average calculation
Understand how column calculations recognize non-printing columns
Define specific columns for display of information defined in the row
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.
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
Labs
Row Linking
Row and General Ledger Linking
After completing this module, students will 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: Practice
This chapter contains a practice tutorial to reinforce the student's knowledge of row linking design concepts covered in the previous exercises. Using row linking, summary and detailed Income Statements are built in a single catalog.
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
Labs
Row Linking Income Statement
After completing this module, students will 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 Tree 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.
Topics
Concept Review
Labs
Crossword Puzzle
Chapter 14: Relational Reporting
This chapter reviews 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. The exercises in this chapter focus on relationships to statistical data.
Topics
Defining the CBR or Base Row(s)
Defining Column Layout calculations that work with CBR in the Row Format
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
Labs
Multiple Base Row Definitions
Using Statistical Information as a Base Row
After completing this module, students will 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 15: Effective Dates
This chapter introduces the concept of using effective dates in the Catalog to dynamically control building blocks in relation to the report date.
Topics
Using save as to create multiple Column Layouts with simple updates to each
Defining effective dates in the catalog
Labs
Variable Income Statement
After completing this module, students will be able to:
Use the effective dates setting when defining a Catalog
Chapter 16: What's Wrong?
This chapter introduces advanced calculations using IF/THEN and IF/THEN/ELSE calculations in report design.
Topics
IF/THEN/ELSE calculations in the Row Format
IF/THEN/ELSE calculations related to Reporting Tree Units
IF ABS (absolute value) calculations in the Column Layout
Labs
Using Conditional Statement
If-Then-Else and Reporting Tree units
Variance Reporting
After completing this module, students will be able to:
Use conditional logic in Row Format and Column Layout calculations
Chapter 17: Additional Formatting Concepts
This chapter introduces a variety of advanced report design topics.
Topics
Using column Z (Additional Text) in the Reporting Tree
Working with Excel or Microsoft( Office Word( and Microsoft FRx
Defining International Formats
Labs
Additional Descriptive Text
After completing this module, students will be able to:
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 Microsoft FRx
Generate Microsoft FRx results to Excel, then use the mail merge function in Microsoft Word to present financial results in a document
Define international formats and assign them to a company in Microsoft FRx
Chapter 18: 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.
Topics
Overview of multiple company consolidations in Microsoft 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
Labs
GL Eliminations
External Eliminations
After completing this module, students will 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 19: OLAP Output
This chapter introduces students to OLAP (Online Analytical Processing) output options within Microsoft FRx. A brief hands-on with an OLAP cube is also included.
Topics
Microsoft FRx and OLAP
Microsoft Local Cubes
Microsoft Server Cubes
Excel Pivot Chart and Excel Pivot Table output options
Terminology in OLAP and Microsoft FRx
Labs
Working with OLAP Cubes in Excel
After completing this module, students will 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 Microsoft FRx terminology
Work with Pivot Charts or Pivot Tables in Excel
Chapter 20: XBRL
This chapter introduces XBRL (eXtensible Business Reporting Language) in Microsoft FRx. XBRL is a standardized financial reporting format that is clear and easy for anyone to interpret and analyze.
Topics
XBRL Overview
Adding an XBRL link and related taxonomy file
Assigning XBRL tags
The XBRL Instance Document output option
Labs
Create an XBRL Document in Microsoft FRx
After completing this module, students will 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 21: 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 Microsoft FRx.
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
Labs
Translated Balance Sheet
Translated Balance Sheet using DAX
Translated Income Statement using DAX
After completing this module, students will 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
This appendix contains solutions to quizzes and tutorials completed during class.
Appendix B: Sample Reports
This appendix covers three additional cash flow reports covering a variety of advanced design concepts, as well as a Full Year Forecast using effective dates in the Catalog.
Based on student needs, this appendix is optional for classroom presentation.
Appendix C: More on OLAP
This appendix is intended for students who are interested in a more detailed understanding of OLAP and available browsers.
This is a version-specific reference outline. Confirm the current syllabus and delivery arrangements with EIEIO.