EXCEL SOLVER ASSESSMENT 2024/25

ECON10151: Computing for Social Scientists

November 18, 2024

1 General Instructions

This assessment tests your proficiency in using the Solver function in Microsoft Excel to solve a problem based on a specified scenario.

1. Prework:

The assessment information sheet is released on Monday,  18 November.  On page 3, you will find details of a scenario that must be set up in Excel before attempting the Blackboard test.

2. The Blackboard Test:

The Blackboard test will be available from 10am on Wednesday, 20 November, until 2pm on Fri- day, 22 November.  During this window, you will have a strict 45-minute time limit to complete 10 questions once you begin the test. Extra time has been added in line with DASS requirements for eligible students.

The test can be accessed in the "Assessment 1: Excel Solver" folder on the "Assessment" page of the ECON10151 Blackboard site.

(a)  Note that the 45-minute limit applies only to answering the test questions.  You will not have time to set up the Excel scenario from scratch during the test, so it is essential to complete all preparatory tasks in advance.

(b)  You must complete the test in one sitting. Saving, quitting, and returning later is not allowed.

(c)  The test includes fill-in-the-blank and multiple-choice questions.  Some questions will relatedi- rectly to the original scenario (described in Section 2 on page 3), while others may require you to adjust the original scenario and examine the effects of these changes.

(d) You must have access to Excel with Solver enabled, and the worksheet containing the orig- inal scenario should be set up and open before the test begins, as you may be asked to make small adjustments to the original scenario during the test.

Save all versions of your worksheet within a single Excel file for submission, with each sheet containing one scenario: the original scenario (which should remain unchanged) and additional sheets with modifications as directed during the test.

(e)  Questions will be randomly selected from a larger pool, so each student may receive a differ- ent set.  If you have completed the prework thoroughly and are prepared for minor follow-up calculations in Excel, you should be able to complete the test confidently within the time limit.

3. Submission of the Excel Workbook:

Your grade will be based on both your responses in the Blackboard test AND the submission of your Excel workbook, where you have completed all Solver calculations for each scenario.

(a)  Save your workbook in the following format: ‘[StudentID]workbook.xlsx ’. For example, if your student ID is 123456, name the file ‘123456workbook.xlsx ’.

(b)  A submission page will be available in the same folder as the Blackboard test for uploading your workbook via Turnitin.

(c) The submission window is open from 10am on Wednesday, 20 November, to 2:10 pm on Friday, 22 November (10 minutes after the test closes).

(d) Please note: failure to submit your Excel workbook will result in a maximum of 50% of the marks earned in the test.

For example, if you score 70% in the Blackboard test and submit the Excel workbook on time, your final grade for the Excel Solver Assessment will be 70%. However, if you fail to submit the Excel workbook, your final grade for the assessment will be calculated as 70% × 0.5 = 35%.

As this is an assessment, please refrain from posting any Solver assessment-related questions on Piazza during the assessment week.

2 The Solver Scenario

In this scenario, you are responsible for managing the weekly grocery purchases for a small community food pantry. Your aim is to buy a balanced selection of food items that satisfy both community demand and nutritional needs. Due to budget limitations, however, the pantry can only purchase a limited quantity of each product.

Based on previous data, you have estimates for the community priority score (in points),nutritional value (per kg),and cost (in £ per kg) for each item, shown in Table 1.

Table 1: Item Priority Scores, Nutritional Values, and Cost

Due to storage and budget constraints, each item has a specified maximum weekly purchase limit, as shown in Table 2.

Table 2: Maximum Purchase Limits for Each Item, in kg

Constraints:

To ensure that the pantry meets community needs while providing nutritionally balanced products, con- sider the following constraints:

1. Budget Constraint: Total spending must not exceed £350.

2. Nutritional Balance: At least 40% of the total items purchased should be high-nutrition items (those with a Nutritional Value of 0.7 or higher).

3. Priority Fulfilment: At least 40 kg of the total purchase should consist of high-priority items (those with a priority score of 15 or more points).

4. Item Purchase Limit: Each item has a maximum allowable quantity, as specified in Table 2.

5. Variety: Select at least five different types of items.

6. Integer Quantities: Ensure that only whole units of each item are purchased.

Objective:

Your objective is to maximise the overall health benefit score by choosing the optimal quantity for each item, balancing the community priority score and nutritional value. The overall health benefit score is calcu- lated as:

Total Health Benefit = i1 (w1 × Priority Scorei × Quantityi +w2 × Nutritional Valuei × Quantityi )

where:

• k is the total number of different food items available for purchase;

• Priority Scorei represents the community priority score (points) for itemi;

• Nutritional Valuei  is the nutritional value of itemi;

• Quantityi is the quantity purchased of itemi (in kg, decision variable);

• w1 and w2  are weights to balance the emphasis on priority versus nutrition.

For our analysis, set:

• w1 = 1.2: This gives a slight priority to community needs.

• w2 = 0.8: This adds value for nutritional balance, while still considering priority.

Your strategy is to maximise the overall health benefit score by determining the optimal weekly quantity for each item, based on the objective function provided and subject to the given constraints.  Use the GRG Nonlinear optimisation method in Solver, setting the precision to 0.000001. In the Solver Options for GRG Nonlinear, ensure that Convergence is set to 0.0001 and Derivatives are set to Forward. To ensure reliability, consider starting Solver with different initial values and verify consistency across the solutions obtained.