Black Thursday: Crass commercialism swallows Thanksgiving.

I love Thanksgiving. It’s a pure celebration of appreciation of all that we have, and especially, of friends and family. And, yes, I’ll admit it, it’s a day devoted to eating, and I love to eat.

We invite all our friends and relatives, as well as friends of relatives and relatives of friends. There’s good food (thanks to everybody), bad jokes, cool magic, raucous singing and some really awful vintage karaoke. I wouldn’t miss it for anything, even if it means spending Black Friday washing dishes and putting the furniture back in place.

Speaking of Black Friday, we had two invitees decline because they work for Walmart, which apparently does not believe in gratitude. The day formerly known as Thanksgiving is now opening day in the Season of Gimme. People are lining up days ahead of time to grab bargains. I’d like to hear from people who would miss a major holiday to do this. Is it worth your time? Is it fun?

As for me, I’m boycotting Walmart and any other stores that trample on my one day devoted to the appreciation of all that we are fortunate to have. The great tsunami of the Season of Gimme has already stormed all the way up to Halloween, consuming all of November and December. I guess my only hope is that Black Friday will eventually move all the way up to September and let us give thanks in peace.

What does all this have to do with decision making? An effective decision maker takes time for assessment. What am I thankful for? What is important to me? What do I have to give? Thanks and giving.

I hope you had a great Thanksgiving. If not, at least stop some time during your day to appreciate what you have, even if you do it while waiting for the stores to open.

Take charge of your life. Live deliberately.

Excel for decision makers or Excel for bean counters?

What the heck kind of a list of Excel essentials is this?

  1. Formulas
  2. Functions
  3. AutoFill
  4. “Bucket brigade” formulas
  5. Descriptive formulas
  6. Naming cells
  7. Goal Seek
  8. Data tables

No charts. No pivot tables. No VBA. No fancy formatting. Nothing about making dashboards or supporting data entry. I’ve just spent the last eight posts without mentioning any of the things that most Excel books and classes focus on.

OK, OK, I’ll give you one hint about charts. Often, when you’re doing cost-benefit comparisons, sensitivity analysis or trade studies, you want to see how one value varies with another. Here’s an example. The picture shows a cost-benefit comparison of several cars you’re considering buying. The costs are on the horizontal axis and the benefit scores are on the vertical axis. The points are spread out relative to both cost and benefit, so you can see immediately which car gives you the most “bang for the buck.” Excel has a lot of types of charts, but the only one that spaces out the horizontal axis properly is the scatter chart. Don’t let the name fool you. You can use it to get a continuous line as well.

So, back to the question. These are the Excel skills that decision makers need. Most Excel training focuses on bean counters. Why does it matter?

Bean counters have too much data and they need to summarize it. Decision makers have too little data and they need to make inferences from it. Bean counters answer the same questions repeatedly (e.g., profits this month versus last). Decision makers are continually faced with changing questions. Bean counters generate results to show to someone else. Decision makers generate results to give themselves insights. Bean counters need to systematize data entry for repetitive tasks. Decision makers pull together whatever data they have available at that moment that could give them insights. Bean counters look to the past. Decision makers look to the future.

Data Tables in Excel: Visualizing the range of possible outcomes

Excel is a great tool for decision makers to ask, “What if?” What happens to the outcome if you change something? Optimize your choice. What happens to the outcome if something beyond your control changes? Prepare for whatever fate might throw at you.

Make a spreadsheet to figure the outcome. Change the inputs and see what you get. Or you can add a data table to look at the effects of a whole range of inputs. Here’s an example.

In the last post, I showed a spreadsheet that figures whether or not the fund you start for your baby will be enough to pay for college when the kid is 18. The inputs are the amount you put in the fund to start, the amount you add to the fund each year, the fund’s growth rate, the growth rate of college costs and the cost of four years of college. The output is the shortfall, the college costs that the fund won’t cover. You can try different inputs for the amount you add to the fund each year and see how the outcome changes. Better yet, use a data table to see a lot of possibilities at once.

