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
- 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:
- In the Scenario Manager, add a new scenario and use Unit Sales Decrease 5% as the scenario name.
- Change the values in the range F12:H12. (Hint:Two scenarios are already defined for the Revenue and Expenses worksheet.)
- Use the information shown in Table 1 below as the values for the changing cells.
Table 1: Unit Sales Decrease 5% Scenario Values
Changing Cell |
Value |
Dane_Units_Sold (F12) |
20900 |
Swede_Units_sold (G12) |
20283 |
Finn_Units_Sold (H12) |
19665 |
- Show the results of the Unit Sales Decrease 5% scenario.
- Compare the results of the three scenarios as follows:
- 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.]
- On the Scenario Summary worksheet, delete column D to avoid repeating the data shown in column G.
- Delete the contents of the range B16:B18 to remove the notes.
- Switch to the Profit worksheet. Determine how changing the number of Dane model kayaks sold will affect gross profit as follows:
- In cell G7, enter a formula that references cell B13.
- Select the range F7:G12 and then create a one-input data table.
- Use the value in cell B12 as the column input cell because cell B12 contains the units sold amount for Dane model kayaks.
- Format the range G8:G12 using the Currency number format with zero decimal places and the $ symbol to match the formatting of cell G7.
- 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:
- Use Goal Seek to set cell C14 (the gross profit per Swede kayak) to a value of 125.
- Change the number of units sold in cell C12 to achieve the goal.
- 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:
- Use Goal Seek to set cell D14 (the gross profit per Finn kayak) to a value of 150.
- Change the number of units sold in cell D12 to achieve the goal.
- 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:
- 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.)
- Use the range B11:D11 as the changing variable cells.
- Adjust the total manufacturing cost by each vendor using the constraints in Table 2 below:
Table 2: Solver Constraints
Requirement |
Cell Reference |
Comparison Operator |
Constraint |
The units produced values must be integers. |
B11:D11 |
int |
integer |
Vendors must produce at least 5,000 units. |
B11:D11 |
>= |
B16 |
Freddi Nautical can produce a maximum of 15,000 units. |
Freddi_Nautical_Units |
<= |
B17 |
Paddle Pro can produce a maximum of 7,500 units. |
PaddlePro_Units |
<= |
B18 |
Green Wave can produce a maximum of 15,000 units. |
Green_Wave_Units |
<= |
B19 |
Scandia Kayaks requires 27,000 Norse kayaks. |
Total_Units |
= |
B20 |
- 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.
- Jay wants to compare the values you found using Solver to the original values. Provide this information as follows:
- 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.)
- Accept the current values for the changing cells, which show the results of the Solver model.
- 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.]
- Change the name of the Scenario Summary 2 worksheet to Vendors Scenario to distinguish it from the other Scenario Summary worksheet.
- Enter the text Optimal Values in cell D3.
- Jay asks you to produce summary statistics on the Norse Projections data. Provide this information as follows:
- Return to the Norse worksheet, and then use the Data Analysis tool to generate Descriptive Statistics.
- Use the range H12:L12 as the input range.
- Indicate that the data is grouped by rows.
- Display the statistics on a new worksheet and use Norse Stats as the worksheet name.
- Produce summary statistics.
- Switch to the Norse Stats worksheet and then modify the worksheet to make its contents easier to read as follows:
- Resize column A to its best fit to display all of the row labels.
- 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
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
- 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:
- In the Scenario Manager, add a new scenario and use Unit Sales Decrease 5% as the scenario name.
- Change the values in the range F12:H12. (Hint:Two scenarios are already defined for the Revenue and Expenses worksheet.)
- Use the information shown in Table 1 below as the values for the changing cells.
Table 1: Unit Sales Decrease 5% Scenario Values
Changing Cell |
Value |
Dane_Units_Sold (F12) |
20900 |
Swede_Units_sold (G12) |
20283 |
Finn_Units_Sold (H12) |
19665 |
- Show the results of the Unit Sales Decrease 5% scenario.
- Compare the results of the three scenarios as follows:
- 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.]
- On the Scenario Summary worksheet, delete column D to avoid repeating the data shown in column G.
- Delete the contents of the range B16:B18 to remove the notes.
- Switch to the Profit worksheet. Determine how changing the number of Dane model kayaks sold will affect gross profit as follows:
- In cell G7, enter a formula that references cell B13.
- Select the range F7:G12 and then create a one-input data table.
- Use the value in cell B12 as the column input cell because cell B12 contains the units sold amount for Dane model kayaks.
- Format the range G8:G12 using the Currency number format with zero decimal places and the $ symbol to match the formatting of cell G7.
- 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:
- Use Goal Seek to set cell C14 (the gross profit per Swede kayak) to a value of 125.
- Change the number of units sold in cell C12 to achieve the goal.
- 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:
- Use Goal Seek to set cell D14 (the gross profit per Finn kayak) to a value of 150.
- Change the number of units sold in cell D12 to achieve the goal.
- 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:
- 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.)
- Use the range B11:D11 as the changing variable cells.
- Adjust the total manufacturing cost by each vendor using the constraints in Table 2 below:
Table 2: Solver Constraints
Requirement |
Cell Reference |
Comparison Operator |
Constraint |
The units produced values must be integers. |
B11:D11 |
int |
integer |
Vendors must produce at least 5,000 units. |
B11:D11 |
>= |
B16 |
Freddi Nautical can produce a maximum of 15,000 units. |
Freddi_Nautical_Units |
<= |
B17 |
Paddle Pro can produce a maximum of 7,500 units. |
PaddlePro_Units |
<= |
B18 |
Green Wave can produce a maximum of 15,000 units. |
Green_Wave_Units |
<= |
B19 |
Scandia Kayaks requires 27,000 Norse kayaks. |
Total_Units |
= |
B20 |
- 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.
- Jay wants to compare the values you found using Solver to the original values. Provide this information as follows:
- 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.)
- Accept the current values for the changing cells, which show the results of the Solver model.
- 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.]
- Change the name of the Scenario Summary 2 worksheet to Vendors Scenario to distinguish it from the other Scenario Summary worksheet.
- Enter the text Optimal Values in cell D3.
- Jay asks you to produce summary statistics on the Norse Projections data. Provide this information as follows:
- Return to the Norse worksheet, and then use the Data Analysis tool to generate Descriptive Statistics.
- Use the range H12:L12 as the input range.
- Indicate that the data is grouped by rows.
- Display the statistics on a new worksheet and use Norse Stats as the worksheet name.
- Produce summary statistics.
- Switch to the Norse Stats worksheet and then modify the worksheet to make its contents easier to read as follows:
- Resize column A to its best fit to display all of the row labels.
- 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
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
- 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:
- In the Scenario Manager, add a new scenario and use Unit Sales Decrease 5% as the scenario name.
- Change the values in the range F12:H12. (Hint:Two scenarios are already defined for the Revenue and Expenses worksheet.)
- Use the information shown in Table 1 below as the values for the changing cells.
Table 1: Unit Sales Decrease 5% Scenario Values
Changing Cell |
Value |
Dane_Units_Sold (F12) |
20900 |
Swede_Units_sold (G12) |
20283 |
Finn_Units_Sold (H12) |
19665 |
- Show the results of the Unit Sales Decrease 5% scenario.
- Compare the results of the three scenarios as follows:
- 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.]
- On the Scenario Summary worksheet, delete column D to avoid repeating the data shown in column G.
- Delete the contents of the range B16:B18 to remove the notes.
- Switch to the Profit worksheet. Determine how changing the number of Dane model kayaks sold will affect gross profit as follows:
- In cell G7, enter a formula that references cell B13.
- Select the range F7:G12 and then create a one-input data table.
- Use the value in cell B12 as the column input cell because cell B12 contains the units sold amount for Dane model kayaks.
- Format the range G8:G12 using the Currency number format with zero decimal places and the $ symbol to match the formatting of cell G7.
- 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:
- Use Goal Seek to set cell C14 (the gross profit per Swede kayak) to a value of 125.
- Change the number of units sold in cell C12 to achieve the goal.
- 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:
- Use Goal Seek to set cell D14 (the gross profit per Finn kayak) to a value of 150.
- Change the number of units sold in cell D12 to achieve the goal.
- 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:
- 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.)
- Use the range B11:D11 as the changing variable cells.
- Adjust the total manufacturing cost by each vendor using the constraints in Table 2 below:
Table 2: Solver Constraints
Requirement |
Cell Reference |
Comparison Operator |
Constraint |
The units produced values must be integers. |
B11:D11 |
int |
integer |
Vendors must produce at least 5,000 units. |
B11:D11 |
>= |
B16 |
Freddi Nautical can produce a maximum of 15,000 units. |
Freddi_Nautical_Units |
<= |
B17 |
Paddle Pro can produce a maximum of 7,500 units. |
PaddlePro_Units |
<= |
B18 |
Green Wave can produce a maximum of 15,000 units. |
Green_Wave_Units |
<= |
B19 |
Scandia Kayaks requires 27,000 Norse kayaks. |
Total_Units |
= |
B20 |
- 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.
- Jay wants to compare the values you found using Solver to the original values. Provide this information as follows:
- 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.)
- Accept the current values for the changing cells, which show the results of the Solver model.
- 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.]
- Change the name of the Scenario Summary 2 worksheet to Vendors Scenario to distinguish it from the other Scenario Summary worksheet.
- Enter the text Optimal Values in cell D3.
- Jay asks you to produce summary statistics on the Norse Projections data. Provide this information as follows:
- Return to the Norse worksheet, and then use the Data Analysis tool to generate Descriptive Statistics.
- Use the range H12:L12 as the input range.
- Indicate that the data is grouped by rows.
- Display the statistics on a new worksheet and use Norse Stats as the worksheet name.
- Produce summary statistics.
- Switch to the Norse Stats worksheet and then modify the worksheet to make its contents easier to read as follows:
- Resize column A to its best fit to display all of the row labels.
- 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
Illustrated Excel 2016 | Module 11: SAM Project 1a
Illustrated Excel 2016 | Module 11: SAM Project 1aIllustrated Excel 2016 | Module 11: SAM Project 1a
Scandia Kayaks
Scandia Kayaks
PERFORMING WHAT-IF ANALYSES
PERFORMING WHAT-IF ANALYSES
PERFORMING WHAT-IF ANALYSES
GETTING STARTED
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.
Open the file IL_EX16_11a_FirstLastName_1.xlsx, available for download from the SAM website.IL_EX16_11a_FirstLastName_1.xlsxFirstLastName
- 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.
Save the file as IL_EX16_11a_FirstLastName_2.xlsx by changing the “1” to a “2”.IL_EX16_11a_FirstLastName_2.xlsx FirstLastName
- 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.
.xlsx
- 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.
IL_EX16_11a_FirstLastName_2.xlsxFirstLastName
- 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.
- 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.
- 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
PROJECT STEPS
- 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.
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.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:
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:Revenue and Expenses
- In the Scenario Manager, add a new scenario and use Unit Sales Decrease 5% as the scenario name.
- Change the values in the range F12:H12. (Hint:Two scenarios are already defined for the Revenue and Expenses worksheet.)
- Use the information shown in Table 1 below as the values for the changing cells.
In the Scenario Manager, add a new scenario and use Unit Sales Decrease 5% as the scenario name.the Scenario Manager, add a new scenario and use Unit Sales Decrease 5% as the scenario name.Unit Sales Decrease 5%
Change the values in the range F12:H12. (Hint:Two scenarios are already defined for the Revenue and Expenses worksheet.) F12:H12HintRevenue and Expenses
Use the information shown in Table 1 below as the values for the changing cells.
Table 1: Unit Sales Decrease 5% Scenario Values
Table 1: Unit Sales Decrease 5% Scenario Values
Table 1: Unit Sales Decrease 5% Scenario Values
Changing Cell |
Value |
Dane_Units_Sold (F12) |
20900 |
Swede_Units_sold (G12) |
20283 |
Finn_Units_Sold (H12) |
19665 |
Changing Cell |
Value |
Dane_Units_Sold (F12) |
20900 |
Swede_Units_sold (G12) |
20283 |
Finn_Units_Sold (H12) |
19665 |
Changing Cell
Value
Dane_Units_Sold (F12)
20900
Swede_Units_sold (G12)
20283
Finn_Units_Sold (H12)
19665
Changing Cell
Value
Changing Cell
Changing Cell
Changing CellChanging Cell
Value
Value
ValueValue
Dane_Units_Sold (F12)
20900
Dane_Units_Sold (F12)
Dane_Units_Sold (F12)
Dane_Units_Sold (F12)Dane_Units_Sold (F12)
20900
20900
2090020900
Swede_Units_sold (G12)
20283
Swede_Units_sold (G12)
Swede_Units_sold (G12)
Swede_Units_sold (G12)Swede_Units_sold (G12)
20283
20283
2028320283
Finn_Units_Sold (H12)
19665
Finn_Units_Sold (H12)
Finn_Units_Sold (H12)
Finn_Units_Sold (H12)Finn_Units_Sold (H12)
19665
19665
1966519665
- Show the results of the Unit Sales Decrease 5% scenario.
- Compare the results of the three scenarios as follows:
- 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.]
- On the Scenario Summary worksheet, delete column D to avoid repeating the data shown in column G.
- Delete the contents of the range B16:B18 to remove the notes.
- Switch to the Profit worksheet. Determine how changing the number of Dane model kayaks sold will affect gross profit as follows:
- In cell G7, enter a formula that references cell B13.
- Select the range F7:G12 and then create a one-input data table.
- Use the value in cell B12 as the column input cell because cell B12 contains the units sold amount for Dane model kayaks.
- Format the range G8:G12 using the Currency number format with zero decimal places and the $ symbol to match the formatting of cell G7.
- 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:
- Use Goal Seek to set cell C14 (the gross profit per Swede kayak) to a value of 125.
- Change the number of units sold in cell C12 to achieve the goal.
- 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:
- Use Goal Seek to set cell D14 (the gross profit per Finn kayak) to a value of 150.
- Change the number of units sold in cell D12 to achieve the goal.
- 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:
- 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.)
- Use the range B11:D11 as the changing variable cells.
- Adjust the total manufacturing cost by each vendor using the constraints in Table 2 below:
Show the results of the Unit Sales Decrease 5% scenario.
- 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.]
- On the Scenario Summary worksheet, delete column D to avoid repeating the data shown in column G.
- Delete the contents of the range B16:B18 to remove the notes.
- Switch to the Profit worksheet. Determine how changing the number of Dane model kayaks sold will affect gross profit as follows:
- In cell G7, enter a formula that references cell B13.
- Select the range F7:G12 and then create a one-input data table.
- Use the value in cell B12 as the column input cell because cell B12 contains the units sold amount for Dane model kayaks.
- Format the range G8:G12 using the Currency number format with zero decimal places and the $ symbol to match the formatting of cell G7.
- 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:
- Use Goal Seek to set cell C14 (the gross profit per Swede kayak) to a value of 125.
- Change the number of units sold in cell C12 to achieve the goal.
- 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:
- Use Goal Seek to set cell D14 (the gross profit per Finn kayak) to a value of 150.
- Change the number of units sold in cell D12 to achieve the goal.
- 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:
- 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.)
- Use the range B11:D11 as the changing variable cells.
- Adjust the total manufacturing cost by each vendor using the constraints in Table 2 below:
Compare the results of the three scenarios as follows:
- 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.]
- On the Scenario Summary worksheet, delete column D to avoid repeating the data shown in column G.
- Delete the contents of the range B16:B18 to remove the notes.
- Switch to the Profit worksheet. Determine how changing the number of Dane model kayaks sold will affect gross profit as follows:
- In cell G7, enter a formula that references cell B13.
- Select the range F7:G12 and then create a one-input data table.
- Use the value in cell B12 as the column input cell because cell B12 contains the units sold amount for Dane model kayaks.
- Format the range G8:G12 using the Currency number format with zero decimal places and the $ symbol to match the formatting of cell G7.
- 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:
- Use Goal Seek to set cell C14 (the gross profit per Swede kayak) to a value of 125.
- Change the number of units sold in cell C12 to achieve the goal.
- 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:
- Use Goal Seek to set cell D14 (the gross profit per Finn kayak) to a value of 150.
- Change the number of units sold in cell D12 to achieve the goal.
- 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:
- 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.)
- Use the range B11:D11 as the changing variable cells.
- Adjust the total manufacturing cost by each vendor using the constraints in Table 2 below:
Create a Scenario Summary Report for result cells B8:B10, F13:H13. B8:B10F13: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.]
On the Scenario Summary worksheet, delete column D to avoid repeating the data shown in column G.Scenario Summary
Delete the contents of the range B16:B18 to remove the notes.
- In cell G7, enter a formula that references cell B13.
- Select the range F7:G12 and then create a one-input data table.
- Use the value in cell B12 as the column input cell because cell B12 contains the units sold amount for Dane model kayaks.
- Format the range G8:G12 using the Currency number format with zero decimal places and the $ symbol to match the formatting of cell G7.
- 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:
- Use Goal Seek to set cell C14 (the gross profit per Swede kayak) to a value of 125.
- Change the number of units sold in cell C12 to achieve the goal.
- 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:
- Use Goal Seek to set cell D14 (the gross profit per Finn kayak) to a value of 150.
- Change the number of units sold in cell D12 to achieve the goal.
- 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:
- 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.)
- Use the range B11:D11 as the changing variable cells.
- Adjust the total manufacturing cost by each vendor using the constraints in Table 2 below:
Switch to the Profit worksheet. Determine how changing the number of Dane model kayaks sold will affect gross profit Profit
- In cell G7, enter a formula that references cell B13.
- Select the range F7:G12 and then create a one-input data table.
- Use the value in cell B12 as the column input cell because cell B12 contains the units sold amount for Dane model kayaks.
- Format the range G8:G12 using the Currency number format with zero decimal places and the $ symbol to match the formatting of cell G7.
- 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:
- Use Goal Seek to set cell C14 (the gross profit per Swede kayak) to a value of 125.
- Change the number of units sold in cell C12 to achieve the goal.
- 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:
- Use Goal Seek to set cell D14 (the gross profit per Finn kayak) to a value of 150.
- Change the number of units sold in cell D12 to achieve the goal.
- 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:
- 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.)
- Use the range B11:D11 as the changing variable cells.
- Adjust the total manufacturing cost by each vendor using the constraints in Table 2 below:
In cell G7, enter a formula that references cell B13.B13
the range F7:G12 and then F7:G12.
Use the value in cell B12 as the column input cell because cell B12 contains the units sold amount for Dane model kayaks.B12
Format the range G8:G12 using the Currency number format with zero decimal places and the $ symbol to match the formatting of cell G7.Currencyzero$
- Use Goal Seek to set cell C14 (the gross profit per Swede kayak) to a value of 125.
- Change the number of units sold in cell C12 to achieve the goal.
- 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:
- Use Goal Seek to set cell D14 (the gross profit per Finn kayak) to a value of 150.
- Change the number of units sold in cell D12 to achieve the goal.
- 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:
- 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.)
- Use the range B11:D11 as the changing variable cells.
- Adjust the total manufacturing cost by each vendor using the constraints in Table 2 below:
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:
- Use Goal Seek to set cell C14 (the gross profit per Swede kayak) to a value of 125.
- Change the number of units sold in cell C12 to achieve the goal.
- 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:
- Use Goal Seek to set cell D14 (the gross profit per Finn kayak) to a value of 150.
- Change the number of units sold in cell D12 to achieve the goal.
- 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:
- 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.)
- Use the range B11:D11 as the changing variable cells.
- Adjust the total manufacturing cost by each vendor using the constraints in Table 2 below:
C14125
C12
- Use Goal Seek to set cell D14 (the gross profit per Finn kayak) to a value of 150.
- Change the number of units sold in cell D12 to achieve the goal.
- 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:
- 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.)
- Use the range B11:D11 as the changing variable cells.
- Adjust the total manufacturing cost by each vendor using the constraints in Table 2 below:
- Use Goal Seek to set cell D14 (the gross profit per Finn kayak) to a value of 150.
- Change the number of units sold in cell D12 to achieve the goal.
- 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:
- 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.)
- Use the range B11:D11 as the changing variable cells.
- Adjust the total manufacturing cost by each vendor using the constraints in Table 2 below:
D14150
D12
- 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.)
- Use the range B11:D11 as the changing variable cells.
- Adjust the total manufacturing cost by each vendor using the constraints in Table 2 below:
NorseScandia 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:
- 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.)
- Use the range B11:D11 as the changing variable cells.
- Adjust the total manufacturing cost by each vendor using the constraints in Table 2 below:
E12Hint
B11:D11B11:D11
Table 2: Solver Constraints
Table 2: Solver Constraints
Table 2: Solver Constraints
Requirement |
Cell Reference |
Comparison Operator |
Constraint |
The units produced values must be integers. |
B11:D11 |
int |
integer |
Vendors must produce at least 5,000 units. |
B11:D11 |
>= |
B16 |
Freddi Nautical can produce a maximum of 15,000 units. |
Freddi_Nautical_Units |
<= |
B17 |
Paddle Pro can produce a maximum of 7,500 units. |
PaddlePro_Units |
<= |
B18 |
Green Wave can produce a maximum of 15,000 units. |
Green_Wave_Units |
<= |
B19 |
Scandia Kayaks requires 27,000 Norse kayaks. |
Total_Units |
= |
B20 |
Requirement |
Cell Reference |
Comparison Operator |
Constraint |
The units produced values must be integers. |
B11:D11 |
int |
integer |
Vendors must produce at least 5,000 units. |
B11:D11 |
>= |
B16 |
Freddi Nautical can produce a maximum of 15,000 units. |
Freddi_Nautical_Units |
<= |
B17 |
Paddle Pro can produce a maximum of 7,500 units. |
PaddlePro_Units |
<= |
B18 |
Green Wave can produce a maximum of 15,000 units. |
Green_Wave_Units |
<= |
B19 |
Scandia Kayaks requires 27,000 Norse kayaks. |
Total_Units |
= |
B20 |
Requirement
Cell Reference
Comparison Operator
Constraint
The units produced values must be integers.
B11:D11
int
integer
Vendors must produce at least 5,000 units.
B11:D11
>=
B16
Freddi Nautical can produce a maximum of 15,000 units.
Freddi_Nautical_Units
<=
B17
Paddle Pro can produce a maximum of 7,500 units.
PaddlePro_Units
<=
B18
Green Wave can produce a maximum of 15,000 units.
Green_Wave_Units
<=
B19
Scandia Kayaks requires 27,000 Norse kayaks.
Total_Units
=
B20
Requirement
Cell Reference
Comparison Operator
Constraint
Requirement
Requirement
RequirementRequirement
Cell Reference
Cell Reference
Cell ReferenceCell Reference
Comparison Operator
Comparison Operator
Comparison OperatorComparison Operator
Constraint
Constraint
ConstraintConstraint
The units produced values must be integers.
B11:D11
int
integer
The units produced values must be integers.
The units produced values must be integers.
The units produced values must be integers.The units produced values must be integers.
B11:D11
B11:D11
B11:D11B11:D11
int
int
intint
integer
integer
integerinteger
Vendors must produce at least 5,000 units.
B11:D11
>=
B16
Vendors must produce at least 5,000 units.
Vendors must produce at least 5,000 units.
Vendors must produce at least 5,000 units. Vendors must produce at least 5,000 units.
B11:D11
B11:D11
B11:D11B11:D11
>=
>=
>=>=
B16
B16
B16B16
Freddi Nautical can produce a maximum of 15,000 units.
Freddi_Nautical_Units
<=
B17
Freddi Nautical can produce a maximum of 15,000 units.
Freddi Nautical can produce a maximum of 15,000 units.
Freddi Nautical can produce a maximum of 15,000 units.Freddi Nautical can produce a maximum of 15,000 units.
Freddi_Nautical_Units
Freddi_Nautical_Units
Freddi_Nautical_UnitsFreddi_Nautical_Units
<=
<=
<=<=
B17
B17
B17B17
Paddle Pro can produce a maximum of 7,500 units.
PaddlePro_Units
<=
B18
Paddle Pro can produce a maximum of 7,500 units.
Paddle Pro can produce a maximum of 7,500 units.
Paddle Pro can produce a maximum of 7,500 units.Paddle Pro can produce a maximum of 7,500 units.
PaddlePro_Units
PaddlePro_Units
PaddlePro_UnitsPaddlePro_Units
<=
<=
<=<=
B18
B18
B18B18
Green Wave can produce a maximum of 15,000 units.
Green_Wave_Units
<=
B19
Green Wave can produce a maximum of 15,000 units.
Green Wave can produce a maximum of 15,000 units.
Green Wave can produce a maximum of 15,000 units.Green Wave can produce a maximum of 15,000 units.
Green_Wave_Units
Green_Wave_Units
Green_Wave_UnitsGreen_Wave_Units
<=
<=
<=<=
B19
B19
B19B19
Scandia Kayaks requires 27,000 Norse kayaks.
Total_Units
=
B20
Scandia Kayaks requires 27,000 Norse kayaks.
Scandia Kayaks requires 27,000 Norse kayaks.
Scandia Kayaks requires 27,000 Norse kayaks.Scandia Kayaks requires 27,000 Norse kayaks.
Total_Units
Total_Units
Total_UnitsTotal_Units
=
=
==
B20
B20
B20B20
- 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.
- Jay wants to compare the values you found using Solver to the original values. Provide this information as follows:
- 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.)
- Accept the current values for the changing cells, which show the results of the Solver model.
- 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.]
- Change the name of the Scenario Summary 2 worksheet to Vendors Scenario to distinguish it from the other Scenario Summary worksheet.
- Enter the text Optimal Values in cell D3.
- Jay asks you to produce summary statistics on the Norse Projections data. Provide this information as follows:
- Return to the Norse worksheet, and then use the Data Analysis tool to generate Descriptive Statistics.
- Use the range H12:L12 as the input range.
- Indicate that the data is grouped by rows.
- Display the statistics on a new worksheet and use Norse Stats as the worksheet name.
- Produce summary statistics.
- Switch to the Norse Stats worksheet and then modify the worksheet to make its contents easier to read as follows:
- Resize column A to its best fit to display all of the row labels.
- Enter Norse Profit Years 1-5 in cell A1 to provide a descriptive heading for the statistics.
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.GRG NonlinearD16
- 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.)
- Accept the current values for the changing cells, which show the results of the Solver model.
- 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.]
- Change the name of the Scenario Summary 2 worksheet to Vendors Scenario to distinguish it from the other Scenario Summary worksheet.
- Enter the text Optimal Values in cell D3.
- Jay asks you to produce summary statistics on the Norse Projections data. Provide this information as follows:
- Return to the Norse worksheet, and then use the Data Analysis tool to generate Descriptive Statistics.
- Use the range H12:L12 as the input range.
- Indicate that the data is grouped by rows.
- Display the statistics on a new worksheet and use Norse Stats as the worksheet name.
- Produce summary statistics.
- Switch to the Norse Stats worksheet and then modify the worksheet to make its contents easier to read as follows:
- Resize column A to its best fit to display all of the row labels.
- Enter Norse Profit Years 1-5 in cell A1 to provide a descriptive heading for the statistics.
Jay wants to compare the values you found using Solver to the original values. Provide this information as follows:
- 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.)
- Accept the current values for the changing cells, which show the results of the Solver model.
- 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.]
- Change the name of the Scenario Summary 2 worksheet to Vendors Scenario to distinguish it from the other Scenario Summary worksheet.
- Enter the text Optimal Values in cell D3.
- Jay asks you to produce summary statistics on the Norse Projections data. Provide this information as follows:
- Return to the Norse worksheet, and then use the Data Analysis tool to generate Descriptive Statistics.
- Use the range H12:L12 as the input range.
- Indicate that the data is grouped by rows.
- Display the statistics on a new worksheet and use Norse Stats as the worksheet name.
- Produce summary statistics.
- Switch to the Norse Stats worksheet and then modify the worksheet to make its contents easier to read as follows:
- Resize column A to its best fit to display all of the row labels.
- Enter Norse Profit Years 1-5 in cell A1 to provide a descriptive heading for the statistics.
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.)Vendors BestHintNorse
Create a Summary report using cell E11 (which has the defined name Total_Units) as the resultcell. E11[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.]
Change the name of the Scenario Summary 2 worksheet to Vendors Scenario to distinguish it from the other Scenario Summary worksheet.Scenario Summary 2Vendors ScenarioScenario Summary
Enter the text Optimal Values in cell D3.Optimal Values
- Return to the Norse worksheet, and then use the Data Analysis tool to generate Descriptive Statistics.
- Use the range H12:L12 as the input range.
- Indicate that the data is grouped by rows.
- Display the statistics on a new worksheet and use Norse Stats as the worksheet name.
- Produce summary statistics.
- Switch to the Norse Stats worksheet and then modify the worksheet to make its contents easier to read as follows:
- Resize column A to its best fit to display all of the row labels.
- Enter Norse Profit Years 1-5 in cell A1 to provide a descriptive heading for the statistics.
Jay asks you to produce summary statistics on the Norse Projections data. Provide this information as follows:
- Return to the Norse worksheet, and then use the Data Analysis tool to generate Descriptive Statistics.
- Use the range H12:L12 as the input range.
- Indicate that the data is grouped by rows.
- Display the statistics on a new worksheet and use Norse Stats as the worksheet name.
- Produce summary statistics.
- Switch to the Norse Stats worksheet and then modify the worksheet to make its contents easier to read as follows:
- Resize column A to its best fit to display all of the row labels.
- Enter Norse Profit Years 1-5 in cell A1 to provide a descriptive heading for the statistics.
Return to the Norse worksheet, and then use the Data Analysis tool to generate Descriptive Statistics.NorseDescriptive Statistics
Use the range H12:L12 as the input range.H12:L12
Indicate that the data is grouped by rows.
Display the statistics on a new worksheet and use Norse Stats as the worksheet name.Norse Stats
Produce summary statistics.
- Resize column A to its best fit to display all of the row labels.
- Enter Norse Profit Years 1-5 in cell A1 to provide a descriptive heading for the statistics.
Switch to the Norse Stats worksheet and then modify the worksheet to make its contents easier to read as follows:Norse Stats
- Resize column A to its best fit to display all of the row labels.
- Enter Norse Profit Years 1-5 in cell A1 to provide a descriptive heading for the statistics.
Resize column A to its best fit to display all of the row labels.
Enter Norse Profit Years 1-5 in cell A1 to provide a descriptive heading for the statistics.Norse Profit Years 1-5
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.
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.Profit The Norse worksheet Final Figure has been intentionally omitted. Norse
Final Figure 1: Scenario Summary Worksheet
Final Figure 1: Scenario Summary Worksheet
Final Figure 1: Scenario Summary Worksheet
Final Figure 2: Revenue and Expenses Worksheet
Final Figure 2: Revenue and Expenses Worksheet
Final Figure 2: Revenue and Expenses Worksheet
Final Figure 3: Profit Worksheet
Final Figure 3: Profit Worksheet
Final Figure 3: Profit Worksheet
Final Figure 4: Vendors Scenario Worksheet
Final Figure 4: Vendors Scenario Worksheet
Final Figure 4: Vendors Scenario Worksheet
Final Figure 5: Norse Stats Worksheet
Final Figure 5: Norse Stats Worksheet
Final Figure 5: Norse Stats Worksheet