View the step-by-step solution to:

https://www.chegg.

https://www.chegg.com/homework-help/questions-and-answers/assignment-ll-enter-media-selection-problem-excel-worksheet-use-excel-s-solver-add-optimal-q28073136

Please help


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 iLearn.

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.

Advertising

Source

Potential Customers

Reached per Ad

Cost per

Ad ($)

Maximum Times

Available per Month

Exposure

Quality Units

Daytime TV

  (1-min. commercial)

1,000

1,500

15

65

Evening TV

  (30-sec. commercial)

2,000

3,000

10

90

Daily Newspaper

  (full pg ad)

1,500

400

25

40

Sunday Newspaper

  (1/2 pg color)

2,500

1,000

4

60

Radio

  (30 second spot)

300

100

30

20

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-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 [5-9].

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       65DT    +          90ET    +    40DN    +        60SN   +  20RA =          Z

      subject to:  1,500DT    +     3,000ET    +    400DN   +   1,000SN   + 100RA  £ 30,000           [1]

                                      DT    +               ET                                                                              ³        10           [2]

                               1,500DT    +     3,000ET                                                                             £ 18,000          [3]

                               1,000DT    +     2,000ET    +   1,500DN  +   2,500SN   + 300RA  ³ 50,000          [4]

                                      DT                                                                                                               £        15           [5]

                                                                       ET                                                                                £        10            [6]

                                                                                                   DN                                                  £        25              [7]

                                                                                                                               SN                        £          4              [8]

                                                                                                                                                    RA £        30              [9]

                                      DT ³ 0,           ET ³ 0,              DN ³ 0,          SN ³ 0,        RA ³          0   [10-14]

Notes:

I strongly recommend that you first practice by solving the small ABC LP problem discussed in class. NDI's LP has more 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 + 1ET + 0DN + 0SN + 0RA ³ 10. In Excel, just enter the coefficients & the RHS: 1 1 0 0 0     10

In the Solver Parameters dialog: (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 (£ vs. ³) in the constraints.

                               

Please answer the questions below in the spaces provided.

Attach a printout of the 1st 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* =                                                 RA* =                                

1b) What's the total number of exposure quality units from this optimal ad strategy?                                     

1c) 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 (i.e., 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 85?         Yes         No  

How could you predict this based on information available in the (original) Sensitivity Report?

3) Would the optimal advertising strategy (i.e., the mix of ads, not the objective function value) of (1a) change if the exposure quality units from Sunday Newspaper ads fell from 60 to 35?       Yes         No

How could 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 10?                                                                                          

How could you predict this based on information available in the (original) Sensitivity Report?

5) By how many units would the optimal total exposure quality units of (1b) change if the client had provided NDI with a budget of $32,000 instead of $30,000?                                                                                            

How could you predict this based on information available in the (original) Sensitivity Report?

Please answer the bottom part! Thank you!

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question