MrExcel.com - Your One Stop for Excel Tips & Solutions

Do 40 What-if Analyses Quickly

February 10, 2022 - by

Do 40 What-if Analyses Quickly

Problem: I want to buy a car, and I want to compare eight price points and four loan terms to calculate the monthly payment amount.

Strategy: You can solve this problem by using a data table. You set up the worksheet as follows:

  • 1. Build the model shown in A1:B4 below. Cell B4 will become the top left corner cell of your what-if table.

  • 2. In cells B5:B5, enter the four possible terms you would like to compare. In cells C4:L4, enter the possible loan amounts.

  • 3. Select the rectangular range B5:L9. The upper-left corner of this range contains the formula to calculate your monthly payment.

The resulting formula from any model is in the top left corner of the table. Car Prices are across the top row. Loan terms are in the left column. Select a range containing the result formula, all top row, all left column, and the blank cells at the intersections of the top row and left column.
Figure 326. Loan terms along the side, loan amounts across the top.

  • 4. Select Data, What-If Analysis, Data Table. Excel will ask you to specify a row input cell. In other words, Excel will take each cell in the top row of the table and substitute it for the row input cell. Because these cells contain prices, choose cell B1 as the row input cell.

  • 5. Next, Excel wants to know where the cells in the first column of your data table should be used. Because B5:B8 contains loan terms, specify cell B3 as the Column Input Cell. Click OK.

Choose Data, What-If Analysis, Data Table. For the Row Input cell, specify the price cell of $B$1. For the Column Input Cell, specify the Term of $B$2.
Figure 327. Each cell in the top row gets plugged into B1.

Excel will enter an array formula for you, based on the original formula in the top-left cell of the table. It will show you the monthly prices for many combinations of terms and price points.

Excel will run the model for each combination of top row and left column.
Figure 328. 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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Sammy Wong on Unsplash