PRDV006: Spreadsheets II: Formatting and Functions

We used several formulas and functions to complete the Personal Budget workbook shown in Figure 14 Results of the Savings Plan Projections. All the formulas and functions entered contain cell references that allow for a variety of what-if scenarios. Goal Seek is a tool that can be used in the process of conducting 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 will 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 will 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 Budget Detail Worksheet  and Figure 16 Budget Detail Worksheet show the Budget Detail worksheet before and after the change in the annual vacation budget. By comparing these two figures you can see that by changing just one input, many of the outputs produced by the formulas and functions in the worksheet changed. The following is a list of the changes that occurred in the worksheet:

• The formula output in cell F12 now shows that we are planning a 1.1% increase in our total spending as opposed to 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 change when the Annual Spend for the Vacation category was increased. To see the changes, compare Figure 14 Results of the Savings Plan Projections to Figure 17 Budget Summary Worksheet. There were a total of fourteen changes in the outputs of formulas and functions on the Budget Summary worksheet. In total, there were twenty-one outputs that changed in the Personal Budget workbook as a result of changing just one input.

Figure 17 Budget Summary Worksheet after Changing the Annual Vacation Budget

One of the most notable changes on the Budget Summary worksheet is the Savings Projection in cell D10. By spending an additional $500 a year on vacation plans, the projected savings value in 10 years decreases 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 a tool such as Goal Seek to determine exactly how much the rate of return would have to 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 the number 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 Solution Calculated by Goal Seek). The status box is telling 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 Final Settings for the Goal Seek Dialog Box 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 Solution Calculated by Goal Seek shows the solution Goal Seek calculated for the rate of return. Notice that in order 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, there is a greater possibility that 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 in order to achieve higher rates of return. Unfortunately, many people end up on the losing end of these risks, which severely compromises their ability to reach their savings goals.

Figure 20 Solution Calculated by Goal Seek