Liquidity Planning - SAP Analytics Cloud standard business content
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:
- Date
- Version
- Account
- Measures:
- In
- Out
- Initial/Correction
- Calc measure: CALC_CLOSING
- Calc measure: CALC_OPEN
CashFlow with Model Measures
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.
Closing Period Calculated Measure
PRIOR function is used inside the iteration expression (ITERATE formula) to retrieve the value that was calculated for the previous member.
Opening Period Calculated Measure
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.
Exception Aggregation Effect on Opening and Closing Balances
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).
Hide Column
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)
- Date
- Version
- Account
- Measures:
- Opening
- In
- Out
- Closing
Measures and Dimensions in Scope
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.
CarryForward Function sample
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:
CarryForward Syntax
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.
Insert Data Action Trigger into the Story
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.
Entering Change into our Outflows
Run CarryForward Data Action
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...).
Analyse the Effect
Since SAP Analytics Cloud is an all in one/complete analytics platform combining BI, Planning/Forecasting/Simulation and Predictive capabilities, we can do everything in the same platform. End users always stay within the same UX, no data needs to integrated back and forth between different systems to execute BI, planning & predictive workflows. Which for example means I build my cash flow forecast, I simulate potential effects of a planned M&A transaction and then analyse the effect within the same platform & UX.
FINAL THOUGHTS
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)
- Liabilities
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!
Cheers,
Blaž
- SAP Managed Tags:
7 Comments
