Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit


EFIM20005: Management Science – Coursework 2025/26


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.

Case: Driving Sign-ups for ChargeSmart EV Charging

“How should I allocate this budget?” thought Maya Patel, Marketing Director at ChargeSmart, a regional provider of electric-vehicle (EV) charging services. ChargeSmartis preparing to launch a membership scheme that offers reduced rates and guaranteed access at peak times across Bristol and the surrounding area. The local authority will shortly vote on a bylaw that would allow ChargeSmart to reserve bays for members at certain council car parks—an important step for the scheme’s success. To secure astrong base of early adopters and build public support ahead of the vote, ChargeSmart must run an intensive four-week awareness and sign-up campaign.

EV adoption is growing, but many drivers remain unsure about charging availability and pricing. ChargeSmart’s objective is to maximize the campaign’s overall impact (reach and persuasion) using a fixed promotional budget while ensuring the message is seen across multiple channels. A national sustainability foundation has just confirmed a £300,000 grant to ChargeSmart for traditional media only (social and search activity is already funded). Maya plans to spend this grant on a mix of newspapers, TV, radio, billboards, and magazines during the four weeks immediately preceding the council vote.

A media consultant has provided the following data on unit cost and relative impact of each placement (impact is scaled so an evening TV spot = 100):

Advertising Medium
Unit Cost (£)
Unit Impact Rating
Half-page, Daily paper
800
55
Full-page, Daily paper
1,400
75
Half-page, Sunday paper
1,200
65
Full-page, Sunday paper
1,800
80
Daytime TV spot
2,500
85
Evening TV spot
3,500
100
Highway Billboard (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

The evening TV spot is judged most persuasive for this audience and is assigned an impact of 100. Other formats are rated relative to that benchmark (e.g., a half-page magazine ad has impact 50). Maya wants to allocate the £300,000 across these options to maximize total impact, while ensuring broad coverage so that drivers who prefer different media are still reached.

1. Press (daily & Sunday). The two most widely read local papers are the Bristol Chronicle and the Avon Times. Across the four weeks, Maya wants at least three half-page ads per week in each paper’s daily (Mon–Sat) edition. She also wants one full-page daily ad in each paper in the week before the vote (and is open to more if beneficial). She also plans one full-page Sunday ad in each paper on the Sunday before the vote. Never run a full-page and half-page ad in the same paper on the same day. Hence, the maximum number of daily press ads (full + half) is 48 (4 weeks × 6 days × 2 papers). The maximum number of Sunday press ads (full + half) is 8 (4 Sundays × 2 papers).

2. Television. Maya wants at least one and at most three daytime TV ads each day for four weeks. She also wants at least one and at most two evening TV ads each night.

3. Billboards. There are 10 available roadside billboard locations for the four-week rental. Maya wants at least one board in each of City Centre, North Ring, and Harbourside (three key commuter flows). 

4. Radio. Visual media help show off ChargeSmart’s app and charger maps, but radio is inexpensive and adds incremental reach. Maya wants at least two 15- second and at least two 30-second radio spots per day, but to control clutter she will cap radio at five 15-second and five 30-second spots per day.

5. Magazines. There are three weekly magazines (West Country Living, Bristol Today, Auto & Tech Weekly). Maya wants at least one full-page ad in each magazine at some point in the four-week period. She will never run a full and half-page in the same magazine within the same week. Therefore, the total number of magazine ads chosen (full + half) must not exceed 12 (4 weeks × 3 magazines × 1 placement each).

While Maya has defined sensible minimums/maximums, she is unsure how much budget those constraints will consume and, if affordable, how best to deploy any remaining funds. Her question: “What is the optimal spend plan?”

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 Maya Patel advising he how he should spend his 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 Alex’ 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 Maya followed by more general suggestions, including a statement of requirements to carry out the extended analysis. Specifically, 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 23 April 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 Alex, incorporating the recommended solution.
  • 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 Excel Spreadsheet in Formula View with explanatory comment.
  • Screen picture of Solver Form with explanatory comment.