Spreadsheets

Unit 3: Advanced Spreadsheet Techniques

Lesson 9: Using Spreadsheets in Decision Making

How do you use a spreadsheet in decision making?

This objective outlines the process of using a spreadsheet in decision making. It introduces a special type of spreadsheet called a "what if" model. A "what if" model is an important decision support tool that can provide additional information about your problem. This additional information allows you to make better decisions.

Step 0: Be able to recognize when a spreadsheet can be applied. In practice you will start with a problem and need to be able to recognize that a spreadsheet can be used to help find a solution. With the knowledge and experience you gain from this module on spreadsheets, you should be prepared to recognize the characteristics of a problem that make it amenable to analysis by a spreadsheet.

Step 1: Understand the problem. At this point you are not sure a spreadsheet will be part of the solution to the problem. You may suspect that a spreadsheet will be helpful, but you can't be sure until you understand the problem better.

Some problems start out vague, like "how can I save money?" or "where should I invest?" Before you can solve the problem, regardless of what method you use, the problem should be stated with enough specificity that you understand the range of possible solutions. For example, the range of possible solutions to "how can I save money?" is very large. "Which loan should I pay off first?" is much more specific, and has a clear and manageable range of possible solutions. Some other examples of clearly defined problems are:

1. Is it more expensive to buy or lease a new car?
2. How much should I invest each month in my 401(K) account so that when I retire I can maintain my current standard of living?
3. How much extra should I pay each month to reduce my car loan?

Once you understand the problem you are better able to decide if you want to use a spreadsheet to help determine the solution.

Step 2: Design the spreadsheet. Most spreadsheets used for decision making fall into one of two categories: (1) those that calculate a solution, and (2) those that simulate the domain. For example, Problem 1 above, "Is it more expensive to buy or lease a new car?" probably requires a spreadsheet that calculates a solution. The spreadsheet would most likely have two sections, one that calculates the cost of leasing a car for some number of years, and another that calculates the cost of buying the same car. The solution space is small (buy or lease) and the spreadsheet can be used to determine the cost of each.

Problem 2 above is more complex, has a larger solution space, and probably requires a spreadsheet that simulates the domain. One of the characteristics that makes this problem more complex is the dependent nature of the variables. A simulation for Problem 2 would probably have as input:

1. Current retirement savings
2. Monthly 401(k) investment
3. Expected rate of return
4. Retirement age

The simulation would most likely calculate:

1. Current net income
2. Net income available during retirement

Finding a solution to this problem is a matter of trying different scenarios or sets of input until one is found that is most desirable or a reasonable compromise. What makes this solution different from the solution for problem 1 is that you are not so much interested in the results calculated by the spreadsheet, but rather the set of inputs that maximize some criteria.

Although there is no algorithm or process for designing a spreadsheet that works in all cases, there are a few activities that might help you make progress:

1. Further refine the problem statement. In Step 1 the problem statement was focused to the point where the set of possible solutions was manageable. It is likely you will use a spreadsheet for only a portion of the original problem. Before you can design the spreadsheet, it should be clear what portion of the problem the spreadsheet is being designed for.

2. Decide if the spreadsheet will be used for calculating the answer, or as a simulation that will allow you to experiment with inputs.

3. Break down the process of creating a spreadsheet by identifying separately the inputs, calculations, and outputs.

Step 3: Do it! (create the spreadsheet) In this step you will use the capabilities of a spreadsheet to implement the solution described by the design created in Step 2. The design process creates a solution plan that is independent of a particular implementation. During the design process you may have been thinking about implementing the solution plan as a spreadsheet, but there should be nothing in the design that requires it.

The process of creating a spreadsheet from specifications or a design calls upon most of the skills that have been covered prior to this lesson. Here are a few specific comments related to creating a spreadsheet for decision making:

1. Remember that you can use the Copy command to copy sections of your spreadsheet. This is useful as you try different scenarios in a "what if" model. Rather than trying to remember the results of each scenario, you can copy sections of your spreadsheet so that the results of several scenarios can be visible at once.

2. Use built-in functions when you can. You may need to create some formulas from the primitive mathematical operations, but it is much easier to use built-in functions when they exist. You can use the Insert Function dialog box to identify what built-in functions are available.

3. Implement in small reusable "chunks." You may find it useful to copy partial solutions from existing spreadsheets. For example, one common partial solution that is required in may spreadsheets is a section that calculates an amortization table. Rather than create this section each time it is needed, it's much easier to copy the section from an existing spreadsheet. To facility this type of "sharing" you can partition your solutions into somewhat autonomous units and save your work so that it is available for future projects.