How good are you at estimating?

Strategic decision-making often requires you to estimate current and future quantities. Here’s a quiz to see how good you are at it.

For each quantity below (2009 data unless noted otherwise), make your best guess. Don’t try to find the answers, just guess.

  1. World camel population
  2. Annual consumption of popped popcorn in the US (quarts)
  3. GNP of Belgium ($M)
  4. Number of cats in the US
  5. Population of Romania, 2007
  6. Amount of dog feces produced each day in the UK (tons)
  7. 2007 US corn production (thousand bushels)
  8. Value of all the tea in China (annual production) ($)
  9. Total amount of gold ever refined (kg)
  10. Acres planted in cotton in the US, 2006 (thousands)

Now how confident are you in your answer? Give a high and a low guess so that you are 90% certain that the true answer lies between high and low.

Don’t check the answers below until you’ve done this!

Businessman with Question

 

 

 

 

 

 

 

alternatives

 

 

 

 

 

 

 

 

 

 

Here are the answers. Give yourself a point for any question where the answer was between your high and low.

1          World camel population         18,871,000

2          Annual consumption of popped popcorn in the US (quarts)            16 billion

3          GNP of Belgium ($M)            $264,000

4          Number of cats in the US       76,430,000

5          Population of Romania, 2007 22,276,056

6          Amount of dog feces produced each day in the UK (tons)    992

7          2007 US corn production (thousand bushels)            13,073,893

8          Value of all the tea in China (annual production) ($)  $1,590,653,400

9          Total amount of gold ever refined (kg)           154,400,000

10        Acres planted in cotton in the US, 2006 (thousands)            15,274

 

How did you do? If you really were 90% certain, you’d expect to get a score of about 9.

I’ve never had anybody get that much. Most people will score maybe 1 or 2. Why is that? It’s the overconfidence bias. We tend to think we know more than we really do. We tend to lock into an answer and then have trouble thinking much beyond it. This is a natural human bias that stifles our imaginations and leaves us unprepared for inevitable changes.

You probably didn’t know the answers to any of these questions. Admit it! Give yourself a wide latitude for the possibilities. Have you ever been caught by the overconfidence bias, or seen others get caught up in it?

Are small schools better?

small schoolCan you get fooled by random events? You bet you can. Here’s an interesting example, recounted by Daniel Kahneman in his book Thinking Fast and Slow.

A survey of average student test scores at over 1600 schools focused in on the top 50. One item stood out. Of the 50 best schools, 6 were small. This was surprising since there were very few small schools in the study. In fact, the small schools were overrepresented among the best by a factor of 4. This started the small school movement, supported by the major foundations and the US Department of Education.

Yet, if you looked at the bottom 50 among the same schools, you would see small schools overrepresented there as well. If the study had focused on fixing bad schools rather than emulating good schools, they might have seen this data and concluded that bigger is better. What’s going on here?

There’s a simple explanation that shows that this anomaly is not only possible, but to be expected. It’s all explained by the Central Limit Theorem, which says that the more items you average, the smaller the variation from one average to the next. If you look at the test scores of individual students, you will see the full range, including both some very poor and very good students. These individual differences swamp any educational differences. You won’t see this spread in the average test scores for a school, since there will usually be a mix of very good and very poor students, who average each other out. In fact, a very large school will closely match the general population, and so will have average test scores very close to those of the general population. In other words, the big schools will all have about the same average test scores. On the other hand, a small school may by chance have a few very good or very poor students who swing the average, so the average scores at the small schools will show more variability than those at the large schools.

Now when we look at the very tails of the distributions, we find small schools, not because they’re better or worse, but simply because their average scores are more variable. So which is better? We really won’t know until someone does an analysis that includes all the schools.

A new view on your data: Pivot Tables

looking around cornersExcel’s pivot tables reorganize, categorize, and summarize your data dynamically however you want. Look at it one way, then in seconds, look at it another way. It has a lot of features, but the basics alone are powerful. Today I’ll show you how to use it on a simple example.

Let’s take the example from last time, a spending log.spending

 

 

Pivot table dialog box

Click anywhere in the data. Then select Data > PivotTable or Insert > Table > PivotTable, depending on your version of Excel. You’ll get a Create PivotTable dialog box. Click OK.

 

Blank Pivot Table Builder

Now you’ll get a new sheet with a blank table, marked only Column area, Row area, and Values area. You’ll also get the PivotTable Builder. This is where you tell Excel what you want for rows and columns and what you want in the body of the table.

 

