Question

Problem 4 Blue ocean insurance services Inc. is working on its advertising strategy and wants to consider four types of media: Internet, Radio, TV, and Newspaper. Their objective is to maximize total exposure to audiences (exposure means number of audiences reached through the ad). The cost of an ad and exposure per ad is provided in the following table Media type Cost per ad Exposure per ad $0.65 $1500 $5000 $3500 Internet 300 Radio 150,000 TV 350,000 Newspaper 200,000 The marketing manager has provided you with the following information 1. Total ad budget should not exceed $100,000 2. Radio and TV must constitute at least 50% of total ads placed 3. Internet should constitute at least 10% of ads placed 4. Newspaper should constitute at least 10% of ads placed a. Formulate the problem using a linear programming model b. Solve your model in Excel, and report the optimal solution. If you get decimals, report them without decimal places (in reality you cannot place fraction of an ad) c. What is the shadow price for the budget constraint? What does it mean?

Answer 1

a) Let the number of ad placed for each media type be indicated by below list

I - Internet
R - Radio
T - TV
N - Newspaper

The objective is to maximize the exposure. Hence the objective function Z is as below

Z = 300I+150000R+350000T+200000N

As total ad budget cannot exceed 100000, we have the below constraint

0.65I+1500R+5000T+3500N $\leq$ 100000

As radio and TV should constitute atleast 50% of the ads placed, we have the below constraint

(R+T)/(I+R+T+N) $\geq$ 1/2

2(R+T) $\geq$ I+R+T+N

2R+2T - I - R -T-N $\geq$ 0

R+T-I-N $\geq$ 0

As Internet should constitute atleast 10% of the ads placed, we have the below constraint

(I)/(I+R+T+N) $\geq$ 1/10

10I $\geq$ I+R+T+N

10I - I - R -T-N $\geq$ 0

9I - R -T - N $\geq$ 0

As Newspaper should constitute atleast 10% of the ads placed, we have the below constraint

(N)/(I+R+T+N) $\geq$ 1/10

10N $\geq$ I+R+T+N

10N - I - R -T-N $\geq$ 0

9N - I - R -T $\geq$ 0

For non-negativity we have the constraint that I,R,T,N $\geq$ 0

b) The problem is formulated in excel as below. The below 2 images show the formulation as well as the formulas used

1 Decision Variables Internet Radio TV 2 Value Newspaper 4 Objective Function 5 Exposure 300 150000 350000 200000 7 Constraints 8 Budget 9 Radio and TV 50% 10 internet 10% 11 Newspaper 10% 0.65 1500 5000 3500 01<= 100000 0 01>= 01>= 0

G H 1 Decision VariablesInternet Radio TV Newspaper 2 Value 4 Objective Function 5 Exposure 300 150000 350000 200000 SUMPRODUCT(B5:E5,SBS2:SES2 7 Constraints 8 Budget 9 Radio and TV 50% 10 Internet 10% 11 Newspaper 10% |<= 1100000 |>= |0 |>=10 |>=10 0.65 15005000 3500 =SUMPRODUCT(B8:E8,SBS2:SES2) -1 SUMPRODUCT(B3E9,$B$2:$ES2) SUMPRODUCT(B10:E10,SBS2:SES2) SUMPRODUCT(B11:E11,$B$2:SES2)

The objective function is highlighted in green color

Post these the solver parameters need to be entered as per the below image.

Solver Parameters Set Objective SFS5 By Changing Variable Cells: SBS2:SES2 Subject to the Constraints: Add SFS9: SF$11 >= SHS9: SH$11 hange Delete Reset All Load/Save lv/l Make Unconstrained Variables Non-Negative Selct a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close

Once this is done, click on solve. The solver will solve the problem and show the message stating the same. Click on ok. The excel will now have the final solution as per below image.

1 Decision Variables Internet Radio TV 2 Value Newspaper 이 6.45134236 6.4513451.611 4 Objective Function 5 Exposure 300 150000 350000 200000 9033815 7 Constraints 8 Budget 9 Radio and TV 50% 10 | Internet 1096 11 Newspaper 10% 0.65 | 1500| 5000 35001 1000001<= 1 38.7081- -11 2.7E-15 I>= 01>= 100000 0 0 0

Thus we can see that the optimal solution after rounding off is as follows

Internet Ads - 6

Radio Ads - 52

TV Ads - 0

Newspaper Ad - 6

Below we can see the sensitivity analysis for this problem.

A B 1 Microsoft Excel 16.0 Sensitivity Report 2 Worksheet: [Book1]Sheet2 3 Report Created: 14-Apr-2019 14:51:22 4 6 Variable Cells Final Value Reduced Objective Allowable Allowable Coefficient Increase Decrease Cell Name Cost 9 ŞB$2 Value Internet 6.451342363 10 $C$2 Value Radio 11 $D$2 Value TV 12 SE$2 Value Newspaper 6.451342363 0 0 0 -116183.5149 300 12994.55882 514548.5714 1E+30 41399.84115 1E+30 200000 147335.5121 514548.5714 51.6107389 350000 116183.5149 14 Constraints 15 16 17 $F$8 Budget 18 $F$9 Radio and TV 50% 19| $FS10 Internet 1096 20 SF$11 Newspaper 10% Final Value Shadow Constraint Allowable Allowable R.H. Side Increase Decrease Cell Name Price 100000 90.33814711 0 2.66454E-15 -1425.149913 0 -13067.62942 100000 100000 1E+30 0 272.6628252 58.82352941 0 139.5306654 74.07050772 1E+30 38.70805418 0 38.70805418

The shadow price for budget constraint is 90.3381. This means that for every additional dollar of budget available, the objective function or exposure will increase by 90.3381 with all other conditions remaining the same.