Exercise 2Scenario Manager

You now own a manufacturing firm and your firm makes 6 products. Each product has the following cost components: Material, Labor, and Marketing. In addition, there is a fixed cost for the firm.

Cost of item in production units

Production units

BA

AB

XC

RC

WA

RT

Material Units

2

2

1

1

3

1

Labor units

1

1

1

2

2

1

Marketing

1

1

1

2

2

1

Production units cost per unit

Material

$7

5

4

5

6

6

Labor

$5

$4

$5

$6

$5

$5

Marketing

$3

$2

$2

$2

$4

$2

Retail Price

40

35

25

28

58

31

Quantity

500

750

650

900

250

600

Fixed cost:$12,000

Use scenario manager to conduct the following stress tests by determining the effect of the following scenarios upon net profit:

#1

Increase retail price, material cost, marketing and labor cost by 12%.

#2

Increase retail price by 5%, increase labor cost by 7%, increase marketing cost by 5% and hold material cost static.

#3

Decrease retail price by 5%, increase labor cost by 5%, decrease marketing cost by 5% and increase material cost by 5%, increase fixed cost by $1,000

#4

Decrease retail price by 10%, decrease material cost by 5%, increase marketing cost by 10%and decrease fixed cost by $2,000.

#5

Increase retail price by 15%, material cost by 12%, decrease labor cost by 2% and increase fixed cost by $5,000

Please make sure that all of your changing and output cells are named by using the define name function.