Why simple spreadsheet software is good for business planning

Studying this technical article and answering the related questions can count towards your verifiable CPD if you are following the unit route to CPD and the content is relevant to your learning and development needs. One hour of learning equates to one unit of CPD. We'd suggest that you use this as a guide when allocating yourself CPD units.

This article was first published in the October 2016 international edition of Accounting and Business magazine.

According to a 2014 survey by BARC Business Intelligence Forum, Excel is by far the most popular business tool for planning purposes in Germany, Austria and Switzerland, where the spreadsheet usage rate is 86% as against 63% for specialised planning and business intelligence tools. 

The reasons are straightforward: Excel is well-known software that sits on computers around the world. It is very flexible, allowing solutions to be created for many functions, such as questionnaire design, analysis of large amounts of data, visualisation of results, etc. Moreover, you can handle the whole process in-house – from the initial design of input, to calculation and output, as well as later adjustments. You don’t have to rely on an external software supplier who might disappear from the scene.

But as we all know, Excel also has downsides: spreadsheet tools are only as reliable as the data put into them. It is easy to imagine the risks that arise from even small mistakes in spreadsheets that are used as the basis for planning and strategic decisions. As journalist and writer Tim Worstall has said, ‘Microsoft’s Excel might be the most dangerous software on the planet.’ The European Spreadsheet Risks Interest Group lists various ‘horror stories’ on its website.

So does this mean that managers undertaking complex calculations with wide implications should use a professional software platform? Data warehouses and planning systems with sophisticated capabilities for data analysis are far more reliable. They normally have data interfaces with many parts of the organisation, which reduces errors in data entry and transfer. And all functions, including data retrievals, calculations and analyses, are professionally designed and tested.

However, businesses also have to perform many calculations that don’t easily fit into standard enterprise planning and data warehouse solutions, such as calculations for new products and feasibility studies for strategic options. These calculations require high levels of flexibility: they often come with many variables, and the business case might still change, which will mean changes to the calculations. These auxiliary calculations would be disproportionally complex to integrate into the main system.

Business warehouse and enterprise planning systems normally have finite levels of standardisation. This makes it difficult to adjust calculations – for example, through various project phases – so spreadsheet calculations can be a helpful alternative. An experienced user can easily adapt every part of the calculation without the help of an outside software vendor or even a programmer. And spreadsheet tools support a number of variants and scenarios too. Used in this way, spreadsheets can be excellent support tools to facilitate strategic decisions. 

Explaining the logic

Starting from an empty sheet, the project team has to think through the business logic of the particular option first. Only then can they model this logic with formulas and calculations. They will immediately see the results of their calculations and can test them against the expected business logic. 

Spreadsheets are often criticised as being unhelpful for strategic planning. But this lack of sophistication can actually benefit the project, as the team is forced to think through the whole business logic in every detail. Since they developed the whole calculation by themselves, they will be able to explain every single aspect to other stakeholders. This will lead to a more insightful discussion with executives and decision-makers. Conversely, the actual calculations within enterprise planning software are often perceived as some sort of black box; few specialists would be able to explain them in detail. 

The same holds true for discussing any initiative throughout the organisation. Spreadsheets are like a common language. This is a huge advantage for strategic decisions. It also eliminates a significant proportion of perceived uncertainty within the financials. There may still be uncertainty about the assumptions and parameters, but the whole team can agree on how the project should be calculated. 

Earning trust

There is one essential pre-condition for spreadsheet tools to truly support strategic decision-making: the tool itself has to earn the trust of all stakeholders and audiences of the results. Only a tool that everybody accepts as reliable will lead to a discussion that focuses on the strategic issues instead of the calculations. Here are some established best practices for building financial models.

• Make sure your tool looks professional and trustworthy. It has to work neatly without any error messages. Have a user-friendly interface for data entries and results. The actual calculations should take place on other sheets. 
• Develop your own general system and structure for Excel tools and rigorously apply it: colour codes, naming conventions, formats, table structures, etc. Other users will then find it easier to orient themselves in your tools.
• Write documentation for all tools. Use it to explain what the tool calculates where, how and why.
• Always have somebody else check your tool from top to bottom. This may even be a job for external consultants. That way you can eliminate existing errors, get valuable advice for improvements and significantly increase your tool’s credibility.
• Generously offer to explain your tool in any detail to everybody interested. This is about credibility again. You also have to make sure that your tool is well understood by all users and recipients of the results.
• Do sensitivity tests, including for limited ranges. Do your results change as expected if you change particular input values? Document the tests and tell everybody about them. 

Dagmar Recklies is a consultant in corporate strategy, strategic positioning and digital communication