Back when we first started this website, I wrote a post with a downloadable excel file that walks the reader through how to create a one variable data table. This is a long overdue follow up to that post to show you how to create a two-variable data table. This will be part 2 of what is now a three-part series on data tables. Part 3 explains how to combine and view multiple results within the same cell of a two-variable data table and you can read the post and watch the video here.
Two Variable Data Tables
Two variable data tables allow the user to view a range of outcomes for a return metric if two of the assumptions were to be altered. You can download the template and watch the video and/or read the step-by-step guide below.
Step-by-step Guide –
Step 1: Link the Return Metric You Want to Analyze
Find an area on your model where you want to set up the data table and use the top left cell (In the template, this would be cell F23) to link to a return metric. Click the cell and type the equals sign (=) and click the cell with the return metric you want to analyze. In our template this would be either cell D14, D15, or D16.
Step 2: Choose Two Input Assumptions (Variables) and Decide on the Ranges You Would Like to Test
In the template in this post, we have four variables we can choose from: the purchase price, inflation, exit year, and exit cap rate. For this example, let’s pick inflation and the exit cap rate.
Directly to the right of the cell where you linked the return metric you want to test, input any number of potential annual inflation rates you like to analyze. For every new inflation rate you want to test, move one cell to the right and input the new number. If you are working within the template, this area is from cell G23 to M23.
Directly below the cell where you linked the return metric you want to test, input any number of exit cap rates you want to analyze with each new cap rate entered in the cell below (F24 to F32 in the template).
Step 3: Highlight the Area of The Data Table and Open the Data Table Prompt
Highlight the entire rectangle/square of cells that include the linked return metric cell in the top left, the bottom cell that contains the last exit cap rate in the range you input underneath the return metric, and the cell that contains the last inflation number that is the furthest to the right from the return metric. In the template, this area is between cell F23 and M32. See the screen shot from the video below for clarification:
Now, click ‘Data’ at the very top in the green bar > What-if Analysis > Data Table
To do the above without using the mouse type Alt > A > W > T
Step 4: Input the cell references for the column variables and row variables
The final step is to input the cell containing the assumption being tested in the row going across the top in the ‘Row input cell’ (D9 in the template) and to input the cell with the assumption being tested in the column going down the left-hand side in the ‘Column input cell’ (D11 in the template), then click ok and the results should appear in you input table. You may need to format the table if the answers are not displayed in the way you prefer.
Hope this data table lesson was helpful. If the write-up was not completely clear for you, check out the video. Also, click here to see part III, which will teach you how to test multiple return metrics within one cell.
About the Author: Michael Belasco has over nine years of real estate and construction experience. He currently works for a global real estate investment, development, and asset management firm in San Francisco managing large scale development projects in the city. Michael has both an MBA and Master in Real Estate with a concentration in Real Estate Finance from Cornell University.