Excel Sales Forecasting Spreadsheets

Excel spreadsheets for sales forecasting - a good idea?

Excel was never designed as a multi-user solution or as a specific sales forecasting solution.  So applying Excel to the problem invariably involves compromise, limited functionality and significant extra manual workload just to keep the system up to date and resilient.

Yet sales forecasting is a critical business process.  Inaccurate forecasts cost money.  Surely Excel should be used only if it is 100% best for the job or there is no alternative option?

The limitations of Excel for your sales forecasting solution, all of which Prophecy addresses, are therefore:

  • no single view of the truth
  • single user
  • potential formula errors
  • inflexible views of the data
  • no forecaster aids
  • cumbersome / error prone consolidation
  • insecure
  • no automatic data loading
  • difficult to add new products / customers
  • difficult to reorganise hierarchies - e.g. assigning customer from one forecaster to another
  • complex, time consuming and error prone year end procedures
  • does not support multi-level forecasting

Please review our detailed comparison between forecasting sales using Excel and sales forecasting using Prophecy in the matrix below.

To evaluate Prophecy further, please view our self-running web demo, download an evaluation system or better still contact us directly to explore further how we can help you.

Requirement

Excel Spreadsheets (the problem..)

Prophecy for Windows (the solution..)

Easy to use ... part 1 It is true that many users are happy(-ish) using Excel as a personal productivity tool.

However, it is difficult and time consuming to tie down an Excel spreadsheet so that users cannot  delete or change formulae or rows / columns which they should not be able to change.
Prophecy was designed from the ground upwards for business users, not IT experts or spreadsheet gurus.

It has a carefully thought out, proven set of forecasting-specific features which help business users productively develop defensible forecasts.

Using the Prophecy Client software it is impossible for a forecaster to inadvertently delete a product or customer, overwrite a formula or data which they do not have rights to.
Easy to use ... part 2 Forecasting spreadsheets in Excel are typically the same, largely fixed template redistributed to each user.  They therefore have, for example, a large number of redundant rows and columns for products that are not actually sold in the particular customer being forecasted.  Navigation and location of the data you want is clumsy, frustrating and time-consuming. Prophecy lets you view and forecast just the products and customers you select.  Your working view typically fits on a single screen.  Prophecy contains several tools and techniques, such as the report navigator and drilldown, which let you navigate the forecasts quickly, simply and clearly.

You can drill up or down the products or customer hierarchies to see less/more detail with just a mouse-click.
Easy to use ... part 3 Excel spreadsheets are fixed and inflexible.  Pivot tables are a great idea, but they are read-only and limited. Prophecy lets you easily create your own forecast update and report layouts by separating data storage and data presentation.

Click and point techniques allow all users to design their own layouts without the need for special report writing syntax or skills.  Prophecy reports work like read-write, drillable pivot tables on steroids!
Supports multiple users without data fragmentation or cumbersome manual consolidations Not supported. Spreadsheets are single-user productivity tools.  They do not support concurrent access except on a read-only basis.

Implementing multi-user forecasting in spreadsheets inevitably means cumbersome data fragmentation and error-prone, time-consuming consolidation processes which cause needless delays and errors.
Yes, out of the box.   Prophecy fully supports multi-user concurrent read-write access without the need for individual per-user files or databases or manual consolidations.

Forecast changes are instantly consolidated as they are saved, so the database is always real-time correct.

Note that not all vendors of sales forecasting applications are able to make this claim!
Supports forecasting by customer and profit through to profit At a very basic level, yes, but back solving is inflexibly implemented.  It is too easy for users to inadvertently overwrite formulae (impossible in Prophecy).  Formulae are mono-directional (i.e. Volume to Profit yes, Profit to Volume no)..

Also, consider the maintenance required - not easy to automate updating costs or cost history.

Consolidation becomes even more complex and error-prone.
Prophecy lets you import the components of profit (prices / costs etc.) from disparate data sources.

It lets you forecast volumes and prices (or any other 'facts' you choose for your database) and automatically solves the dependent facts in real time, up and down the product and customer hierarchies.

You can also 'back-solve' - change profit and see what volume will be required to deliver it.   (Subject to your security settings.)

In Prophecy, all (relevant) cells can be modelled and compared with Budgets or previous years - product totals, time totals, Profit back to Volume, percent year over year or versus Budget. Prophecy always 'knows' the dependent cells and hierarchically solves them.

Generate any report format you like
YES - you can generate any format you like, if you're prepared to work at it!  But in a multi-user forecasting environment you are probably constrained to forcing all users to use a standard format.  You could then use formula links which reference the standard layout to generate new formats.  But the spaghetti would get ever more complex and difficult to maintain!

Maintainability when the data or product/customer structures change is a major issue. Data duplication is also likely to occur across different spreadsheets.

Pivot tables inspired Prophecy's report and update screens but only as a starting point!

It is hard or impossible to securely hide or protect certain parts of spreadsheets.
YES - Prophecy has a very powerful and flexible report generator that any PC user will find easy to use.

