excel sheet

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

Model

Advertising model DS 601
(LHS)
Inputs Exposures Exposures Exposures
Exposures/ad to groups Revenge Sunday NF The Simpsons SportsCenter Homeland Rachael Ray CNN The Good Wife Actual Required Excess
Men 18-35 5 6 5 0.5 0.7 0.1 0.1 3 73.1 ≥ 60 13.1
Men 36-55 3 5 2 0.5 0.2 0.1 0.2 5 60.4 ≥ 60 0.4
Men >55 1 3 0 0.3 0.0 0.0 0.3 4 32.4 ≥ 28 4.4
Women 18-35 6 1 4 0.1 0.9 0.6 0.1 3 60 ≥ 60 0
Women 36-55 4 1 2 0.1 0.1 1.3 0.2 5 61 ≥ 60 1
Women >55 2 1 0 0 0.0 0.4 0.3 4 28.5 ≥ 28 0.5
Total exposures 21 17 13 1.5 1.9 2.5 1.2 24 19.4
Cost per ad 140 100 80 9 13 15 8 140
Cost per million exposures 6.667 5.882 6.154 6.000 6.842 6.000 6.667 5.833
Advertising Plan Revenge Sunday NF The Simpsons SportsCenter Homeland Rachael Ray CNN The Good Wife
No. of ads purchased 0 0 8 25 2 9 3 6
Total Cost 1890 ≤ 2000 Total Budget

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

Q & A

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).
The total cost is $1890.
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.

Sheet3