Introduction
Excel is a powerful tool for making all sorts of decisions. All assumptions and results are visible. Its spreadsheets are easy to change to explore alternatives. They're easy to expand to answer further questions. You can use Excel for personal decisions. You can use it for business decisions, without having to wait for a simulation or analysis staff to work out answers. Spreadsheets help to focus on the key issues, suggest new avenues of investigation, demonstrate possible outcomes, and optimize results. This book assumes only basic knowledge of standard computer techniques, such as menus, scroll bars, and saving files. It’s OK if this is your first use of Excel. You'll cover the basics of using Excel in the first chapter, and then focus on features and functions that best support decision-making. Rather than cover all capabilities of Excel, as many excellent books already do, Simple Spreadsheets for Hard Decisions highlights those features that are most useful for decision models and says just enough about others that you can explore them if you wish.

1. Hard decisions: Deciding with help from Excel
Before you analyze a decision, be sure you understand the question and options. What exactly is the question? What does success look like? What are the alternatives? What do you know? What don't you know?

2. Simple spreadsheets: A workbook and its cells
This chapter gives you basic techniques for building a spreadsheet in Excel. The worksheet is a large grid of cells. There are three basic types of information you can put in the cells: text, data, and formulas. Formulas show results of calculations, which change automatically with any change in the data. There are just a few basic techniques you need to get started, and they're all described in Appendix A.

Case Study 1. How much do I need to save for college? Preview of coming spreadsheets
Look at a finished spreadsheet to see how it works. This one examines a plan to save money for a baby’s future college education. Look at the formulas that make it work. Change the inputs to see the effect on the outcome.

Case Study 2. Should I change jobs? Pros and cons
A good general decision technique is to list pros and cons. Some of the items are more important than others, so assign each one a rating from 1 to 5, with 5 being the most important. Use the Excel AutoSum capability to add all the ratings on each side to get a numerical comparison. Finally, check your reaction to the result, and think about whether any considerations were forgotten.

3. The magic formula: Formulas for a simple spreadsheet
Formulas are the workhorses of Excel. Formulas make the predicted outcome change in response to inputs. A formula starts with an equal sign and combines references to cells, arithmetical operations, functions, and parentheses. Build your first simple spreadsheet. Use it to figure the total cost of several computer components you may be thinking of buying, including sales tax. If the total is too high, change or remove some of the items until you get an amount you can afford.

4. Send in the clones: Using AutoFill
AutoFill is a way to tell Excel “and so on.” Use it to continue a series of numbers such as 1, 2, 3,… Use it to clone formulas. For example, write a formula to compute the total cost of the first sales order in a list. Then clone it down the column to do the calculation for all the other sales orders. This powerful feature will let you build complex spreadsheets using just a few formulas. You can use named cells or absolute references to control how much of your formula changes as you copy it.

Case Study 3. Do I need to cut back spending? Building a spreadsheet from a single formula
Check whether income and expenses are in line. Write a single formula to compute the difference and AutoFill it to all months.

5. Call in the bucket brigade: Complex calculations by cloning simple formulas
Often you can write a simple formula that works together with its clones to do a complex calculation. Like a bucket brigade, each row does some simple calculation on the numbers passed to it, and then passes the result on to the next row. The final answer takes into account all the rows and can represent surprisingly complex relationships. Examples are running totals, accumulated funds, inflation, investment growth, and present value. In particular, many long-term decisions revolve around some quantity--such as rent, cost of living, or investment value--that goes up by some percentage each year. A single formula can be cloned to look at the results over as many years as you wish. Throughout this book you will see fairly complex calculations come from having simple ones build successively on each other.

6. Decision taming: From a vague question to a spreadsheet answer
At this point, you have most of the techniques you need. But, you'll find that many important decisions are vaguely stated. There are four steps to get you from there to a solid decision. Expand the question, as described in Chapter 1. Plan the spreadsheet, defining rows, columns, inputs, and outputs. Build the spreadsheet based on your plan, using bucket brigade formulas as much as you can. Use the spreadsheet, after first checking that it is working, to examine results, play "what if" with the inputs, and expand the model as it suggests other questions.

Case Study 4. How much can I afford to spend? Building a check register
Build a checking account log. First decide what you want the spreadsheet to do (track checks and deposits and compute balances). Lay out the spreadsheet to look like a checkbook register. The first row starts things going by telling Excel how much you already have in the account. The other rows each represent either a check or a deposit. A single good balance formula works for both, and so can be cloned down to all of the rows.

Case Study 5. Marry a millionaire! Predicting future finances
Build a spreadsheet to predict how much money will be left each year if you start with a million dollars conservatively invested, and if spending keeps up with inflation. It turns out that spending must be low if the money is to last a long lifetime.

7. Learning to function: Using Excel’s built-in functions
Excel provides a powerful library of functions that can be used in formulas. Some examples generate random numbers, make calculations that depend on conditions, or compute the maximum, the average loan payments, or complex statistics. Browse the function library to select the one you want.

