Categories

Get Number Of Periods Between Dates In Adaptive Planning

If within a Modelled Sheet you have a date field and need to calculate how many periods the row has been active for, then you can create a formula to capture this.

Example

If we assume we have an input account on our Modelled Sheet labelled `StartDate` then our formula for determining how many periods have happened since `StartDate` would be as follows:

``( ( year( this ) - year( ROW.StartDate ) ) * 12 ) + ( month( this ) - month( ROW.StartDate ) )``

This assumes the time stratum of the sheet is monthly.

What if I don’t want negative periods in the result?

This formula will also insert negative values for months before the `StartDate` value. If you’d like to return a zero value for periods prior to the `StartDate` then you might want to add to your formula an `if` condition:

``if ( this >= ROW.StartDate, ( ( year( this ) - year( ROW.StartDate ) ) * 12 ) + ( month( this ) - month( ROW.StartDate ) ), 0)``

What if I have an end date field?

You could extend this example eve further by checking if the current period is before an end date input value too. We’ll assume the code name is `EndDate`:

``if ( this >= ROW.StartDate, if (this <= ROW.EndDate, ( ( year( this ) - year( ROW.StartDate ) ) * 12 ) + ( month( this ) - month( ROW.StartDate ) ), 0), 0)``

What if the end date is an optional field?

Here we would need to extend our formula by checking if the `EndDate` field has a value:

``````if ( this >= ROW.StartDate,
if (ISBLANK(ROW.EndDate) OR this <= ROW.EndDate,
( ( year( this ) - year( ROW.StartDate ) ) * 12 ) + ( month( this ) - month( ROW.StartDate ) )
, 0 )
, 0 )``````

As the logic for this type of formula can become difficult to manage as additional complexities come into the model what I tend to do is create a “flag” account that determines whether something is active.

Therefore, I move the logic to determine if something is active to a new Modeled Account and label this `IsActive`, it then would have the following logic:

``````if (this >= ROW.StartDate,
if (ISBLANK(Row.EndDate), 1,
if (this <= ROW.EndDate, 1, 0)
)
, 0 )``````

Then in my period formula I would change this to now be:

``````if (ROW.IsActive = 1,
( ( year( this ) - year( ROW.StartDate ) ) * 12 ) + ( month( this ) - month( ROW.StartDate ) )
, 0)``````

It’s a lot easier to read and much easier for other developers to understand (including yourself when you come back to your code months and years later!).

Conclusion

Calculating the number of periods between dates in a Modeled Sheet can help with formulas needing this calculation such as loans, leases and employee/payroll formulas.

Look at separating out your logic when you develop your formulas and try to keep them small.

Categories

Create Weekly Cash Flow Model With Cube Sheets

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:

 if ( isBlank( ASSUM.CF.WeekNum ), 0, if ( ACCT.CF.OBal = 0, if ( ASSUM.CF.WeekNum = 1, ACCT.CF.CBalMonthly[time=this-1,Weeks=Week 4] + ACCT.CF.CBalMonthly[time=this-1,Weeks=Week 5] , 0 ) , ACCT.CF.OBal ) )

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:

 if ( isBlank( ASSUM.CF.WeekNum ), 0, if ( isBlank( ACCT.CF.OBal ), if ( ASSUM.CF.WeekNum = 1, if ( ASSUM.WksMonth[time=this-1] = 5, ACCT.CF.CBalWeekly[time=this-1,Weeks=Week 5], ACCT.CF.CBalWeekly[time=this-1,Weeks=Week 4]) , if( ASSUM.CF.WeekNum = 2, ACCT.CF.CBalWeekly[Weeks=Week 1], if ( ASSUM.CF.WeekNum = 3, ACCT.CF.CBalWeekly[Weeks=Week 2], if ( ASSUM.CF.WeekNum = 4, ACCT.CF.CBalWeekly[Weeks=Week 3], if ( ASSUM.CF.WeekNum = 5 AND ASSUM.WksMonth = 5, ACCT.CF.CBalWeekly[Weeks=Week 4], 0 ) ) ) ) ) , ACCT.CF.OBal ) )

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:

 if ( isBlank( ASSUM.CF.WeekNum ), 0, if ( ASSUM.CF.WeekNum <= ASSUM.WksMonth, if ( ASSUM.CF.WeekNum >= 2, ACCT.CF.NetCashWeekly[Weeks=Week 1], 0 ) + if ( ASSUM.CF.WeekNum >= 3, ACCT.CF.NetCashWeekly[Weeks=Week 2] , 0 ) + if ( ASSUM.CF.WeekNum >= 4, ACCT.CF.NetCashWeekly[Weeks=Week 3] , 0 ) + if ( ASSUM.CF.WeekNum = 5 and ASSUM.WksMonth = 5, ACCT.CF.NetCashWeekly[Weeks=Week 4] , 0 ) + if ( isblank( ACCT.CF.OBal ), if ( ASSUM.WksMonth[time=this-1] = 5, ACCT.CF.CBalWeekly[time=this-1, Weeks=Week 5], ACCT.CF.CBalWeekly[time=this-1, Weeks=Week 4] ), ACCT.CF.OBal ) + ACCT.CF.NetCashWeekly , 0 ) )

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:

 if ( isBlank( ASSUM.CF.WeekNum ), 0, if ( ACCT.CF.WeekNum = ASSUM.WksMonth, ACCT.CF.OBalMonthly[Weeks=Week 1] + ACCT.CF.NetCashMonthly , 0 ) )

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.

