An important aspect within any business to effectively plan is the ability to monitor cash flow.

Everybody in business knows “cash is king” and that “cash flow is the life blood of any business”.

Without having cash you can’t do anything.

Being able to monitor a weekly cash flow statement in Adaptive Planning is a little cumbersome, due to its standard monthly time-frame structure, however, creating a cash flow statement is not an impossible task.

Here’s how I was able to structure a weekly cash flow statement:

Step 1 – Design Cube Sheet

1. Design a Cube Sheet with Dimensions of Time (standard monthly time frames), and a Dimension of Weeks (this dimensional set would contain values of “Week 1”, “Week 2”, “Week 3”, “Week 4”, “Week 5” – denoting the number of weeks found in any one month, 5 being the maximum).
2. Set the horizontal axis of the Cube Sheet to Weeks (not Time ).
3. Create an assumption account within the Cube Sheet to contain the numeric values of the Dimension Weeks . An assumption can only have its data set on the “Top Level (Only)” level within the Cube Sheet, but its data is available to all within the Cube Sheet.
4. You will need to set the values for each month for each Dimension Weeks in the “Top Level (Only)” sheet (e.g. “Week 1” = 1, “Week 2” = 2, “Week 3” = 3, “Week 4” = 4, “Week 5” = 5). It takes about 2-3 minutes to do for a complete year.
5. Create an Initial Opening Balance standard account . This will be where you’ll enter the opening balance for each company at the beginning of your plan. If you have a company that has many levels (i.e. departments) underneath this initial opening balance would ONLY ever be entered on the Company ONLY level – it needs to be entered only once.
6. Create Opening Balance (Weekly) and Opening Balance (Monthly) calculation accounts. When creating these calculation accounts before formulas have been inserted into them I usually just insert a 0 in their Formula box.
7. Create Net Cash (Weekly) and Net Cash (Monthly) calculation accounts. When creating these calculation accounts before formulas have been inserted into them I usually just insert a 0 in their Formula box.
8. Create Closing Balance (Weekly) and Closing Balance (Monthly) calculation accounts. When creating these calculation accounts before formulas have been inserted into them I usually just insert a 0 in their Formula box.
9. Create the remainder of your Cube Sheet accounts according to how you would want to lay out your cash flow statement. You’ll likely find that the remainder of your cash flow accounts will be calculation accounts – as they will be pulling data from elsewhere (such as GL lines or other sheets). More on what you need to know below!
10. Design the layout of your Cube Sheet so that it fits nicely with how you’d like it to “look and feel”.

Step 2 – Create your Calculation Accounts

When you’re beginning to formulate your calculation accounts you’ll need to be mindful of one important aspect within Cube Sheets: Cube Sheets need to reference other cells to determine whether calculations can be performed.

You will notice that if you just insert a simple formula into a calculation account you’ll see an error below the formula box which says:

Error: Cube Calculation Formulas must evaluate to zero when the cube’s other accounts are zero.

To circumvent this error we can wrap our formulas in an if statement that refers to one of our accounts within our Cube Sheet. The way I handle this with our structure above is (and for which the remaining formulas in the section underneath):

if ( isBlank( ASSUM.CF.WeekNum ), 0, ... put your calculations in here ... )

What this means is that if there has been something input into the “Top Level (ONLY)” WeekNum account then the calculation will proceed – which is what I want.

If you want more control you’d likely need to create a Standard Account within your Cube Sheet that enables a user to enter a value into this row that is then checked by your if-statement, which may then look a little something like:

if ( ACCT.CF.MyStandardAccount = 0, 0, ... do calculations in here ... )

Step 3 – Cash Flow Formulas

Once you’ve finished formulating your calculations for those accounts listed in Step 8 above, you will now need to create your formulas for the Net Cash (Weekly) account. This will be unique to how and what accounts you’ve listed according to Step 8 above.

However, other formulas for calculating Opening Balances, Closing Balances and Net Cash (Monthly) would be something of the following format.

Net Cash (Monthly) Calculation Account

if( ASSUM.CF.WeekNum = 0, 0, if( ASSUM.WksMonth = 4, ACCT.CF.NetWeeklyCash[Weeks=Week 1] + ACCT.CF.NetWeeklyCash[Weeks=Week 2] + ACCT.CF.NetWeeklyCash[Weeks=Week 3] + ACCT.CF.NetWeeklyCash[Weeks=Week 4], if ( ASSUM.WksMonth = 5, ACCT.CF.NetWeeklyCash[Weeks=Week 1] + ACCT.CF.NetWeeklyCash[Weeks=Week 2] + ACCT.CF.NetWeeklyCash[Weeks=Week 3] + ACCT.CF.NetWeeklyCash[Weeks=Week 4] + ACCT.CF.NetWeeklyCash[Weeks=Week 5], 0 ) ) )

This simple formula obtains the Net Cash (Weekly) amounts and adds their amounts up. It also checks to make sure it adds up the correct cells according to the number of weeks in that month.

Opening Balance (Monthly) Calculation Account

A similar, yet more minimised formula which helps to calculate the Opening Balance for each month and uses the same principles as the weekly account is the following:

Opening Balance (Weekly) Calculation Account

This either obtains the Initial Opening Balance from the Standard input row or the previous periods’ Closing Balance (Weekly), fairly simple:

Closing Balance (Weekly) Calculation Account

The important aspect for this script is it needs to reference a balance from a previous time period (or from a Standard row – such as our Opening Balance account).

Referencing within the same sheet to other dimensions does appear to throw errors – such as circular reference errors (even though with this type of error it will still calculate correctly!) or may even throw illegal errors (which will still show a red number, but the calculation will not work).

Therefore, to try to keep it as error-free as possible I’ve used the previous periods’ closing balance as seed – or the initial opening balance at the start of the year, and the code is as follows:

Closing Balance (Monthly) Calculation Account

The last calculation is therefore the closing balance for the end of the month. Here we will do just as we did with the Net Cash (Monthly) calculation account as shown:

Conclusion

In this article we’ve seen how to construct a Cube Sheet with specific requirements of creating our Cash Flow Statement.

Hopefully some of the formulas can help with how you construct your cash flow statement within Adaptive Planning.