Saint Peter’s College ?advertising Budget Trade Off Unit Profit Excel Worksheet Complete and analyze the model in the second Excel tab titled Advertising Trade-Off: – Please do not accept if you are not confident in this course.

·Again, use Excel to calculate Unit Profit for each product (in cells B8:I8) and Total Profit (in cell B19).

Don't use plagiarized sources. Get Your Custom Essay on

Saint Peter’s College ?advertising Budget Trade Off Unit Profit Excel Worksheet Complete and analyze the model in the second Excel tab titled Advertising T

Just from $13/Page

·From the first sensitivity report (Optimal Solution), find the maximum allowable advertising budget and enter this number into the advertising budget RHS constraint cell C33. This is, in effect, increasing the advertising budget.

·Run Excel Solver for this change in the Advertising Trade-off Tab (only) to create another optimal production plan and create another sensitivity report. Analyze the tactical and strategic information provided by the Solver solution and answer the following:

-How is the objective (profit) impacted by the additional advertising budget as compared to the profit shown in the Optimal Solution Tab? And, what is the impact to the product demand constraints (cells B28 – B31)? (1 points)

-What is the trade-off between increased advertising expenditures and profit for Hawley Co. is expending more advertising budget worth the additional profit? (1 points)

-How would an increase in advertising budget affect the optimal production plan? (1 points)

-Assume each department claims to require a proportional amount of advertising budget in order to attain the projected sales for each of their products. Calculate the portion of advertising budget for each product as a percentage of total Sales – determine how much advertising budget each product should be allocated. Now, allocate the new advertising budgets for each product proportionally in cells B15 thru E15. Do Not Re-Run Solver! What is the profit impact and are there any issues between the RHS and LHS demand constraints – is this a viable approach? (1 points) Costs and prices:

Celling price

Material costs

Production costs

Unit profit

r = regular time; o = overtime

Table lamps (T)

Floor lamps (F)

Tr

To

Fr

Fo

120

120

150

150

66

66

85

85

16

18

16

18

38

36

49

47

Department 1

Ceiling lamps (C )

Pendant lamps (P)

Cr

Co

Pr

Po

100

100

160

160

50

50

80

80

12

15

12

15

38

35

68

65

Department 2

Decision Variables:

Units produced

Advertising

Tr

72960

T

$18,000

To

0

F

$0

Objective function:

max (Profit) =

$9,044,480

Constraints:

Capacity constraints:

Department 1 regular time

Department 1 overtime

Department 2 regular time

Department 2 overtime

Demand constraints:

Table lamps

Floor lamps

Ceiling lamps

Pendant lamps

LHS

92,960

0

90,000

24,000

RHS

100,000

25,000

90,000

24,000

72,960

20,000

79,000

35,000

72,960

20,000

100,000

35,000

Advertising constraint

$18,000

$18,000

Fr

20000

C

$0

Fo

0

P

$0

Cr

55000

Co

24000

Pr

35000

Po

0

Microsoft Excel 16.35 Sensitivity Report

Worksheet: [Hawley Data Models V5.xlsx]Optimal Solution

Report Created: 3/27/20 2:31:29 PM

Variable Cells

Cell

$B$13

$C$13

$D$13

$E$13

$F$13

$G$13

$H$13

$I$13

$B$15

$C$15

$D$15

$E$15

Name

Units produced Tr

Units produced To

Units produced Fr

Units produced Fo

Units produced Cr

Units produced Co

Units produced Pr

Units produced Po

Advertising T

Advertising F

Advertising C

Advertising P

Final

Reduced Objective

Allowable

Allowable

Value

Cost Coefficient

Increase

Decrease

72960

0

38

1E+30

2

0

-2

36

2

1E+30

20000

0

49

87.8

2

0

-2

47

2

1E+30

55000

0

38

0 22.11428571

24000

0

35

1E+30

0

35000

0

68 22.11428571

0

0

0

65

0

1E+30

18000

0

-1

1E+30

11.61

0 -17.56

-1

17.56

1E+30

0 -27.36

-1

27.36

1E+30

0 -11.61