Categories

Why Is The Sum Of Previous Periods Not Working?

If you’ve ever wanted to calculate the sum of the previous periods in your calculations, but found when you applied the range formula it just didn’t work out right, here’s a reason to why it may not have worked.

Check Time Parameter in Formula

Firstly, when calculating a sum of the previous ranges in a time-based sheet (such as calculated accounts in a Modelled Sheet, or any row in a Standard Sheet, or an Assumption row, or any row in a Cube Sheet – where the horizontal axis is Time) your formula should contain the `time` parameter followed by a range of time elements all separated by a colon.

For example:

``ASSUM.WksMonth[time=this-11:this-1]``

The above formula simply calculates the number of accumulated weeks in a month from the previous month to eleven months ago.

Your range formula therefore needs to be of the pattern:

• `time` – the parameter denoting the capture of a time range.
• `this` – in reference to what time-period to capture (by default if your sheet is set to `month` then it is fetching the time period in months). You can fetch quarters or years too using `this.qtr` or `this.year`.
• `-X` – where X is the offset number of periods to start FROM (must be the earliest date value).
• `:` – (optional) using this means you’ll be using a range of time periods to capture.
• `this-X` – as above, references the current sheets period, otherwise you can use `this.qtr` or `this.year` to fetch other time periods.
• `-X` – where X is the offset number of time periods to calculate TO (must the latest date value).

Check Account Settings

So assuming we are using the correct pattern for writing our sum range formula the only element remaining is how we’ve created our account. This means if our pattern doesn’t work as expected it’s likely our settings on the account you’re referencing is not correct.

As an example, for a Cube Sheet where we’ve created a Calculation account we need to ensure that the Time rollup field is set to Sum of rolled-up values and not “Average of rolled-up values” or “Weighted average of rolled-up values”. Having it set to the other two will calculate the average being applied to your range, but will not give you the correct value if you’re looking to obtain the sum.

If you need to keep the setting of the account to an average, but would like to be able to still calculate the sum of a time-period range I would look at using a rudimentary form such as:

``ASSUM.WksMonth[time=this-1] + ASSUM.WksMonth[time=this-2] + ... ASSUM.WksMonth[time=this-11]``

It would really depend on what the focus of your account is doing and for establishing the correct settings when developing these accounts.

Check Sheet Settings

One other check you could perform is to make sure you have the correct understanding of the time period references in your sheet.

If you conduct budgeting fairly infrequently you may forget the time structure of a sheet and when you create your formulas you might think the sheet’s settings are of a particular time period, when in fact they are not.

This happened with one particular modelled sheet I was operating on where it was set to `daily` for the time periods. This would mean a formula like `ROW.SomeAccount[time=this-11:this-1]` would only be referencing the previous 11 days, not months for which I was intending.

To check the time stratum of your modelled sheet click on the Columns and Levels link, then on the Settings icon, then Settings, you should see a page like so:

Conclusion

If you’re struggling to fetch the right values in your formula, and the returned values just aren’t working out right, check the following:

1. The formula range is referencing the correct range you’re seeking;
2. The account settings are correct, including any references you may have to other ROWS; and
3. The sheet’s settings are as you’ve anticipated in designing your formula.