The Flaw of Averages

Analyses based on average performance often won't yield the results you expect. Many Excel users overlook this important fact.

by Prof. Sam Savage
Stanford University
Adapted from the San Jose Mercury News, October 8, 2000.

"The only certainty is that nothing is certain."

So said the Roman scholar Pliny the Elder. And some 2000 years later, it's a safe bet he would still be right.

The Information Age, despite its promise, also delivers a dizzying array of technological, economic, and political uncertainties. This often results in an error I call the Flaw of Averages, a fallacy as fundamental as the belief that the earth is flat.

The Flaw of Averages states: Plans based on the assumption that average conditions will occur are usually wrong.

A humorous example involves the statistician who drowned while wading a lake that was, on average, only three feet deep.

But in real life, the flaw continually gums up investment management, production planning, and other seemingly well-laid plans. The Flaw of Averages is one of the cornerstones of Murphy's Law (What can go wrong does go wrong).

Fortunately, super-fast computers can overcome this problem by bombarding our plans with a whole range of inputs instead of single average values. Today, this technique, known as simulation, is at the center of such diverse activities as Wall Street investing and military defense planning.

But back to the flaw, and an area that's important to all of us: investing for the future.

Suppose you want your \$200,000 retirement fund invested in the Standard & Poor's 500 index to last 20 years. How much can you withdraw per year?

The return of the S&P has varied over the years but has averaged about 14 percent per year since its inception in 1952. You set up an annuity analysis in your spreadsheet, an annuity that requires an initial amount of \$200,000 and a growth rate for the fund.

"I need a number," you say to yourself, so you plug in 14 percent for the growth rate. Now you can play with the annual withdrawal amount until your money lasts exactly 20 years. If you do this you will be pleased to find that you can withdraw \$32,000 per year, as shown in the first figure.

Even if the return fluctuates in the future, as long as it averages 14 percent per year, the fund should last 20 years, right?

Wrong! Given typical levels of stock market volatility there are only slim odds that the fund will survive the full time. The remaining charts simulate this retirement strategy with actual S&P 500 returns starting in various years.

Notice that the level of average returns over any particular 20-year period is no guarantee of success. The real key is to get off to a good start, which is what separates the chart starting in 1974 from its neighbors.

For this example the Flaw of Averages states: If you assume each year's growth at least equals the average of 14 percent, there is no chance of running out of money. But if the growth fluctuates each year but averages 14 percent, you are likely to run out of money.

The results in these charts are not the result of a rigorous scientific study, and should not be used for making investment decisions. But they should at least have you asking yourself: Why isn't someone doing something about this?

People are. One of the first was William F. Sharpe, a Nobel laureate in Economics, who recently left Stanford to spend full time simulating retirement benefits. "I expected people to question the specifics of our simulation algorithms," reflects Sharpe about the launch of Palo Alto-based Financial Engines Inc., "but to my surprise, everyone else out there was just plugging in averages."...as in the first chart.

The Flaw of Averages distorts everyday decisions in many other areas. Consider the hypothetical case of a Silicon Valley product manager who has just been asked by his boss to forecast demand for a new-generation microchip.

"That's difficult for a new product," responds the product manager, "but I'm confident annual demand will be between 50,000 and 150,000 units."

"Give me a number to take to my production people," barks the boss. "I can't tell them to build a facility with a capacity of between 50,000 and 150,000 units!"

So the product manager dutifully replies, "If you need a single number, the average is 100,000."

The boss plugs the average demand and the cost of a 100k capacity fab into a spreadsheet. The bottom line is a healthy \$10 million, which he reports to his board as the average profit to expect.

Assuming that demand is the only uncertainty, and that 100,000 is the correct average, then \$10 million must be the best guess for profit. Right? Wrong! The Flaw of Averages ensures that average profit will be less than the profit associated with the average demand. Why? Lower-than-average demand clearly leads to profit of less than \$10 million. That's the downside. But greater demand exceeds the capacity of the plant, leading to a maximum of \$10 million. There is no upside to balance the downside.

This leads to a problem of Dilbertian proportion: The product manager's correct forecast of average demand leads to an incorrect forecast of average profit, so he gets blamed for giving the correct answer.

A computerized cure for the Flaw of Averages is Monte Carlo Simulation, first used for modeling uncertainty during development of the atomic bomb. It generates thousands of scenarios covering all conceivable real world contingencies in proportion to their likelihood.

In the 1950s, Harry Markowitz, a brash young graduate student at the University of Chicago, dealt another blow to the flaw. "I was reading the contemporary investment theory, which was strictly based on averages," recalls Markowitz. "I said to myself: 'this can't be right.' " His resulting portfolio theory, which was based on both risk and average outcomes, revolutionized Wall Street and won him a Nobel Prize. Markowitz also devoted much of his career to designing simulation systems.

Simulation-based acquisition is now used routinely in the military. Its instigator was William J. Perry, who in spite of a bachelor's degree, master's degree, and doctorate in math, has had a remarkably well-rounded career as a Silicon Valley entrepreneur, U.S. Secretary of Defense, and Stanford professor.

In 1996, while at the Pentagon, Perry issued a directive stating that models and simulations must be used to reduce the time, resources, and risks of the acquisition process. Perry says in retrospect, "With tens of thousands of uncertainties, it was just a perfect application for simulation."

A dramatic example of the savings that resulted from Perry's directive is related by John D. Illgen of Santa Barbara-based Illgen Simulation Technologies Inc., who says: "In response to improvements in foreign weapon systems, the Navy was preparing to spend tens of millions of dollars to upgrade its shipboard defensive systems. With a \$250,000 simulation we were able to show that the present defensive system was adequate to meet the increased threat."

While many of today's managers still cling tenaciously to "flat earth" ideals, the innovators are abandoning averages and facing up to uncertainty. Those who dare discover a New World of managerial tools including simulation, decision trees, portfolio theory, and real options.

And what happens when one of these innovators is confronted by someone cloaking themselves behind a single number? The story of the emperor's new clothes says it all.

 Excel User's Home ExcelUser Blog Site Map Contact Excel for Business Excel Dashboards Excel Solutions Exploring Excel BI for Excel Business Tools Excel Catalog Affiliate Program Excel Help Portal

ExcelUser, Inc.
http://www.ExcelUser.com