Many of you are familiar with the challenges of creating a cash flow statement on paper, now combine that with reporting tool like Management Reporter (MR) that pulls data from your Dynamics ERP system. In looking at a cash flow statement it would seem fairly straight forward, similar to building a balance sheet. However, there are a three items that require some thought and more advanced knowledge with MR report writing.
Beginning Balances
Beginning from prior periods or years are needed in the calculations of the cash flow statement. The column definition will define the period and year for that column but in the row definition you have the ability to modify that row with an account modifier and pull in different periods. Using the row modifier you are able to override the period in all the columns for that row. You have several options for what period you want to report on for that row.
Double Click on Image to See Better
Calculations Using the Beginning Balances
Now that you have the beginning balances added to your row definitions you may have the need to pull in amounts from different columns in order for your calculations in the column definition to work properly. To accomplish that you can add a related formula in the row definition for a specific row. In the below example that row 100 will return the value in column B equal to column B row 1270 and the value in column E equal to column C row 1270.
Double Click on Image to See Better
FASB Style Cash Flow Statements
Now the more challenging aspect of building a cash flow statement is involved. Most company’s use a FASB format of a cash flow statement which requires items such as fixed assets to be reported in more detail such as additions to fixed assets and disposals of fixed assets.
During our MR training classes we discuss the different ways that everyone is using to produce their cash flow statements and what can be done to eliminate the need to export to excel and adjust numbers to complete the cash flow statement. Below are a few of the ideas that have come from our classroom discussions, each having some pros and cons.
External Worksheet
The training manuals for MR uses the example of pulling in data from an external excel worksheet. The setup for this is more involved so I will refer you to the Microsoft MR manuals, as they do a good job of describing the setup for this.
This is probably the easiest way to input the data for the details needed in the cash flow statement.
The downside of this is that the excel worksheet can be unsecured and is prone to being incorrectly changed or deleted by others. The other potential issue is that the excel spreadsheet is only for that specific year and if you are running prior year data you would need to modify the external link in MR to pull from a different spreadsheet.
Print Controls
Another option is to use the print control in the row definition and select DR for all debits and CR for all credits. In the example below row 880 will return all the debits or additions and row 895 will return all the credits or dispositions.
The benefit of this method is that the report does not require any maintenance of outside worksheets or other additional setup.
The potential problem with this method is what happens if an adjusting entry is made to those accounts, then the amounts may not reflect the true additions for disposals.
Double Click on Image to See Better
Statistical Ledger or Unit Accounts
So far this method has proven to be the best solution based on our discussions in the classroom. The idea is to setup a statistical ledger (Dynamics SL) or Unit Accounts (Dynamics GP) and enter the one side journal entry as part of your month end process to an account for additions to fixed assets and another account for disposals of fixed assets, repeating this idea for other account details needed. Refer to the following blogs for setting up Statistical ledgers in Dynamics SL ……………………………………….… and Dynamics GP https://www.boyerassoc.com/blog/how-to-use-dynamics-gp-unit-accounts-to-enhance-your-management-reports . This is similar to the above beginning balances but you would select the Book Code Modifier not the Account Modifier for Dynamics SL then you would enter the account # in the Link to Financial Dimensions used for the details you are looking for. For Dynamics GP you would just enter the Unit Account number for the Link to Financial Dimensions.
Double Click on Image to See Better
The benefits of this are that the numbers are secured from being changed or deleted just like a normal journal entry, the numbers are stamped with the year and period posted and will pull into you MR report according to the date parameters setup in your reports, and you can accommodate for any adjusting entries made to the accounts.
The downside is that if the account balance changes after your initial one sided entry your report may not balance. You can then build check figures in the report to alert you that these numbers do not tie which you can then post an additional one sided entry to correct the balances.