Question

Solve for the optimal solution and sensitivity report with the information above and answer the following questions:

Super Grain Corp.Advertising-Mix Problem Data Cells parameters Changing cells decisions Output cells depends on changing cells Objective cell TV Spots Magazine Ads 1300 600 SS Ads 500 Exposure per Ad (thousands) Cost per Ad ($thousands) 300 150 100 90 Ad Budget Planning Budget Budget Spent 3775 1000 V Budget Available 4000 1000 30 40 VI Number Reached per Ad (millions) Total Reached Minimum Acceptable 1.2 0.1 5 Young Children Parents of Young Children 0.5 0.2 0.2 5.85 TV Spots Magazine Ads Coupon Redemption 0 4 0 per Ad ($thousands) SS Ads 120 Total Redeemed 1490 = Required Amount 1490 TV Spots Magazine Ads 3 14 SS Ads 7.75 Total Exposures (thousands) 16175 Number of Ads л Maximum TV Spots 15

1. What is the range of the number of exposures for TV spots that the current optimal solution remains optimal?

2. If the unit exposure of TV spots increases by 500,000 and that of SS (Sunday supplements) ads increases by 250,000 per $1,000, would the current optimal solution remain optimal? Explain why or why not and show your computation.

3. How much could the total expected number of exposures be increased for each additional $1,000 added to the planning budget?

4. What is the range of the planning budget that the change of the number of exposures per $1,000 addition of the planning budget in the previous problem would remain valid?

5. If both advertising and planning budgets were increased by $50,000, would the shadow prices remain unchanged? Why or why not. Show your computation.

6. What would the new total number of exposures be for the previous problem? Compute it.

7. If the minimum number of young children required to be reached increased by 1 million (from 5 millions), how would the total number exposures change? Show your computation.

Answer 1

Q.1 What is the range of the number of exposures for TV spots that the current optimal solution remains optimal?

Ans : The objective is to maximise the exposure & meeting various other criteria & budget. The optimal solution is as per given variable in the excel screen shot where TV Sports exposure is 3900K (1300*3). The moment you change TV Sports from 3 to any number the solution does not remain optimal hence there is no range but single number.

Q.2 If the unit exposure of TV spots increases by 500,000 and that of SS (Sunday supplements) ads increases by 250,000 per $1,000, would the current optimal solution remain optimal? Explain why or why not and show your computation

Ans : The exposure is only increasing per ad and other values and limitation remains as it is hence the solution remains the same. The total exposure in new solution reaches to 19612K from 16175K

Q.3. How much could the total expected number of exposures be increased for each additional $1,000 added to the planning budget?

Ans : Number of exposures will not increase since only addition to planning budget would not help, along with planning budget ad budget is also required to be added

Q.4 What is the range of the planning budget that the change of the number of exposures per $1,000 addition of the planning budget in the previous problem would remain valid?

Ans : There is no range since Number of exposures will not increase since only addition to planning budget would not help, along with planning budget ad budget is also required to be added

Q.5 : If both advertising and planning budgets were increased by $50,000, would the shadow prices remain unchanged? Why or why not. Show your computation

When budget is increased, the number of ads for Magazine & SS ads can be changed in such a manner that we have maximum exposure with required limitation & budget.

The shadow price for existing solution works out to : 3.387435 per exposure (16175/4775)

The Shadow price after budget increase and adjusting Ads for magazine and SS ads brings shadow price down to 3.380174 (17043/5041.9)

Particulars	TV sports	Magazine Ads	SS Ads	Total	Budget / Condition
Exposure per Ad (1000)	1,300	600	500
Cost per AD (1000)
Ad Budget	300	150	100
Planning budget	90	30	40
Number reached per ad (Mil)
Young Children	1	0	-
Parents of Yound children	1	0	0
Coupon redemption per Ad (1000)	-	40	120
Number of Ads	3	16	7
Exposure (1000)	3,900	9,600	3,543	17,043
Coupon Redemption	-	640	850	1,490	1,490
Number reached (Mil)
Young Children	3.60	1.60	-	5.20	>=5
Parents of Yound children	1.50	3.20	1.42	6.12	>=5
Budget Spent (1000)	900	2,400	709	4,009	4,050
Planning budget spent (1000)	270	480	283	1,033	1,050

Q.6 : What would the new total number of exposures be for the previous problem? Compute it.

17043K will be new exposure

Q.7 If the minimum number of young children required to be reached increased by 1 million (from 5 millions), how would the total number exposures change? Show your computation.

Ans : When minimum number of children to be reached criteria changed to 6 Million, it becomes un-achievable solution. Since SS ads has no reach to children, we have to adjust number of TV Sports and Magazine only. To reach 6 Million reach, the minimum TV ad required is 4 and 12 of magazine. With 4 TV Ad and 12 Magazine ad due to budget constraint the maximum SS Ad could be 7 and as a result the coupon redemption achieved is 1320 in place exact requirement of 1490

Particulars	TV sports	Magazine Ads	SS Ads	Total	Budget / Condition
Exposure per Ad (1000)	1,300.00	600.00	500.00
Cost per AD (1000)
Ad Budget	300.00	150.00	100.00
Planning budget	90.00	30.00	40.00
Number reached per ad (Mil)
Young Children	1.20	0.10	-
Parents of Yound children	0.50	0.20	0.20
Coupon redemption per Ad (1000)	-	40.00	120.00
Number of Ads	4.00	12.00	7.00
Exposure (1000)	5,200.00	7,200.00	3,500.00	15,900.00
Coupon Redemption	-	480.00	840.00	1,320.00	1,490.00
Number reached (Mil)
Young Children	4.80	1.20	-	6.00	>=6
Parents of Yound children	2.00	2.40	1.40	5.80	>=5
Budget Spent (1000)	1,200.00	1,800.00	700.00	3,700.00	4,000.00
Planning budget spent (1000)	360.00	360.00	280.00	1,000.00	1,000.00