Administration

This is the third of three assessments for this unit. It accounts for 60% of total unit marks. Please submit your work presented in a single Word document via Turnitin. The assessment is to be completed individually and independently.

Hypothetical Case: Protecting the Red Pandas

“So how am I going to spend this money?” thought Lisa Rodriguez as she sat surrounded by pictures and posters of red pandas in her office. An ardent conservationist, Lisa is the president of the “Friends of the Red  Pandas” – a non-profit organization working to pass legislation to protect red pandas.

Red pandas are small, arboreal mammals with a reddish-brown coat and along, bushy tail. These charming creatures grow to an average length of 2 to 2.5 feet and weigh between 7 to 14 pounds. Red pandas are found in the mountainous regions of Eastern Himalayas, including parts of Nepal, India, Bhutan, and China. Their primary threats include habitat loss due to deforestation and illegal hunting.

Lisa's organization has been supporting a bill before the local legislature to preserve the natural habitats of red pandas and implement stricter penalties for illegal hunting. This bill is scheduled for a crucial vote in the  legislature. Lisa recently  received a phone call from a National Environmental Protection organization indicating that they are going to donate £300,000 to Friends of the Red Pandas to help raise public awareness about the plight of red pandas and encourage votes in favour of the bill. Lisa already has a Social Media campaign planned and funded, so she intends to use this money to purchase various types of traditional advertising media to “get the message out” during the four weeks immediately preceding the vote.

Lisa is considering several different advertising alternatives: newspapers, TV, radio, billboards, and magazines. A marketing consultant provided Lisa with the following data on the costs and effectiveness of the various types of media being considered.

According to the marketing consultant, the most effective type of advertising for this type of problem would be short TV ads during the evening prime-time hours. Thus, this type of advertising was given a “unit impact rating” of 100. The other types of advertising were then given assigned unit impact ratings that  reflect their expected effectiveness relative to an evening TV ad. For instance, a half-page magazine ad is expected to have half the effectiveness of an evening TV ad and is therefore given an impact rating of 50.

Lisa would like to allocate the £300,000 to these different advertising alternatives in away that will maximize the impact achieved. However, she realizes it is important to spread her message via several different advertising channels as not everyone reads newspapers, watches TV, or listens to the radio.

The two most widely read newspapers in the region are the Mountain Gazette and the Valley Tribune. During the four weeks prior to the vote, Lisa would like to have half-page ads in the daily (Monday – Saturday) versions of each of these papers at least three times per week. She also wants to have one full-page ad in the daily version of each paper the week before the vote, and she is willing to run more full-page ads if this would be helpful. She also wants to run full-page ads in the Sunday editions of each paper the Sunday before the vote. Lisa never wants to run a full-page and half-page ad in a paper on the same day. So the maximum number of full and half-page ads that can be run in the daily papers should be 48 (i.e. 4 weeks x 6 days per week x 2 papers = 48). Similarly, the maximum number of full- and half--page ads that can be run in the Sunday papers is eight.

Lisa would like to have at least one and no more than three daytime TV ads every day during the four-week period. She also wants to have at least one adon TV every night but no more than two per night.

There are 10 billboards locations throughout the region that are available for use during the four weeks before the vote. Lisa definitely wants to have at least one billboard in each of the cities of Mountainville, Valleyburg, and Highland Park.

Lisa believes that the ability to show pictures of the cute, fluffy, lovable red pandas in the print media offers a distinct advantage over radio ads. However, the radio ads are relatively inexpensive and may reach some people that the other ads will not reach. Thus, Lisa would like to have at least two 15-second and at least two 30-second ads on radio  each  day. However, she would like to limit the number of radio ads to five 15-second ads and five 30- second ads per day.

There are three different weekly magazines in which Lisa can run ads. Lisa wants to run full-page ads in each of the magazines at some point during the four-week period. However, she never wants to run full- and half-page ads in the same magazine in a given week. Thus, the total number of full- and half-page magazine ads selected should not exceed 12 (i.e., 4 weeks x 3 magazines x 1 ad per magazine per week = 12 ads).

While Lisa has some ideas about the minimum and maximum number of ads to run in the various types of media, she’s not sure how much money this will take. And if she can afford to meet all the minimums, she’s really confused about the best way of spending the remaining funds. So again, Lisa asks herself, “How am I going to spend this money?”

Task

1. Formulate and solve this problem as a Linear Programme in Excel.

2. Solve the integer form and the LP relaxation. Produce the sensitivity report for the LP relaxation.

3.  Using the above output, analyse the above case and write a report for Lisa Rodriguez advising her how she should spend her money. The report should include:

•   Statement of the recommended solution.

•    Detailed Discussion of the interpretation of the sensitivity report.

•    Discussion of assumptions made in applying Linear Programming to  Lisa’ problem.

•    Formulation of the Linear Programming problem. Additionally, you should provide the rationale behind the strategy followed to formulating the mathematical programming model.

•   Screenshots of the Excel spreadsheet model.

•    Extended analysis that would be useful to Lisa followed by more general suggestions, including a statement of requirements to carryout the extended analysis. Specifically, you should also make suggestions on how this project could be properly expanded to potentially accommodate other equally important needs.