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.
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)?
- Mall Traffic share
- Shop Traffic conversion
- Gross Margin (Cost of Goods Sold)
- Sales staff commissions
- Expense Inflation
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.
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.
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.
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.
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.
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.
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.
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.
All we now need to do is press the ok button and Excel will run the process, do the calculations, store the 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.
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.
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.
We select the highlighted range and pick Data => What if Analysis => Data Tables.
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.
As discussed above, sales conversion rate goes in as an input to Row input cell.
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:
- Traffic, leads, prospects.
- Share of traffic & leads.
- Sales conversion of traffic & leads.
- Growth rate (fed by improvement in market share, leads, conversions).
- Ticket sizes & frequency of purchase.
- Expense inflation.
We track the following metrics:
- Operating Income
- Revenue Growth
- Sales teams
- Number of customers
- 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.