Asking questions of a sales workbook without rebuilding it in a BI tool

Most sales teams under fifty people run on a workbook. It has a sheet per region, a sheet per rep, a summary tab and a set of formulas somebody built three years ago. It is usually right. What it cannot do is answer a question that nobody built a tab for. This guide sets out how to ask questions of that workbook, checked, without rebuilding it.

The workbook is the model

The columns the team has used for years are the data model. Region, rep, customer, product, net sales, month. Under different names, in a different order, on several sheets, but there. Reading the structure and mapping those columns once is the whole setup.

The four steps

1. Read the structure. Every sheet, its header row, its columns, a sample of values. Merged headers and subtotal rows are detected, not assumed away.
2. Map once. Which column is the region, which the rep, which the account, which the product, which the value, which the date. Guessed from the headers and confirmed by the person who keeps the workbook.
3. Validate. The totals on the summary tab against the sum of the detail sheets. The rep sheet against the region sheet. Every value a number. Every row with a region. The report says what agrees and what does not.
4. Ask. "Top ten accounts by growth in Texas." "Which reps have accounts with no orders this quarter." The question is turned into a calculation, the calculation runs on the rows, and the answer names the rows.

A worked example

A workbook with a Sales sheet of 4,000 rows and a Summary tab. The Summary says Q3 total is $1,412,300. The Sales sheet sums to $1,403,577.

The variance is the three blank-region rows plus a formula on the Summary tab that includes a row the Sales sheet no longer has. Both are found before the first question is asked. After the fix, "top ten accounts by growth in Texas" is answered from the 4,000 rows, with the ten rows listed.

Why this beats rebuilding

Rebuilding the workbook in a BI tool takes weeks, produces a model the team does not recognise, and is out of date the first time someone adds a column to the workbook. Reading the workbook as it is keeps the team's model, keeps the team's trust, and takes the new column as a mapping change.

Where it goes wrong

Formulas that reference other files. A cell pulling from another workbook reads as a value with no provenance. The validation report treats it as a value and says so.

Hidden sheets and rows. Hidden rows are still rows. The read includes them and the report counts them, so a filtered view is not mistaken for the data.

Two people's copies. The workbook on the shared drive and the one in a rep's inbox differ. Validate the one that is uploaded and name it in every answer.

Questions the data cannot answer. "What is our share of wallet" needs a wallet the workbook does not have. The right answer is that the data lacks it, not a guess.

Every week, the same workbook

Mapped once, the same workbook uploaded each week produces the same validation and answers the same questions on the new rows. Covirage reads workbooks this way, in the browser, and shows the validation report first. The Excel and Sheets page describes it, and you can upload a workbook and see the report on your own rows.