How to model Cash Flow and other (conceptually) similar use cases with SAP Analytics Cloud
One of my first lessons when talking about the profits with the accountant was the answer to the question “How much profit are we going to have in the year YYYY?” The accountant replied with a question “Well, how much do you want to have?”. Or as the father of Shareholder Value, Alfred Rappaport stated: “Profit is an opinion, cash is a fact!”. As we all can see, accounting can be creative discipline, of course within the boundaries of the accounting standards and other rules and regulations. On the other side, cash flow or our net liquidity position has not much to do with the accounting creativity. But it has everything to do with the health of our business, like do we actually get the money in after we sell our goods or services, do we sell for more than our production costs are (hence get more in, then getting out), do we have enough money in our bank accounts for salaries of our employees or even will we have enough money for the acquisition that we plan to do next month. In the end also some company valuations used in M&A processes are based on a free cash flow. So, I hope we all can agree, Cash is King.
…and that’s why I would like to show you this example of how to model Cash Flow planning/forecasting scenario in SAP Analytics Cloud with special focus on the so called carry forward routine (the routine which writes ending balance of previous period into the opening period of the current period). Please note that carry forward approach can be used in many different scenarios, like headcount planning, inventory level, etc so in all use cases where we are operating with opening & closing balances and related inflows & outflows.
In this blog post I will show you how you can execute carry forward with the help of model formula (Example 1: Model formula) and with the help of data actions (Example 2: Use data action – carry forward advanced formula).
EXAMPLE 1: Model formula
Since release 2022.19 it is possible to use so called ITERATE formula (more info in What’s new blog post) to create carry forward calculations in the model. ATTENTION: Customers are usually receiving updates within quarterly release cycles so this is planned (with all the usual disclaimers) to be part of the 2022.QRC4 release (upgrade is scheduled between 11. Nov. and 13. Nov.). You can find more info in this SAP Note.
I have created a simple measure based model with dimensions & measures as follows:
- Calc measure: CALC_CLOSING
- Calc measure: CALC_OPEN
Measure Initial/Correction will serve to enter the initial opening balance, just to keep it separate from inflows and outflows. Measures In and Out will serve for entering inflows and outflows for certain Account items (so in my case I keep it simple…just 2 accounts). Measure CALC_CLOSING is the one using ITERATE (and PRIOR) function for creating the ending balance of each period that we have in our table. I have used CALC_OPEN to calculate an opening balance of the period based on the closing balance and inflows & outflows of that period.
Here are formulas for calculated measures. Please also note there is a handy little helper available on the right side of the screen for every function that you are using.
PRIOR function is used inside the iteration expression (ITERATE formula) to retrieve the value that was calculated for the previous member.
CALC_OPEN is a fairly straight forward computation, I guess there’s no need for further explanation.
I have also setup exception aggregation rules for both measures, LAST across DATE dimension for CALC_CLOSING and FIRST across DATE dimension for CALC_OPEN. this needs to be done since both measures are “semi-additive”, which in our case means that it makes sense to sum them up across all dimension but DATE. When we view data across DATE dimension and we are viewing it on a higher node like YEAR, we would like to have the FIRST and the LAST value from both measures displayed.
Please note that the ITERATE formula is being calculated only on the members that are NOT filtered out in you table widget. As a consequence of that, if you wish to start with correct opening balance for period 1, I suggest that you add the initial opening balance into the period before. That’s why I’m using measure Initial/Correction for this. So in my case I wanted to start my carry forward in Jan.2022, so I have added Initial balance into Dec.2021 and then I have hidden Dec.2021 column (please note that I have used a Hide Column action from the context menu).
You can also use INVERSE formula type with ITERATE formula and that way enable the possibility to change any of the items that are part of the ITERATE formula.
This was the Model Formula example in the next chapter we are going to look into the data action approach.
EXAMPLE 2: Use data action – CarryForward advanced formula
Another option to implement carry forward routine is to do it via the Advanced Formula step of Data Actions. Here is a simple example just to show usage concept.
I’m using the same measure based model than in previous case, except some other measure will be in scope. This time we are only using base measures (so no calculated measures, since everything will be calculated and persisted during the data action execution)
I have created a data action with Advanced Formula Step where I’ve specified the opening and closing periods and the formula for calculation of the closing period as shown below.
In my case I have created a super simple data action just to focus on the mechanism of moving end-balances into the opening-balance of the next period and include all inflows and outflows. Of course you could also make a data action a lot more complicated and also more user friendly with some variables that user can select (such example could be the time period for which one would want to calculate it). Data Action panel has also available helper in the form of a pop-up as soon as you start typing the function name as seen below:
When we are done with a data action creation we should only bring it into the end-user interface in the shape of a button/trigger.
When we are done it’s “only” a matter of execution -entering new numbers, running the trigger and the calculation in the backend and analysing the effect.
In our case there is a prompt for selecting the version/slice of data where we want to run this script. Of course it can be also automated (=hard coded) if needed, so that user does not have to enter version info and/or other parameters (or it can be fully parametrised…).
Here are some additional thoughts that might help you find the most suitable approach for such use cases. Please note items listed below should not be treated as “pros et contras”, but more like some observations and consideration points that could guide you in your modelling effort.
- Concept & Use Case aspect
- direct vs. indirect cash flow method approach
- simulation use case and how to run it
- do we plan to use predictive capabilities of SAC in our Cash Flow use case
- by using Time Series forecasting for some simple scenarios
- by using Regression approach to predict DAYS LATE (we want to predict the number of days it will take an invoice to clear in the system.)
- Model formula vs. Data action approach
- model formula approach:
- does not persist data (calculations done on the fly)
- you need to define “initial opening balance” in a period before the actual start
- end user gets the resulting ending balance as soon as data is entered into the table, so there is no need to press any buttons
- data action approach:
- persist the data into the opening and closing balances
- no need for the model formula
- clean model
- user needs to interact by activating the trigger
- model formula approach:
- Other modelling aspects for cash flow forecasting
- opening & closing balances in account dimension
- opening & closing balances in measures
- opening & closing balances in a generic dimension (as sort of flow dimension)
- the usage of account dimension in the first place
- if there is accounting logic & semantics
- do we work with different account types, like
- Income (INC)
- Expense (EXP)
- Assets (AST)
Please note that we have also introduced a standard business content for Cash Flow/Liquidity planning. You can find more information in our SAP Analytics Cloud help section. The standard content package also includes Liquidity Prediction, where we use Smart Predict to forecast liquidity for the coming 12 months based on historical cash flows and identified influencers. (Thanks Antoine CHABERT to pointing that out!).
I hope you will find this content useful + that you will use it in your modelling assignments. …and like said at the beginning “Cash is King”, so model CF with full attention & focus!
Hi Blaz Zabukovec nice blog!
SAP Analytics Cloud offers a "ready to be deployed" business content that illustrates how liquidity planning can be dealt with, also using predictive planning.
This is the link in case readers are interested to explore this content. https://help.sap.com/docs/SAP_ANALYTICS_CLOUD/21868089d6ae4c5ab55f599c691726be/b834ee07a87042c1a163af1869472dd8.html
Hi Antoine CHABERT ,
Thanks for the feedback, in fact I will include into the blog post!
Great blog & use case Blaz! Thanks for sharing 🙂
Thanks for the feedback!
is there an option to use this with a rolling forecast layout? seems as if you can only change the base member for the first version that is included..
However, correcting the 2nd version seems to be not possible.
Hi Tobias Binkert ,
a rolling forecast layout is always using two versions (actuals=ACT for past data & forecast=FCT for future, for example) and then calculating joint end result on the fly. In this case I would suggest that you create a "joint" version that would be filled with the ACT data (by overwriting FCT part of it on a regular basis, so when new ACT data is available) and then you can use the approach of carry forward model formulas or even carry forward data action. To simplify the process you could even create a schedule in calendar for data action to "import" ACT or simply use the trigger button for loading it. I hope this helps.
I have my doubts about the methodology with the formula Iterate.
If you enter cash flows in the initialization column, they are subtracted from the initial amount in the Calc_Open row. The formula Closing=ITERATE (PRIOR()+[Initial]+[Int]-[Out],[Initial],[d/Date]) is not applicable when data is imported (e.g. by query) and must be developed further. Do you have an idea how this can be prevented?
Furthermore I have thought about a classic Liquidity Planning Example. Best case would be to pull the opening balances via existing queries into Calc_Open so that these are then taken into account for the calculation.
You would then consider the Calc_Open row as an initialization column and simply adjust the formulas accordingly:
So you leave only [Initial] out of the calculation because it is taken into account in the basemember anyway.
I would be very happy if we can further develop this idea / blog.