a

Save e06c1Manufacturing as e06c1Manufacturing_LastFirst

2

b-c

Create range names for B18 and B21; edit range name Employee_Hourly_Wage to include current year

5

d

B22: Use range names to subtract Gross Profit from Total Production Cost

5

e

Create a new worksheet called Range Names; paste range names

5

Goal Seek

a

Use Goal Seek to determine how many units must be sold to exhaust total inventory of raw materials

5

b

Question 1: Enter value in Q & A sheet

3

One-Variable Data Table

a

E3:E22: Fill series from 10 to 200 in steps of 10

5

b

Enter references to the Total Production Cost, Gross Profit, and Net Profit

5

c

Create a one-variable data table in range E2:H22 using B4 as Column input; apply Accounting Number format, two decimal places

5

d

Create custom number formats for F2:H2

5

e

Question 2: Use one-variable data table

3

Two-Variable Data Table

a

Copy E3:E22 to E26:E45

5

b

F25: Type $15, and fill series to $40 in $5 steps

5

c

Use custom nunber format for net profit

5

d-e

Create a two-variable data table in range E25:K46 using B6 as Row input and B4 as Column input; apply Accounting Number format, two decimal places

5

f

Q&A sheet: Questions 3 and 4: two-variable data table

3

Scenario Manager

a-c

Create three scenarios: Best Case, Worst Case, and Most Likely

5

d-e

Generate a scenario summary report using Total Production Cost and Net Income

5

f

Q&A sheet: Question 5: Scenario Summary Report

3

Use Solver

a-g

Use Solver: Calculate highest Net Income, Units Sold as variable; raw materials, labor, and maximum production capability as constraints; generate Answer Report

5

h

Q&A sheet: Questions 68: Solver Answer Report

3

h

Format Q & A sheet to landscape orientation

5

i-j

Create a footer on all five worksheets with student name on left, sheet name code in center, and file name on right

3

Delta Paint				One-Variable Data Table: Production
Input
Units Sold	100
Unit Selling Price	$ 30.00
Employee Hourly Wage	$ 15.00
Maximum Capabilty per week	200
Limitations
Raw Materials in Units	15000
Required raw materials per unit	3
Required Labor Hours Per Gallon	0.25
Labor Hours available	2,000
Expenses
Raw Materials Consumed	300
Labor Hours Consumed	25
Total Production Cost	$ 375.00
Income
Gross Profit	$ 3,000
Net Profit	$ 2,625
				Two-Variable Data Table: Production and Manufacturing Time

100

0