8. Useful Functions: Introducing some handy functions
Functions introduced are SUM, AVERAGE, MIN, MAX, IF, SUMIF, COUNTIF, SUMPRODUCT, RAND, and financial and lookup functions.

Case Study 6. Can I afford this car? Loan payments
Many financial decisions involve loans. Build a spreadsheet to calculate the monthly payments and how they break down into principal and interest each month. Browse the built-in functions and let Excel help you fill them in. You find again that once you get formulas for the first two months, you can fill in as many months as you want by copying. A few sanity checks convince you that this spreadsheet works as you wish. Use the spreadsheet to calculate the loan amount remaining if the car is sold after a year.

9. It depends: Using conditionals
The IF function lets you make your formula act in a specific way, based on conditions anywhere else in the spreadsheet. Just about any complex decision has these types of dependencies and Excel can handle them using this conditional function. Facility with the IF function allows you to develop very sophisticated and powerful models. The IF function can be part of a larger formula, even inside another IF. SUMIF lets you add only those items that meet your conditions.

Case Study 7. Does my checking account balance? Extending the checkbook spreadsheet
Back to the bank account example. Expand the spreadsheet to compute the bank balance, which depends on which checks and deposits have cleared. Use the IF function to write a formula to compute the bank balance depending on what has cleared. Now you can use your spreadsheet to balance your checkbook. Even though you only care about the most current of the bank balances, the intermediate balances shown convince you that the spreadsheet is doing what you expect.

10. Comparing apples and oranges: Making a decision with multiple objectives
Hard decisions are often hard because there are multiple measures of “goodness”. There are tradeoffs—performance vs. price or returns vs. risk. You want a big house with low maintenance, in a good neighborhood and at a low price. Does it exist? No. You have to make trade-offs. This is especially difficult when the criteria are a mix of objective and subjective, and expressed in different units. One simple solution is to rank order the alternatives relative to each criterion. For more precision, normalize the scores so that they can be compared. Weight the totals using SUMPRODUCT to put most emphasis on the key considerations. Another technique is to make imaginary even swaps until it is clear which alternative is best.

Case Study 8. Which apartment should I choose? Even swaps
Use the even swap method to choose among three apartments that you are considering renting. Use a spreadsheet to keep track of the swaps.

Case Study 9. Which car should I buy? Balancing multiple objectives
Decisions like choosing a car are difficult because there are a lot of considerations to balance, both objective and subjective. Even the objective considerations are hard to compare. Furthermore, some considerations are more important to the buyer than others. First, gather all the data and give the subjective considerations ratings. Next, use a simple formula that represents the percentage of “perfect” each car is, relative to each consideration. Assign a percentage, called a weighting, to each consideration. The weighting reflects its relative importance. Finally, a simple formula gives you a number that represents the relative desirability of each car. If you're surprised or disappointed in the result, reexamine the considerations, rankings and weightings.

11. Chart your course: Visualizing data with charts
Charts are an effective way to present a lot of information in a concise way. Excel offers many formats, such as column, bar and pie charts. Only the XY (Scatter) plot is designed to look at how changes in one value affect another. The Chart Wizard walks you through creating a chart of your data.

Case Study 10. Which car is the best value? Comparing cost and benefit
In the last case study cost was one of many considerations used in choosing a car to buy. Another approach separates cost from the other considerations and plots desirability against cost, for a visual representation of value or “bang for the buck.”

Case Study 11. Buy or rent? Comparing annual payments
Should you buy a house or rent? Build a spreadsheet that compares the annual out-of-pocket cost of each. Inputs include cost of the house, monthly rent, expected annual rent increases, down payment on the house, and the home loan interest rate. Use the PMT (payment) function provided by Excel to compute the fixed loan house payments. You see that eventually renting costs more than buying. Exactly how soon this happens depends on those input values.

Case Study 12. Buy or rent? Comparing total costs
The previous case study indicates that you might want to rent if you will be living there briefly, but it has yet to really answer the question. You must still look at that big down payment and the money you will receive when you eventually sell the house. So, expand the spreadsheet to compute the total cost of owning or renting for a certain number of years, at the end of which you'll sell the house. Apply bucket brigade formulas to go from year to year. Use the IF function to compute text messages (“Rent” or “Buy”). You discover that, if you stay put for more than a couple of years, it costs less to buy. In fact, it appears that you get paid to live in the house for many years.

Case Study 13. Buy or rent? Present value
In the last chapter you saw that if you stay in the house long enough, it will cost you nothing or you will even get paid to live in your house! Is this possible? Well, no, since you must take into account that money now is worth more than money later. Use present value multipliers to modify the spreadsheet. As is usually the case in decisions, some of the inputs (such as the cost of inflation) are guesses. Change these to see what happens. Finally, the decision comes down to whether you can live with these uncertainties and whether the non-monetary benefits of buying or renting justify the relative costs.

