In my last blog, I started to outline how planning becomes more complex as an organization grows. And as that complexity increases, how traditional planning tools such as Excel start to fail. But for many organizations the move to a more robust enterprise planning product has its own challenges, which we will look at later on. For now let’s continue with our story of XYW company as it becomes a listed, public company.
Planning as a public company
XYZ Company, our small manufacturer and re-seller of pens, continues to do well. As part of its strategy for growth it intends to expand the product range to include complimentary items such as pencils, rulers, rubbers and ink. The management team also plan to expand to multiple locations throughout the country, with some having local manufacturing capabilities. This expansion will be partially achieved through acquisition, funded by becoming a public company and raising capital by selling shares on the local stock exchange.
Planning has now become a more extensive process that not only has to cope with the new products and sites involved, but also with providing a clear, realistic strategy that communicates to investors ‘how’ and ‘when’ financial returns will be generated. As a consequence, the planning process includes all the current planning activities described in the previous blog, plus:
- Reviewing the market for writing products so management can decide where growth lies and how XYZ can take a major share (strategic planning);
- Analyzing what changes will be needed to the current operation in order to achieve the predicted market share (operational planning)
- Assessing how much additional funding will be required and what infra-structure will be used (capital planning).
- Once the above has been agreed the management team can then decide how it wants to allocate its resources (financial and HR planning) to make the revised operation a reality, and …
- How best to optimise its production and logistic capabilities (sales and operational planning) to maximise profitability
As the plan gets implemented, it’s now vital to:
- Track actual and forecast performance to see what is being achieved (forecasting)
- Identify and mitigate risks that could derail the plan (risk management)
- Reassess priorities so that adjustments can be made to either keep the plan on track or improve its performance.
As can be seen, planning is now a multi-user activity that encompasses a range of tasks that are way beyond the capabilities of a spreadsheet.
Before we look at what an enterprise planning system should allow, let’s first look at why spreadsheets fail. Despite their many excellent capabilities, spreadsheets fail at this level due to limitations caused by their fundamental design. This can be summarized as the following basic issues:
‘Single user’ means that only one person can update the contents of a spreadsheet file at a time. That’s not a problem for personal use, but when used as an enterprise planning application where actions are to be planned and consolidated from across the organization, this presents a major problem. To get round this limitation, the different parts if the plan are typically split into multiple files so that users are provided with just their portion. But even within small organizations the number of spreadsheets can rapidly increase to 10s or even 100s of files.
This proliferation of files now causes its own maintenance and control issues. For example, if you give someone a spreadsheet to fill in a budget or forecast, how do you know that the version they send to you is the latest one, and that it has the same contents as the one they are viewing now? The short answer is you don’t know as you can’t control when they are no longer allowed to change values entered, and what version they send to you.
Similarly, if you want to consolidate the answers, you’ll need a sheet that ‘links’ to all the other sheets to get the ‘latest’ data. But if that latest data is not actually the latest data (and you wouldn’t know), the integrity of the consolidated plan is always in question. And what happens if you issue a new sheet with new rules/accounts – what happens if they don’t use that version? For these reasons version control becomes an unmanageable nightmare.
Lack of workflow capabilities
As discussed earlier, most planning applications require a distinct set of operations to be carried out in a set order. For example, there is no point planning raw materials until after the sales forecast has been entered and approved. Similarly, when a forecast has been generated then it shouldn’t be changed until the next round of planning. In the same way, data on the current actual spend should be loaded before we ask departments to review and forecast spend in future periods.
The order in which things take place needs to be carefully controlled and orchestrated so that everyone knows what they need to do and when. And those overseeing the process need to know what the status is and where there may be ‘bottlenecks’ that are holding up others in the chain. None of these capabilities exist within a spreadsheet-based system.
All data held in a spreadsheet is typically referenced by an intersection of row, column and sheet. A particular cell reference ‘C23’ has no particular meaning – it is only by applying rules or macros that the content of any cell takes on its meaning. It is true that Excel has the capability to define range names, but as this facility involves a high degree of maintenance and can’t be used to track how Excel calculates a particular value, they are rarely used.
Cell references are fine when the system is dealing with a relatively simple analysis, such as displaying the P&L for a single company for one year. But when the data has to deal with multiple companies, with multiple versions of the data (actual, budget, forecast) over multiple years, with a mixture of Balance Sheet, P&L and statistical accounts, then controlling the meaning of a particular cell and the way it should be treated within a calculation becomes increasingly difficult.
For example, creating a variance or adding up accounts over time, needs knowledge about the account type in order to create the correct formula. Balance Sheet accounts can’t be accumulated over time; creating a budget/actual variance with P&L accounts, isn’t a simple subtraction, as you need to know whether the account is debit or credit. Copying formula between types will give the wrong answer so it’s not even safe to ‘drag’ formulae between rows and columns!
And if we insert a new row/column to cope with a new service/product line, there is a real danger that the rule logic will be compromised. If you’re lucky you will get a #VALUE error message to let you know there is an issue. If you’re unlucky, the error will go undetected until a crucial decision is taken and the error becomes apparent.
Limited Business View
Spreadsheets only hold one view of the data, unless that data is duplicated via cell links. This view is fixed by determining what the rows and columns represent. For example columns may be set up as time, with accounts displayed as rows, and the different sheets representing departments. Of course you can mix dimensions such as displaying actual and budget values within a particular time period as columns.
The way the spreadsheet is laid out gives you one view of the business. But what if you want a different view from the way the data was collected? For example, the budget/cash forecast will typically be entered with the columns representing each period next year. However reporting actual results will want to pick up just one of those budget periods (the ‘current’ period) and then compare it with actual and forecast results. Of course things are never simple as the forecast month that is picked up will change each month and so any cell references to the original budget will have to change.
And what if you want to analyze spend by market sector or by product? To do this requires a different view of the data, where row and columns represent different items. But that involves either duplicating content or creating a large number of error-prone cell links to switch the data around.
Finally as no organization is static, this network of interconnected sheets will then have to be updated for new departments/products, or changes to the business structure, without impacting integrity.
As mentioned earlier, these limitations are caused by the fundamental architecture of a spreadsheet and are the direct cause of a number of major issues when used for enterprise planning and reporting. Issues that will lead to wrong results, many of which will go undetected.
In the next blog, we will look at what makes an enterprise planning system different and the cost and complexity involved in implementing a solution.