4_LP application Marketing Finance Ingredient Blending(1) -...

Microsoft Excel 14.0 Sensitivity Report
Worksheet: [Week3 LP application Marketing Finance Ingredient Blending.xlsx]Media Selection
Report Created: 8/21/2015 3:43:22 PM
Variable Cells
Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
$B$3 Solution TV ads 1.8181818182 0 20000 5500 11000
$C$3 Solution Radio ads 10 0 12000 1.00000E+030 1000
$D$3 Solution Newspaper ads 3.1818181818 0 9000 1222.2222222 3666.6666667
Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$E$8 Ad cost RHS 100000 1 100000 24000 20000
$E$9 TV ads RHS 1.8181818182 0 4 1.00000E+030 2.1818181818
$E$10 Radio ads RHS 10 1000 10 3.8888888889 4.6666666667
$E$11 Newspaper ads RHS 3.1818181818 0 7 1.00000E+030 3.8181818182
$E$12 Staff max ads RHS 15 5000 15 2.8 2.3333333333

x1
x2
x3
Solution
1.8181818182 10 3.1818181818
Total
20000 12000 9000 185000
Constraints RHS Signs LHS
Budget Constraint ($)
15000 6000 4000 100000 <= 100000
TC Constraint
1 1.8181818 <= 4
RC Constraint
1 10 <= 10
NA Constraint
1 3.1818182 <= 7
1 1 1 15 <= 15
Number of
Television
Commercials
(TC)
Number of Radio
Commercials (RC)
Number of
Newspapers Ads
(NA)
Audience Exposure
(People)
Advertising firm
Constraint
Solution
Analysis
It is not realistic to round television commercials to 2, with 10 radio commercials and 3 newspapers ads. Because the
solution will then exceed the $100,000 budget constraint by $2,000. Thus the store has two options: 1) Increase its
advertising budget by $2000 and make it $102,000 or 2)have 1 TC, 10 RC, and 3 NA with audience exposure of
167,000, or 18,000 less than the optimal amount (almost 10% decrease).

Microsoft Excel 14.0 Sensitivity Report
Worksheet: [Week3 LP application Marketing Finance Ingredient Blending.xlsx]Sheet2
Report Created: 8/21/2015 4:28:13 PM
Variable Cells
Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
$B$3 Solution $ investment in Topeka 0 -0.00952381 0.19047619 0.00952381 1.0000E+030
$C$3 Solution $ investment in Crosswind 3000 0 0.2 1.0000E+030 0.00952381
$D$3 Solution $ investment in Genserve 0 -0.028125 0.171875 0.028125 1.0000E+030
$E$3 Solution $ investment in Savings cost 1000 0 0.05 0.15 1.0000E+030
Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$F$11 Max investment in Genserve & Topeka RHS 0 0 2000 1.0000E+030 2000
$F$8 Initial Investment RHS 4000 0.2 4000 1.0000E+030 2200
$F$9 Crosswing min investment RHS 3000 0 800 2200 1.0000E+030
$F$10 Savings Cert min investment RHS 1000 -0.15 1000 2200 1000

x1
x2
x3
Solution
0 3000 0 1000
Total
Profit
0.1904761905 0.2 0.171875 0.05 650
Constraints
RHS Signs LHS
Initial Investment
1 1 1 1 4000 = 4000
0 1 0 0 3000 >= 800
0 0 0 1 1000 >= 1000
1 0 1 0 0 <= 2000
$ investment in
Topeka
$ investment in
Crosswind
$ investment in
Genserve
$ investment in
Savings cost
Crosswing min
investment
Savings Cert min
investment
Max investment in
Genserve & Topeka

Microsoft Excel 14.0 Sensitivity Report
Worksheet: [Week3 LP application Marketing Finance Ingredient Blending.xlsx]Portolio1b
Report Created: 8/21/2015 4:36:59 PM
Variable Cells
Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
$B$3 Solution $ investment in Topeka 0 -0.4 8 0.4 1.00000E+030
$C$3 Solution $ investment in Crosswind 100 0 6 1.000E+030 0.2857142857
$D$3 Solution $ investment in Genserve 0 -1.8 11 1.8 1.00000E+030
$E$3 Solution $ investment in Savings cost 1000 0 0.05 0.15 1.00000E+030
Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$F$11 Max investment in Genserve & Topeka RHS 0 0 2000 1.000E+030 2000
$F$8 Initial Investment RHS 4000 0.2 4000 1.000E+030 2200
$F$9