Microsoft Excel 16.0 Sensitivity Report Worksheet: [Case P12.1 Modified.xlsx]12.2 Modified Report Created: 5/5/2020 12:42:50 AM Variable Cells Cell $C$36 $D$36 $E$36 $F$36 Name Number of Ads TV Number of Ads RA Number of Ads NP Number of Ads OL Final Reduced Objective Allowable Value Cost Coefficient Increase 12 0 130000 1E+030 3 0 17000 500 0 -750 15000 750 10.5 0 35000 8333.3333333 Constraints Cell $G$43 $G$44 $G$45 $G$46 $G$47 $G$48 $G$49 Name Max TV Total Max Radio Total Max Online Total Max Radio Ratio of Total Authorized Total Min Radio (Commitment) Total Min TV Ratio of Total Authorized Total Cost & Budget Total Final Shadow Constraint Allowable Value Price R.H. Side Increase 12 25000 0 3.5 3 0 0 1E+030 10.5 0 0 1E+030 3 0 0 1E+030 3 -500 0 10.935483871 12 0 0 9.45 24000 70 24000 250 Allowable Decrease 25000 1E+030 1E+030 1000 Allowable Decrease 0.1666666667 15 0.5 8.475 1 1E+030 5250 Constraint Television Radio 130000 17000 Cost per advertisement 1500 250 Maximum media usage 12 18 Audience per advertisement A commitment of 3 radio spots was made to the broadcaster. Radio must not exceed 45% of total ads authorized. TV must be at least 10% of the total ads authorized. The budget is $24K (Entire budget to be spent.) Newspapers were added to ad options with no upper limit. Assume that a fractional ad represents print size or air time that is reduced proportionately. OBJECTIVE DETERMINATION Maximize media exposure. DEFINE VARIABLES Let TV be the number of TV ads Let RA be the number of Radio ads Let OL be the number of online ads Let NP be the number of newspaper ads OBJECTIVE FUNCTION Total media exposure = 130,000 TV + 17,000 RA + 18,000 NP + 35,000 OL SUBJECT TO THE CONSTRAINTS TV 0.10*(TV + RA + NP + OL) TV > 3 1500 TV + 250 RA +225 NP + 500 OL = 24000 TV, RA, NP, OL > 0 MODEL TV RA Number of Ads 12 3 130000 17000 1 0 0 0 0 0 1 0 1 1 Exposure Max TV Max Radio Max Online Max Radio Ratio of Total Authorized Min Radio (Commitment) Min TV Ratio of Total Authorized Cost & Budget 1 1500 0 250 Newspapers Online 15000 35000 225 500 --- 11 NP OL Total Ads 0 10.5 26 15000 35000 Total Exposure 1,978,500 0 0 1 0 0 Total 12.0 3.0 10.5 3.0 3.0 d proportionately. 0 0 0 0 0 RHS 12 18 11 11.475 3 0 225 0 500 12.0 24,000.0 > = 2.55 24000