I have been a financial consultant to businesses for many years and it always surprises me how many businesses do not forecast their cashflows. Cashflow is one of those things that doesn’t seem to fall into the urgent category and is therefore often neglected.
Forecasting can be difficult given things change quickly and the future is inherently uncertain. Forecasting requires us to make some assumptions that could turn out to be inaccurate which makes many of us uncomfortable or feel like the exercise is a waste of time.
There are clear benefits of knowing what your cashflow may look like in the near to medium future especially when you are undergoing the following activities:
- Deciding whether to replace equipment or extend the life of existing equipment
- Considering acquiring a new business
- Negotiating with suppliers
- Valuing your business
- Refinancing or communicating with your financiers
- Wanting to anticipate funding needs well in advance for a special project, for-day-to-day operations or to communicate with shareholders
- Proactively trying to win customers via incentives and promotions
There is now more choice than ever for cashflow forecasting tools. Some software programs appear quite impressive in terms of their visual outputs however the pros and cons of each solution can be difficult to weigh up. Many software tools have limitations, so it is important to ask the right questions before buying. For example, some forecasting software does not interface with accounting systems or exports only hardcoded numbers to other programs such as Microsoft Excel.
I regularly use Microsoft Excel for 3-way forecasting (P&L, Cashflow and Balance Sheet) and therefore have provided some of my learnings about using Excel for forecasting below.
When to use a spreadsheet for forecasting
Like other software programs, Excel has limitations and potential issues when it comes to using it as your major forecasting tool. These issues include the risk of accidental errors and reusing templates that are no longer fit for purpose. However, the risk of errors can be minimised through well designed spreadsheets with the appropriate checks and balances. Excel is still probably one of the best options for small-to-medium business in the following cases:
- When deciding on whether to finance a specific project. Starting a new product line or taking on a new contract that requires investment.
- Preparing for an acquisition. When preparing for an acquisition it is important to understand the cashflows of the target business to determine an appropriate offer price and to understand the combined value of your business and the target by modelling expected synergies.
- Preparing for sale or a partial divestment. As the value of a business is based on its future cashflows, both the potential buyers and you will want to see and understand the forecast cashflows to determine an appropriate price.
- Deciding whether to replace equipment or extend the life of existing equipment. Both scenarios can be modelled out over a specific period and compared based on net present value (NPV).
- Valuing your business. Forecast cash inflows and outflows are a key input for a valuation based on the discounted cashflows (DCF) method of valuation.
- Simple monthly, quarterly or yearly forecasting. Spreadsheets are very useful for setting up forecasting at regular intervals regardless of what interval you choose. The chosen intervals can then be easily rolled up into a yearly overview.
Once a forecast is prepared in Excel, different scenarios can be built in to allow the end user to see and compare the result of these scenarios in the one table. One scenario may include what would happen to the P&L and cashflow if the sales volume of a product line went up by 10%.
When not to use a spreadsheet for forecasting
There are some cases where I do not recommend using a spreadsheet:
- If the spreadsheet needs to be updated frequently and perhaps you have more than a few people who need to update it.
- You have operations that span many locations and or business units that use separate financial systems, resulting in multiple raw data sources that need to be linked to or input into Excel.
- Management wants to drill down on the output and or wants to see the output on dashboards that update regularly.
In the above cases it might be better to implement a dedicated forecasting software solution.
What every Forecasting Spreadsheet should have
If you do decide to use Microsoft Excel for forecasting, your models should include the following attributes to make the model robust, easily auditable and set out in a logical manner for the end user
- All models should have an input page that allows the end user to update the model as and when required. Updates to the input page should flow throughout the model so there is no need to update any other worksheets in the model. Keeping all inputs on one worksheet minimises the risk of model errors
- The formulas in your model should not contain any hardcoded numbers
- Timelines should be consistent across worksheets in the model
- Formulas should be quite simple to understand it is good practice to break each formula down into steps so the end user can understand the calculations, and
- Develop an output page to summarise the important information for decision making purposes.
When to give up on Forecasting
NEVER!!! – There are people to help. Knowing your cashflow can only arm you with the information you need to help make important decisions andsteer your way to prosperity or out of a hole.