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 cost. Increase in Sales per Unit of Advertising Product Stain remover Liquid detergent Powder detergent Unit cost Television 0% 3 -1 Print Media 1% 2 Minimum Required Increase 3% 18 4 4 $1 million $2 million A B D E F G Profit & Gambit Co. Advertising-Mix Problem 2 3 Television Print Media 4 Unit Cost (Smillions) 2 5 6 Increased Minimum 7 Sales Increase 8 3% 3% Stain Remover Liquid Detergent Powder Detergent Increase in Sales per Unit of Advertising 0% 1% 3% 2% -1% 4% 9 IV IV 18% 18% 8% 10 4% 11 12 Total Cost 13 Television Print Media (Smillions) 10 14 Advertising Units 3 Requirement: Refer to, or recreate, the spreadsheet model for this case and generate the sensitivity analysis report. Answer the following questions based on the results at optimal solution on the spreadsheet and the generated sensitivity report only. Requirement: Refer to, or recreate, the spreadsheet model for this case and generate the sensitivity analysis report. Answer the following questions based on the results at optimal solution on the spreadsheet and the generated sensitivity report only a) Identify a non-binding constraint, and explain what does it mean using the above case's context? b) Suppose there was an error in estimating the costs for the TV advertisement. Provide a range for the cost of a TV ad within which the current optimal advertising plan would not be affected. Support your answer. c) What would be the impact of reducing the liquid detergent's sales target from 18% to 15% on the total costs? Explain. 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. 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. 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 cost. Increase in Sales per Unit of Advertising Product Stain remover Liquid detergent Powder detergent Unit cost Television 0% 3 -1 Print Media 1% 2 Minimum Required Increase 3% 18 4 4 $1 million $2 million A B D E F G Profit & Gambit Co. Advertising-Mix Problem 2 3 Television Print Media 4 Unit Cost (Smillions) 2 5 6 Increased Minimum 7 Sales Increase 8 3% 3% Stain Remover Liquid Detergent Powder Detergent Increase in Sales per Unit of Advertising 0% 1% 3% 2% -1% 4% 9 IV IV 18% 18% 8% 10 4% 11 12 Total Cost 13 Television Print Media (Smillions) 10 14 Advertising Units 3 Requirement: Refer to, or recreate, the spreadsheet model for this case and generate the sensitivity analysis report. Answer the following questions based on the results at optimal solution on the spreadsheet and the generated sensitivity report only. Requirement: Refer to, or recreate, the spreadsheet model for this case and generate the sensitivity analysis report. Answer the following questions based on the results at optimal solution on the spreadsheet and the generated sensitivity report only a) Identify a non-binding constraint, and explain what does it mean using the above case's context? b) Suppose there was an error in estimating the costs for the TV advertisement. Provide a range for the cost of a TV ad within which the current optimal advertising plan would not be affected. Support your answer. c) What would be the impact of reducing the liquid detergent's sales target from 18% to 15% on the total costs? Explain. 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. 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
