Automating the 3-Way Forecast, also referred to as 3 Statement Model, has always been a strong point of Calxa. This key set of reports is the foundation for most financial analysis of a business including scenario planning and sensitivity analysis. The initial data comes from your accounting system. From that, you then simply add your Profit & Loss and Balance Sheet budgets. The final step is setting the timing of inflows and outflows. Calxa provides smart tools to help with these.
Who Needs A 3-Way Forecast?
Way back in the depths of time, banks would require a 3-way forecast when you applied for a loan. Once it was approved, they rarely asked for more. In the last 10 years that has changed with many banks wanting updated reports quarterly or even monthly.
For the banks, it’s simple risk management. They want to be able to react early to any anticipated change of circumstances and it normally makes sense for a borrower to keep their lender informed. Similarly, lenders of all sorts, not just banks, don’t like surprises. This means, regular reporting is an important way of keeping them informed. By providing them with a 3-way forecast, you give them the full picture of what’s happening in the business.
The 3-way forecast also demonstrates to your bank that your cashflow forecast has solid accounting integrity. The internet is awash with cashflow forecast spreadsheet templates but you could plug any numbers into them. Yes, they will give you a predicted bank balance but unless you also tie that in with what’s happening with debtors, creditors and inventory, it has no credibility.
A three-way forecast also gives you a complete, 360-degree view of the future of your business. As the Corporate Finance Institute say, “a 3 statement model links the income statement, balance sheet, and cash flow statement into one dynamically connected financial model. 3 statement models are the foundation on which more advanced financial models are built.”
What Is A 3 Statement Model?
A 3-way forecast, also known as 3 statement model, is simply an integrated set of reports. It comprises:
Future changes to the P & L Budgets or the Balance Sheet budgets are automatically reflected in all 3 reports, as are changes to any of the cashflow settings.
Traditionally, this has been challenging to achieve in a spreadsheet, but tools like Calxa simplify the process. The difficulty has been in avoiding circular references. Spreadsheets weren’t designed for double-entry accounting!
How Do You Prepare A 3-Way Forecast?
You don’t have to be a brain surgeon to prepare a 3-Way Forecast. But it helps if you use a methodical approach. A more stepped process will help you work through each part until you end up with a full 3-Way Forecast.
Start with Actuals
Start by reviewing your current accounting data and check for things like:
- Is it up to date?
- Is everything reconciled?
If you don’t start with the right balances, you have nothing solid to build on. Use the handy checklist in our guide the Foundations of a Great Cashflow Forecast Guide. This will walk you through an ordered way of checking the integrity of your actuals.
Create a Budget
If you don’t already have a budget, use the Budget Factory to create one. This will take last year’s actual results – or the current year projection – and propel them forward. You can set a different percentage change for each year. Or, start your forecast from the profit figure and build the budget from there.
Alternately, if you have existing budgets in a spreadsheet, import those into Calxa. If you run your business with different departments or cost-centres, create your Profit & Loss budgets at that level and consolidate them to the organisation-level budgets. This helps you plan with greater granularity. You can later compare actuals to budgets and measure your performance.
Add Formulas to Budgets
- Coming soon in Calxa
- Set COS as percentage of Sales, Super based on wages, etc
- Driver-based budget
Balance Sheet Budgets
Most of the balance sheet accounts, certainly the difficult ones, are calculated for you. You just need to pay attention to the bigger things like asset purchases, loan repayments and that sort of thing. Don’t neglect these as they can have a big impact on your cashflow projections.
Calxa calculates the movements on your bank account and on your payables and receivables accounts. You don’t need to budget for GST/VAT or taxes deducted from payroll. We use the tax codes applied to each account to determine the tax amounts and timing is managed in the Cashflow Settings.
Loans can be tricky to budget for but the Loan Wizard will make it easier for you. Simply enter the loan amount, term and interest rate (or the monthly repayments and any balloon or residual payment). Calxa will then calculate the principal and interest components month by month for the term of the loan and set the budget for you.
Timing and Cashflow Settings
Timing is everything when it comes to cash flow forecasts.
Our default setting for income accounts is a cashflow type of Debtor Days. Each time you update from Xero, MYOB or QuickBooks, we calculate the average days outstanding of your overall debtors. From this, we do some statistical analysis to estimate what gets received in the month of invoicing, the following month, the next month and so on. It’s not based on the terms you set for your customers but on how they actually pay you. So, it gives a realistic estimate of your future payment patterns.
Similarly, we estimate the timing of most Cost of Sales and Expense accounts based on your average outstanding Creditor Days.
For GST and VAT, you select a schedule that reflects the months in which you pay your taxes or you can customise the schedule to suit your specific needs. Similarly, choose a schedule for when you pay PAYE/PAYG/Payroll Tax and estimate the average amount you withhold. This will then split your budgeted gross pay into the net pay paid out immediately and the tax that’s forwarded to the government later.
Consolidating The 3-Way Forecast
Many businesses comprise more than one entity and it makes sense then to create a 3-way forecast for the whole group. You’ll find all the details in our Group Consolidation Guide but the short version is:
- Set up the forecasts for each individual entity
- Create an organisation group to combine the entities
- Review the exchange rates we’ve brought in to make sure they suit your needs.
Reporting On The 3-Way Forecast
You have choices when it comes to reporting in Calxa. With hundreds of report templates, Calxa gives you the option to run individual reports or grouping them into a Report Bundle. You can even used bundle kits we have pre-configured.
3-Way Forecast Bundle Kit
The simplest starting point is to use the Bundle Kit for a 3-Way Forecast. You’ll find this in Reports, Bundles, Create New Bundle. Simply choose your organisation and the budget you want to use and it will create a bundle with the P&L Forecast, Balance Sheet Forecast and Cashflow Forecast, just as you need. You can download a sample 3-Way Forecast Bundle, you will find the button at the end of this article.
The bundle includes both charts and reports with numbers. Depending on your audience, you can turn off or remove the ones you don’t need. Use charts if you’re presenting to non-accountants. Your bank will want numbers but the charts can still help them grasp the big picture before they delve into the detail.
If you’re doing a consolidated three-way forecast, you will need to create the bundle for one entity and then configure each report separately to use your organisation group.
Another reason to change the report configuration would be to use an Account Tree to summarise the lines (or just to put headings in). The standard 3-way forecast bundle also assumes you want a report for 12 months ahead. If you need something longer-term, simply configure each report and change the template to something that suits. You can report for up to 10 years. If you need to report further ahead than that, copy the bundle and set the start date to 10 years from now.
Automating Your 3-Way Forecast
Once you’re satisfied with your report bundle, create a workflow to schedule delivery to yourself each month. Like many things in business, familiarity improves the value of forecasts. You’ll learn to recognise patterns and see what’s happening in your P&L, your Balance Sheet and your cashflow forecast. By reviewing them regularly, you’ll quickly spot anomalies.
Add KPIs To Your 3-Way Forecast
Don’t limit yourself to the traditional contents of the 3-way forecast. Adding KPIs to the mix can provide extra information and help you see things you might otherwise have missed. If you’re preparing your forecast for a bank, you can bet that they will be calculating their own KPIs on the data you give them. Be one step ahead by reviewing them yourself first. Here’s some suggestions that will guide you:
- Financial Ratios for Loan Applications
- Balance Sheet Analysis Using KPIs
- 7 Important KPIs for Cash Management
Choose the KPIs that suit your needs and add them to your bundle. You can use either a chart or include them at the bottom of some of the reports.
Review Your 3-Way Forecasts
The first question with any forecast is, does it look sensible? If you’ve never had more than $10,000 in the bank, a forecast that shows you’ll have millions soon is immediately suspect. Not impossible but you need to understand why. There shouldn’t be surprises in a 3-way forecast.
The Reasonableness Test
Use the relationships between the 3 reports in your forecast to test reasonableness. If your profit is increasing each month or year, you would expect the debtor balance to be increasing in the balance sheet. You would also normally expect the cash balance to increase but there are reasons it may not:
- You could be forecasting an increase in inventory levels (and consequently purchases) to supply future sales.
- You could be forecasting the purchase of fixed assets from cash.
- You may be planning to move some of the surplus cash to a term deposit account or use it to pay off a loan.
Review your balance sheet carefully:
- Is there a significant change in debtors between the beginning and end of the forecast? This could be caused by increasing sales but make sure there is that connection.
- Similarly, review changes in inventory and creditors or payables accounts.
- Are loan accounts decreasing over time?
A quick way to review the changes in your balance sheet over the forecast period is to run the Where Did Our Money Go? report for the same dates. It will show the summarised Profit and Loss and just the movements in the Balance Sheet accounts. This will help you understand the relationship between the projected Net Profit and the change in your expected bank balances.
The Practical Test
Once you’ve created your forecast, monitor it each month. How closely does it predict your bank balance? Just be aware though, that cashflow forecasts exhibit some of the characteristics of quantum mechanics: the act of observing and monitoring it changes behaviour and therefore outcomes.
If your forecast is projecting a small overdraft at the end of the month, it’s likely that you will act to avoid that. You’ll try to get more sales, reduce or postpone expenditure. If you’re successful, it doesn’t mean your forecast was useless. Quite the opposite in fact. It’s what prompted you to act and make changes in your business.
3-Way Forecast Scenarios
So, you’ve done your first 3-way forecast. What next? Do another one!
Your forecast is just one of many potential futures for your business. There are many unknowns and therefore many possible outcomes. The solution is to create multiple budgets to model the most likely scenarios and run a number of 3-way forecast bundles. By working through the different scenarios, you can help your business plan for any situation. Our article on cashflow scenarios to mitigate a crisis will give you the tips you need.
Exchange Rate scenarios
The other variable that can affect a business that trades globally, is fluctuations in exchange rates. What if your currency goes up or down against your major trading partners? In Calxa, you can create Exchange Rate Scenarios to model future changes. Then create a copy of your organisation group using that scenario and use that in your forecasts.
Knowing the likely impact of exchange rate movements will help you decide if it makes sense to lock in future rates or hedge them.
Summary on Automating the 3-Way Forecast
The 3-way forecast (or 3-statement model in some parts of the world) is a vital tool to understanding what’s happening in a business. By combining the P&L forecast, Balance Sheet forecast and Cashflow forecast, it gives a 360-degree view of the organisation. Bringing together these 3 reports demonstrates the integrity of the whole forecast. It enables you to check that the forecast is sensible, consistent and internally coherent.