In this assignment, you'll enter the Media Selection Problem below into an Excel worksheet and then use Excel's Solver add-in to optimally solve the linear program. Additional instructions are given in our textbook and in the Excel example files posted on Learn The situation. Advertising firm Night & Day, Inc. (NDI) has been hired to conduct an ad campaign for a client. After considering all the possible advertising media and the target market, NDI has limited next month's ads to 5 sources (listed in the table below), each of which reaches a different number of potential customers, costs a certain amount, and has its own exposure quality." Exposure quality units measure the relative value of one ad in each source and take into account factors such as audience demographics, image presented, and ad quality. Potential Customers Reached per Ad 1,000 Cost per Ad ($) 1,500 Maximum Times Available per Month Exposure Quality Units 2,000 3,000 Advertising Source Daytime TV (1 min. commercial) Evening TV (30-sec. commercial) Daily Newspaper (full page ad) Sunday Newspaper (1/2 page color) Radio (30 second spot) 1,500 400 2,500 1,000 300 100 NDI's goal is to maximize the total exposure quality from the ad campaign. The client has given NDI an advertising budget of $30,000 - see constraint (1) - and has imposed several restrictions on its use. Specifically, at least 10 TV commercials must be used, but no more than $18,000 may be spent on these commercials - see constraints (2) and (3). Also, the client wants at least 50,000 potential customers to be reached through all sources - see constraint (4). Finally, the fourth column of the table above gives limits on the number of times each advertising source may be used in a month - see constraints (51-19). LP Formulation: NDI has 5 decisions to make: DT - number of Daytime TV ads to run ET - number of Evening TV ads to run DN-number of Daily Newspaper ads to run SN -number of Sunday Newspaper ads to run RA = number of Radio Ads to run Maximize 650T + 90ET + 40DN + 755N subject to: 1,500DT + 3,000ET + 400DN + 1,000SN | DT + 1,500DT + 3,000ET 1,000DT + 2,000ET + 1,500DN + 2,500SN DT + 2ORA - Z + 100RA S 30,000 ET 10 $ 18,000 + 300RA 2 50,000 S 15 S 10 S 25 [6] 171 DN SN DT 20, 20, DN 20, SN 20, RAS RA > 30 0 (9) [10]-[14] The solution is: 100 5 30000 300 ow- DT ET ON SNR A 1500 3000 400 1000 110 1500 30001 1000 2000 1500 2500 0 0 0 100 0 0 1 0 0 0 1 0 0 0 Used Available 30000 11.96491 18000 18000 5491228 50000 11.96491 1 5 22.63158 12 Obj. Fun. 11.964910 22.631580 30 14 Max. 650 90 40 75 2 0 16 2 9282.456 DT 1500 ET DN 3000 400 1 0 3000 1 2000 1500 0 0 SN RA Used 1000 100 54912.28 0 0 5 11.96491 0 0 0 0 0 22.63158 10 so 0 1 5 30 Available 30000 10 18000 50000 15 0 0 1 0 25 Obj. Fun. 11.964910 22.63158 0 30 Max. 650 90 40 75 20 Notes: I strongly recommend that you practice first by solving the small ABC LP problem discussed in class since NDI's LP has several more variables (columns) and constraints (rows) than the ABC problem. Many numbers ("coefficients") in the constraints are 0 or 1, e.g., constraint [2] can be rewritten as: 1DT + 1 ET + ODNOSN + ORA 10. In Excel, just enter the coefficients & the RHS: 11000 10 In the Solver Parameters dialor: (1) carefully specify the Objective, Changing Variable, and Constraints cells; (2) check the box that says "Make Unconstrained variables Non-Negative" to take care of constraints (10)-[14]; and (3) choose "Simplex LP" from the "Select a Solving Method" menu. Finally, double-check the direction of the inequality signs (s vs. 2) in the constraints. Please answer the questions below in the spaces provided. Attach a printout of the 1" page of your Excel spreadsheet showing the LP model formulation). 1a) What's the optimal advertising strategy, i.e., how many ads of each type should be run? DT* = DN* ET SN R A* = 1b) What's the total number of exposure quality units from this optimal ad strategy? 1) What's the total number of potential customers reached from this optimal ad strategy? Answer questions 2-5 using shadow prices and sensitivity analysis information found in the Sensitivity Report after solving the above LP formulation. You don't need to re-solve the model with modified data. 2) Would the optimal advertising strategy lies, the mix of ads, not the objective function value) of (1a) change if the exposure quality units from Daytime TV ads increased from 65 to 1007 Yes No How can you predict this based on information available in the (original) Sensitivity Report? 3) Would the optimal advertising strategy (ie, the mix of ads, not the objective function value) of (1a) change if the exposure quality units from Sunday Newspaper ads fell from 75 to 35? Yes No How can you predict this based on information available in the (original) Sensitivity Report? 4) By how many units would the optimal total exposure quality units of (1b) change if the client had required NDI to run a total of at least 11 TV ads instead of 107_ How can you predict this based on information available in the original) Sensitivity Report? 51 Rv how many units would the optimal total exposure quality units of (1b) change if the client had provided NDI with a budget of only $29,000 instead of $30,000? How can you predict this based on information available in the (original) Sensitivity Report