4_LP application Marketing Finance Ingredient Blending(1) - Microsoft Excel 14.0 Sensitivity Report Worksheet[Week3 LP application Marketing Finance

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

This preview shows page 1 - 6 out of 23 pages.

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
Image of page 1
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).
Image of page 2
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
Image of page 3
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
Image of page 4
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
Image of page 5
Image of page 6

You've reached the end of your free preview.

Want to read all 23 pages?

  • Fall '05
  • EL
  • LHS, Constraint, R.H. Side

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture