THREE WAY FINANCIAL FORECAST
I have worked in finance for over 25 years but it was only in recent years that I came across the term ‘Three Way forecast’. I guess it is a term that has migrated from the United States. It describes a forecast that involves a Profit and Loss forecast, a Balance Sheet forecast and a Cash Flow forecast. These are tools that we use all the time and when they are combined together they offer a comprehensive forecast for your business.
I worked in Paris in the late 1990’s for a British based conglomerated focused on the Foam, plastics and fibre industries. I returned to Ireland in 2000 and took a job in Corporate Finance in BDO Simpson Xavier. I worked with Con Quigley and at the time he had developed a template excel based Three Way Financial model. We didn’t call it that, but that’s what it was. It’s hard to imagine now but it was cutting edge and that model became my primary tool for many years.
To put it in perspective, only three years previously I worked in KPMG on the delisting of a Public Company and we had to produce the financial tables for the prospectus. I imported the excel tables into the word document we were preparing and after a long day of changes and revisions I would make my changes and sync the word document with the excel sheet. First thing next morning I would print the next draft (from my Macintosh SE/30) for the Partner to review. After a few days of this he took me aside as he was worried that I was up all night typing the document. At the time all accounts went through the typing pool to maintain quality so he had never experienced this use of technology. I might not have put him in the picture….
Today we have many software solutions to facilitate preparing these models. Most accountants still use Excel and the complexity varies widely. It is rare for me to come across one as effective as Con’s, designed over twenty years ago. Quite often accountants just do a Profit and Loss, avoiding a Balance Sheet and the need to get it to work (Balance). If a cash flow is required it is often a standalone document, again avoiding the need to get it to reconcile with the other two documents. Sometimes this is all that is needed.
I worked on a fundraising a couple of years ago and the financial model was developed by a finance house on the East Coast USA. The complexity was fantastic, with at least twenty sheets involved and the ability to model multiple funding rounds with many types of financial instruments and clearly showing the dilution effect. It was a template and required significant experience to bend it into shape for our client. It was also a Three Way Forecast and it clearly demonstrated the cash requirement to fund the business.
So what makes for a good Three Way Forecast? My priority is the ability to print (PDF) into three summary pages no matter how complex the model, including key assumptions. Complexity and detail are fine if you are driving the model but for the reader it must be concise.
Using colours, headings, shading etc. to highlight key figures is very important. Some historic data needs to be included and generally monthly information for the first year and yearly after that. The monthly detail can be included for following years but make sure it can be grouped out of sight.
Weekly information is used in a 10 or 13 week rolling cash flow. This is often required in challenged situations or for a restructuring but for growth businesses it is usually not required. Quarterly information is typically required in information relating to the markets and can find its way into a prospectus that has those ambitions but again for typical growth businesses it is unlikely that quarterly information adds value.
Most users require five year projections in a standard three way forecast. That said, three often works fine and I have regularly had to produce a picture for ten years’ time to demonstrate the long term potential. It is common in property structures to show long term outcomes but in growth businesses it is unlikely that investors are interested in holding beyond five years.
The historical data is of most use in the profit and loss as it can demonstrate the growth you have been experiencing but the most important aspect of this part of the model is to incorporate the assumptions so that you can adjust the model in real time. Also, if gross margin or EBITDA targets for example are key then make sure they are obvious.
The balance sheet is a point in time and the key information is usually the working capital requirement on a monthly basis. If you drive up Revenue, what is the impact on working capital and most importantly cash. Fixed asset investment and the mix of funding could be important.
The cash flow statement usually does not require historical data and the indirect method is best to reconcile to the other statements. Learning to read the cash flow is a skill you should perfect as it adds real value when planning changes to your business plan and anticipating the effect on funding.
Usually businesses produce a three way cash flow for a specific event and then put it aside. The effort to maintain it is too onerous. If you use a cloud based accounting platform like Xero, there are many third party applications that you can use to maintain this information in real time. You need to put the time in, to get your nominal ledger in shape and to set up the applications, but after that it will run smoothly and provide the crucial financial information on demand for your team.
A good model will allow you to run sensitivity analysis and scenario analysis to model all possible outcomes. If you can do this in real time then the leadership team are equipped to focus their decision making on realistic options much quicker, giving them the possibility of maximising an opportunity.
Remember that your model needs to include the aspects that are key to your business. Work need to go into adapting a template to make sure it has the right focus. We use Fathom with some customers which has 27 KPI’s as standard! it’s a powerful tool but it needs to be refined by the user to get the best from it. We also use Clarity which has a really slick bar to move the dial for sensitivity analysis and Datadear which when used in conjunction with Excel gives you real time data the way you want it.
The future is accurate, timely and relevant information at your fingertips. The Three Way Forecast is the gold standard for financial information and if you don’t use it, you should.
(Photo Pars Sahin)
Leave a Reply