Using Excel for Forecasting – The Pros and Cons

Excel is widely used for forecasting and budgeting in businesses large and small, for a number of very good reasons. Excel is awesome for ad-hoc data analysis.

But for structured and repeatedly-used forecasting models, the limitations of Excel become apparent. These are more of an issue as a business increases in size, with revenue into the millions and higher. Other forecasting software is then worth considering, some of which can be used with Excel.


EXCEL'S STRENGTHS FOR FORECASTING

1. Excel is a very flexible tool, to build forecasting models to suit any business
2. Excel is typically already installed on the PCs of every person involved in forecasting, so is effectively free of charge
3. Every forecasting “contributor” out in the business has at least a basic understanding of how to use Excel
4. Using Excel can avoid the IT department’s involvement, notably to control models that need to be tweaked rapidly
5. Excel has very useful graphical facilities to present the results

There have been recent improvements in Excel relevant to forecasting and reporting, notably PowerQuery and other PowerBI functions. Excel is now at the 2016 version and online in Office 365.

But most of the key issues with using Excel for forecasting remain. So specialist forecasting software can provide significant improvement.


KEY LIMITATIONS OF EXCEL FOR FORECASTING

(1)    Excel is Fundamentally Single-user

Excel is designed to be used as a “personal productivity tool”. Any one spreadsheet file cannot be updated by more than one person simultaneously. Using Excel amongst multiple users in “collaborative forecasting” involves workarounds.

(2)    Drawbacks of Multiple Spreadsheets

A very common workaround is to split the model into multiple files, each to be updated separately and pulled together into a summary.

Correctly maintaining links with the correct spreadsheet version is a major issue.  Furthermore, contributors must be stopped from changing a spreadsheet format even slightly, or it ruins links. However the summary is structured, there’s risk of major error, and significant time needs to be expended to minimise errors.

(3)    No In-built Aggregation

Much of forecasting involves simple aggregation, for example:

• Adding together the sales projections for multiple products
• Adding the staff costs of multiple departments
• Adding multiple companies or business units

Excel has none of this structure built-in.  Formulae or pivot tables need to be set up, including cross-references across sheets.  The bigger the model, the more formulae and complexity, and the more difficult it is to ensure all ranges and links continue to work correctly.  Late nights. Weekends. Missed deadlines.

(4)    Lack of Audit Trail

Most formulae provide no immediate audit trail analysis of each aggregated figure. Pivot tables can help. But manually tracing through the origins of each figure becomes too time-consuming to do regularly.

There is also no audit trail of who made what changes, which can be important in larger situations.

(5)   Lack of an In-Built Database

Excel does not have its own underlying in-built database. There are two workarounds:

• It is possible to link Excel to a customised database, such as Microsoft Access. But this adds considerable complexity.  
• A “data sheet” can be created, but this has to be reliably populated and analysed, and can quickly become too large and unwieldy.

(6)    Comparison of Actual to Forecast/Budget Figures 

Actual data can be imported into Excel, but this is complicated to do in the equivalent way to forecasts and budgets at each level of each hierarchy.  Detailed comparison is similarly difficult between actual and forecast, or between versions of forecasts.

(7)    Calculation of Balance Sheet and Cash Flow 

Many forecasts are built with the P&L as the starting point. Cash flow is built using formulae, such as applying debtor days. The balance sheet is produced from both, adding in depreciation and other adjustments driven by formulae.

The beauty of Excel is total flexibility. But keeping control of formulae remains a major headache as the format of the P&L or cash flow sheets change.

(8)    Lack of Documentation

Excel spreadsheet models are notorious for lack of documentation of their logic, especially as they grow. The bigger the model, the more difficult it is for new personnel to understand it.  As time goes by, many Excel forecasting models remain untouched for fear of breaking them.

(9)    Lack of Workflow

Controlling the submission and approval of forecasts becomes a major headache as the number of contributors increases. It can be the biggest single problem in larger businesses.

Excel has no in-built workflow controls. Submissions have to be controlled and approved manually, or by superimposing separate workflow software.

 
ALTERNATIVES FOR FORECASTING

Specialist forecasting and budgeting software can overcome all these issues, and can be affordable for businesses large and small. Depending on the cost, good software will include some or all of:

• A central multi-dimensional database, with each dimension describing entities, time periods, products, etc. These are in multi-level hierarchies such as country/region/area.
• Versions of forecasts, budgets and actuals can be clearly identified
• Automatic and accurate aggregation at each level of the hierarchy, across all dimensions and versions
• Automatic analysis of composition of aggregated figures
• Actual figures can be imported in the same hierarchies as forecasts, allowing easy comparison.  Versions of forecasts can also be easily compared.
• The database and software is multi-user, allowing everyone who updates the figures to do so in parallel in one central easily-controllable place
• In “collaborative forecasting”, quick and automatic generation of templates for contributors’ submissions, which they can’t tamper
• Centralised formulae for producing cash flow and balance sheet, used repetitively and reliably
• With much functionality automated within the software, the need for application documentation is reduced. The model is easier to learn when personnel change, especially if someone has previous experience of the software.
• Workflow in-built to control submissions, approvals, and automating the chase of overdue actions.
• Changes to data can be logged in an audit trail by user.
• Some software uses Excel with an add-on for data templates, whilst some are used intuitively through a browser. This minimises the need for additional software on contributors’ devices, and minimises training.
• Excel can often be easily used for reporting, thereby maintaining its benefits
• Overall providing quicker, easier and more reliable submission control and analysis of the summary position

For larger businesses, a fully-functional system for collaborative forecasting may have an entry level for the software alone of £50,000 or more.  Nonetheless this can be money well spent, given the benefits.

At the other end of the spectrum, small businesses often used to use WinForecast, but this is no longer available or supported. The market is developing for equivalent tools.

In the mid-market there are now affordable solutions, either cheaper versions of the big software, or tools focusing on specific aspects such as controlling submissions.  Larger businesses can potentially re-grade to save money.

Otherwise there can be more efficient and effective ways of using Excel.

One way or another, the forecasting, budgeting and period-end analysis processes can be made quicker, more reliable, and better value for the business.


INTERESTED IN TALKING FURTHER?

Do leave a comment, or call Chris Challis on 07836 774439 to discuss your specific situation and potential solutions.