Microsoft Cash Flow Statement

Document Sample
Microsoft Cash Flow Statement Powered By Docstoc
					Microsoft FRx
Intermediate and Advanced Report Design

Creating a Cash Flow Report , Using an If Then Statement
based upon Units of the Reporting Tree, Doing Currency
Translation via an Excel Spreadsheet

John R. Kordek -
                       Creating a Cash Flow Statement
Below are two ideas that might save you some time when you create your first Cash Flow
Report in Microsoft FRx.

   1. If you are currently doing Cash Flow in Excel – Copy your description Column from Excel to FRx.
      This can save on unnecessary typing. It is always possible to copy from FRx to Excel or Excel to
      FRx. There is no Spell Check in FRx, so use the one in Excel.
   2. Open up your Balance Sheet Row Format and copy the account groupings from there to your
      Cash Flow Row Format. The Balance Sheet Row Format already has your accounts that you need
      Itemized there. Why re-create the wheel?

                                    Finished Cash Flow Report

                                              Page 2
                                                                                           The Balance
                                                                                           Sheet Row
                                                                                           Format has
                                                                                           the accounts
                                                                                           already copy
                                                                                           Column H
                                                                                           from here to
                                                                                           your New
                                                                                           Row Format.

                                       Balance Sheet Row Format

                               First Trick to Cash Flow is using
                               the “C” in Column E to show the
                               Sources and (Uses) of cash
                               correctly. When an asset goes up it                          Second
                               is a use of cash you spent money                             Trick to Cash
                               on inventory. When a Liability like                          Flow is the
                               A/P goes up it is a source of cash                           use of the
                               you are not paying your bills.                               Account
Row 1010 brings in the Cash                                                                 Modifier of
Balance from the General                                                                    /BB. This
Ledger. When Row 1010 and                                                                   brings in the
Row 920 have the same                                                                       beginning
number then you know your                                                                   balance for
Cash flow report is correct.                                                                cash. The /Y
                                                                                            brings in the
                                                                                            YTD balance.

                                         Cash Flow Row Format        We told FRx in the Catalog to Print
                                                                     Zero Balance Rows. On row 1030 the
                                                                     “XO” tells FRx to not print this row if it
                                                                     is Zero. It will only show up when you
                                                  Page 3             are out of balance.

To get the /BB and /Y
modifiers get the drop down
box on the Cash Row. (1)

Then get the Row Modifier
drop down window in the                                              3
lower left hand corner. (2)

That takes you to the Account
Modifier drop down window
where you can choose to use
the /BB or /Y. (3)

                                   Account Modifier Drop Down Window Found in Column H

                                                                                             Cash Flow Reports are all about
                                                                                             the Net Change. A CUR Column
                                                                                             captures the net change for a
                                                                                             Month and a 1 to BASE CUR
                                                                                             Column captures the net
                                                                                             change for the YTD Amounts.
                                                                                             The /BB and /Y Account
                                                                                             Modifiers work just fine with a 1
                                                                                             TO BASE CUR Column.
                                                  Cash Flow Column Layout
 FRx by default does
 not print zero
 balance rows and it
 also prints blanks
 instead of zeros.
 Both of these
 options were
 changed on this

                              Report Option to Print Zero Rows and to Print Zeros instead of Blank

                                                            Page 4
Using an If Then Statement based upon Units of the Reporting Tree.
You must be on FRx 6.7 Service Pack 4 at a minimum to use If Then based
upon Tree units.

                    Denver & San Francisco Reports Combined

                                   Page 5
San Francisco gets 40% of the Corporate Expenses

                    Page 6
Denver gets 60% of the Corporate Expenses

The Components of the Report follow

                Page 7
                      The Row Format

                       Row 1120 has XR (Suppress Roll
                       Up) in Column F. The Full
                       Account Number ignores the
BXB & BXC with a
                       account mask in the tree and
“type=3” creates
                       shows up in every unit of the            Row 1120 is using the Fully
a dotted Box
                       Tree. The parent then has that           Qualified Account Number
across the report.
                       account number multiplied by             to bring in all of the
                       the number of children in the            Corporate Expenses. This
                       tree.                                    can lead to problems when
                                                                using Reporting Trees.

                                                        The If Then Statement uses the H Code from the
                                                        Reporting Tree to tell FRx to do a different
                                                        calculation depending upon what unit of the Tree
                                                        it is in. The H Code is enclosed in ( ).

                     The Column Layout

                            Page 8
     The “H Code”
     is found in                                        The Row Format is saved in
     hidden                                             Column I to let FRx know that
     Column A.               The Reporting Tree         there is an additional GL
                                                        Column in the Row Format.       Column Q tells
                                                                                        FRx where to get
                                                                                        the GL Data
                                                                                        from. GL2 is
                                                                                        Column I in the
                                                                                        Row Format.
                                                                                        GL1 is Column H
                             If Then Reporting Tree
                                                                                        in the Row

Column A exposed.
Column A is created by
the use of Column C from   Tree with Column A Exposed
the first row combined
with a carrot and the
description from the row
you are on.

                                 If Then Catalog

                                    Page 9
Currency Translation from U.S. Dollars to British Pounds using an Excel

                                                                  Column C has no
                                                                  Underscores, Sub
                                                                  Totals and Rounding is

                                     The Excel Spreadsheet with Translation Rates

                                                  The Row Format

                                                                   REM (Remarks) rows do not print.
                                                                   They help make coding out the report
                                                                   a little easier to reference the right
                                                                   translation rate in the Row Format.

The /CPO makes FRx move
dynamically across the columns of
the Excel Spreadsheet based upon
the report date. In January (month
one) it goes from A8 to B8…

                                                                     The B,D keeps FRx from trying to
                                                                     balance the Translation Rates in
                                                                     Column C of the Column Layout.

To get the Rounding Adjustment Box in the Row Format go to
Edit – Rounding Adjustments.

                                                                 The “Plug Row” is the Currency
                                                                 Translation Gain Loss Row. It
                                                                 must be a GL Account row so
                                                                 we just put a fake account #

      The Rounding Adjustment Row is found under Edit then Rounding Adjustments

                               SR=Suppress Rounding
                               SU=Suppress Underscores
                               ST=Suppress Totals.
                               Rounding is done via a
                               Special Format Mask.

                            The Translation Column Layout

                  Column W tells FRx where to get the Excel
                  Spreadsheet Addresses. Column W tells it
                  what Spreadsheet to get them from.

          Balance Sheet Translation Reporting Tree

                                       This Box must be checked to pull in Excel
                                       Spreadsheet numbers.

                   BS_Trans Catalog Screen

This Report must be Calculate
Columns First.

              The Advanced Tab of the Catalog

         The Formatting Tab is set to No Rounding


Shared By: