excel question(2 problems)

jimmyttt
DS4111920.xlsx
Home>Mathematics homework help>Statistics homework help>excel question(2 problems)

#19

Advertising model with nonlinear reponse function (All monetary values are in $1000s, and all exposures to ads are in millions of exposures.)
Constant in advertising response function for various groups for different shows
Revenge Sunday Night Football The Simpsons SportsCenter Homeland Rachael Ray CNN The Good Wife
Men 18-35 93.061 116.808 84.772 43.647 26.711 11.99 11.793 79.534
Men 36-55 61.129 76.527 61.528 47.749 19.655 10.281 9.982 89.217
Men >55 33.376 57.84 9.913 30.075 10.751 11.51 22.218 65.543
Women 18-35 105.803 40.113 66.998 22.101 42.451 29.403 8.236 72.145
Women 36-55 71.784 26.534 46.146 16.151 34.609 24.276 10.426 92.831
Women >55 56.828 17.209 8.887 9.101 8.46 31.149 23.105 71.321
Coeffcient of exponent in advertising response function for various groups for different shows
Revenge Sunday Night Football The Simpsons SportsCenter Homeland Rachael Ray CNN The Good Wife
Men 18-35 0.029 0.055 0.093 0.071 0.087 0.038 0.029 0.045
Men 36-55 0.084 0.05 0.085 0.094 0.018 0.09 0.054 0.051
Men >55 0.071 0.068 0.077 0.027 0.039 0.051 0.013 0.036
Women 18-35 0.035 0.063 0.069 0.074 0.06 0.012 0.039 0.035
Women 36-55 0.089 0.057 0.061 0.055 0.014 0.022 0.046 0.04
Women >55 0.01 0.033 0.078 0.078 0.035 0.05 0.072 0.03
Cost per ad 140 100 80 9 13 15 8 140
Advertising plan
Revenge Sunday Night Football The Simpsons SportsCenter Homeland Rachael Ray CNN The Good Wife
Number ads purchased 5.792 0 4.188 0 22.361 12.152 17.137 0
Exposures to each group from each show
Revenge Sunday Night Football The Simpsons SportsCenter Homeland Rachael Ray CNN The Good Wife
Men 18-35 14.389 31.866 35.305 14.716 10.573 2.369 1.823 18.248
Men 36-55 23.550 19.242 23.922 20.047 1.946 4.177 2.681 22.818
Men >55 11.253 18.830 3.567 4.354 2.174 2.944 1.611 12.336
Women 18-35 19.414 12.264 22.072 7.704 12.462 1.974 1.665 13.238
Women 36-55 28.914 7.461 13.735 4.406 2.696 2.904 2.439 19.197
Women >55 3.198 2.994 3.230 3.308 1.552 7.832 7.879 11.376
Constraints on number of exposures
Actual exposures Required exposures
Men 18-35 73.682 >= 60
Men 36-55 61.352 >= 60
Men >55 29.995 >= 28
Women 18-35 75.586 >= 60
Women 36-55 60 >= 60
Women >55 40.827 >= 28
Objective to minimize
Total cost $1,755.989
Comparison
Revenge Sunday Night Football The Simpsons SportsCenter Homeland Rachael Ray CNN The Good Wife Total Cost
w/sportscenter 4.836 0 2.794 21.852 16.284 8.285 15.289 0 $1,555.535
w/o sprtscemter 5.792 0 4.188 0 22.361 12.152 17.137 0 $1,755.989
It was optimal in both cases for no ads to purchased for Sunday Night Football and The Good Wife. With the addition of no ads being bought for SportsCenter, there will be an increase in the number of other bought television shows.
The total cost changed much because of the cheap cost if the SportsCenter ads.

#20

Advertising model with nonlinear reponse function (All monetary values are in $1000s, and all exposures to ads are in millions of exposures.)
Constant in advertising response function for various groups for different shows
Revenge Sunday Night Football The Simpsons SportsCenter Homeland Rachael Ray CNN The Good Wife The View
Men 18-35 93.061 116.808 84.772 43.647 26.711 11.99 11.793 79.534 5
Men 36-55 61.129 76.527 61.528 47.749 19.655 10.281 9.982 89.217 7
Men >55 33.376 57.84 9.913 30.075 10.751 11.51 22.218 65.543 10
Women 18-35 105.803 40.113 66.998 22.101 42.451 29.403 8.236 72.145 15
Women 36-55 71.784 26.534 46.146 16.151 34.609 24.276 10.426 92.831 35
Women >55 56.828 17.209 8.887 9.101 8.46 31.149 23.105 71.321 35
Coeffcient of exponent in advertising response function for various groups for different shows
Revenge Sunday Night Football The Simpsons SportsCenter Homeland Rachael Ray CNN The Good Wife The View
Men 18-35 0.029 0.055 0.093 0.071 0.087 0.038 0.029 0.045 0.03
Men 36-55 0.084 0.05 0.085 0.094 0.018 0.09 0.054 0.051 0.03
Men >55 0.071 0.068 0.077 0.027 0.039 0.051 0.013 0.036 0.03
Women 18-35 0.035 0.063 0.069 0.074 0.06 0.012 0.039 0.035 0.08
Women 36-55 0.089 0.057 0.061 0.055 0.014 0.022 0.046 0.04 0.08
Women >55 0.01 0.033 0.078 0.078 0.035 0.05 0.072 0.03 0.08
Cost per ad 140 100 80 9 13 15 8 140 10
Advertising plan
Revenge Sunday Night Football The Simpsons SportsCenter Homeland Rachael Ray CNN The Good Wife The View
Number ads purchased 1.732 0 0.028 19.467 11.562 0 10.131 0 24.318
Exposures to each group from each show
Revenge Sunday Night Football The Simpsons SportsCenter Homeland Rachael Ray CNN The Good Wife The View
Men 18-35 4.559 10.614 12.612 5.050 3.736 0.764 0.578 5.963 0.253
Men 36-55 8.277 6.348 8.423 7.174 0.603 1.484 0.891 7.543 0.354
Men >55 3.862 6.426 1.238 1.374 0.702 0.973 0.495 3.962 0.506
Women 18-35 6.223 4.147 7.547 2.659 4.190 0.605 0.538 4.244 1.941
Women 36-55 10.255 2.494 4.627 1.468 0.829 0.908 0.798 6.214 4.529
Women >55 0.976 0.956 1.123 1.150 0.498 2.584 2.709 3.611 4.529
Constraints on number of exposures
Actual exposures Required exposures
Men 18-35 60.000 >= 60
Men 36-55 60.033 >= 60
Men >55 28.000 >= 28
Women 18-35 60.000 >= 60
Women 36-55 60.000 >= 60
Women >55 52.897 >= 28
Objective to minimize
Total cost $894.457
Comparison
Revenge Sunday Night Football The Simpsons SportsCenter Homeland Rachael Ray CNN The Good Wife The View Total Cost
no "The View" 4.836 0 2.794 21.852 16.284 8.285 15.289 0 0 $1,555.535
With "The View" 1.732 0 0.028 19.467 11.562 0 10.131 0 24.318 $894.457
It was optimal in both cases for no ads to purchased for Sunday Night Football and The Good Wife. With the addition of a new show "The View" is coming out, there will be an decrease in the number of other bought television shows.
The total cost changed much because of the cheap cost if the The View ads.