1 / 49

Project 7

Project 7. Jason C. H. Chen, Ph.D. Professor of Management Information Systems School of Business Administration Gonzaga University Spokane, WA 99258, USA Chen@gonzaga.edu. Forecasting Values With What-If Analysis: Using Data Tables, Scenarios, Goal Seek, and Solver.

joy
Download Presentation

Project 7

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Project 7 Jason C. H. Chen, Ph.D. Professor of Management Information Systems School of Business Administration Gonzaga University Spokane, WA 99258, USA Chen@gonzaga.edu Forecasting Values With What-If Analysis: Using Data Tables, Scenarios, Goal Seek, and Solver

  2. Create a one-variable data table Create a two-variable data table Create scenarios Use goal seek to determine the monthly contribution required to product a specific retirement amount Use solver to determine optimum values Use the trend worksheet function to predict future sales Excel Skills

  3. Key Terms • adjustable cells • The cells that will change to produce a solution when using Solver. The adjustable cells must be related to the target cells. • Constraints • A method for restricting values in a Solver model. • data table • A range of cells that shows how changing certain values in a formula changes the result.

  4. Key Terms • Goal Seek • A method for finding the input value that a formula must have to return a specified result. When using Goal Seek, Excel varies the value in a cell that you specify until a formula that's dependent on that cell returns the result you want. • input cell • The cell in a worksheet in which a list of input values from a data table is substituted. • Scenario • A named set of input values that you can substitute in a worksheet model.

  5. Key Terms • Solver • An Excel tool for determining the maximum or minimum value of one cell by changing other cells. • target cell • In Solver, the cell that will display the optimal value is called the target cell.

  6. Objectives • Create a one-variable data table • Create a two variable data table • Use Goal Seek to constrain vales in a what if analysis • Use the TREND function to predict future sales • Create and save multiple scenarios • Use Solver to determine which value produces a desired result

  7. Running Case • Mr. Traylor now wants to know whether the Selections, Inc. finance department will be able to use Excel to analyze existing data to determine future performance..

  8. Project Challenge • First, they want to post to the corporate intranet a simple worksheet for employees to look at different investment scenarios for their 401K retirement plans. • Second, he wants to know if Excel can predict next quarter’s sales, based upon the existing data. Then, he wants to see how constraining expenses will impact profits. Finally, he wants to somehow save these scenarios so he can present this data to the e-commerce management team.

  9. Forecasting Future Values • With a One-Variable Data Table • With a Two-Variable Data Table

  10. Task 1: To Create a One-Variable Data Table 0. Open a new file 1. Rename the first worksheet tab Base Retirement Plan 2. Enter the text constants in column A and cell B12. Change the format of all text values in column A to Bold. 3. Select the range A5:B5, and click the Merge and Center button on the Formatting toolbar. Select cells A11:B11 and merge and center the selection. 4. Type 1000 in cell B6 and 250 in cell B7. Format both entries to currency two decimals places. 5 Type 5% in cell B8.

  11. Task 1: To Create a One-Variable Data Table (cont.) 6. Type 35 in cell B9. Create a custom format for the cell that displays the text Years after the value. How do you create a custom format? 6. (cont). Select the cell B9, click Format, Cells, and click the Number tab (it may already be selected). b) Select Custom as the format category, and enter 00 “Years” in the Type: text box. Click OK to accept the format.

  12. Task 1: To Create a One-Variable Data Table (cont.) • Apply the All borders format to the ranges A5:B9 and A11:B18. • Type =b8 in cell A13. Type the percentage displayed in cells A14:A18

  13. Task 1: To Create a One-Variable Data Table (cont.) 9. Place the insertion point in cell B13. Type =FV(B8/12, B9*12, -B7,-B6) as the formula in this cell. 10. Save the workbook as Retirement Calculator.xls FV(rate, periods, payment) it returns the future value of an investment based on periodic, constant payments, and a constant interest rate

  14. Task 1: To Create a One-Variable Data Table (cont.) 11. Select the range A13:B18. Click Data, Table, as shown. 12. Type b8 in the Column input cell: text box of the Table dialog box. Click OK.

  15. Task 1: To Create a One-Variable Data Table (cont.) 13. Adjust the width of columns A and B to display all values. Format cells B14:B18 to currency with 2 decimal places. 14. Save the workbook.

  16. Task 2: To Create a Two-Variable Data Table • Rename the Sheet2 worksheet tab as Adjusted Retirement Plan • 2. Copy cells A1:B18 of the Base Retirement Plan worksheet, and paste the selection into the Adjusted Retirement Plan worksheet, beginning at cell A1. • 3. Change the width of Columns B through F to 14.00 • 4. Highlight the range A13:A18 and drag the selection down one row as shown.

  17. Task 2: To Create a Two-Variable Data Table (cont.) 5. Highlight cells B14:B18 and press [Del] (not Edit, Delete) to delete the formulas. 6. Move the the formula in cell B13 to cell A13, and apply bold formatting. (use Edit, Cut, then Paste) 7. Apply the All Borders format to the range A11:F19

  18. Task 2: To Create a Two-Variable Data Table (cont.) 8. Type Monthly Contribution in cell B12 and merge and center the label through cell F12. 9. Merge and center the label in cell A11 through cell F11.

  19. Task 2: To Create a Two-Variable Data Table (cont.) 10. Type 100, 150, 200, 250, and 300 in cells B13, C13, D13, E13, and F13 respectively. Format these values as currency, with no decimal places, and as Bold.

  20. Row (b7) Col.(b8) Task 2: To Create a Two-Variable Data Table (cont.) 11. Select the range A13:F19. Click Data, Table. 12. In the Table dialog box, type b7 as the row input cell and b8 as the column input cell, as shown. Click OK. The Two-Variable data table substituting the interest rate (b8) and monthly contribution (b7) values in the FV formula.

  21. Task 2: To Create a Two-Variable Data Table (cont.) The Two-Variable data table substituting the interest rate (b8) and monthly contribution (b7) values in the FV formula. 13. Format all resulting values as currency, with two decimal places. 14. Save your workbook.

  22. Goal Seek and What-If Analysis • Using Goal Seek to constrain values in a What-If analysis • Determine what monthly contribution will yield a specific retirement amount after 35 years

  23. Task 3: Use Goal Seek to Determine the Monthly Contribution Required to Produce a Specific Retirement Amount • Click Tools, Goal Seek • In the Goal Seek dialog box, type a13 in the Set cell: text box, 500000 in the To value: text box, and b7 in the By changing cell: text box. 3. Click OK. Goal Seek finds a solution.

  24. Task 3: Use Goal Seek to Determine the Monthly Contribution Required to Produce a Specific Retirement Amount (cont.) 3. Goal Seek finds a solution (cont.) 4. Click Cancel to restore the worksheet to its original settings.

  25. Check Point: What monthly contribution is required to generate a retirement account of $1.5 million at 14% interest?

  26. Predict Future Sales based on Historic Sales Data • TREND FUNCTION

  27. Task 4: Use TREND Function to Predict Future Sales 1. Open the Selections R-E Values.xls workbook. 2. Select the range of worksheets from Boston to Seattle. 3. Place the insertion point in cell E5. Because you have selected a range of worksheets, the formula you create will appear in each worksheet in the rang. 4. Click the Insert Function button on the Standard toolbar. The Paste Function dialog box will appear.

  28. Task 4: Use TREND Function to Predict Future Sales (cont.) 5. Click All in the function category list, and scroll the Function name; list to display the TRENDworksheet function. 6. Click OK. The formula Palette will appear. 7. Type b5:d5 in the Known-Y’s box. Click OK. Excel calculates the predicated value.

  29. Task 4: Use TREND Function to Predict Future Sales (cont.) 7. (cont.) Type b5:d5 in the Known-Y’s box. Click OK. Excel calculates the predicated value.

  30. Task 4: Use TREND Function to Predict Future Sales (cont.) 8. Copy this formula to cell E6, and to cells E12:E16

  31. Task 4: Use TREND Function to Predict Future Sales (cont.) 9. Click cell F5. Modify the formula so it sums cells B5:E5 (used to be B5:D5) Copy the formula to the other cells in the workbook range that calculate the total for each revenue or expense item (i.e., F6:F7 and F12:F16)

  32. Task 4: Use TREND Function to Predict Future Sales (cont.) 10. Rename the 1st Quarter Summary worksheet tab Sales Summary. 11. Use the Fill handle to copy the formulas from the range E5:E11 to F5:F11. Click cell A1. 12. Save the workbook. The Sales Summary worksheet should display the totals for April.

  33. Working with Scenarios • A scenario is a set of values that Microsoft Excel saves and can substitute automatically in your worksheet. • Use scenarios either • to forecast the outcome of a worksheet model, or • Restore a worksheet to existing values.

  34. Task 5: To Create Scenarios 1. Open the Solver Data.xls workbook. 2. Click the Boston worksheet tab to make it the active sheet. 3. Click Tools, Scenarios. 4. The Scenario Manager dialog box will appear. As you can see, there are no scenarios in the workbook. Click Add.

  35. Task 5: To Create Scenarios (cont.) 5. The Edit Scenario dialog box will now appear. Name the scenario Boston Projected, and enter the range B12:D16 (or select range using mouse) in the Changing Cells: textbox. Click OK.

  36. Task 5: To Create Scenarios (cont.) 6. The Scenario Values dialog box will appear. This verifies the data that will be saved as a part of the scenario. Because these are the values in the range B12:D16, click OK.

  37. Task 5: To Create Scenarios (cont.) 7. The Scenario Manager will appear again, and the scenario you just created will appear in the list. Click the Close button. 8. Repeat the procedure listed above to create four additional scenarios, named Dallas projected, Denver Projected, Indianapolis Projected, and Seattle Projected. 9. Save the workbook. Now that you have created five scenarios that will return each worksheet to its beginning state, you are ready to forecast optimum expense values for each worksheet.

  38. SOLVER • A common task in conducting a what-if analysis is to see how constraining certain values in a worksheet will affect other values. • You can use Excel’s Solver to determine the maximum or minimum value of one cell by changing other cells. • Target cell • Adjustable cells • constraints

  39. Task 6: To Calculate Optimum Values Using Solver • Click the Boston worksheet tab to make it the active sheet. • 2. Click Tools, Solver. • (if Solver is not available, click Tools, Add-ins then select Solver Addin, the click OK) 3. You will control the total expenses by changing the variable expense figures. Enter the values shown in the figure in the appropriate areas of the Solver dialog box.

  40. Task 6: To Calculate Optimum Values Using Solver (cont.) 3. (Repeated) You will control the total expenses by changing the variable expense figures. Enter the values shown in the figure in the appropriate areas of the Solver dialog box.

  41. 4. You will now add four constraints to the solution. Click the Add button. For the first constraint, you will limit utilities to a total of $3500 for the Quarter. Enter the values as shown in the Figure. Click Add. 5. For a second constraint, set the value of cell E14 less than or equal to 36000. Click Add. 6. For the third constraint, set the value of cell E15 less than or equal to 4200. Click Add. 7. For the final constraint, set the value of cell E16 less than or equal to 18000. Click OK. Task 6: To Calculate Optimum Values Using Solver (cont.)

  42. Task 6: To Calculate Optimum Values Using Solver (cont.) 8. Click Solve. The Solver Results dialog box appears. 9. Click the Save Scenario button. Name the scenario Boston Optimized and click OK. 10. Click ok in the Solver Results dialog box. The solution is displayed in the Figure.

  43. Task 6: To Calculate Optimum Values Using Solver (cont.) Notice that Excel has altered the data according to each constraint you specified. 11. Save and close the workbook.

  44. Solution – Data Tables & Goal Seek

  45. Using the TREND Function

  46. Solver

  47. Project 7 Forecasting Values With What-If Analysis: Using Data Tables, Scenarios, Goal Seek, and Solver

More Related