New Perspectives Excel 2016 | Module 10: SAM Project 1a Hurdy Headphones WHAT-IF ANALYSES AND SCENARIOS GETTING STARTED ? Open the file NP_EX16_10a_ FirstLastName _1

Subject:MS ExcelPrice:19.87 Bought3

Share With

New Perspectives Excel 2016 | Module 10: SAM Project 1a

Hurdy Headphones

WHAT-IF ANALYSES AND SCENARIOS

GETTING STARTED

?

Open the file

NP_EX16_10a_

FirstLastName

_1.xlsx

, available for download

from the SAM website.

?

Save the file as

NP_EX16_10a_

FirstLastName

_2.xlsx

by changing the “1” to

a “2”.

o

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

NP_EX16_10a_

FirstLastName

_2.xlsx

still open, ensure that your

first and last name is displayed in cell B6 of the Documentation sheet.

o

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

add-in

.

If this add-in is not available

on the Data tab in the Analyze

group (or if the Analyze

group is not available),

install

Solver

as follows:

o

In Excel, click the File tab, and then click the Options button in the left

navigation bar.

o

Click the Add-Ins option in the left pane of the Excel Options dialog box.

o

Click the Manage arrow, click the Excel Add-Ins option, and then click the

Go button.

o

In the Add-Ins dialog box, click the Solver Add-In check box and then click

the OK button.

o

Follow any remaining prompts to install Solver

.

PROJECT STEPS

1. Kylie and Derek Hurdy own Hurdy Headphones, a company that manufactures

wireless and noise-cancelling headphones and personalized earphones in

Pittsburgh, Pennsylvania. As an intern at the company, you are developing a

workbook that includes the financial details of each product line. Kylie and

Derek have asked you to use the data to analyze scenarios that involve cutting

expenses and raising prices. They want you to find the most profitable mix of

products using the most cost-effective means of production.

Switch to the

Wireless

worksheet. Perform a break-even analysis for wireless

headphones as follows:

a.

In cell B27, use Goal Seek to set cell

B27

to a value of

 

b.

Change the number of units sold in cell

B26

to break even, or reach a

Gross Profit of $0. (

Hint:

The number format applied to cell B27 will display

the value of $0 as $ -.)

2.

Create a one-variable data table to calculate sales, expenses, and profits based

on the number of wireless headphones sold as follows:

a.

In cell D5, enter a formula that references cell

B4

, which is the expected

units sold for this product.

b.

In cell E5, enter a formula that references cell

B19

, which is the expected

total sales for this product.

c.

In cell F5, enter a formula that references cell

B20

, which is the expected

total expenses for this product.

d.

In cell G5, enter a formula that references cell

B21

, which is the expected

gross profit for this product.

e.

Select the range D5:G10 and then complete the one-variable data table,

using cell

B4

as the Column input cell for your data table.

3.

Create a two-variable data table to calculate the gross profit based on the

number of wireless headphones sold and the price per unit:

a.

For the range D14:K19, create a two-variable data table using the price

per unit (cell

B5

) as the Row input cell.

b.

Use the units sold (cell

B4

) as the Column input cell.

4.

Apply a custom format to cell

D14

to display the text

Units Sold/Price

in place

of the cell value.

5.

Switch to the

Noise-Cancelling

worksheet. Create a

Scatter with Straight

Lines

chart based on range D4:F14 in the data table Noise-Cancelling – Break-

Even Analysis.

6.

Modify the new chart as follows:

a.

Resize and reposition the chart so that it covers the range D15:H32.

b.

Remove the chart title from the chart.

c.

Add

Sales and Expenses

as the vertical axis title and

Units Sold

as the

horizontal axis title.

7.

Change the Bounds Axis Options as follows:

a.

Change the Minimum Bounds of the vertical axis to

1750000

and the

Maximum Bounds to

2650000

.

b.

Change the number format of the vertical axis to

Currency

with

0

decimal places and

$

as the symbol.

c.

Change the Minimum Bounds of the horizontal axis to

8500

. (

Hint

: The

Maximum Bounds should automatically change to 12,500.)

8.

Create two scenarios to compare the costs for standard components and green

components in the noise-cancelling headphones as follows:

a.

In the Scenario Manager, add two scenarios using the data shown in bold

in Table 1 below.

 

New Perspectives

Excel 2016 | Module 10: SAM Project 1a

b.

The changing cells for both scenarios are the nonadjacent cells

B11

and

B14

.

c.

Close the Scenario Manager without showing any of the scenarios.

Table 1: Noise-Cancelling Headphones Scenario Values

Values

Scenario 1

Scenario 2

Scenario Name

Standard

Environmental

Noise_Cancelling_Variable_Cost (B11)

