excel sheet

R. Saltzman Assignment 4: TV Ads w/ Variations Name .

DS 601 Applied Management Science Due Date: Next Week

Create a new file for each of the following 3 variations of the TV Advertising Problem studied in class. Please note the following important items:

· Name your 3 files Lastname-Asmt41.xlsx, Lastname-Asmt42.xlsx, and Lastname-Asmt43.xlsx.

· Email them to me by 3:00 PM next Thursday, attaching all three files to one email. Send the email from your SFSU email account to avoid it being flagged as spam by the SFSU server and not reaching me.

· On each problem, start with the original problem formulation solved with integer constraints on all decision variables. Include these integer constraints on all 3 problems.

1. (5 pts.) In addition to the constraints in the original advertising problem, suppose that General Flakes also wants to obtain at least 180 million exposures to men, and at least 160 million exposures to women.

1a) How far off is the original model’s optimal solution from satisfying each of these two conditions?

1b) Modify the model to ensure these conditions are met; rerun Solver to find a new optimal solution.

1c) How much does the optimal total cost increase compared to that of the original model?

2. (5 pts.) Return to the original advertising model (i.e., ignore the conditions of problem 1 above). Suppose that General Flakes decides it will place at most 10 ads on any given show.

2a) Modify the model to incorporate this condition, and then re-optimize to find the new optimal solution. How much does satisfying this condition cost GFC compared to the optimal solution for the original problem?

2b) Run a sensitivity analysis (either via SolverTable or manually with Solver, copying and pasting the results as needed) to see how sensitive the optimal solution (ad strategy) and total costs are to the maximum number of ads allowed on any one show. Let the maximum number of ads on any one show range from 5 to 17 in increments of 2 (i.e., use 5, 7, 9, …, 17).

2c) Make a chart (“tradeoff curve”) from the results showing how total costs change as a function of the maximum number of ads allowed.

3. (5 pts.) In the dual-objective version of the advertising model, we maximized the total number of excess exposures while placing a budget constraint on the total advertising cost.

3a) Go back to minimizing the total advertising cost but add a constraint that places a lower limit on the total number of excess exposures, i.e., Total Number of Excess Exposures ≥ 0 (the lower limit is 0).

3b) Run a sensitivity analysis on this lower limit (either via SolverTable or manually with Solver, copying and pasting the results as needed), where the range of values goes from 0 to 50 in increments of 5. Report both the optimal ad strategy and its total cost.

3c) Make a tradeoff curve from the results showing how total costs change as a function of the lower limit on the total number of excess exposures.