-1

11.61

1E+30

Constraints

Cell

$B$23

$B$24

$B$25

$B$26

$B$28

$B$29

$B$30

$B$31

$B$33

Final

Shadow Constraint Allowable

Name

Value

Price

R.H. Side

Increase

Department 1 regular time LHS

92960

0

100000

1E+30

Department 1 overtime LHS

0

0

25000

1E+30

Department 2 regular time LHS

90000

38

90000

21000

Department 2 overtime LHS

24000

35

24000

21000

Table lamps LHS

72960

38

0

7040

Floor lamps LHS

20000

49

0

7040

Ceiling lamps LHS

79000

0

0

1E+30

Pendant lamps LHS

35000

30

0

55000

Advertising constraint LHS

$18,000.00

26.36

18000 9777.777778

Allowable

Decrease

7040

25000

55000

24000

72960

20000

21000

21000

18000

Costs and prices:

Celling price

Material costs

Production costs

Unit profit

r = regular time; o = overtime

Table lamps (T)

Floor lamps (F)

Tr

To

Fr

Fo

120

120

150

150

66

66

85

85

16

18

16

18

38

36

49

47

Department 1

Ceiling lamps (C )

Pendant lamps (P)

Cr

Co

Pr

Po

100

100

160

160

50

50

80

80

12

15

12

15

38

35

68

65

Department 2

Tr

Cr

55000

Decision Variables:

Units produced

Advertising

Unit Sales

To

80000 0.160000032

T

F

$27,778

$0

$9,600,019

$3,000,000

Objective function:

max (Profit) =

$9,302,228

Constraints:

Capacity constraints:

Department 1 regular time

Department 1 overtime

Department 2 regular time

Department 2 overtime

Demand constraints:

Table lamps

Floor lamps

Ceiling lamps

Pendant lamps

Advertising constraint

LHS

100,000

0

90,000

24,000

RHS

100,000

25,000

90,000

24,000

80,000

20,000

79,000

35,000

80,000

20,000

100,000

35,000

$27,778

$27,778

Fr

20000

C

$0

$7,900,000

Fo

0

Co

24000

P

$0

$5,600,000 Total Sales: $26,100,019

Pr

35000

Po

0

Microsoft Excel 16.35 Sensitivity Report

Worksheet: [Hawley Data Models V5.xlsx]Advertising Trade-Off

Report Created: 3/27/20 2:44:39 PM

Variable Cells

Cell

$B$13

$C$13

$D$13

$E$13

$F$13

$G$13

$H$13

$I$13

$B$15

$C$15

$D$15

$E$15

Name

Units produced Tr

Units produced To

Units produced Fr

Units produced Fo

Units produced Cr

Units produced Co

Units produced Pr

Units produced Po

Advertising T

Advertising F

Advertising C

Advertising P

Final

Reduced Objective

Allowable

Allowable

Value

Cost Coefficient

Increase

Decrease

80000

0

38

0

2

0.160000032

0

36

2

0

20000

0

49

82.6

0

0

0

47

0

1E+30

55000

0

38

0 19.37142857

24000

0

35

1E+30

0

35000

0

68 19.37142857

0

0

0

65

0

1E+30

27778

0

-1

1E+30

10.17

0 -16.52

-1

16.52

1E+30

0 -25.92

-1

25.92

1E+30

0 -10.17

-1

10.17

1E+30

Constraints

Cell

$B$23

$B$24

$B$25

$B$26

$B$28

$B$29

$B$30

$B$31

$B$33

Final

Shadow Constraint Allowable

Allowable

Name

Value

Price

R.H. Side

Increase

Decrease

Department 1 regular time LHS

100000

2

100000 0.160000032

24999.84

Department 1 overtime LHS

0.160000032

0

25000

1E+30

24999.84

Department 2 regular time LHS

90000

38

90000

21000

55000

Department 2 overtime LHS

24000

35

24000

21000

24000

Table lamps LHS

80000.16

36

0

24999.84 0.160000032

Floor lamps LHS

20000

47

0

24999.84 0.160000032

Ceiling lamps LHS

