What is a Three-Way Forecast?
A three-way forecast, also known as the three financial statements model, is a financial tool that consolidates three essential reports into a single, comprehensive forecast. It integrates your financial statements – your Profit & Loss statement (income statement), balance sheet, and cash flow projections to provide a holistic view of your future cash position and overall financial health.
It is the only way to understand your future business’s position and determine the actions the owners need to pivot for future events.
The report is grounded in accounting integrity since the cash flow forecast is derived from real-time data in your balance sheet and profit and loss statements. This accuracy makes a three-way forecast particularly valuable for banks and investors when looking at the Perth SME business accounts.
Beyond offering detailed financial projections that clarify your business model’s prospects, three-way forecasts are precise, reliable, and deliver the most insightful analysis of your company’s future financial position.
Why is Three-Way Forecasting Important for a Business?
A three-way forecast is crucial for a business because it illuminates potential future financial scenarios, helping you ensure that your company can meet its obligations to suppliers and employees.
When Should You Use a Three-Way Forecast?
- When you and your management team seek to gain confidence in your cash position.
- When you want to make your business more attractive to potential investors and lenders.
- When your goal is to establish and maintain financial stability for your company, both now and in the future, for your Perth SME.
- When you want to gain insight into your business operations, your business accounting and your business finances.
The Three Financial Statement Model
Profit and Loss (Income) Forecast:
The profit and loss statement is one of the most critical financial documents for most stakeholders, demonstrating a business’s ability to generate profit. While the income statement provides reasonably accurate information, it does not disclose the assets and liabilities required to achieve that profit, nor does it necessarily reflect the actual cash flows generated by the business. So, relying solely on the income statement may offer an incomplete picture of the company’s financial health.
Balance Sheet:
The balance sheet becomes significantly essential when analysed in conjunction with the profit and loss statement, as it reveals the level of investment necessary to support the sales and profits reflected in the income statement. A balance sheet forecast showing the reduction (or increase) in debt and investments made in working capital and fixed assets is crucial for providing a clear view of the Perth SME company’s financial position at any given point.
Cash Flow Forecast
The cash flow forecast is a vital financial statement that highlights changes in cash inflows and outflows. It offers a more accurate representation of a company’s cash movements than the income statement. A fully integrated three-way forecast includes projected cash flows for the next 12 months or annually for up to 10 years, providing a comprehensive view of future cash positions.
How to build a 3-way forecast in Excel
Building a three-statement model involves several key steps:
- Input Historical Financial Data into Excel: Enter historical financial information into your Excel model.
- Establish Forecasting Assumptions: Determine the key assumptions driving your financial forecasts.
- Forecast the Income Statement: Develop projections for the income statement based on your assumptions.
- Forecast Long-Term Assets: Project the performance of long-term capital assets.
- Forecast Financing Activities: Estimate future financing activities, such as debt and equity transactions.
- Complete the Income Statement: Finalize the income statement with all necessary data.
- Complete the Balance Sheet (Excluding Cash): Populate the balance sheet, excluding cash-related items.
- Complete the Cash Flow Statement and Cash Position: Finalize the cash flow statement and reconcile the cash position on the balance sheet.
This guide will provide detailed instructions for each of these steps.
Input Historical Financial Information into Excel
In this step, the company’s historical financial data is gathered and entered into Excel by downloading, typing, or pasting the information. Once the data is in Excel, basic formatting should be applied to ensure the information is organised and aligns with the structure of your financial model.
Determining the Assumptions to Drive the Forecast
When calculating key drivers include revenue growth, profit margins, capital expenditures, and working capital components such as accounts payable, inventory, and accounts receivable.
These calculations will form the basis for the assumptions in forecasting future financial performance.
Forecasting the Income Statement
With the assumptions established, the next step is to forecast the income statement, starting with revenue and progressing down to EBITDA (Earnings Before Interest, Taxes, Depreciation, and Amortization). At this stage, it is also necessary to create supporting schedules for capital assets and financing activities.
Forecasting Capital Assets
Before completing the income statement, it is essential to forecast capital assets, including property, plant, and equipment (PP&E). This involves taking the previous period’s closing balance, adding capital expenditures, deducting depreciation, and arriving at the new closing balance. Depreciation can be calculated using various methods, such as straight-line, declining balance, or as a percentage of revenue.
Forecasting Financing Activity
The next step is to create a debt schedule to understand the interest cost of the business. This includes accounting for any changes in debt.
Interest expense can be calculated using the opening or average debt balance. Alternatively, a detailed interest expense schedule can be used if available.
Completing the Income Statement
With depreciation and interest expenses forecasted in their respective supporting schedules, these figures can now be referenced in the income statement, allowing you to complete this core financial document.
Completing the Balance Sheet
The balance sheet can now be completed (except for cash). Working capital items, such as accounts payable, accounts receivable, and inventory, are forecasted based on assumptions regarding average days payable, receivable, and inventory turnover.
Finalising the Cash Flow Statement and Cash Balance on the Balance Sheet
The cash flow statement will allow you to finalise the three-way forecast. To accurately capture the company’s cash flow, each of the three primary sections must be completed: cash from operations, cash from investing, and cash from financing.
Benefits of using Excel to prepare a 3-way forecast
Perfectly tailored
Excel is incredibly powerful as a programming language. Writing your own forecast from the ground up will allow you to create a forecast that perfectly fits your needs and desires.
Free software cost
Most businesses already have Excel on their computers, so using Excel to create a forecast will not increase the software cost of the business.
Disadvantages of Inventory Forecasting in Excel
Time-Consuming
A 3-forecast in Excel requires manual data syncing, complex programming and updates, which can be labour-intensive, particularly at scale. While the software’s subscription costs may be low, the need for dedicated personnel can increase labour expenses.
Only huge businesses can typically afford the labour cost of developing a 3-way model when that part of the business is an overhead cost.
If you consider a company like Futrli, the forecasting data syncs with Xero, Sage and Quickbooks.
Prone to Errors
Forecasting in Excel carries a risk of errors, mainly if data is imported incorrectly or if formulas break. Unlike automated inventory planning tools, Excel is susceptible to human error during data entry, potentially leading to inaccurate results.
Foreign currency conversions and consolidations
If you operate across multiple countries and require consolidated financial reporting, Excel can become difficult to program and link to different FX clearing hours to convert currency in different denominations to Australian dollars.
High labour cost to prepare
Utilising Excel for forecasting demands a thorough understanding of accounting, finance, and business, programming various formulas, and the ability to construct a forecasting model tailored to the business’s needs, which can require significant time and effort.
Typically, the cost of employing a person to manage this technical work is very high and beyond the reach of most businesses. So, most businesses will then employ a very high-cost labour person to do a forecast, and that person will have excess capacity- and that person’s excess capacity will then be used to do low-paying work (like bank reconciliations or payroll reviews).
Limited Scalability
Excel can struggle with processing large datasets, making it less effective as a business grows. While spreadsheets may be suitable initially, they become impractical for inventory planning at scale.
Lack of Real-Time Data
Since data entry in Excel is done manually, the information used for forecasting is not real-time, which can limit the accuracy and relevance of the forecasts.
Poor knowledge management
If a person prepares a tailored Excel 3-way model, it is normally a work of art. Sadly, when that person leaves, the knowledge of using the art also goes. The new incumbent will typically not appreciate or want to rewrite the model from the ground up.
This can be an incredibly costly and time-consuming operation.
Lack of trust
If you prepare a 3-way forecast to show to a bank, investor or potential buyer, that person will want to rely on your forecast. However, if the forecast is prepared in Excel, the integrity of the Excel document must be investigated to ensure fundamental coding issues do not arise. If a person uses a specialist forecast model, the person relying on your forecast can review your data in the model. Still, they can also rely on the software’s internal controls, knowing it is properly managed.
Software programs to help forecasting
Specialist software programs like Futrli, Fathom, and Calxa reduce the spending on administration labour, both in hours and hourly, through specialist software programs. This stops the need for your administration team to become software programmers and focus on your business.
At Westcourt, we have a deep range of experience in implementing, maintaining, and understanding the business impact of a 3-way forecast. We actively work with clients to leverage our forecasting knowledge so they can cost-effectively shift the administration and finance team away from software programming and data checks to real-time business insights.