179.00

182.50

Noise_Cancelling_Fixed_Cost (B14)

300000

350000

9.

Switch to the

Earphones

worksheet. Create a

Scatter with Straight Lines

chart based on range D6:I14 in the data table Personalized Earphones – Net

Income Analysis.

10.

Modify the new chart as follows:

a.

Resize and reposition the chart so that it covers the range D15:I32.

b.

Remove the chart title from the chart.

c.

Reposition the chart legend to the

Right

of the chart.

d.

Add

Net Income

as the vertical axis title and

Units Sold

as the

horizontal axis title.

e.

Change the colors of the chart to

Color 5

(1

st

row in the Monochromatic

palette). (

Hint

: Depending on your version of Office, the name of the color

may be different.)

11.

Change the Bounds Axis Options for the new chart as follows:

a.

Change the Minimum Bounds of the vertical axis to

-100000

and the

Maximum Bounds to

620000

.

b.

Change the number format of the vertical axis to

Currency

with

0

decimal places and

$

as the symbol.

c.

Change the Minimum Bounds of the horizontal axis to

10500

and the

Maximum Bounds to

14500

.

12.

Edit the chart series names as follows:

a.

For Series 1, set the series name to cell

E5

. (

Hint:

The series name should

automatically update to “=’Earphones’!$E$5”.)

b.

For Series 2, set the series name to cell

F5

.

c.

For Series 3, set the series name to cell

G5

.

d.

For Series 4, set the series name to cell

H5

.

e.

For Series 5, set the series name to cell

I5

.

 

New Perspectives

Excel 2016 | Module 10: SAM Project 1a

13.

Hurdy Headphones wants to determine whether subcontracting the

manufacture of their personalized earphones to other electronics companies

would reduce the costs of the earphones. Switch to the

Earphone Suppliers

worksheet, and then run Solver to solve this problem as follows:

a.

Set the objective as minimizing the value in cell

E10

(Total Costs).

b.

Use the range

B4:D4

as the changing variable cells.

c.

Adjust the number of units produced by each company using the following

constraints:

o

E4=

12500

, the total number of earphones produces

o

E10 <=

1200000

, the maximum total cost

o

B4:D4 <=

5000

, the maximum number of earphones produced by a

single supplier

o

B4:D4 should be an Integer

d.

Run Solver, keep the solution, and then return to the Solver Parameters

dialog box. Save the model to the range

A14:A21

, and then close the

Solver Parameters dialog box.

14.

Switch to the

All Products

worksheet. Use the Scenario Manager to create a

Scenario Summary report that summarizes the effect of the Status Quo,

Subcontract, and Raise Prices 5% scenarios. Use cells

B17:D17

as the result

cells.

15.

Switch back to the

All Products

worksheet. Use the Scenario Manager as follows

to compare the profit per unit in each scenario:

a.

Create a Scenario PivotTable report for result cells

B17:D17

.

b.

Remove the Filter field from the PivotTable.

c.

Change the number format of the

Profit_per_Unit_Sold_Wireless

,

Profit_per_Unit_Sold_Noise_Cancelling

, and

Profit_per_Unit_Sold_Earphones

fields (located in the Values box of the

PivotTable Field List) to

Currency

with

2

decimal places and

$

as the

symbol.

d.

Use

Wireless

as the row label value in cell B3,

Noise-Cancelling

as the

value in cell C3, and

Earphones

as the value in cell D3.

e.

In cell A1, use

Profit per Unit Sold

as the report title.

f.

Format the report title using the

Title

cell style.

g.

Resize column A using AutoFit. Resize columns B-D to

15.00

.

16.

Add a PivotChart to the

Scenario PivotTable

worksheet as follows:

a.

Create a

Clustered Column

PivotChart based on the PivotTable.

[Mac

Hint: PivotCharts are not available on Excel 2016 for the Mac, so insert a

Clustered Column chart and adjust the data, and the legend and axis

formatting to match Final Figure 6.]

b.

Resize and reposition the chart so that it covers the range A8:D20.

c.

Hide the field buttons in the chart.

[Mac Hint: PivotCharts are not available

on Excel 2016 for the Mac, so Mac users can ignore this instruction.]

 

Your workbook should look like the Final Figures below. Save your changes, close the

workbook, and then exit Excel. Follow the directions on the SAM website to submit

your completed project.

Final Figure 1: Wireless Worksheet

Final Figure 2: Noise-Cancelling Worksheet

Final Figure 3: Earphones Worksheet

Final Figure 4: Earphone Suppliers Worksheet

 

Final Figure 5: Scenario Summary Worksheet

Final Figure 6: Scenario PivotTable Worksheet

Final Figure 7: All Products Worksheet