- Single-User - With a spreadsheet only one person can update the contents at a time. To get round this limitation, spreadsheets are typically split into multiple files so that users are provided with just their portion of the data for updating. But even with small organizations the number of spreadsheets can rapidly increase to 10s or even 100s of files, creating a huge ‘version control’ issue.
- Lack of workflow capabilities - The budgeting process requires 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 budget has been entered and approved. When a budget has been submitted then it shouldn’t be changed unless it is authorised by the administrator. Similarly, data on current actual spend and forecasts may be required before departments are asked to review and submit their budget. The only way this can be controlled using spreadsheets is for an administrator to manually issue individual sheets as required and chase up responses.
- Cell meaning - 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. This means each cell rule needs to be carefully checked that it correctly treats statistical and different types of financial account. Dragging a formula between cells is bound to lead to errors – many of which will go undetected.
- 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. If an analysis is required by market sector or product, then more spreadsheets will need to be created where row and columns represent different items. This also will involve either duplicating the content or creating a large number of error-prone cell links to switch the data around.
- Multi-dimensional - Modern planning systems are set up in terms of the business dimensions in which they operate. This will include the organization structure; the accounts used to budget and report results; the time periods in which it reports – e.g. weekly, monthly, season; the versions of data to be held e.g. actual, budget, forecast; any line of business or major product grouping. It is the intersection of these ‘dimensions’ that define a particular value – for example, actual sales of product A by department North, in July 2014. Each item is a member of a business dimension.
When it comes to reporting, the user simply states what dimension(s) are to form the column, row and ‘off grid’ member. For example, a report can be set up to show actual vs budget for each month as columns; the accounts making up the P&L as rows, and the ‘off-grid’ member can be department, which means the report will display each department on a new page.
- Business hierarchies - Dimension members can be arranged as one of more hierarchies. For example, the total company member can be defined as the aggregation of 4 divisions, which themselves can be defined as the aggregation of other departments. These hierarchies can then be used to consolidate data from those entities at the bottom of the structure to give intermediate consolidated results.
- Named-based rules - Rules are set up using the names of dimensions and their members. This not only makes them easy to understand, but also makes it easy to add new members as the existing rules don’t change and the integrity of results is preserved.
- Multi-user, role based security - Enterprise systems recognize that multiple people will be accessing them, each with different roles and responsibilities. This means that a system can be used by many people from across the organization, but that each person is automatically controlled in terms of access to the data and features they are allowed to use.
- Unlimited size - Today’s systems have limits that are way greater than those found in a spreadsheet. It means that the design of the system need not be limited by the technology, although performance may be compromised if they are allowed to become too large.
- Financial Intelligence - Financial intelligence refers to the ability to deal with financial numbers. That includes dealing with different currencies; performing currency conversion at multiple rate types, e.g. average, opening, closing rate; detecting and posting exchange gain/losses, as well as the recognition of different account types, e.g. Balance Sheet, P&L and Statistical types. This greatly simplifies the setting up of calculation rules as these then automatically deal with data in the right way – e.g. whether accounts are summed over time, converted or consolidated.
- Process control - The last capability is in the area of process control, i.e. when users are allowed to enter data, and who should then approve their submissions before it is consolidated with other results. For example, budgets may be required by a certain date. Performance Management systems automatically remind users and chase them up as deadlines approach. As they complete their submissions these systems validate their answers and then inform those involved in approval. Once submitted users are no longer allowed to enter or change data unless the approver rejects their submissions.
At all times administrators are able to see the status of the data – has data entry started, has it been approved, is the submission late, and so on. They can also see audit trails of how data has changed over time.With such a contrast between spreadsheets and enterprise systems, it’s surprising that 50% of organisations still stick with spreadsheet-based budgeting. Part of the reason for this is that in the past, enterprise systems were both complex and expensive to buy as well as maintain. But as you’ll see in the next blog, things are changing fast.
- Assess the current use of technology for budgeting. If spreadsheets, how many are there?
- Identify the major causes of delay in capturing budgets and producing results