Cashflow is one of those things that doesnโt seem to fall into the urgent category and is, therefore, often neglected until it’s too late.
Forecasting can be difficult, given things change quickly and the future is inherently uncertain. It 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. However, a reasonable forecast with the best assumptions you can make is far better than no forecast.
There are clear benefits of knowing what your cashflow may look like in the near to medium future, especially when undergoing the following activities:
- Wanting to anticipate funding needs and any likely shortfalls well in advance for a special project, day-to-day operations or to communicate with shareholders
- 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
- Proactively trying to win customers via incentives and promotions
Forecasting tools
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 asking the right questions before buying is important. For example, some forecasting software does not interface with accounting systems or exports only hardcoded numbers to other programs such as Microsoft Excel.
Microsoft Excel can also be used for three-way forecasting (Profit and loss, cashflow, and balance sheet). Below are some key insights when using Excel for forecasting.
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 businesses for the following cases:
- Simple monthly, quarterly or yearly forecasting. Spreadsheets are useful for setting up forecasting at regular intervals, regardless of the interval you choose. The chosen intervals can then be easily rolled up into a yearly overview.
- 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. The value of a business may be 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 key inputs for a valuation based on the discounted cashflows (DCF) method.
- Refinancing or extending your existing facility. When looking at cashflow gaps, you may need to go back to your bank with adjustments to your existing facilities or request new facilities. Having a three-way budget to provide to the bank in support of the application is key.
Once a forecast is prepared in Excel, different scenarios can be built to allow the end user to see and compare the results of these scenarios in 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 we 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, implementing a dedicated forecasting software solution might be better.
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 and steer your way to prosperity or out of a hole.
