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

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).

·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