Joe Bright, the marketing manager for Mountain Mist soda needs to decide how many TV spots and magazine advertisements to run during the next quarter....

Joe Bright, the marketing manager for Mountain Mist soda needs to decide how many TV spots and magazine advertisements to run during the next quarter. Each TV spot costs $5,000 and is expected to increase sales by 300,000 cans. Each magazine advertisement costs $2,000 and is expected to increase sales by 500,000 cans. The total soda advertising budget for the next quarter is set at $100,000; however Mountain Mist wants to spend no more than $70,000 on TV spots and no more than $50,000 on magazine ads. Mountain Mist earns a profit of 5 cents on each can of soda it sells. Joe has decided to use linear programming to find the most profitable mix of advertisements. He asks for your help.

(a)  (1 mark) What are the decision variables for this problem?

(b) (3 marks) Using decision variables identified in part (a), formulate the objective function for this problem. Is the quantity of interest to be maximized or minimized?

(c)  (2 marks) What is the limited resource in this problem? Using decision variables from part (a), formulate the constraint for this resource.

(d) (2 marks) What other constraints are relevant to this problem? Using the decision variables from part (a), formulate those constraints.

(e)  (1 mark) Give the full mathematical model for this problem.

(f)   (4 marks) Use EXCEL Solver to obtain a solution to the linear programming problem from part (e), together with an Answer Report and a Sensitivity Report. Provide a printout of your EXCEL spreadsheet and of the two Excel reports with your assignment submission.

EXCEL Instructions: All EXCEL output should bear your e-mail ID. To ensure this, you will need to save your EXCEL file as 'E-mail ID Advertising Plan.xls'. In addition, your constraint names should begin with your initials, e.g. JB_Budget.

Please refer to Topic 5 in the Excel Booklet or the Linear Programming Supplement to the textbook (pdf file posted on the course website) for detailed instructions on how to set up your spreadsheet and use Solver.

(g) (5 marks) Mountain Mist's major competitor has just launched its new TV advertising campaign. Joe worries that Mountain Mist's TV spots will be less effective and only increase sales by 200,000 cans. Would the solution obtained in part (f) still be optimal? Which of the EXCEL reports helps you answer this question? Justify your answer carefully. How would Mountain Mist's total profit change, if at all?

(h) (4 marks) Prepare a report that summarizes linear programming results. Your report should discuss the following issues:

·        The optimal number of TV spots and magazine advertisements and the maximum increase in profit next quarter.

·        The actual cost of Mountain Mist's advertising and the amount left in the budget, if any.

·        The total cost of the optimum number of TV spots, and of the magazine advertisements.

·        Which report enables you to answer these questions?

(i)    (6 marks) What is the impact on the maximum profit if the budget is increased by $10,000.

Include in your answer a discussion of the shadow price.