Microsoft FRx Report Design Essentials II

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

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

Basic knowledge of accounting principles

Microsoft FRx Report Design Essentials I

Exam Microsoft FRx certification exam:

About FRx Software Corporation products

Terminology

Demo company account structure

Sample report demonstration

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

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

Row Repair

Column Repair

Build a Tree

Build a Catalog

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

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

Combined Worksheet Method

Separate Worksheet Method

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

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

Gross Profit per Unit

Modified Favorable/(Unfavorable) Budget Variances

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

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

Using Full Account Codes

Using Multiple Segments

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

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

Defining Departments in the Row Format

Unit Restrictions in the Row Format

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

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

Monthly Statement of Cash Flow

Current and Year to Date Statement of Cash Flow

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

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

FASB Style Statement of Cash Flow

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

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

Allocation Journal Entry

Allocation Reporting

Two-pass Allocation Reports

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

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

Modified Allocation of Corporate Expenses

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

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

Row Linking

Row and General Ledger Linking

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

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

Row Linking Income Statement

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

Concept Review

Crossword Puzzle

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

Multiple Base Row Definitions

Using Statistical Information as a Base Row

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

Using save as to create multiple Column Layouts with simple updates to each

Defining effective dates in the catalog

Variable Income Statement

Use the effective dates setting when defining a Catalog

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

Using Conditional Statement

If-Then-Else and Reporting Tree units

Variance Reporting

Use conditional logic in Row Format and Column Layout calculations

Using column Z (Additional Text) in the Reporting Tree

Working with Excel or Microsoft( Office Word( and Microsoft FRx

Defining International Formats

Additional Descriptive Text

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

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

GL Eliminations

External Eliminations

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

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

Working with OLAP Cubes in Excel

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

XBRL Overview

Adding an XBRL link and related taxonomy file

Assigning XBRL tags

The XBRL Instance Document output option

Create an XBRL Document in Microsoft FRx

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

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

Translated Balance Sheet

Translated Balance Sheet using DAX

Translated Income Statement using DAX

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