Management Consulting Tools – 5 Ways Consultants Use Spreadsheets (That Could Be Improved)

Of all the management consulting tools out there, the spreadsheet is possibly the most prevalent. Consultants use spreadsheets on a daily basis in the analysis stage of their projects, ultimately creating models that draw conclusions from a data set to inform the outcome of a project. Lately we’ve examined how the spreadsheet is becoming an outdated tool with alternatives that automate many of the things that must be done manually in spreadsheets. But in order to really understand how consultants would benefit from replacing spreadsheets with more updated tools, it helps to take a closer look at exactly what consultants do with Excel.

Here are a few examples of common ways consultants use spreadsheets and the problems that accompany them.

1. Model Creation

Consultants use spreadsheets to create data models, a combination of formulas that manipulate data to form the main analysis of a project. Models need to provide accurate conclusions and also be easily understandable and easy to update. Ideally, a completed model can be used as a standardized asset that a firm can use over and over again to solve the same problem.

The problem with creating data models in spreadsheets is that they are not easily shareable or repeatable. They frequently live on individual hard drives rather than being stored in a central database where they can be used again. Rather than allowing a firm to use and build on existing models, firms are often forced to recreate the wheel every time, costing them both time and money. Consultants need a tool that stores their models in the cloud, allowing for easy sharing within the firm so that models can be used repeatedly and the firm’s intellectual property can grow continuously.

2. Pivot Tables

Ask almost any consultant what his or her favorite Excel functions are, and pivot tables will almost certainly be one of them. Pivot tables have the ability to slice and dice data by any dimension of the data set, and they help consultants derive insights from data sets by comparing different parts of them. Ideally, they will help you understand what you can know from the data you have and where you need to fill in the gaps with further research.

But there are downsides to pivot tables. They are limited in capacity and only work smoothly with datasets that aren’t too big; when the data exceeds the capacity, Excel will slow down significantly or may crash. Additionally, some data isn’t conducive to pivot tables and must be converted into the correct format. Finally, pivot tables may not update when additional data is entered, requiring a manual update or an entirely new pivot table.

In place of Pivot Tables, what consultants need is

3. Data Cleansing

Because data is collected in all different formats, collecting data in a spreadsheet often means that a number of different formats end up in a single spreadsheet. Not only is it important to have consistent formatting for the sake of clarity, but Excel can actually only handle a limited number of unique formats in a worksheet and will crash if that limit is exceeded. Thus consultants need to be familiar with basic data cleaning functions: VALUE() for converting text to numbers, TEXT() for converting numbers to text, functions to remove duplicates and achieve consistent capitalization, etc.

The basic problem with data cleansing in spreadsheets is that although Excel has many shortcuts for doing so, consultants shouldn’t have to spend any time on data cleansing. Modern management consulting tools should have the capability to cleanse data as it is collected so that the format is consistent from the get-go. Any consultant who has experienced the joy of nested TRIM() or SUBSTITUTE() commands to account for user error and data entry oversight is likely ready for something different. Automatically clean data will save consultants valuable time that could be spent on analysis.

4. Basic Logic Functions

Creating a data model requires using a number of different Excel logic functions to manipulate the data and draw conclusions. Some of these include lookup and reference functions (VLOOKUP, HLOOKUP), math/trig functions (SUMIF, SUMPRODUCT), statistical functions (AVERAGEIF, STDEV), etc.

The problem with the logic functions in Excel is that these are highly subject to human error. The smallest error can have enormously harmful effects, as we have seen in the London Whale incident and many other cases. In order to avoid costly errors, consultants need a tool that will allow them to manipulate data without manual input of logic functions.

5. Charts & Graphs

Manual chart and graph creation is a large part of what consultants do, especially in their early years at a firm. New consultants at many large firms, for example, spend their first three years manipulating “chevrons” on PowerPoint slides trying to get the graphs and charts to look “just right.” While an attractive and easily understable output is desirable, current methods of manually tweaking PowerPoints are not cost-effective. Ideally, today’s consultants should be able to present straight from the data set, rather than wasting time putting together a presentation. At the very least, graphs and other visuals should be easily exportable from the data set.

Digital Transformation: The New Face of Management Consulting Tools

A closer examination of how consultants use spreadsheets clearly shows the gaps that must be filled. Spreadsheets may get the job done eventually, but they are error prone, time-consuming, and offer results that are ultimately not repeatable. A digitally transformed process uses management consulting tools that eliminate these manual and time-consuming steps that ultimately don’t add value to client engagements. Instead, consultants can automate analytics so they spend more time extracting insight from data and less time manipulating data to get to the insights.

Source: B2C