Start by deciding what you want for Values. This is the data you’re going to total by categories, so it only makes sense if it’s numerical, something you can sum. Here, the only field name like that is Amount. Of course, that’s exactly what you want to look at, your spending. Drag the Amount field name into the lower right Values box. You will now see a tiny pivot table showing your total spending. You’d really like to break it down by category, so drag the Category field name into the Column Labels box. Now you have your total spending by category. Break that down by month by dragging the Month field name into the Row Labels box. (You may have to scroll in the Field name to find Month.) Here’s what the PivotTable Builder looks like when it’s filled in.

Completed Pivot Table Builder

Completed Pivot Table Builder

 

It looks complicated, but this just took a few steps. Now you can slice and dice your data any way you want. Drag Category out of the window and you immediately get a new table, summarized only by month. Drag Paid by into the Column Labels box to get your spending by month and payment method.

This feature does more, but these simple steps are all you need to slice and dice on the fly. I love Excel for its flexibility. You don’t need to pre-define reports the way you do in a database program. Try it on your data.

Final pivot table

Final pivot table

Dealing with data: Sort and Filter

 

alternativesHave you ever been overwhelmed with data? How can you organize it? How can you make sense of it all? I’ve tried a lot of databases, and I keep coming back to Excel. The main reason is that databases are designed to give you fixed reports, and the whole thing is structured to give you those reports. Well, when I have a lot of data, I’ll look at it from multiple views, each of which will trigger other questions. I can’t predict what I’ll want to look for next month or the month after that. Excel allows you to look into the data dynamically. The two major techniques for doing this are Sort/Filter and Pivot Tables. Let’s explore Sort and Filter. I’ll show you how to use Pivot Tables next time.

However you plan to look into the data, the first thing you need to do is organize your data into rows and columns. The rows represent the individual items and the columns are the categories and amounts. For example, for tracking your spending, each row will represent a payment and the columns might be the date, amount, payee, and any categories you might be interested in.

spendingIt also works best if your rows represent things at the lowest, most detailed level. For example, if you are tracking the costs, diagnoses, and insurance payments of your doctor visits, each row will represent one visit to one doctor. If you visit the same doctor twice, that’s two different rows. If you see two different doctors on the same day, that’s two rows. Then these Excel tools will let you look at things from a higher level if you want to, say, look at a single doctor or specialty.

It’s easy to turn on Sort and Filter. Click anywhere in your table and then find Sort and Filter. It might be called simply “Filter” in the Data menu, or may be an icon that looks like a funnel. Try Home > Editing > Sort & Filter > Filter. It depends on your Excel version, and it may appear multiple places. When you select it, little arrows appear at the top of the columns. Notice that Excel figures out how big your table is, even if you don’t formally make it a table.

filterNow when you click on that little arrow, it opens a window that allows you to sort or filter the whole table based on the values in that column. For example, if you open the drop-down on Category, deselect “(Select All)” and then click on Clothing, You’ll see just your clothing purchases.

sort filter dialogClick “Clear Filter” to put it all back. You can sort your purchases by amount, simply by opening the drop-down on Amount and clicking Descending (to put your biggest purchases on top). You can even use multiple sorts and filters at once. For example, see all your January Visa purchases, or list your entertainment purchases in order of cost. There are much more complex filtering options that you can explore.

Next month I’ll show you how to do basic Pivot Tables. I’m now going to a monthly blog, the second Monday of the month. Thanks for reading.

Which investment is better?

compare investmentsI have $10,000 to invest over the next six years. I have a chance to buy equipment that will save me $2000 a year for five years and after that I could sell it for $3000. Looks like $3000 ahead! But before I jump into this, I should compare it with other investment opportunities. Maybe I have a chance to invest it at 6% for the six years, and I figure the risks are comparable for the two choices.

Here’s a spreadsheet to look at investments like this. Fill in the interest rate you’re using for comparison and the amount you’re investing. Then fill in how much you will get each of years 1 through 6. (If you have to put in more money any year, enter that as a negative.) The cumulative column shows how much you’ll have total (positive in black or negative in red) at the end of each year, taking into account what you’ll give up by not investing the money.

IRR1The Cumulative column shows how much I’ll be ahead (black) or behind (red) relative to investing the money at 6%. I’m in the hole until Year 6, but I end up $765.45 ahead. All else being equal, this is a better than the 6% investment.

But how much better is it? Try various rates. Try 7%. The bottom line is still positive, so this is better than a 7% investment. Try 8%. Negative. Not as good. Now I can use Goal Seek to find the rate that gives comparable results. I tell Goal Seek to make the bottom line exactly zero by changing the rate. Here’s what I get.

IRR2

