Illustrated Excel 2016 | Module 11: SAM Project 1a Scandia Kayaks PERFORMING WHAT-IF ANALYSES   GETTING STARTED Open the file IL_EX16_11a_FirstLastName_1

Subject:MS ExcelPrice:19.87 Bought3

Share With

Illustrated Excel 2016 | Module 11: SAM Project 1a

Scandia Kayaks

PERFORMING WHAT-IF ANALYSES

GETTING STARTED

• Open the file IL_EX16_11a_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as IL_EX16_11a_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
  • With the file IL_EX16_11a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
    • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
    • This project requires you to use the Solver and the Analysis ToolPak add-ins. If these add-ins are not available on the Data tab in the Analysis group (or if the Analysis group is not available), install Solver and the Analysis ToolPak as follows:
      • In Excel, click the File tab, and then click Options in the left navigation bar.
      • Click the Add-Ins option in the left pane of the Excel Options dialog box.
      • Click the Manage arrow, click the Excel Add-Ins option, and then click the Go button.
      • In the Add-Ins dialog box, click the Analysis ToolPak and the Solver Add-In check boxes and then click the OK button.
      • Follow any remaining prompts to install the Analysis ToolPak and Solver.

PROJECT STEPS

1. As an intern in the Financial department of Scandia Kayaks, you assist Jay Chen, a financial analyst. Jay has asked you to work with the company’s current revenue and sales data in Excel to develop scenarios and what-if analyses that identify ways to increase profits.

Switch to the Revenue and Expenses worksheet. Jay wants to know how profits would change if the company sold 5 percent fewer kayaks of each model. Create a scenario to provide this information for Jay as follows:

1. In the Scenario Manager, add a new scenario and use Unit Sales Decrease 5% as the scenario name.
2. Change the values in the range F12:H12. (Hint:Two scenarios are already defined for the Revenue and Expenses worksheet.)
3. Use the information shown in Table 1 below as the values for the changing cells.

Table 1: Unit Sales Decrease 5% Scenario Values

1. Show the results of the Unit Sales Decrease 5% scenario.
2. Compare the results of the three scenarios as follows:
   1. Create a Scenario Summary Report for result cells B8:B10, F13:H13. [Mac Hint: If a “The command you chose cannot be performed with multiple selections” prompt appears, click the OK button until the Scenario Summary Report appears.]
   2. On the Scenario Summary worksheet, delete column D to avoid repeating the data shown in column G.
   3. Delete the contents of the range B16:B18 to remove the notes.
   4. Switch to the Profit worksheet. Determine how changing the number of Dane model kayaks sold will affect gross profit as follows:
      1. In cell G7, enter a formula that references cell B13.
      2. Select the range F7:G12 and then create a one-input data table.
      3. Use the value in cell B12 as the column input cell because cell B12 contains the units sold amount for Dane model kayaks.
      4. Format the range G8:G12 using the Currency number format with zero decimal places and the $ symbol to match the formatting of cell G7.
      5. Jay asks you to determine how many Swede model kayaks the company needs to sell to earn a gross profit per unit of $125.00. Provide this information to Jay as follows:
         1. Use Goal Seek to set cell C14 (the gross profit per Swede kayak) to a value of 125.
         2. Change the number of units sold in cell C12 to achieve the goal.
         3. Jay wants to perform a similar analysis for the Finn model kayaks by determining how many Finn kayaks to sell to earn a gross profit per unit of $150.00. Provide this information to Jay as follows:
            1. Use Goal Seek to set cell D14 (the gross profit per Finn kayak) to a value of 150.
            2. Change the number of units sold in cell D12 to achieve the goal.
            3. Switch to the Norse worksheet. Scandia Kayaks is designing a new kayak model named Norse and plans to work with three vendors to manufacture the new kayak. Jay wants to know how to minimize the total manufacturing cost. Run Solver to solve this problem as follows:
               1. Set the objective as minimizing the value in cell E12 (Mfg_Total_Cost). (Hint: Be sure to select the Min option to minimize the value.)
               2. Use the range B11:D11 as the changing variable cells.
               3. Adjust the total manufacturing cost by each vendor using the constraints in Table 2 below:

Table 2: Solver Constraints

1. Run Solver, keep the solution, and then return to the Solver Parameters dialog box. Make unconstrained variables non-negative and use GRG Nonlinear as the solving method. Save the model beginning in cell D16, and then close the Solver Parameters dialog box.
2. Jay wants to compare the values you found using Solver to the original values. Provide this information as follows:
   1. In the Scenario Manager, add a scenario using Vendors Best as the scenario name. (Hint:The Norse worksheet already contains a predefined scenario containing the original values of cells B11:D11.)
   2. Accept the current values for the changing cells, which show the results of the Solver model.
   3. Create a Scenario Summary report using cell E11 (which has the defined name Total_Units) as the resultcell. [Mac Hint: If a “The command you chose cannot be performed with multiple selections” prompt appears, click the OK button until the Scenario Summary Report appears.]
   4. Change the name of the Scenario Summary 2 worksheet to Vendors Scenario to distinguish it from the other Scenario Summary worksheet.
   5. Enter the text Optimal Values in cell D3.
   6. Jay asks you to produce summary statistics on the Norse Projections data. Provide this information as follows:
      1. Return to the Norse worksheet, and then use the Data Analysis tool to generate Descriptive Statistics.
      2. Use the range H12:L12 as the input range.
      3. Indicate that the data is grouped by rows.
      4. Display the statistics on a new worksheet and use Norse Stats as the worksheet name.
      5. Produce summary statistics.
      6. Switch to the Norse Stats worksheet and then modify the worksheet to make its contents easier to read as follows:
         1. Resize column A to its best fit to display all of the row labels.
         2. Enter Norse Profit Years 1-5 in cell A1 to provide a descriptive heading for the statistics.

Your workbook should look like the Final Figure on the following pages. In the Profit worksheet, cells C13 and D13 are intentionally blurred. The Norse worksheet Final Figure has been intentionally omitted. Save your changes, close the workbook, then exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: Scenario Summary Worksheet

Final Figure 2: Revenue and Expenses Worksheet

Final Figure 3: Profit Worksheet

Final Figure 4: Vendors Scenario Worksheet

Final Figure 5: Norse Stats Worksheet