excel sheet

profilejimmyttt
Tan-Asmt42.xlsx.xlsx
Home>Mathematics homework help>Statistics homework help>excel sheet

Model

Advertising model DS 601
(LHS)
Inputs Exposures Exposures
Exposures/ad to groups Revenge Sunday NF The Simpsons SportsCenter Homeland Rachael Ray CNN The Good Wife Actual Required
Men 18-35 5 6 5 0.5 0.7 0.1 0.1 3 69.2 ≥ 60
Men 36-55 3 5 2 0.5 0.2 0.1 0.2 5 60.2 ≥ 60
Men >55 1 3 0 0.3 0.0 0.0 0.3 4 34.1 ≥ 28
Women 18-35 6 1 4 0.1 0.9 0.6 0.1 3 60.3 ≥ 60
Women 36-55 4 1 2 0.1 0.1 1.3 0.2 5 60.8 ≥ 60
Women >55 2 1 0 0 0.0 0.4 0.3 4 33.2 ≥ 28
Total exposures 21 17 13 1.5 1.9 2.5 1.2 24
Cost per ad 140 100 80 9 13 15 8 140
Advertising Plan Revenge Sunday NF The Simpsons SportsCenter Homeland Rachael Ray CNN The Good Wife
No. of ads purchased 0 0 8 7 2 3 0 8
Total Cost 1894

Note: All monetary values are in $1000s, and all exposures to ads are in millions of exposures.

Q & A

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?
The new optimal solution is $1894, and the original problem's optimal solution is $1880. which is $1894-$1880=$14 increase for satisfying this condition cost.
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.

Sheet3