In this two-part series, William Buck Manager Business Advisory, Eric Flammang provides his recommendations for monitoring and managing cash inflow and outflows effectively during COVID19, using a three-way forecast.
Part Two: Three-way forecast models in practice
When it comes to three-way financial modelling, small businesses can now receive packages which integrate with your cloud-based accounting data file or ERP system and perform algorithmic forecasting for less than $1,000 a year. But in practice, Excel is still widely used and can sometimes be as powerful as any other software if used properly.
Listed below are the 10 commandments I believe businesses should follow when setting up their three-way cash flow forecasting models in excel:
- Use colour coding to distinguish between inputs and formulas
- Build a stand-alone 3 statement-model on one worksheet
- Clearly separate the assumptions or drivers from the rest of the model
- Use clear headers and subheads (with bold shading) to clearly distinguish sections
- Use the cell comments function (shift + F2) to describe calculations or assumptions that need explaining
- Build in error checks such as ensuring the balance sheet balances (without a plug)
- Pull forward (or repeat) information where it helps users follow the logic of the model (i.e. pull forward EBITDA from the income statement to the cash flow valuation section)
- Avoid linking to other Excel workbooks unless absolutely necessary (and if so, clearly indicate those links exist)
- Avoid circular references unless necessary (and use iterative calculation to solve them); and
- Use tables, charts and graphs to summarise key information.
And for Excel beginners, listed below are the most important Excel tips for financial modelling:
- Use as many keyboard shortcuts as possible
- Keep formulas and calculations simple – break them down into smaller steps
- Use the grouping function to organise sections of the financial model
- Use F5 (go to special) to quickly locate all hardcoded numbers or formulas
- Use “Trace Precedents” and “Trace Dependents” to audit the model
- Use “XNPV” and “XIRR” to apply specific dates to cash flows in the context of return on investment calculations
- Use “INDEX MATCH” over “VLOOKUP” for looking up information
- Use a combination of date functions (e.g. “EOMONTH”) and IF statements to make dates dynamic
- Remove gridlines when presenting or sharing the financial model; and
- Memorise all the most important Excel formulas for financial modelling.
But more importantly, if unsure, speak to your accountant and business advisors. They are highly trained and experienced with forecast modelling. A model is only useful and relevant if robust-built – don’t hesitate to invest in this area and obtain the expert advice you require.
Conclusion
Even before the outbreak of COVID-19, we were living in a period of rapid change and great uncertainty due to economic cycles, constantly evolving legal frameworks, increasing competition, and disruptive technologies. Business leaders should identify lessons learned from this crisis, including the adoption of scenario planning as part of ongoing efforts in developing plans and projections to remain liquid and “agile”.
Three-way cash flow forecasts, scenario and sensitivity analyses and variance analyses are powerful tools available to businesses to help them regain control and quickly and regularly assess and adopt available strategies.
In this environment, businesses must take advantage of the various options available to them including the current government stimulus measures and re-negotiating their current finance and leasing / supply and employment arrangements.
You can read Part 1 | The importance of cash flow forecasting and ‘how to’ during COVID19 here
