PRDV006: Spreadsheets II: Formatting and Functions

We used several formulas and functions to complete the Personal Budget workbook shown in Figure 14. All the formulas and functions entered contain cell references that allow for various what-if scenarios. You can use Goal Seek to conduct these what-if scenarios. Goal Seek maximizes the benefits of Excel’s cell-referencing capabilities by changing inputs to precise values to achieve specific outputs produced by formulas or functions.

We begin by changing one of the inputs in the Personal Budget workbook through the following steps:

1. Click the Budget Detail worksheet tab.
   
   
2. Click cell D9.
   
   
3. Type the number 2000. Instead of planning a decrease in our vacation spending, we see what happens to our budget if we spend the same amount as last year, which was $2,000.
   
   
4. Press the ENTER key.

Figure 15 and Figure 16 show the Budget Detail worksheet before and after the change in the annual vacation budget. Compare these two figures to see that many of the outputs, formulas, and functions produced in the worksheet changed by changing just one input.

Here is a list of the changes that occurred in the worksheet:

• The formula output in cell F12 shows that we are planning a 1.1% increase in our total spending instead of a −1.7% decrease.
  
  
• The formula output in cell F9 changes from −25% to 0%.
  
  
• The SUM function in cell D12 changes from $17,950 to $18,450.
  
  
• The SUM function in cell C12 changes from $1,496 to $1,538.
  
  
• The AVERAGE function in cell D14 changes from $1,994 to $2,050.
  
  

 

Figure 15 Budget Detail Worksheet before Changing the Annual Vacation Budget

Figure 16 Budget Detail Worksheet after Changing the Annual Vacation Budget

In addition to the changes in the Budget Detail worksheet, outputs of formulas and functions on the Budget Summary worksheet also changed when the Annual Spend for the Vacation category was increased. To see the changes, compare Figure 14 to Figure 17. There were 14 changes in the outputs of formulas and functions on the Budget Summary worksheet. In total, 21 outputs changed in the Personal Budget workbook due to changing just one input.

Figure 17 Budget Summary Worksheet after Changing the Annual Vacation Budget

One of the most notable changes in the Budget Summary worksheet is the Savings Projection in cell D10. Spending an additional $500 a year on vacation plans decreases the value of the projected savings in 10 years by $5,865. However, what if the rate of return were to increase? An increase in the rate of return could recover the decrease in the future value of our savings plan. We can use Goal Seek to determine how much the rate of return would increase to achieve our savings plan target of $25,000.

The following steps explain how to use Goal Seek to accomplish this goal:

1. Click the Budget Summary worksheet tab.
   
   
2. Click the Data tab of the Ribbon.
   
   
3. Click the What-If Analysis button in the Data Tools group of commands.
   
   

4. Click Goal Seek from the list options (see Figure 18 Selecting Goal Seek from the What-If Analysis Options). This opens the Goal Seek dialog box.

   Mouseless Commands

   Goal Seek: Press the Alt key on your keyboard and then the letters A, W, and G one at a time.

   
   

   Figure 18 Selecting Goal Seek from the What-If Analysis Options
   
   

5. Click the Collapse Dialog button next to the "Set cell:" input box on the Goal Seek dialog box.
   
   
6. Click cell D10 on the Budget Summary worksheet.
   
   
7. Press the ENTER key on your keyboard.
   
   
8. Place the mouse pointer over the "To value" input box in the Goal Seek dialog box and click.
   
   
9. Type 25000 in the "To value" input box in the Goal Seek dialog box.
   
   
10. Click the Collapse Dialog button next to the "By changing cell" input box in the Goal Seek dialog box.
    
    
11. Click cell D13 on the Budget Summary worksheet.
    
    
12. Press the ENTER key on your keyboard.
    
    
13. Click the OK button on the Goal Seek dialog box.
    
    
14. Click the OK button on the Goal Seek Status dialog box (see Figure 20). The status box tells you that Excel found a value for cell D13 that produces an output of $25,000 for the FV function in cell D10.
    
    
15. Figure 19 shows the final settings for the Goal Seek dialog box before clicking the OK button.
    
    

Figure 19 Final Settings for the Goal Seek Dialog Box

Figure 20 shows the solution Goal Seek calculated for the rate of return. Notice that to achieve the target savings plan of $25,000, the rate of return must increase to 7.8%. Initially, it appears that we can spend the additional $500 a year on vacations and still achieve our savings goal of $25,000. However, achieving a 7.8% annual rate of return will require us to make riskier investments with our savings.

Thus, we could lose a substantial amount of our savings. This is the downside of decreasing your overall savings rate. If you save less money, it forces you to take higher risks with the money you have to achieve higher rates of return. Unfortunately, many people end up losing out on these risks, which severely compromises their ability to reach their savings goals.

Figure 20 Solution Calculated by Goal Seek