F G I 1 Problem 3. 3 Recall the Profit and Gambit Case in Chapter 2 (p.58) of your E-Book where management had decided to undertake a major new advertising campaign using both television and print media. The objective in this problem was to determine how much to advertise in each medium to meet the sales goals at a minimum total costi 5 6 7 8 Increase in Sales per Unit of Advertising 9 Television Print Media 11 Product Stain remover Liquid detergent Powder detergent 0% 3 1 12 Minimum Required Increase 3% 19 4 196 2 4 13 Unit cost $1 million $2 million 15 16 17 Requirement: Refer to, or recreate, the spreadsheet model for this case and generate the sensitivity analysis report. 18 Answer the below questions based on the results at optimal solution on the spreadsheet and the generated sensitivity report only. 19 a) Identify a non-binding constraint, and explain what does it mean using the above case's context? 20 21 b) Suppose there was an error in estimating the costs for the TV advertisement. Pfovide a range for the cost of a TV ad within which the current optimal advertising plan would not be affected. Support your answer. 22 23 c) What would be the impact of reducing the liquid detergent's sales target from 18% to 15% on the total costs? Explain. 24 25 26 27 d) Discuss the effect of applying 40% and 20% discounts on the unit cost of TV ads and Print media ads respectively and simultaneously. Will that affect the optimal mix and the total cost? Why or why not? Explain. 28 29 e) Assume that management is considering adjustments in the marketing campaign such that the required increase in sales of liquid detergent would range from 10% to 40%. Use the solver table, with increments of 5%, to analyze (using your own words) the impact of this change on the recommended mix of advertising units, the actual amount of increased sales, and the corresponding total cost of the campaign. 30 31 32 F G I 1 Problem 3. 3 Recall the Profit and Gambit Case in Chapter 2 (p.58) of your E-Book where management had decided to undertake a major new advertising campaign using both television and print media. The objective in this problem was to determine how much to advertise in each medium to meet the sales goals at a minimum total costi 5 6 7 8 Increase in Sales per Unit of Advertising 9 Television Print Media 11 Product Stain remover Liquid detergent Powder detergent 0% 3 1 12 Minimum Required Increase 3% 19 4 196 2 4 13 Unit cost $1 million $2 million 15 16 17 Requirement: Refer to, or recreate, the spreadsheet model for this case and generate the sensitivity analysis report. 18 Answer the below questions based on the results at optimal solution on the spreadsheet and the generated sensitivity report only. 19 a) Identify a non-binding constraint, and explain what does it mean using the above case's context? 20 21 b) Suppose there was an error in estimating the costs for the TV advertisement. Pfovide a range for the cost of a TV ad within which the current optimal advertising plan would not be affected. Support your answer. 22 23 c) What would be the impact of reducing the liquid detergent's sales target from 18% to 15% on the total costs? Explain. 24 25 26 27 d) Discuss the effect of applying 40% and 20% discounts on the unit cost of TV ads and Print media ads respectively and simultaneously. Will that affect the optimal mix and the total cost? Why or why not? Explain. 28 29 e) Assume that management is considering adjustments in the marketing campaign such that the required increase in sales of liquid detergent would range from 10% to 40%. Use the solver table, with increments of 5%, to analyze (using your own words) the impact of this change on the recommended mix of advertising units, the actual amount of increased sales, and the corresponding total cost of the campaign. 30 31 32