It uses a 'pivot table paradigm, similar but more appropriately implemented than Excel spreadsheets.   Prophecy pivot tables support drag and drop, multiple nesting levels, drilling up and down hierarchies using mouse clicks, multiple level undo/redo, individual cell or rules-based cell colouring, linked 'live' graphs and cell change indicators.  Also it is read-write - so there is no difference between a report screen and an update screen, they are one and the same!

There are no report maintenance issues - Prophecy reports always replay with the latest data in the database and are filtered using the latest security settings each time a user replays them.
Generate statistical forecasts in a way that is helpful to non-statistically literate business forecasters. You can use spreadsheets to generate forecasts using basic statistical algorithms such as linear regression.

But you're on your own with these, they can be difficult to set up and it's down to you to use and interpret the resulting control statistics wisely.
Prophecy is the ideal tool for 'judgemental' / 'managerial' forecasting.   At the same time, Prophecy supports all the standard statistical forecasting algorithms and offers 3 different ways of delivering them  - on-demand, batch and wizard driven.

Prophecy helps non-expert users use statistical forecasts more safely by using a 'wizard' driven approach combined with 'expert system' textual judgements on how reliably the statistical forecasts describe the historical data.

Latest actuals can be loaded into the system via a simple import procedure. The only way of automating this for spreadsheets  is via ODBC queries and complex lookups between the query results and the formatted cells in the forecasting spreadsheets.

And you still need to aggregate over the hierarchies so that your users can see the data at any level of product and customer.

In a multi-user environment there are likely to be many spreadsheets that will need to be maintained in this way.
A single Prophecy script will automate your period-end procedure.   You can run it from the Prophecy Database Manager, click a Windows shortcut icon or even schedule it using the Windows Task Scheduler.

A script can read in any number of data files, solve missing data (e.g. calculate actual price from volume and revenue actuals) and aggregate up over the product and customer hierarchies.
Forecast, analyse and report at any level of product and customer detail. Theoretically this is possible, but consider what is involved in adding new products or customers to everyone's spreadsheet.

No real-time consolidation, so latency is introduced to the forecasting process - i.e. you have to wait for the consolidation to find out how your forecast compares to the business plan at more aggregate levels.
Prophecy uses multi-level product and customer hierarchies, allowing you to forecast, report and analyse on your actuals, forecasts and budget data at any relevant level of detail.

You can drill up and down the hierarchies using your mouse.

All consolidation of forecast changes you make is in real-time.
New products or customers can be easily added. Not easy!   Lots of maintenance of individual spreadsheets.   Possibly requires updating of a large number of spreadsheet formula ranges.   Error prone, time consuming. New products or customers can be added (by the Database Manager) at any time, either by click and point in the Prophecy Database Manager application or by reading in an external hierarchy file.
Methods for automatically identifying 'risky' forecasts. If you decide to have a go at implementing this feature in Excel, we applaud you! Prophecy has a 'Prophecy Advisor' which
  • dynamically analyses the forecast as you enter it
  • writes a descriptive commentary in real time, as you forecast
  • highlights forecasts which are outside of previous history
  • makes alternate suggestions based on statistical algorithms and recent running rates.
Security - controlling who can see or change each item Very tricky, cumbersome and unreliable to implement across multiple spreadsheets, tabs, cell ranges, rows or columns.   Only for the very, very persistent! The Prophecy security model lets you set a user's access by product, customer, measure and user-type.   Time periods which are actuals (i.e. history) are automatically protected.

Prophecy security is low-maintenance, but highly granular.

You can even grant or withdraw individual power-user features on a per-user basis.
All cells are 'active' - i.e. you can change any cell (subject to your security) and back solve, seamlessly and in real time. Cell backsolving can be performed, but requires detailed set-up and manual processes each time. YES - standard in the package, including all report totals (time, measure, products, customers), percent comparisons and unit variances.
Easy to track how forecasts and actuals have changed through time Possible, but not easy.   The spreadsheet will get very large (and cumbersome / difficult to navigate). Yes.   You decide how many 'rolling' forecast versions you require for your database.

You can analyse and graph any number of versions within a Prophecy report, allowing you to audit how the forecasts have changed through time (and assess whether your forecasters have got more accurate nearer the point of sale).

In addition, you can capture every single forecast change, who made it and when, to an external SQL database table.
Accuracy Analysis instantly available NO - unless you are highly disciplined in archiving past forecasts and have the spreadsheet skills to compare them.

Not easy even then, without making the spreadsheet large, cumbersome and difficult to navigate.

Accuracy analysis has to be easy, otherwise it won't get done, and the lessons of history will be lost.
Accuracy analysis as a routine is essential for improving forecast accuracy, because it allows you to identify and learn from your mistakes.

In Prophecy you can perform an accuracy analysis for any selection of products and customers, for any time horizon and business measure in the system.

You can report on all the data, or just the exceptions.   Statistics are provided which help you assess whether you have a bias towards over - or under-forecasting.
Top

More Information:  This page compares forecasting with Excel versus forecasting with Prophecy at the 'nuts and bolts' end.  For a more conceptual case for moving to a purpose-built forecasting software solution please see our whitepapers page

Quicklink
: The Case for a Specialised Sales Forecasting Software Solution.

Speak to an expert now. Call : +44 (0)1494 785574

This web site does not use cookies.  Please click here for our full Privacy Policy.