Excel spreadsheets for sales forecasting - valid solution?
Many companies do use Excel spreadsheets for sales forecasting. And struggle.
They struggle because Excel is a fantastic personal productivity tool for numeric data. But it was never designed as a multi-user solution or as a specific sales forecasting solution. So applying Excel to the problem invariably involves big compromises, limited functionality and significant extra manual workload just to keep the system going.
And as we all know, there are other issues common to spreadsheet based data:
|
|
Delivering a secure, robust, appropriately functional and reliable solution for sales forecasting is considerably more complex (and therefore expensive / time-consuming) than it seems at first sight. We know.... we wrote Prophecy!
The table below compares Prophecy to delivering the same sales forecasting features in an Excel spreadsheet or other home grown forecasting system. The table is by no means exhaustive!
Look out for the
buttons in the table below for on-line demos which start immediately, require no
install and run in your web browser.
|
Requirement |
Prophecy for Windows |
Excel Spreadsheets |
||||||
|---|---|---|---|---|---|---|---|---|
| Easy to use for business users |
Prophecy was designed from the ground upwards for business users, not IT experts
or spreadsheet gurus. It is therefore exceptionally easy to use, and 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.
|
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. Excel is not a forecasting system and, other than its statistical functions, has very little forecasting-specific functionality. |
||||||
| Supports multiple users without data fragmentation or cumbersome manual consolidations |
Yes, out of the box. Prophecy supports multi-user concurrent read-write access
without the need for individual per-user files or databases or manual consolidations.
Even for users accessing Prophecy over the
web. Note that not all vendors of sales forecasting applications are able to make this claim! |
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 manual consolidations. | ||||||
| Supports forecasting and modelling through to profit |
Prophecy lets you import the components of profit (prices / costs etc.) from disparate
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.
|
At a very basic level, yes, but backsolving 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 access to updated cost actuals or history. Consolidation becomes even more complex and error-prone. |
||||||
|
Generate any report format you like |
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 better implemented than Excel spreadsheets. The Prophecy pivot table supports drag and drop, multiple nesting levels, drill 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. 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.
|
YES - you can generate any format you like, if you're prepared to work at it! Maintainability when the data changes 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. |
||||||
| Generate statistical forecasts in a way that is helpful to non-statistically literate business forecasters. |
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. These demos show Prophecy's 3 statistical forecasting modes:
|
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. |
||||||
| Latest actuals can be loaded into the system via a simple import procedure. |
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, as shown in the demo:
|
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. |
||||||
| Forecast, analyse and report at any level of product and customer detail. |
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.
|
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. |
||||||
| New products or customers can be easily added. | 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. | Not easy! Lots of maintenance of individual spreadsheets. Possibly requires updating of a large number of spreadsheet formula ranges. Error prone, time consuming. | ||||||
| Forecast over the web |
Yes. The Prophecy Client software can operate against a drive letter and
path (direct mode) or against a web server URL over the internet (
web mode ). The physical database is identical, and supports concurrent access by both web users and direct mode users. There is no loss of functionality for web based users. |
Very limited. Can 'log out' and 'log in' spreadsheets from a web portal,
but this is cumbersome and still requires subsequent consolidations etc.. Excel spreadsheets can get quite large, and the whole spreadsheet is transmitted each time. In Prophecy, only the data the user has requested is transmitted. |
||||||
| Identify 'risky' forecasts. |
Prophecy has a 'Prophecy Advisor' which
|
If you decide to have a go at replicating this feature in Excel, we applaud you! | ||||||
| Security - controlling who can see or change each item |
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. |
Very tricky, cumbersome and unreliable to implement across multiple spreadsheets, tabs, cell ranges, rows or columns. Only for the very, very persistent! | ||||||
| All cells are 'active' - i.e. you can change any cell (subject to your security) and back solve, seamlessly and in real time. | YES - standard in the package, including all report totals (time, measure, products, customers), percent comparisons and unit variances. | Cell backsolving can be performed, but requires detailed set-up and manual processes each time. | ||||||
| Easy to track how forecasts and actuals have changed through time |
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. |
Possible, but not easy. The spreadsheet will get very large (and cumbersome / difficult to navigate). | ||||||
| Accuracy Analysis instantly available |
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.
|
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. |
The Bottom Line...
- Forecasting and planning involves many users. Spreadsheets are essentially single-user productivity tools whereas Prophecy was designed from the ground up as a multi-user system.
- Spreadsheets do not naturally handle hierarchical data. Prophecy is designed specifically to handle multi-level, multi-user forecasting and planning over large hierarchies of products and customers, with no need for manual consolidation, ever. More iterations of budgets and forecasts can therefore be performed, more powerfully, and in less time. Resulting in better business plans and more accurate sales forecasts.
- Spreadsheets cause data duplication and fragmentation. Prophecy gives all users a single, consistent view of the truth.
- Spreadsheet systems are inherently insecure. Formulae can be overwritten or changed, it is hard to protect sensitive data. Also, spreadsheets can 'walk', together with all that sensitive data!
- Spreadsheet forecasting systems require extensive manual maintenance. Prophecy Filters and Scripts allow you to automate all maintenance of your forecasting and planning system, saving you time,hassle and minimising the chances of errors.
- You may think you won't need some of the functionality listed on this page. But remember, Prophecy contains many techniques which have proven effectiveness in real-world sales forecasting situations. Time spent on forecasting should be invested in improving quality and accuracy, not mundane, non-value added data management tasks. And isn't it reassuring to know that the extra functionality is there if and when you need it?
- Prophecy is a robust and proven application which has been under continuous development since its launch in 1998. It is written in 100% C++ and contains over 150,000 lines of carefully constructed code. It has already been thoroughly tested and 'debugged' through being worked hard by real users in numerous live implementations around the world. It delivers far more functionality than it is possible or cost-effective to deliver via spreadsheets or home grown developments. Why spend considerable time and effort to re-invent a less effective wheel, when Prophecy already exists and provides clearly proven benefits to existing customers?
- Don't delay the benefit stream. You can implement Prophecy and improve your forecasting process far earlier and with less resource than by developing, testing, debugging, re-testing, and documenting a home grown solution, only to end up with something which is much less functional than Prophecy, and far more heavily dependent on key internal developers, who may 'up and leave'. Customers have been using Prophecy since 1998 - please give us the opportunity to show you Prophecy at first hand so that you can judge for yourself!
Top
Speak to an expert now. Call : +44 (0)1494 785574
Data Perceptions
