DO 40 WHAT-IF ANALYSES QUICKLY

Figure 317 The formula is replicated for each cell.

If you are looking for a monthly payment of $425, you will have to either negotiate down to a price of $21,995 with a 60-month loan, $23,995 with a 66-month loan, or choose a 72-month loan.

The formulas in the table are live. You can reenter new values in the first column and row of the table in order to zoom in on possible scenarios.

Additional Details: You can also change the formula in B4, and the table will update.

RANDOM WALK DOWN WALL STREET

The previous example is the classic use of the Data Table function. However, while judging the ModelOff World Financial Championships in New York in 2012, I met professor Simon Benninga and he demonstrated a very different use for data tables.

First, build a column that represents 100 coin flips. If the RAND() is > .5 then you win a penny, otherwise you lose a penny. Add a graph. Every time you press F9, Excel runs the 100 coin flips again and the graph updates.

Figure 318 Simulating 100 coin flips using RAND().

You might be interested in some statistics from these 100 coin flips. What was the highest you were ever ahead? What was the lowest you were ever behind? Where did you finish after 100 coin flips. Set up formu- las going across a row with =MAX(A2:A101), =MIN(A2:A101), and =A101.

Now - say that you want to run the 100 coin flip experiment 1000 times. Select the blank cell to the left of your formulas, the three formulas, and then 1000 blank rows below. Select Data, What-If, Data Table. You will leave the Row Input Cell blank. For the Column Input Cell, choose any blank cell outside of the table.

Figure 319 Run the what-if table based on a blank cell.

This is a seemingly bizarre request. You are telling Excel to take the 1000 blank cells in K2:K1001, plug them in to the blank J1 cell, and record the results of the Max, Min, and Final. Since those cells are the results of formulas containing =RAND() or =RANDBETWEEN(), each row in the resulting data table rep- resents the results of 100 coin flips. In all, you’ve effectively modeled 100,000 coin flips.

2

Figure 320 Each row shows the statistics after 100 coin flips.

This technique works because your model is based on one of the random functions.

WHAT-IF FOR 3 OR MORE VARIABLES

Problem: The previous trick is cool, but what if I have three or more variables to change?

Strategy: If you have 3 variables to change, make many copies of the worksheet in the above example and change the third variable in each copy of the table.

If you have 3 or more variables, you can reluctantly use Excel’s Scenario Manager as described in this topic. If your manager has $99 in the budget, you can instead buy the MrExcel.com Monte Carlo Manager to handle multiple variable scenarios easily.

Excel’s Scenario Manager is found in the What-If dropdown of the Data ribbon tab. The tool will let you specify any number of input variables and any number of output variables. For each scenario, you have to type the input variables into a dialog box. The Scenario Manager will then produce a report of all the scenarios.

1. This step is optional, but the output report will be more meaningful if you name all of the input cells and all of the output cells.

2. Select Data, What-If Analysis, Scenario Manager.

3. Click the Add... button in the Scenario Manager dialog.

4. Type a name for the scenario using the current values. Specify the input cells by clicking the first cell and Ctrl+clicking the other input cells. Click OK.