Homework answers / question archive / Illustrated Excel 2016 | Module 11: SAM Project 1b Adventure Paddle PERFORMING WHAT-IF ANALYSIS GETTING STARTED Open the file IL_EX16_11b_FirstLastName_1

Illustrated Excel 2016 | Module 11: SAM Project 1b Adventure Paddle PERFORMING WHAT-IF ANALYSIS GETTING STARTED Open the file IL_EX16_11b_FirstLastName_1

MS Excel

Share With

---

Illustrated Excel 2016 | Module 11: SAM Project 1b

Adventure Paddle

PERFORMING WHAT-IF ANALYSIS

GETTING STARTED

• Open the file IL_EX16_11b_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as IL_EX16_11b_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_11b_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.

*Depending on your version of Office, the Analysis group may be called the Analyze group.

 

PROJECT STEPS

1. As a staff accountant at Adventure Paddle, you assist Travis Rice, a senior accountant. Travis 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. Travis wants to know how profits would change if the company sold 10 percent fewer paddle boards of each type. Create a scenario to provide this information for Travis as follows:

1. 1. In the Scenario Manager, add a new scenario and use Unit Sales Decrease 10% 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.

 

Changing Cell	Value
Inflatable_Units_Sold (F12)	18900
Fiberglass_Units_Sold (G12)	19845
Bamboo_Units_Sold (H12)	16650

 

1. 4. Show the results of the Unit Sales Decrease 10% 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 the prompt “The command you chose cannot be performed with multiple selections.” 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.
3. Switch to the Profit worksheet. Determine how changing the number of inflatable paddle boards 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 inflatable paddle boards.
   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.
4. Travis asks you to determine the price of fiberglass paddle boards the company needs to set to earn a gross profit per unit of $300.00. Provide this information to Travis as follows:
   1. Use Goal Seek to set cell C14 (the gross profit per fiberglass paddle boards) to a value of 300.
   2. Change the price in cell C8 to achieve the goal.
5. Travis wants to perform a similar analysis for the bamboo paddle boards by determining the price of bamboo paddle boards to earn a gross profit per unit of $155.00. Provide this information to Travis as follows:
   1. Use Goal Seek to set cell D14 (the gross profit per bamboo paddle boards) to a value of 155.
   2. Change the price in cell D8 to achieve the goal.
6. Switch to the Yoga worksheet. Adventure Paddle is designing a new paddle board for yoga and plans to work with three vendors to manufacture the new paddle boards. Travis 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:

 

Requirement	Cell Reference	Comparison Operator	Constraint
The units produced values must be integers.	B11:D11	int	integer
Vendors must produce at least 6,000 units.	B11:D11	>=	B16
AllTech can produce a maximum of 14,000 units.	AllTech_Units	<=	B17
GCraft can produce a maximum of 8,500 units.	GCraft_Units	<=	B18
H2OSports can produce a maximum of 14,000 units.	H2OSports_Units	<=	B19
Adventure Paddle requires 28,000 yoga paddle boards.	Total_Units	=	B20

 

6. 4. 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.
7. Travis 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 Yoga 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 result cell. [Mac Hint: If the prompt “The command you chose cannot be performed with multiple selections.” 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.
8. Travis asks you to produce summary statistics on the Yoga Projections data. Provide this information as follows:
   1. Return to the Yoga 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 Yoga Stats as the worksheet name.
   5. Produce Summary statistics.
9. Switch to the Yoga 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 Yoga Profit Years 2020 - 2024 in cell A1 to provide a descriptive heading for the statistics.

Your workbook should look like the Final Figures on the following pages. In the Profit worksheet, cells C8 and D8 are intentionally blurred. The Yoga 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: Yoga Stats Worksheet