At BPM, many clients ask us for our views on their different alternatives for undertaking their business planning modelling. We are obviously of the belief that, all things considered, best practice spreadsheet models remain the most attractive, and the following comparison of the three main available solutions provides provides the reasoning behind this view:
- Freeform Spreadsheets are spreadsheets containing assumptions, calculations and outputs that are manually entered and constructed to forecast the performance of the business based on operational and accounting rules;
- Best Practice Spreadsheets are also spreadsheets containing assumptions, calculations and outputs. However, these models are constructed using bpmToolbox in line with the Best Practice Spreadsheet Modelling Standards to provide greater control, consistency and transparency over their development and use; and
- Fixed Systems are dedicated software packages such as Hyperion Planning® and SAP Planning®. These are constructed for the specific purpose of business planning, with coding and calculations performed outside of the Microsoft® suite of products.
The following table compares the three different approaches across the key construction and use parameters:
|Parameter||Freeform Spreadsheets||Best Practice Spreadsheets||Fixed Systems|
|Flexibility||Very High||Very High||Low|
|Development Time||User-Dependent||Low||Very High|
|Construction Cost||Very Low||Low||Very High|
|Client Control||Very High||Very High||Low|
|Customisation||Very High||Very High||Low|
|Dedicated Software||Not Required||Not Required||Required|
|Risk of Error||Very High||Medium||Low|
|Internal Development||Very High||Medium||Very Low|
|Security||Very Low||High||Very High|
|User Training||Medium||Low||Very High|
|Data Compatibility||Very High||Very High||Low|
|Calculation Methodology||Visible Excel Formulas||Visible Excel Formulas||Hidden Calculations|
|Implementation Time||Low||Very Low||High|
Each of these parameters is discussed below:
One of the main benefits of spreadsheets over Fixed Systems is the flexibility. There are very few constraints over what can be constructed and analysed within a spreadsheet.
Development time for freeform spreadsheets is dependent on the user, both in terms of skill and depth of analysis. The application of the Best Practice Spreadsheet Modelling Standards substantially reduces the development time of best practice spreadsheets, especially when combined with best practice modelling tools, even more so when compared to the development and implementation of fixed systems.
Construction cost is typically in line with the development time. However, freeform spreadsheets often have the lowest cost as these can be developed by in-house staff with a basic knowledge of spreadsheets. The construct cost of best practice spreadsheets is increased slightly by the knowledge required to adhere to the Best Practice Spreadsheet Modelling Standards, which either requires a trained in-house resource or external modellers to initially construct. This cost is still substantially below the cost of implementing a fixed system.
Client control is much lower for fixed systems due to the difficulty associated with making changes to the underlying logic and the difficulty associated with in-house users understanding the logic being used. This is much greater with spreadsheets as it usually only requires formula or layout adjustments. The flexibility of spreadsheets also allows the client to control the computations to match highly specific transactions or operations within their business.
As with client control, this is substantially lower in fixed systems compared to spreadsheets. This becomes an issue for those companies with highly specific operations, transactions or forecasting principles.
Fixed systems typically require dedicated software for all users, which may also involve a licensing arrangement. The universal nature of spreadsheets mean that they are unlikely to constitute any incremental cost as part of an implementation process.
Risk of error is reduced with fixed system, due to the specific nature of their construction. There is a great deal of risk associated with spreadsheets, often due to the ease with which they can be customised and as a result, over-complicated by an individual without the appropriate skill set. A lack of structure in construction is also a factor which adds to the risk. However, this component of the risk is mitigated by the structure implemented using the Best Practice Spreadsheet Modelling Standards.
Most office workers are familiar with freeform spreadsheets and internal development is prevalent, albeit providing very little control over quality. There are relatively few individuals trained and modelling in best practice and fewer still trained in the development of fixed systems.
The main advantage of fixed systems over freeform spreadsheets comes with the security of the overall system. Fixed systems are highly secure platforms with a well-protected software structure. The complexity of the system also prevents users attempting adjustments due to a lack of knowledge. Freeform spreadsheets are totally unsecure, making corruption and formula degradation highly likely. When the Best Practice Spreadsheet Modelling Standards are employed they harness the protection and security properties of spreadsheets in a regimented manner. This substantially lowers the security risk that typifies modern spreadsheet use.
User training might be required on freeform spreadsheets as the lack of principles often makes identification of assumptions and information flow difficult to understand without formal training. Best practice spreadsheets have a lower training requirement due to the information provided to users by such principles as purpose-based formatting and worksheet-specific layouts. There is a very high requirement for training with fixed systems, which can add to the implementation cost and the cost to maintain the overall system.
Spreadsheets are the universal business analysis tool and therefore information compatibility is automatically very high between organisations, and office applications, when using spreadsheets. Most fixed systems are integrated with spreadsheet products but data retrieval is not necessarily automatic and often troublesome.
A key benefit of the freeform and best practice spreadsheet approaches is that the user can often understand the logic that is being employed and hence how most figures have been derived. This benefit is often missing or highly inaccessible within fixed systems, which are often developed and maintained by external software programmers, with no clear calculation mechanism visible for the user to understand.
Implementation time consists of user training, data population, distribution of software, etc. This is substantially higher in fixed systems due to the use of a new platform and a lack of user understanding. Spreadsheets are a universal business analysis platform and therefore much of the implementation cost and training time is eliminated. This only leaves training on the specific planning model as the main component of implementation.