79000

0

0

1E+30

21000

Pendant lamps LHS

35000

30

0

55000

21000

Advertising constraint LHS

$27,778.00

24.92

27778

34722 0.222222267

Costs and prices:

Celling price

Material costs

Production costs

Unit profit

r = regular time; o = overtime

Table lamps (T)

Floor lamps (F)

Tr

To

Fr

Fo

120

120

150

150

66

66

85

85

16

18

16

18

38

36

49

47

Department 1

Ceiling lamps (C )

Cr

Co

100

100

50

50

12

15

38

35

Department 2

Decision Variables:

Tr

Units produced

To

60000

T

Advertising

0

F

$0

$0

Objective function:

max (Profit) =

$9,535,850

Constraints:

Capacity constraints:

Department 1 regular time

Department 1 overtime

Department 2 regular time

Department 2 overtime

Demand constraints:

Table lamps

Floor lamps

Ceiling lamps

Pendant lamps

Advertising constraint

LHS

80,000

0

90,000

24,000

RHS

100,000

25,000

90,000

24,000

60,000

20,000

100,000

44,450

60,000

20,000

100,000

44,450

$18,000

$18,000

Fr

20000

C

$0

Fo

0

P

$18,000

Cr

76000

Co

24000

Pendant lamps (P)

Pr

Po

Pout

160

160

160

80

80

80

12

15

27

68

65

53

Department 2

Pr

14000

Po

0

Pout

30450

Microsoft Excel 16.35 Sensitivity Report

Worksheet: [Hawley Data Models V5.xlsx]Outsourcing Option

Report Created: 3/27/20 2:55:14 PM

Variable Cells

Cell

$B$13

$C$13

$D$13

$E$13

$F$13

$G$13

$H$13

$I$13

$J$13

$B$15

$C$15

$D$15

$E$15

Name

Units produced Tr

Units produced To

Units produced Fr

Units produced Fo

Units produced Cr

Units produced Co

Units produced Pr

Units produced Po

Units produced Pout

Advertising T

Advertising F

Advertising C

Advertising P

Final

Reduced Objective

Allowable

Allowable

Value

Cost Coefficient

Increase

Decrease

60000

0

38 0.645833333

2

0

-2

36

2

1E+30

20000

0

49

90.125

2

0

-2

47

2

1E+30

76000

0

38

0

23

24000

0

35

1E+30

0

14000

0

68

23

0

0

0

65

0

1E+30

30450

0

53

12 0.885714286

0 -0.465

-1

0.465

1E+30

0 -18.025

-1

18.025

1E+30

0 -9.425

-1

9.425

1E+30

18000

0

-1

1E+30

0.465

Constraints

Cell

$B$23

$B$24

$B$25

$B$26

$B$28

$B$29

$B$30

$B$31

$B$33

Final

Shadow Constraint

Name

Value

Price

R.H. Side

Department 1 regular time LHS

80000

0

100000

Department 1 overtime LHS

0

0

25000

Department 2 regular time LHS

90000

15

90000

Department 2 overtime LHS

24000

12

24000

Table lamps LHS

60000

38

0

Floor lamps LHS

20000

49

0

Ceiling lamps LHS

100000

23

0

Pendant lamps LHS

44450

53

0

Advertising constraint LHS

$18,000.00

26.825

18000

Allowable

Increase

1E+30

1E+30

30450

30450

20000

20000

14000

1E+30

1E+30

Allowable

Decrease

20000

25000

14000

14000

60000

20000

30450

30450

18000

Purchase answer to see full

attachment

The price is based on these factors:

Academic level

Number of pages

Urgency

Basic features

- Free title page and bibliography
- Unlimited revisions
- Plagiarism-free guarantee
- Money-back guarantee
- 24/7 support

On-demand options

- Writer’s samples
- Part-by-part delivery
- Overnight delivery
- Copies of used sources
- Expert Proofreading

Paper format

- 275 words per page
- 12 pt Arial/Times New Roman
- Double line spacing
- Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Delivering a high-quality product at a reasonable price is not enough anymore.

That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.