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!

About Andrew Chan
Andrew Chan is an Business Consultant who gives you accurate, consistent and timely information so that you can make better and faster decisions. He is an Associate of Society of Actuaries with over 20 years of IT experience. Apart from strong analytical skills and proven technical background, he was also a former system director at Manulife who had extensive project management experience. If you are looking for someone to gather, consolidate, validate, visualize and analyze data, look no further! Andrew can provide the most cost effective business analytics solution so that you can explore, optimize, predict and visualize your business. Don’t guess on any decision, no matter it is finance, operation, marketing or sales! Always ask for evidence!

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

  1. Pingback: Flaw of average (FOA) – Excel Scenario Manager « Better and Faster Decisions

  2. Pingback: Flaw of average (FOA) – Stochastic Model in Excel « Better and Faster Decisions

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: