I have the dubious privilege of having been working prior to the invention of both the personal computer (PC) and applications such as spreadsheets and word processors. Since their arrival, there has been an explosion in the use of spreadsheets to plan all areas of businesses large and small. While this has delivered significant value it doesn’t come without some disadvantages. This blog describes these drawbacks and discusses one alternative planning platform, Anaplan, which can retain many of the advantages of spreadsheets while avoiding the negative aspects.
A brief history
Prior to the introduction of the PC, most planning was performed on paper or on mainframe computers. The effort involved in planning or rather re-planning on paper limited its use in this area. Mainframes were only available to the largest organisations and their cost and inflexibility along with the limitations of text based screens they were only used in a very few use cases such as MRP.
Then in the late 1970’s and early 1980’s personal computers from IBM, Microsoft, Apple and others arrived, along with the personal productivity applications including the spreadsheet. The low cost of the PC and the ease of use of the spreadsheet liberated the end users to be able to create their own solutions which resulted in an explosion in its use. The use cases spanned every area of business planning from financial to sales to supply chain and manufacturing. In many instances these solutions involved multiple linked spreadsheets of many megabytes each. I have over the years built spreadsheets for resource planning, long term costing and manufacturing yield analysis.
Why do planners like spreadsheets?
From what I have seen over the years and my own personal experience there are several different reasons why spreadsheets are so popular with planners.
The figure above summarises these and I will elaborate on each of these:
Flexibility
Spreadsheets are extremely configurable to meet the user’s requirements. They typically have built in functions to perform complex financial, mathematical and statistical calculations. On top of this even more sophisticated functionality can be provided via VBA based Add-Ins. Lookups and references to data in other worksheets or even other spreadsheets are powerful and often used features.
Familiarity
Spreadsheets have become ubiquitous within business and the number of different products in widespread use has reduced over the years to a handful of which Microsoft® Excel seems by far the most common. A word of warning however is that employers should not expect school leavers or even many graduates to be familiar with products such as Excel. Outside certain disciplines such as Business Management they aren’t!
Availability
Part of the reason why spreadsheets became so common is that the software was relatively lowly priced so that organisations could afford to deliver it to all employees – even Planners! The software often came as part of a suite of products including word processor etc.
Ease of use*
It is relatively easy to learn how to use spreadsheets and there also many free self-learning resources online. However, and the reason I have asterisked the box in the figure above, is that the majority of users have little idea of how to use any of the advanced functionality – even functions or Pivot tables. This can result in some very needlessly cumbersome spreadsheets.
Personal control
A planner’s job is to control some aspect of the business and as a result they also like to be in control. A spreadsheet is something they can own and develop themselves without having to be in thrall to the IT department.
Immediate results
In the past there was an expectation of planning runs taking a whole night to complete and planning cycles were often done weekly. In business now you could well have taken an order, picked it, despatched it and delivered it in that time. In the future you will probably have also manufactured it to order as well! Therefore there is a drive for the results to changes in inputs or decisions to be reflected much more quickly – preferably in real time. A spreadsheet, unless it has become too large, can deliver this; even using multiple cores on the task.
Communicating output
It is easy to create summary tables and/or charts in spreadsheets and these can be easily shared by email.
So what's the problem?
So on the face of it we have solutions that are easy to build, affordable and that planners like to use. It is no wonder weaning them off them is hard. But do we even need to? Are there downsides to these solutions? The truth is that there are several serious issues that arise at least one of which can and has can presented an existential threat to organisations.
|
Collaboration |
• It is difficult to effectively and safely share creation or modification of the plan with multiple collaborators. There is a real risk of corruption and with the exception of a couple of products you cannot have different users update different parts of the plan at the same time. • It is very hard to restrict visibility of subsets of the data for compliance and there is a lack of change audit. Often you will have different input spreadsheets for different parts of the business and users. • The different steps in the planning process need to be managed manually possibly leading to a lack of visibility and transparency as well as requiring a great deal of effort. |
|
Ownership |
• The advantage of personal control described above is a two edged sword. The other side is that a spreadsheet solution is typically created and maintained by one person – often over several years. They don’t have an incentive to document how the solution works and spreadsheets are not really self-documenting. So when they leave the organisation or move to another role the replacement is often left owning something that he doesn’t understand and for which there is no information to lean on. • There is usually no governance or change management process around a spreadsheet solution and the products being personal tools should not really be expected to support an Application Lifecycle Management process. |
|
Complexity/ Risk |
• It is too easy for inconsistencies & errors in formulas to occur and history is littered with examples of organisations that have been brought low by errors in spreadsheets – for example Conviviality falling into Administration (The Guardian) and Barclays being left with hundreds of Contracts they hadn’t intended to buy - The Register). I invite the reader to follow these links to understand where things went wrong. • One of the main issues is that there is insufficiently defined separation between the logic of the solution – i.e. the formulae and the data in the cells. Compounding this every cell can have a different formula and I have lost count of the number of times someone has replaced a formula in one cell with a value of their own choosing. • The nature of spreadsheets makes them very hard to audit and can only be a long laborious slog. • Commonly a spreadsheet starts off being relatively small and simple and grows in both size and complexity with time. It can reach a point where it becomes so complex and large that it is no longer understandable even to the original creator and performance on recalculation is also unacceptable. |
|
Familiarity breeds…. |
• Almost no spreadsheet creators have formal training – basically hacking their way around like an occasional golf player. • Maintenance of the spreadsheet(s) is often laborious and done to tight timelines – increasing chance of errors and giving no time for checks |
|
Functional Limitations |
• SC planning functions can be complex and hard to model in a spreadsheet requiring resort to VBA or an Add-In. |
The key premise
We can see from the above that there is strong motivation for planners to use spreadsheets but that this has serious negative consequences. So the key premise is:
In order to keep the planners on board we need solutions that preserve enough of the advantages to keep them on board while removing or mitigating the problems – so how do we achieve this?
In the remainder of this blog I will describe how one planning product, Anaplan, can achieve this.
A quick introduction to Anaplan
Anaplan is a cloud computing SaaS platform originally developed in the UK with a patented, in-memory calculation engine that came to market in 2010 and has experienced rapid growth since then.
It is one of the few planning products that was built from the ground up specifically for performing planning calculations rather than being a general purpose database or platform on which planning is then added on top. While in its first years most of the focus and implementations were in the Finance area it has since progressed into Sales, HR, Marketing and in the last few years Supply Chain planning.
Preserving the positives
The first vital consideration is to make sure that the go to solution retains enough of the key advantages of the spreadsheet to keep the planners on board.
|
Flexibility |
|
|
Familiarity / |
|
|
Ease of use |
|
|
Personal control |
|
|
Immediate results |
|
|
Communication |
|
Let’s illustrate some of the above with examples from Anaplan.
The figure below is a small subset of the pre-built models provided by Anaplan as part of the subscription. Customers can download these and modify them to their heart’s content. Note that Anaplan Partners also provide pre-built models:
The figure below shows an example of a Dashboard – the main Interface for users. In this case it is a browser intended for customers to be able to view their forecast and request adjustments. You can see that this is being displayed in a standard browser and Anaplan works with all the standard ones. There can be many different Dashboards for different steps in the process and different roles. There are four open here with one being displayed although it is possible to undock dashboards to display multiple at once.
Some key features of Anaplan dashboards I would like to emphasise are:
- Dashboards aren’t simply for displaying results: they can be used to enter data as well. In the above figure the blue text is in cells open for input.
- Different elements of the dashboard – charts and tables can be synchronised. In the above one product 12PC x 16BX truffles is being displayed. Using what are called page selectors a user can navigate to another product and all other synchronised elements will be updated as well. Using the selector we will change to select 4PC x 24BX truffles. The selection for the table below is also automatically synchronised.
- You should also note in the selector another useful feature for users. A list of data such as products or customers can be structured as a hierarchy with as many levels as required and this will be reflected in the drop downs. Furthermore if desired values can automatically be aggregated up the hierarchy. In the figure below we are displaying the totals for Boxes:
- Spreadsheets are somewhat inflexible in how data is displayed. The format of display and how the data is stored are combined in one. To display in a different way you usually have to resort to creating a Pivot table against the data but this doesn’t allow input of data into the new structure. Anaplan on the other hand separates the storage of the data from the manner in which it is displayed and that gives greater flexibility. Multiple views of a module can be pre-defined and users in Anaplan can also change the view dynamically while in the Dashboard. Let’s take the example of the table in the figure above. The different dimensions of the data in this module are Time, SKU, Customer and Line Items. In Anaplan Line Items are the planning values – input or calculated. In the original Pivot the Line Items are Rows, Time makes up the columns and different SKUs and Customers can be shown using the Selectors.
By swapping SKU and Line Item we can display SKUs in the rows and show one Line Item as a time. Note that data can still be input and the aggregation up the hierarchy:
Let’s have a look at how calculations are defined in Anaplan and why they are somewhat familiar to users. I will use the same table as above as the example.
The rows in the above are the different Line Items in this module and in the second column the formulae are defined. The similarity to Excel formulae should be immediately obvious. In the first a baseline forecast is being copied from another module. The match to week, sku and customer is performed implicitly. In the second this value is multiplied by a price from a third module. In several later line items a logical IF statement checking an input Boolean value in ‘Activate Override’ is being used to choose between alternative calculations in IF THEN ELSE statements. These are simple examples and much more complex calculations are possible. For example from statistical forecast calculations:
IF (IF history range holdout OR forecasted? THEN ROUND('log- Y*'[SELECT: Trend History Type.'De-Seasonalized History'] * 'm- Forecast Seasonality', 1, NEAREST) ELSE 0) < 0 THEN 0.0001 ELSE IF history range holdout OR forecasted? THEN ROUND('log- Y*'[SELECT: Trend History Type.'De-Seasonalized History'] * 'm- Forecast Seasonality', 1, NEAREST) ELSE 0
So to summarise there are products of which Anaplan is an example that both preserve many of the advantages of spreadsheets and indeed take the functionality well beyond them.
Mitigating the disadvantages
That is all very well but what I haven’t laid out as yet is how we eliminate or at least mitigate the inherent disadvantages of spreadsheets.
|
Collaboration |
• Multiple Users can be using solution at same time making it an enterprise rather than personal solution • Data can include text so users can post reasons for changes – see the CPFR example above. • There is a simple Workflow in Anaplan and a more powerful Enhanced Workflow is under development although Anaplan make no guarantee it will be made generally available. • Roles can be used to limit what data users can read and/or write. |
|
Ownership |
• The solution and data is stored in the Cloud and accessible to all authorised users and not on one persons laptop! • The overall solution can be split into multiple ‘models’ and ‘modules’ within a model with different people responsible for managing them. • ALM process available to enforce change governance. |
|
Complexity / |
• Models can be rolled back to any point of change in past • Clearer separation of data and formulae with all cells of a value (‘line item’) using the same formula. Dimension attributes such as Product stored in separate lists • Design of ‘tables’ (Modules) can be exported to spreadsheet for audit. • There is a history of who changed what data values and when. |
|
Familiarity breeds… |
• Implementation is an opportunity to ensure a sustainable pool of internal expertise is created. • A customer should be able to design the solution so it is largely self-maintaining – i.e. little maintenance or copy/paste each planning cycle. • Users can enter plans directly into the solution – perhaps as an evergreen plan and avoiding a cumbersome and error prone consolidation process. |
|
Functional limitations |
• The structure of Anaplan and the formulae and pre-supplied functions allow surprisingly sophisticated solutions to be created in SC including Statistical Forecasting, DRP and Shelf life planning • Anaplan released an Optimiser ‘engine’ in 2018 which can enable calculation of cost or profit optimised plans in many different scenarios. • Data can be readily exported and imported to leverage other analytics platforms such as R (https://www.r-project.org/) |
From the above I hope I have made the case that modern planning products such as Anaplan can mitigate the draw backs involved in the use of spreadsheets.
Challenges
Moving spreadsheet solutions to enterprise platforms is not without its challenges and these have to be recognised and addressed before embarking on this journey:
- The solution becomes an organisation owned tool rather than a personal one – change management. The owner of the spreadsheet solution must be treated with special sensitivity. They will normally have made a significant investment in the development of the solution and the loss of their creation can be demoralising. They should be heavily involved in the development of the new solution to overcome this.
- Spreadsheet users can be like addicts – they will need to be detoxed. Good training and communication plans are essential.
- Implementation will need to be treated as a project rather than a personal enterprise. This includes a defined governance, budget, project scope and objectives, sponsorship and change management.
In conclusion
Planning software products such as Anaplan that are built specifically to support planning that leverage leading edge in-memory analytic platforms and business user centric interfaces can be deployed to wean planners off spreadsheets.
If you would like to know how we can help you to create a solution that takes advantage of Anaplan’s advanced capabilities, we would be delighted to discuss your needs and of arranging a live demo at no cost to yourselves.
Drop me an email to steve.rampton@olivehorse.com and I do hope you enjoyed the read.
Anaplan Practice Lead - Olivehorse Consulting