4.1, including the following spreadsheet showing its formulation and optimal solution. Exposures per Ad Ad Budget...

Transcribed Image Text:

4.1, including the following spreadsheet showing its formulation and optimal solution. Exposures per Ad Ad Budget Planning Budget Number of Ads Max TV Spots Adjustable Cells Cell Name TV Spots 1,300,000 $C$13 $D$13 $E$13 Constraints Consider the Super Grain Corp. case study as presented in Section $300,000.00 $90,000.00 TV Spots 0 <= 5 Magazine Ads 600,000 Consider the sensitivity report generated by Excel Solver. Cost per Add $ 150,000.00 $ 30,000.00 Magazine Ads 20 TVSpots Number of Ads Magazine Ads. Number of Ads SS Ads Cell Name $E$8 Ad Budget Spent $F$9 Planning Budget Spent Final Value 0 20 10 SS Ads 500,000 Final Value $100,000.00 $ 40,000.00 SS Ads 10 Reduced Cost -50000 0 0 Shadow Price 3.00 5.00 Budget Spent $4,000,000.00 $1,000,000.00 <= Objective Allowable Coefficient Increase 1300000 600000 500000 Constraint R.H. Side 4000000 1000000 $ 4,000,000.00 $ 1,000,000.00 Total Exposures 17,000,000.00 50000 150000 300000 Budget Available Allowable Increase 1000000 600000 Allowable Decrease 1E+30 50000 33333.33333 Allowable Decrease 1500000 200000 Then use this report to independently address each of the following questions. a. How much could the total expected number of exposures be increased if both the advertising budget and planning budget were increased by $250,000 each? (20 points) b. If only $150,000 can be added to either the advertising budget or planning budget, where should it be added to do the most good? (20 points) c. If $100,000 must be removed from either the advertising budget or the planning budget, from which budget should it be removed to do the least harm? (20 points) d. If the Exposures per TV spot ad increases by 40,000 and Exposures per Sunday supplement ad decreases by 6,600 does the optimal solution change? (20 points) e. If the Exposures per TV spot ad increases by 20,000 and Exposures per Magazine ad becomes 700,000 does the optimal solution change? (20 points) 4.1, including the following spreadsheet showing its formulation and optimal solution. Exposures per Ad Ad Budget Planning Budget Number of Ads Max TV Spots Adjustable Cells Cell Name TV Spots 1,300,000 $C$13 $D$13 $E$13 Constraints Consider the Super Grain Corp. case study as presented in Section $300,000.00 $90,000.00 TV Spots 0 <= 5 Magazine Ads 600,000 Consider the sensitivity report generated by Excel Solver. Cost per Add $ 150,000.00 $ 30,000.00 Magazine Ads 20 TVSpots Number of Ads Magazine Ads. Number of Ads SS Ads Cell Name $E$8 Ad Budget Spent $F$9 Planning Budget Spent Final Value 0 20 10 SS Ads 500,000 Final Value $100,000.00 $ 40,000.00 SS Ads 10 Reduced Cost -50000 0 0 Shadow Price 3.00 5.00 Budget Spent $4,000,000.00 $1,000,000.00 <= Objective Allowable Coefficient Increase 1300000 600000 500000 Constraint R.H. Side 4000000 1000000 $ 4,000,000.00 $ 1,000,000.00 Total Exposures 17,000,000.00 50000 150000 300000 Budget Available Allowable Increase 1000000 600000 Allowable Decrease 1E+30 50000 33333.33333 Allowable Decrease 1500000 200000 Then use this report to independently address each of the following questions. a. How much could the total expected number of exposures be increased if both the advertising budget and planning budget were increased by $250,000 each? (20 points) b. If only $150,000 can be added to either the advertising budget or planning budget, where should it be added to do the most good? (20 points) c. If $100,000 must be removed from either the advertising budget or the planning budget, from which budget should it be removed to do the least harm? (20 points) d. If the Exposures per TV spot ad increases by 40,000 and Exposures per Sunday supplement ad decreases by 6,600 does the optimal solution change? (20 points) e. If the Exposures per TV spot ad increases by 20,000 and Exposures per Magazine ad becomes 700,000 does the optimal solution change? (20 points)

Answer rating: 100% (QA)

a The total number of expected exposures could be increased by 3000 for each additional 1000 added t... View the full answer