I’ll use a one-dimensional data table with one input, organized in a column. Here’s how you do it. First list all the values of the input you want to check in a column and leave empty cells just to the right it for the corresponding output values that Excel will calculate for you. In the cell just above these empty cells, put a formula to calculate the output. Then select the whole table, including the formula but not the headers. Choose Data Table from the Data menu or on the Data tab, in the Data Tools group, click What-If Analysis, and then click Data Table. In the dialog box that appears, enter the cell with your input in the Column input cell and leave the Row input cell blank.

Here I’ve added a table in columns F and G to consider amounts to add to the fund each year, from nothing to $20,000. I’ve used AutoFill to list all the values from $0 to $20,000 in $1000 increments in F14:F34. I put column headers in F13:G13 for my input (the yearly amount) and output (the resulting shortfall in the 18th year). Now I need to tell Excel how to calculate the output. There’s not a simple formula to do that—it’s the whole spreadsheet. But that actually makes it easy. The output is the final shortfall in cell D33. The formula is simply =D33 and I enter that at the top of the Shortfall column in the data table, G14. I select F14:G35, call up Data Table, put my input B9 in for the Column input cell and click OK. Excel automatically fills in the entire Shortfall column.

Now I can see how changing the yearly amount changes the shortfall at college time. (Note that cell G14, italicized, is there to hold the formula–it’s not part of the table.)

This is an example of using a data table to think about your options and the effect of each one. You can also use it to think about your risks. One of those is the rising costs of college. What if it went up by more than 9% a year? Make a table to examine shortfall versus the cost growth of college and you’ll see that the cost growth is a major factor. You’ll probably want to keep an eye on college costs over the years and adjust your savings accordingly.

Data tables are admittedly confusing, but well worth the trouble to figure out. You can even use them to make what is called a Monte Carlo model to look at random occurrences or fluctuations and assess your risks. If anyone is interested in that, please add a comment and I’ll describe it in a future post.

How have you used tables?

Goal seek: Getting to the outcome you want, using Excel

Microsoft Excel will not only let you play “what-if” with your choices, it will also help you figure out what you need to do to get where you want to be. You can work the problem backwards with a nifty little feature called Goal Seek.

Say you want to start a college fund for your new baby. You start with $10,000 in the fund and add $15,000 to it each year. You have it well invested so that it earns 8% each year. But college is expensive. You figure $140,000 for four years, and that grows at 9% a year. Your first question is whether you will save enough by the time the kid is 18. Here’s a spreadsheet to answer that question. It’s built using two bucket brigade formulas and one other, all AutoFilled down the columns. The inputs are all named. (Check out my previous posts if this doesn’t make sense.) You see that you’ll be more than $50,000 short when you compare the amount you’ve saved and the cost of college at age 18. By the way, you can download this spreadsheet from http://www.decisionspreadsheets.com/free-spreadsheets/ under Investment, Save for college.

Now you might try some “what-if.” Will I get there if I start with more money? if I add more each month? if I get a better return? if I choose a less expensive college? You can change any of the inputs and see what that does to the outcome.

Better yet, you can calculate exactly what you need to do to reach your goal. Say you decide to add more money to the account each year. You know $15,000 isn’t enough. Exactly how much will you need? Here’s how to get Excel to calculate it for you. Choose What-If Analysis > Goal Seek in the Data Tools group or select Goal Seek from the Tools menu. You’ll get a dialog box. You want to make the shortfall at age 18 (D26) become 0 by changing the added amount (B2). Put this in the dialog box by clicking on the cells as in the picture. ($B$2 is the same cell as B2.) Click OK and Goal Seek gives you your answer in cell B2: Adding $16,567 to the fund each year will do it. You can use Goal Seek to answer other questions, such as: How much do I need to put in to start? How much does my investment need to grow?
Goal Seek is a simple, but powerful, tool for decision makers to determine what they need to do to get where they want to be.

Have you used it? Please share your ideas and experiences.