Automating the 3-Way Forecast, also referred to as 3 Statement Model, has always been a strong point of Calxa. It’s an easy way to prepare a 3-Way Forecast. This key set of reports is the foundation for most financial analysis of a business including scenario planning and sensitivity analysis.
This guide will step you through how to prepare a 3-Way Forecast. 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.
Table of Contents
How to prepare a 3-way forecast?
Consolidating the 3-way forecast
Reporting on the 3-way forecast
Add KPIs to your 3-way forecast
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 and many people filed it away in the bottom drawer. 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:
- A Cashflow forecast
- Also, a Balance Sheet forecast
- And a Profit & Loss forecast
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. This guide on 3 Statement Model explains this in more depth.
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 to 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. So here is how to prepare a 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 forecast – and project 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
- Set Cost of Sales as a percentage of Sales, Superannuation based on wages, etc
- Build driver-based budgets from non-financial Metrics. A coffee shop might multiply the price of coffee by the predicted sales to get their revenue. Knowing the cost of each cup lets you forecast the Cost of Sales figure too.
- Construct your wages budgets based on your team and how much each is paid.
Balance Sheet Budgets
In Calxa, the most difficult balance sheet accounts, like bank, debtors and creditors, GST or VAT, 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.
Some balance sheet budgets, like purchasing an asset or moving money into a cash management account, are relatively simple to forecast. Budgeting for loans is straightforward using our Loan Wizard.
Income received in advance is more complex and sometimes invoice factoring can be most challenging of all. But the general rule is to set your budgets to mimic what happens with the actuals in your accounting system. Then you won’t go far wrong.
Bank, Taxes, Payables and Receivables
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.
Loan Wizard
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.
We do recommend that you use a new liability account for each loan, as well as a separate Interest expense account. This makes it much simpler to manage if you decide to repay the loan early or if you are entering a potential loan that hasn’t been approved yet. In most cases, you don’t necessarily need to add these accounts into your accounting system, though most people do for the liabilities. For the interest expense, add sub-accounts for the budget for each loan in Calxa and they will automatically accumulate to match the actuals from the accounting system.
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.
This 3-minute video guides you through a quick set up shows you how to prepare a 3-way forecast.
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. You can create Exchange Rate scenarios and apply them to the group so that you can easily answer questions like, what happens if our currency appreciates, or is devalued?
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 use bundle kits we have pre-configured.
3-Way Forecast Bundle Kit
So, how do you prepare a 3-way forecast? The simplest starting point is to use the Bundle Kit for a 3-Way Forecast.
- You will find this in Reports, Bundles, Create New Bundle
- Simply choose your organisation or organisation group if you’re consolidating
- Select the budget you want to report on, including any scenarios
- After that, select the Account Tree you want to use for grouping your accounts
- It will create a bundle with the P&L Forecast, Balance Sheet Forecast and Cashflow Forecast, with a report and chart for each. There’s a button at the end of this article that will show you a sample bundle.
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.
The standard 3-way forecast bundle assumes you want a report for 12 months ahead. If you need something longer-term, simply configure each report and change the number of periods 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 (or your client if you are an accountant) each month or quarter. 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. Some people call this a 4-way forecast but we suspect that’s just a fun marketing term.
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 in your account tree to have them directly in your reports. Or customise a dashboard with charts to show the numbers you want and add that to the bundle. You have choices on how to present this information and you can choose what is most suitable for your audience.
Add a Dashboard to your 3-Way Forecast
Pictures can indeed tell a story. Add our standard Cash Management dashboard to your 3-way forecast bundle, or customise one to suit your needs. You can choose the charts and KPIs that tell your story and highlight the important information your audience needs.
Review your 3-Way Forecast
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.
If you are forecasting a consolidated group and the numbers don’t look quite right, use the Cashflow Forecast Comparison report to see the breakdown. In short, it will give you columns by entity and you can run it for any date range. That will help you identify a problem with the setup or prepare for something that may have been unexpected.
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.
Model the Most Likely Scenario
The solution is to create multiple budget scenarios to model the most likely outcomes and run a number of 3-way forecast bundles. With Scenarios in Calxa, you can layer your potential futures. Create one for a 10% decline in revenue, which is likely to include changes to cost of sales. Create another for reducing your workforce. Run a forecast to see what happens if just the revenue reduction happens and another to include the staff reduction. Think of the different responses to a situation and model each change with a Scenario. In the Budget builder, you can preview your budget with any combination of scenarios to see how it will affect your profit.
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.
