Use Excel to handle flaw of average (FOA) – Data Table

What is flaw of average (FOA)? Before I discussed FOA, let’s make sure we understand what average is. Some people call it expected value, mean, median or mode. It has many definitions but generally, it is a single value that corresponds to the "middle" of the dataset. In the following diagram, the middle line is the average (mean) of the dataset which has a normal distribution.

We use average to do a lot of forecast, planning and projection, e.g. cash flow projection, project planning, inventory planning…etc. They are often wrong! Why? Let’s go back to look at the diagram. Average (mean) is only a very thin line, i.e. 50% of the data is bigger than it and the other 50% is smaller than it. So if our estimation is based on average, the chance to have a correct expected value is very thin. This is the flaw of average! We cannot do planning based on a single value because we would lose sight of the whole picture! We have to evaluate a spectrum of scenarios for our planning to make sure we understand the full picture!

Excel provides a number of advanced scenario features and I am going to discuss one of them today.

Data Table

I want to find out how much I need to save for my retirement at 65 that can support my living for 20 years.. I am 45 (I wish) and I need $60,000 per year. My best estimate for inflation rate and discount rate are 3% and 4% respectively. The Excel spreadsheet is very simple and I need over 2 million dollars.

But what if the inflation rate is 5%? Or 10%? How about the discount rate? I want to test more scenarios to answer my "what-if" questions. I can change the input cells D2 and E2 and record the result F2 but there is an easier way in Excel which is the Data Table.

Setting up Data Table in Excel is very easy.

• In cell G4 (the upper left-hand cell of the Data Table), enter ‘=F2‘.
  
• Specify the range of Inflation Rate as the column header (H4.. U4), 1% to 14%.
  
• Specify the range of Discount Rate at the left most column (G5..G18), 1% to 14%.
  
• Highlight the whole data table
  
• Select Data Table… under What-If Analysis in the Data ribbon.
  

• Data Table Dialogue would be displayed.
  
• Enter $D$2 (Discount Rate) and $E$2 (Inflation Rate) to Row input cell: and Column input cell: respectively.
  
• Click OK button.
  

• Excel would automatically populate all the results in the Data Table.
  

I can now see a better picture on how much I need for my retirement. Data Table is a very handy tool if we want to find out about other "what-if" scenarios. However, it has a very serious constraint; it can only support up to 2 variables. So if I want to find out how much I need when I live 25, 30, 35 years after my retirement instead of 20 years, then we need other "what-if" tools.

Please stay tuned for my next blog!

Wish you to improve productivity and make better decisions in 2011 with Excel!

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!