The next common error I’d like to address is the flawed Data Table.

Excel has a wonderful feature that automatically summarizes the results of your model under a range of scenarios.  The Excel menu item Data Tables lets you see how a given output cell would be affected if you were to modify one or two input cells.  The problem is that many DealMinions don’t know how to avoid common mistakes with data tables.  I used to be so concerned about “unreliable” data tables that I would build formulas long-hand at all costs.  The DCF data table beginning in cell G351 of the Level I Knowledge Base model (highlighted below) provides an example of this masochistic formula. Click here to open the model. 

Let’s try Excel’s Data Table feature instead.  In this case, we are running a data table on the DCF Value per Share (cell B354).  We want to see how it varies if we change the assumptions for Terminal Value Growth Rate (cell I336) and Weighted Average Cost of Capital (cell I338). 

1. Link the top left cell of the table to the cell you want to calculate.
   
2. Clear the contents of the inside of the table.




3. Make sure the row and column headers shown to the top and left of the table are inputs, NOT LINKED to the model itself.  For example, in the screenshot shown above, cell H350 can NOT be linked to I338 and F353 can NOT be linked to I336. 




4. Now you’re ready to select the data table region (F350:I355) and choose Data Table from Excel’s menu.  For the Row Input Cell, choose I338, since WACC is varied in the row at the top.  For the Column Input cell, choose I336, since Terminal Value Growth Rate is varied in the column to the left. 
   
   
   
   
5. The finished data table is shown below.  Click here to open and review it. 
   
   

So far we’ve shown how to create a data table.  With that background we’re now ready to explain how to avoid common mistakes when working with them. 

• The row and column input cells can NOT be linked to the rest of the model.  Otherwise, you may find that the midpoint of your table is fine but other values fail to calculate properly!
  
  
• This brings up an important check.  Run your model under a different scenario to ensure that the value you calculate long-hand (in this case B354) equals the value that the data table had predicted. 
  
  
• You’ll find that the input cells themselves can’t be on a different worksheet from the table. 
  
  
• Excel runs a bit slowly when there are numerous data tables running, so it’s a good practice to select Tools Options Calculation and check Automatic Except Tables.  This avoids recalculating data tables, except when you hit F9... 
  
  
• …BUT if you hit Print before recalculating your file via F9 you can end up with a data table that is out of sync with your model!  For this reason, it’s a good idea to create a check such as the one shown in B7 which ensures that the midpoint of the table equals the value you calculated long-hand.  This technique (as more fully described in the Level I Knowledge Base) can help you scan all important checks prior to saving or printing.  

Good luck! 

   Vince Scafaria
   Chief Executive Officer
   DealMaven Inc.
   www.dealmaven.com

1. Financial Analysis Fundamentals
2. Financial Modeling