The answer is that this investment is exactly as good as investing the money at 7.6%.

Here’s another way I could use this spreadsheet and Goal Seek. I’m looking for a 10% return on my investment since I think it’s risky. How much should I offer to pay for the equipment?

IRR3

If I could buy the equipment for $9,275, I could make 10% on the investment.

The value of 7.6% that we got is called the internal rate of return, often abbreviated as IRR. This is a useful measure of how good the investment is. Take, for example, a similar investment that pays more each year, but has no salvage value at the end, as represented in this spreadsheet.

IRR4

The internal rate of return here is 7.9%, a better investment.

Now let’s compare it to a completely different type of investment. Suppose someone borrows $10,000 and in six years (without any interest payments) pays you $15,000. That’s more than you’d get back from the other investments. But if we find the internal rate of return, it’s only 7.0%. Money sooner is more valuable than money later.

IRR5

If you’re interested in how you’d build a spreadsheet like this, it’s the same techniques used in others—(1) create names for the inputs, (2) click on cells to get Excel to write a formula for you using only the current and preceding line and named inputs, and (3) AutoFill.

Take a look at the key formula for the cumulative in Year 1.

IRR6

You’ll get this if you click on the cells of interest while thinking through what you’d compute. Start with the cumulative you had the year before (the $10,000 in the cell just above, C5) and add in a year of lost interest on it (Rate*C5). On top of that, you get back the Amount in/out, so add in the $2000 in the cell to the left (B6). Click on the cells to build this formula rather than worrying about cell numbers. Once again, Excel formulas are easier to write than to read. You may notice that this formula is exactly the same as the one we used to look at investment growth. It works just fine even if some of the numbers are negative. This formula uses only the current and previous rows and named inputs, so we can AutoFill it down to complete the spreadsheet.

By the way, Excel has a function for calculating the internal rate of return directly, IRR. In this spreadsheet, =IRR(B5:B11) gives you the same 7.6% we got before.

There’s a lot of data here, but how can I make sense of it?

notimeOnce in a while I find myself with a lot of really good information that I’d like to use. Great! Put it into Excel. Not always so easy, even if it’s already computerized. I ran across this recently in looking at Medicare payments for doctor visits. The mymedicare.gov site tells you everything about each of your doctor visits–dates, charges, payments, procedures. You can look at it, or you can download it as PDF or text. Not one of these gives you the one-row-per-doctor-visit format you’re used to seeing and working with in Excel. The text version seems to be the best, but here’s what it looks like if you copy it into Excel (plus a lot of blank lines that I’ve removed):

medicare text

One column, with keywords for what you’d like to become columns. Then below each claim are several “claim lines,” each with its own data, spanning several rows. This is good information, but it complicates things, because the number of claim lines varies, so you can’t just count off to find the next claim.

It’s those keywords that make it possible to work with this, because they’re in the same cell with the data. For example, to see all the dates you had a doctor visit, filter this on “Service Start Date”. Then copy that somewhere else. Filter on “Provider:” (don’t forget the “:”) to get a list of doctors that you can line up with the dates, and so on. Add column headers, do a global replace to get rid of the keywords, and you’ve got it.

I don’t usually need macros, but this is one of those tedious tasks that calls for a little automation. Here’s a data conversion spreadsheet, Medicare with macro. Yes, it’s all blank, but it includes a macro called Medicare. Put your Medicare text data in the first column of Sheet 1, run the macro, and you’ll get something that looks like this in Sheet 2. It also lists all the procedures in order.

Medicare converted

Now you can sort it by date, filter it by doctor, or use any of the other ways Excel can examine a bunch of information. Pivot tables, anyone? If you have data in a similar form from some other source you can edit the macro. Wouldn’t it be nice, though, if everybody gave us data already in Excel-friendly form?

 

What if you could visualize your financial future for the coming year?

Hiker woman looking forward in the snowy mountainHappy new year! This is a time for looking back and for looking ahead. How did you do financially this past year? Was it what you expected? What do you expect in the coming year, and the years ahead? If you could magically produce a spreadsheet that could help you look ahead, what would you ask it? Here are some of the types of questions you might want answered:

  • How can I get out of debt?
  • How much do I need to save to reach my goal?
  • Can I pay off my house faster?
  • Should I refinance my house?
  • How much money do I need to retire?
  • How much can I spend each year in retirement?

You can probably think of others. The good news is that you can easily build spreadsheets to explore questions like these. Of course, there are no certainties in financial planning. That’s why you may want to play “what if” and maybe do some Monte Carlo analysis. Best wishes for a new year filled with fun and no unpleasant surprises.