联系我们： 手动添加方式: 微信>添加朋友>企业微信联系人>13262280223 或者 QQ: 1483266981

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 a long, 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
EFIM20005: Management Science – Case Coursework 2023/24
2
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.
Advertising Medium Unit Cost (￡) Unit Impact Rating
Half-page, Daily paper 800 55
Full-page, Daily paper 1400 75
Half-page, Sunday paper 1200 65
Full-page, Sunday paper 1800 80
Daytime TV spot 2500 85
Evening TV spot 3500 100
Highway Billboard Rent
for 4-Weeks
750 35
15-second radio spot 150 45
30-second radio spot 300 55
Half-page, magazine 500 50
Full-page, magazine 900 60
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 a way
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 ad on 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 carry out the extended analysis. Specifically,
3
you should also make suggestions on how this project could be properly expanded to
potentially accommodate other equally important needs.
Submission requirements
Submission is required by Thursday 9 May by 1pm.
The report should be uploaded to Turnitin in Microsoft Word. You should use a 12-
point font size and line-spacing of 1.5 with blank line between paragraphs. Margins
should be set to “Normal”. 1500 words is suggested as a guideline for length. It may
be longer or shorter than this, but if shorter, it will be assessed as to whether it has
covered the salient issues in sufficient depth. If longer, it will be assessed from the
perspective of whether unnecessary material has been included. So, word length is an
element of academic judgement in marking, not a penalty for failing to meet a word
count.
Use Harvard System for referencing.
Report structure you should follow
1. Statement of Findings:
Present the analysis results for Lisa, incorporating the recommended solution.
Provide insightful commentary on the disparities between integer and relaxed LP
solutions.
Deliver a detailed interpretation of the sensitivity report.
2. Assumptions:
Engage in a comprehensive discussion of case and model assumptions, assessing their
realism.
Ideally, support assumptions with relevant references.
3. Formulation:
Clearly articulate the mathematical formulation of the problem.
Offer an interpretation of variables and constraints.
Illuminate the rationale guiding the formulation strategy for the mathematical
programming model.
4. Extensions:
Showcase any additional work undertaken, justifying its utility.
Discuss potential, sensible extensions for the project.
Specify the requirements for extending the project, fostering clarity and direction.
Note:
Screen picture of Excel Spreadsheet in Number View with explanatory comment.
Screen picture Excel Spreadsheet in Formula View with explanatory comment.
Screen picture of Solver Form with explanatory comment.
4