Case Study 14. Pension or lump sum? Comparing retirement options
Should you take your pension as an income stream or lump sum when you retire? Build a spreadsheet to compute the present value of the income stream. Then explore the impact of the assumed interest rate on the lump sum. It may be worthwhile to delay retirement if that interest rate is going down.

Case Study 15. Do I have enough life insurance? Present value of an income stream
Use present value to decide on the amount of life insurance you need to carry to cover payments from a divorce settlement.

12. Whatever might happen: Using data tables
To make a good decision, you need to look at all the possible outcomes. You don't know what the future holds. Can you handle the risks? Make a data table that compares several inputs that you want to vary (in this example, assumed interest rate for retirement) and their corresponding output (lump sum pension payment). Start with a spreadsheet that accepts the input and gives you the output. Make a column of the input values you want to consider. AutoFill helps. Use a data table to get Excel to fill in all the outcomes for you.

Case Study 16. Which medical insurance plan? What-if analysis
Many decisions depend on future events that you cannot predict. An example is choosing medical insurance. Should you pay extra to get a low deductible? What percentage coverage should you choose? The answer depends on your medical needs in the coming year. What if you have few doctor visits? What if you have major, unexpected medical expenses? Excel helps you look at your out-of-pocket costs for various possibilities. Use data tables to compare alternative health care plans under the full range of possible medical expenses. Chart the results for easier comparison.

13. Working backwards: Goal Seek
You've probably found yourself using many of the spreadsheets to see whether you will reach a goal. Will I have enough for my child’s education? Will my retirement funds last the rest of my life? You'll often do trial and error to find out what you need to do to get to your goal. Excel’s Goal Seek feature finds the input that gives you a desired output.

Case Study 17. Are we saving enough for college? Visualizing the future to reach a goal
The example in the previous chapter shows how to build a model in steps, by examining the results, asking questions, and adding to the spreadsheet. Apply these techniques to build a model to address the question “How much do I need to save each year for my child’s future college education?” Include investment growth and the future increase in the cost of college. Use Goal Seek to get the answer. Next modify the spreadsheet to look at the strategy of increasing amounts saved each year.

Case Study 18. Can I afford to retire? Making your money last a lifetime
Build a spreadsheet to predict income and spending during retirement. The savings that provide some of the income are in various accounts with different growth rates. There are additional sources of income, such as Social Security or rental income, that may not be available every year. Spending, too, may vary year-to-year, for example, when the house gets paid off. Spending will increase with inflation. Some of the income is taxable and some isn't. Model a withdrawal strategy that smooths out the tax burden. Check how much you'll have left when you're 90. Play what-if to look at various possible futures. Use Goal Seek to find a comfortable spending limit.

14. The great unknown: Looking at risks
It’s relatively easy to make a decision if you know the consequences of each alternative. Unfortunately, you don't have a crystal ball. The good news is that you can talk about what might happen, and even assign probabilities to it. You can then use the function RAND to put random numbers in your models to show you possible outcomes. Make it a Monte Carlo model to look at hundreds of possible outcomes and estimate risks. Build a spreadsheet to decide whether a coin toss game is a good bet.

Case Study 19. Can I afford aggressive investment risks? Random events
Turn the spreadsheet from the last case study into a Monte Carlo model. Use it to estimate the probability that the money will last at least 40 years. You learn that the high-risk, nonstandard investments that you thought would make you rich will just as likely leave you destitute. This is in spite of the average outcome being very good.

15. The bottom line: Summary
First, make sure you understand the question. What are you trying to decide? How will you measure success? Are there multiple criteria? How much risk can you accept? Next identify the alternatives. List every influence on the outcome. Vary the ones you don't know. List key characteristics of the alternatives. These are all part of your inputs. Create names for all of your inputs so you can use them in formulas. Set up the problem in a table. Use bucket brigade formulas. Build complicated formulas from the inside out. Browse the function library. Do sanity checks on your finished model. Use Goal Seek to get answers. Do sensitivity analysis to find out what really drives the outcome. Use normalized, weighted scores or the Even Swap method if you have multiple criteria. Finally, think about what you learned, and expand the model as appropriate.

Appendix A: Summary of Excel techniques
This appendix is a quick reference to the Excel features and functions that are used in the chapters and case studies. When you run across an unfamiliar function or technique in the text, find out about it here.

Appendix B: Useful formulas
This is a summary of formulas that were used in the case studies, along with some new ones that you can explore on your own.

Appendix C: Decision questionnaire
The questionnaire walks you through some of the questions you need to ask yourself when you start thinking about your decision. Here are several examples of completed questionnaires from the case studies.

Appendix D: Notation
Special notation is used to highlight commands and formulas, and to point the reader to the Excel techniques summarized in Appendix A. A descriptive notation for formulas makes them easier to understand.

Appendix E: Recommended books.
There are many books that cover the mechanics of using Excel. Here we list three that help you make decisions.