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.
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!
Calxa’s 3-Way Forecasting
The Budget Basics
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 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.
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 – these can have a big impact on your cashflow projections so don’t neglect them.
At this point, it may be a good time to refresh your knowledge on the difference between Budgets and Cash Flow Forecast. This is particularly useful to understand timing of inflows and outflows discussed in the next section.
Timing of Payments and Receipts
The 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.
Fine-tuning the 3-Way Forecast
While the default settings are great for a first draft and close enough for many situations, sometimes you need to modify them to get a better projection. In the Cashflow Forecast Settings, by selecting the Advanced View, you can modify the Cashflow Type of each account.
Each of the options modifies the profile for this account. You can influence things like how much gets paid this month, next month or the following month. Use this to modify the settings just the way you need them.
One of the most common changes to make is to set the Cashflow Type to Profile and then 100% Current. This implies that whatever is in the budget hits the bank in the same month. This makes sense for those payments you make by direct debit each month. It can include expenses like phone, rent, bank fees, and so on. While many of them are too small to have a significant effect on the overall cashflow forecast, some of them can be major. Even the small ones can add up to something that makes a difference.
Automating the 3-Way Forecast
We’ve provided a sample Report Bundle Kit to get you started on reporting. Choose the option to Create a New Bundle and choose the 3-Way Forecast. Select your organisation (if you have more than one) and the budget you want to use. This will create a Report Bundle containing the key components of your 3 Statement Model. It contains not just the numerical reports but also charts to give you an instant snapshot of your predictions.
When creating your own forecast in Calxa, follow these few steps:
- Configure your Cashflow Forecast report or the chart if you prefer your projection graphically.
- Now, add it to a Report Bundle.
- Follow this with the Balance Sheet forecast and then the Profit and Loss Projection.
- Set the starting date for each of them as Relative Month +1. This will then always give you a co-ordinated 3-way set for the future, whenever you run the bundle.
Create a workflow to then deliver your automated 3-way forecast bundle to yourself, your client, your boss on your chosen date each month. This will give you an up to date, fully consistent, 360-degree view of the future of your business.
Improving the 3-way Forecast
One of the common uses of the 3-way forecast, for both bankers and accountants, is to use the information to calculate KPIs and ratios to assess the health of the business. Adding some of these KPIs directly to your 3-way forecast bundle will save time and effort. We have some suggestions on important KPIs for cash management to get you started.