Homework answers / question archive / Illustrated Excel 2013 Unit K: SAM Project 1a Clef Notes Software Modeling data with scenarios, what-if analyses, goal seek, and solver Project Goal M Project Name Project Goal PROJECT DESCRIPTION Benjamin Stein works at Clef Notes Software, a California company that develops and sells music production software to a broad range of clients including individuals, music professionals, and music production companies and studios

Illustrated Excel 2013 Unit K: SAM Project 1a Clef Notes Software Modeling data with scenarios, what-if analyses, goal seek, and solver Project Goal M Project Name Project Goal PROJECT DESCRIPTION Benjamin Stein works at Clef Notes Software, a California company that develops and sells music production software to a broad range of clients including individuals, music professionals, and music production companies and studios

MS Excel

Share With

---

Illustrated Excel 2013 Unit K: SAM Project 1a

Clef Notes Software

Modeling data with scenarios, what-if analyses, goal seek, and solver

Project Goal

M Project Name

Project Goal

PROJECT DESCRIPTION

Benjamin Stein works at Clef Notes Software, a California company that develops and sells music production software to a broad range of clients including individuals, music professionals, and music production companies and studios. Benjamin has asked you to analyze the company’s current sales statistics using the data tools in Excel to develop scenarios and what-if analyses to help identify areas of future opportunity.

GETTING STARTED

· Download the following file from the SAM website:

· IL_Excel2013_UK_P1a_FirstLastName_1.xlsx

· Open the file you just downloaded and save it with the name:

· IL_Excel2013_UK_P1a_FirstLastName_2.xlsx

· Hint: If you do not see the .xlsx file extension in the Save file dialog box, do not type it. Excel will add the file extension for you automatically.

· With the file IL_Excel2013_UK_P1a_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 the use of the Solver add-in. If this add-in is not available in the Analysis group (or if the Analysis group is not available) on the DATA tab in the Ribbon, install Solver by following the steps below.

· In Excel, click the FILE tab, and then click the Options button in the left navigation bar.

· Click the Add-Ins option in the left pane of the Excel Options window.

· Click on the arrow next to the Manage box, click the Excel Add-Ins option, and then click the Go button.

· In the Add-Ins window, click the check box next to the Solver Add-In option and then click the OK button.

· Follow any remaining prompts to install Solver.

PROJECT STEPS

Go to the Current Year worksheet. Open the Scenario Manager and create a new scenario called Unit Sales Decrease 10% that changes the values in the range $F$12:$H$12 (Hint: Two scenarios are already defined for the Current Year worksheet). Use the information shown in Table 1 as the values for the changing cells.

Table 1: New Scenario for Current Year Worksheet

 

Microsoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright © 2014 Cengage Learning. All Rights Reserved.

 

Changing Cell	Value
F12 (Basic_Clef_Units_Sold)	85500
G12 (Premium_Clef_Units_Sold)	42300
H12 (Ultra_Clef_Units_Sold)	3150

 

Show the results of the existing scenario named Unit Sales Increase 10%.

Create a Scenario Summary Report for result cells B8:B10, F13:H13.

In the Scenario Summary report worksheet, make the following changes:

a. Delete Column D.

b. Delete the contents of cells B16:B18.

Go to the Profitability worksheet and create a one-input data table by following these actions:

c. Enter a reference to cell B13 into cell G7 (Hint: The formula in cell G7 should be =B13).

d. Select the range F7:G12 and create a data table using the value in cell B12 as the column input cell.

e. Apply the Currency number format, with no decimal places, to the range G8:G12.

Use Goal Seek to determine the number of Premium Clef units that will need to be sold in order to make a gross profit per unit of $250. Use cell C12 as the changing cell and cell C14 as the goal cell.

Use Goal Seek to determine the number of Ultra Clef units that will need to be sold in order to make a gross profit per unit of $500. Use cell D12 as the changing cell and cell D14 as the goal cell.

Go to the Subcontractor worksheet. Clef Notes software is planning to release a new product, Treble Clef, and needs to contract the manufacturing of the product out to three different subcontractors. Create a Solver Model to Minimize the Manufacturing_Total_Cost (in cell E13) to produce the required number of Treble Clef units by following the directions below.

f. Use cell E13 (which has the defined name Manufacturing_Total_Cost as the target cell, with the goal of the solver set to determine the minimum value of that cell (Hint: When you select the Min option in the Solver Parameters Dialog box, you will not need to enter a specific target value).

g. Use the range B12:D12 as the changing variable cells.

h. Begin adding constraints in the Solver model by constraining the values in the range B12:D12 to integers, as shown in Figure 1.

Figure 1: Solver Model Constraints

 

i. Add additional constraints to the Solver model as shown in Table 2.

Table 2: Additional Solver Model Constraints

 

Cell Reference	Comparison Operator	Constraint
B12:D12	>=	B17
B12 (which has the defined name Karma_Inc_Units)	<=	B18
C12 (which has the defined name Code_Crew_Units)	<=	B19
D12 (which as the defined name Lorenz_System_Units)	<=	B20
E12	=	B21

 

 

Confirm your Solver Parameters Dialog box matches Figure 2 (Hint: The constraints may be listed in a different order).

Figure 2: Solver Parameters Dialog Box

j. Save your Solver Model by clicking the Load/Save button in the Solver Parameters Dialog box, selecting cell D17 in the Load/Save Model dialog box (as shown in Figure 3), and clicking Save button (Hint: Values representing the Solver parameters will appear in the range D17:D26). After saving your Solver Model, you will return to the Solver Parameters Dialog box.

 

Figure 3: Load/Save Model Dialog Box

k. Click Solve in the Solver Parameter dialog box, keeping the Solver Solutions in your workbook, and then click OK.

With the Solver Solutions still being shown in the Subcontractor worksheet, create a Scenario Summary report by following the directions below (Hint: The worksheet already contains a predefined scenario):

l. Create the Scenario Summary report using cell E13 (which has the defined name Manufacturing_Total_Cost) as the resultant cell.

m. Change the name of the Scenario Summary worksheet to Subcontractor Scenario.

n. Change the value shown in cell D3 to read Optimal Values.

o. Adjust the width of column D to best fit the content it contains.

p. Delete the contents of the cells B11:B13.

Return to the Subcontractor worksheet. Create a new scenario named Optimal Values, using cells B12:D12 as the changing cells, and using the current values shown in the Subcontractor worksheet as the values for the changing cells (Hint: The cells in the range B12:D12 have defined names).

Your workbook should look like the Final Figure on the following page. Note, the Profitability, Subcontractor, and Subcontractor Scenario worksheet Final Figures have been intentionally omitted. Save your changes, close the workbook, and exit Excel. Follow the directions on the SAM website to submit your completed project.

 

Final Figure 1: Scenario Summary Worksheet