Excel Solver Assessment 2023/24
ECON10151: Computing for Social Scientists
Judith Guo & Zaheen Umar
November 20, 2023
1 General Instructions
Below you will find some information relating to a scenario and you will be asked to use the Solver function
within Microsoft Excel in order to provide a solution.
This information sheet is released on Monday, November 20.
At 10am on Wednesday, November 22, a Blackboard test will open inside the Assessments folder of the
ECON10151 Blackboard site. This test will remain open until 5pm on Friday, November 24. During the test,
you will be asked to answer 10 questions within a maximum time of 45 minutes. DASS extra time has been
implemented into this test for students entitled to it.
These questions will involve you entering values, answering true and false questions and selecting from
multiple-choice answers. Some of the questions will relate to the answer for the scenario described below
(original scenario), while others will ask you to make some changes to the original scenario and consider
the impact of these changes. For this reason, it is expected that you will have access to Excel with Solver
functionality during your test attempt. You need to save two copies of your worksheet in a single Excel
file: one for the original scenario, which you will not edit, and one which you will edit as required.
1
2 THE SOLVER SCENARIO
2 The Solver Scenario
You are the director of a film festival. Several production houses have submitted films to be screened at your
festival. Your audience research has revealed the following information about these films:
Film Name Viewers(Millions) Popularity Score
Ocean’s Abyss 3.5 0.5
Galactic Voyage 7 0.8
Mystical Prairie 2.8 0.5
Rift Valleys 1.5 0.3
Chronicle of Lora 2.1 0.6
Lost in the Sky 3 0.4
Emerald Curse 1 0.7
Desert Mirage 0.7 0.2
Frozen Echoes 2.5 0.65
Whispering Shadows 1.2 0.55
Given the festival’s schedule, you can only screen 20 film slots. Some production houses are willing to
offer multiple screenings of their films. The maximum number of screenings each film can have is provided
below:
Film Name Maximum Screenings
Ocean’s Abyss 4
Galactic Voyage 3
Mystical Prairie 2
Rift Valleys 3
Chronicle of Lora 4
Lost in the Sky 1
Emerald Curse 5
Desert Mirage 6
Frozen Echoes 3
Whispering Shadows 2
Your goal is to maximise the sum of the square root of viewership, adjusted by the natural logarithm of
the screenings, and then further weighted by the popularity score:
n
∑
i=1
√
viewersi× ln(1+ screeningsi)×Popularityi
The logic here is that the square root function represents diminishing returns from increasing viewership,
while the logarithm of screenings ensures the function does not grow too rapidly with multiple screenings.
The "+1" in the logarithm ensures it remains positive and non-infinite, and n is your sample size.
2
3 SUBMISSION OF THE EXCEL WORKBOOK
Your strategy is to maximise festival awareness and word-of-mouth by optimising the screenings based on
the above objective function. Ensure that only whole film slots are allocated, and each film must be screened
at least once per the agreement with the production houses. Use the GRG Nonlinear optimisation method
within Solver, setting the precision for the routine to 0.000001. As always, consider initiating the solver
routine from different starting values to ensure solution consistency.
3 Submission of the Excel Workbook
Your grade will be based on your responses to the test and the submission of your Excel spreadsheet where
you have done your working using Solver (your solutions for all scenarios faced).
You will need to save your spreadsheet as ‘[StudentID]workbook.xlsx’ before submitting. For example,
if your student number is 123456, you should save the file as ‘123456workbook.xlsx’. A Turnitin submission
page will be available inside the Assessment folder on Blackboard where you can submit your spreadsheet.
When submitting your spreadsheet, please enter your student ID in the ’Submission title’ box, followed by
the title for the work, e.g. "12345678 - Excel Solver‘". Ensure that you confirm the submission when you
reach the preview page. If you experience any issues when submitting, please send a back-up copy of the
submission document via email to soss.assessment@manchester.ac.uk. The submission window is from
10amWednesday, November 22 till 5:30pm Friday, November 24 (30mins after the quiz closes). Failing
to submit the Excel spreadsheet will only get you 50% of the marks you have achieved in your test.
3