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 1*^{st}* 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

- How do I pass a pointer to a 2D array as a function parameter in C++?

- Describe the concept of inelasticity as it impacts on the development of a market-based or competition-driven price for a manufacturing-related semi-finished

- A 0.1253-g sample of copper metal is dissolved in 30 mL of concentrated HNO3 to form Cu2+ ions and then water is added to make the total volume of 750.0 mL.