Data Tables, Data Analysis & Excel

4 mins read

In this post we look at how to use EXCEL’s Data Tables to stress test your financial model. This is our third post in our Data Analysis series using Excel spreadsheets. The series covers correlations, trailing correlations, histograms, pivot charts and data tables. See the following prior posts for data analysis context.

  1. Correlations basics
  2. Data Analysis. 5 tools in twenty minutes
  3. Data Tables introduction

Data tables as a scenario analysis tool

There are many questions that Excel can help you answer without spending a great deal of manual time spent in tweaking and hacking models or calculating and storing values one by one. Simple questions that allow you to see the impact of changes in prices, margins, customer profiles on profitability and valuations. Unfortunately while the tools exists, the only way you could use the above in Excel within your financial model is if you construct your model correctly. To construct your model correctly you have to start with a thesis for the business. What are the core value drivers for our retail shop (introduced in the previous post)?

  1. Mall Traffic share
  2. Shop Traffic conversion
  3. Gross Margin (Cost of Goods Sold)
  4. Sales staff commissions
  5. Expense Inflation

Model design

While building the model, you have to ensure that the each of the above is a variable input in the design of your model. While you can simulate each of these “designed variables” for a first pass its best to work with a static spreadsheet.


But don’t limit design to just inputs. What you want to see your model produce, its output, also drives design.

model outputs

For instance if the focus of the model (among other things) is to determine the number of transactions per day in order to breakeven, the model needs to project that. As per the above model for the shop to breakeven it needs to average between 2,000 to 2,500 tickets every month. Assuming 25 open days a month that translates into 800 to 1,000 sales transactions a day using the default baseline assumptions.

How would this change if the average ticket size goes up by 20% and the gross margin increases by another 5%?

Between the two ends (inputs and outputs) you have got the design figured out.

Now assume for a second that you have got a basic financial model out there that uses the above design parameters and produces the financials for a retail store shop in a mall in the Middle East. We are not looking for rocket science or a 10,000 cell, 100 tab Excel wizardry.  Just a simple model that projects financial statements for the next 3 years.

How can you use the Excel Data Table functionality to produce the answers we had shared in our previous post.

Income statement

Data Analysis and Data Tables. Getting started.

Here is the plan. We will start with a single dimension (one variable) and a single measurement metric. We will follow that up with two dimensions (two variables) and a single measurement metric. And we will finish with a single dimension (one variable) and multiple measurement metrics. For convenience we will refer to the two terms as variable and metric.

One variable one metric

For our first task we want to plot the impact of ticket sizes (bill, amount purchased) on the number of transactions we need in a year to breakeven. We start off by building a simple grid of varying ticket size value that we want to test and the breakeven transaction result.

Data table - building the grid

When we are done, Excel will take the input cells (Ticket sizes), plug them in the designated slot (that we need to identify) and store the result (the number of transactions required to breakeven) in the data table. In the image above, 250, 300, 350 and 400 are all static values. 31,018 refer to the cell where the number of break even transactions are calculated from the financial model.

Breakeven transactions

Our next step is to select the area marked by the border. From cell Q94 to cell R98. This will become our designated data table area.

What if analysis

With the cells selected click on the Data tab in the main tab bar above and pick What if Analysis. Within the drop down menu displayed select Data Tables.

Data table - column input cell

You will see small pop up window with an option to put in Row input cell and Column input cell. In this instance since we have laid out Ticket Sizes in a column we will pick column input cell.

Column input field

We now identify the cell in the spreadsheet model where Excel will take the values defined in the data table column above and plug them to recalculate the results from the financial model. We are  telling Excel to pick all the values between 250 and 400 one by one, recalculate the results (breakeven transactions needed) and store them in the data table.

Data table input cell

All we now need to do is press the ok button and Excel will run the process, do the calculations, store the results.

Data Table results

Migrating to two dimension analysis

Now that we have successfully tackled the one dimensional table, let’s look at a more complex scenario.

We want to examine the impact of ticket sizes and sales conversion rates on operating income of our retail shop.

2x2 data table

To do this we start off by building a grid. Within the grid we put ticket size as our master column and conversion rates as our master row.

data table -  conversion rate and ticket size

The variable that we want to track (operating income) is a calculated value. We put a cell reference at the underlined location to the point in our spreadsheet where operating income is calculated.

input cells

We select the highlighted range and pick Data => What if Analysis => Data Tables.

selection of input cells

The two inputs are the two locations in our spreadsheets where these drivers are defined. The first is the location which takes conversion rate as an input; the second is the location which takes ticket size as an input.

Row input cell selection

As discussed above, sales conversion rate goes in as an input to Row input cell.

Column input cell selection

While ticket size goes in as an input to Column input cell. We press ok and the Excel output is ready for review in our data table.

Here are the three original data tables from our previous post that led to this discussion. Go ahead and see if you can build them by yourself and on your own.

Drivers & metrics

For a typical startup financial model we model the following core drivers:

  1. Traffic, leads, prospects.
  2. Share of traffic & leads.
  3. Sales conversion of traffic & leads.
  4. Growth rate (fed by improvement in market share, leads, conversions).
  5. Ticket sizes & frequency of purchase.
  6. Resources.
  7. Expense inflation. 

We track the following metrics:

  1. Revenues
  2. Operating Income
  3. Revenue Growth
  4. Valuation
  5. Breakeven
  6. Sales teams
  7. Number of customers
  8. Fixed costs

You can mix and match these to produce your own analysis as well as identify the areas of focus. Drives and metrics that can mean the difference between success and failure.

Comments are closed.