关键词 > ENGSCI/STATS255

ENGSCI / STATS 255 FC Assignment 2

发布时间：2022-04-06

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

Departments of Engineering Science and Statistics

ENGSCI / STATS 255 FC

Assignment 2

1. Attempt ALL questions.

2. Assignments may be typed in a word processor of your choice, or handwritten neatly.

3. Set answers out in order of the questions. Do NOT jump between questions.

4. There is no need to copy the questions out in your submission.

5. You can generate your Excel output either by printing the required reports directly to paper (print in landscape mode where appropriate), or by using the Snipping Tool (Windows) or Command - Shift - 4 (Mac). You can display formulae in Excel by using Control - ’or Command - ’or by using Formulas → Formula Auditing → Show Formulas (see image below, left). Excel output showing formulae should include row and column headings; use Page Layout → Sheet Options → Headings → Print (see image below, right) to enable this in printouts.

Submission: Submit TWO files to the Canvas dropbox for Assignment 1:

• a single PDF file containing all your written answers and Excel screenshots.

• a single XLSX file containing your Excel worksheets with Solver models for Questions 1 and 2.

Assignments must be submitted before the due date and time. Handwritten assignments will need to be scanned. Prepare your assignments well in advance of the deadline in case of technical issues, as no extensions will be provided in this case.

If you need to re-submit, please submit both files each time.

Notes:

• Summarising, analysing and communicating information is an important part of Operations Research. For this reason you will be expected to write answers which clearly communicate your thoughts. The mark you receive will be based on your written English as well as your technical work.

• We encourage working together. Discussing assignments and methods of solution with other students or getting help in understanding from staff and students is acceptable and encouraged. You must write up your final assignment individually, in your own words.

• By submitting this assignment, you confirm that you understand the University’s policies on cheating, plagiarism and group work; that your submission is entirely your own work and you have not allowed access to any part of the assignment to any other person. See the appropriate sections in the Course Outline for more details.

• This assignment consists of FOUR questions, and is marked out of 60 marks. This assignment makes up 7.5% of the final assessment for this course.

Question 1: Supplying Noodle Packets (15 marks)

After the success of their marketing campaign for Korean-style noodles, the Statistics students have seen a significant increase in the demand for Korean-style noodles amongst Auckland students. As such, they are concerned with how they can efficiently send the crates of noodles from their import hubs to the required distribution centres.

They import crates of noodles at the three hubs A, B, and C, but need to ship them to their distribution centres in Mount Wellington, Onehunga, Epsom, Avondale and Takapuna so they can be sold or sent on to students. These centres need 150, 60, 40, 100, and 50 crates respectively, each month. The hubs import 150, 175 and 75 crates respectively, each month. The cost (in $) for transporting a single crate from a hub to a distribution centre is given in the table below.

	Mount Wellington	Onehunga	Epsom	Avondale	Takapuna
A	0.75	1.25	2.75	2.5	5
B	5.5	3.5	4.5	4	2
C	4.5	3.5	3.5	3	1.5

The Statistics students wish to minimise the cost of shipping the noodles from the import hubs to the distribution centres.

(a) Carefully formulate the above problem as a balanced transportation problem.

Use the standard Tabular Transportation format given in lectures with the ≤ supply constraints in the rows, and remember to include all costs, supplies, demands and any bounds that are needed. (b) Enter the problem from part (a) into Excel and solve it. Hand in three screenshots or printouts:

two of the resulting Excel spreadsheet: one showing the optimal values and the other showing the formulae; and one of the resulting sensitivity report. Follow instructions regarding output from the cover page. Be sure to also submit your Excel workbook to Canvas.

The following describe independent changes to the problem formulated in part (a). Use your formula- tion and / or Excel output to answer the following questions. You should NOT re-solve these problem

extensions in Excel.

(d) Due to another importer cancelling their order, there is now an additional 100 units available at hub

A. Detail the changes required to the Tabular Format in (a) to account for this change so that it is still a balanced transportation problem.

(e) How will the optimal solution and shipping cost change if the cost of transportation per crate from

hub B to Onehunga increases by $0.50 per unit?

(f) By how much will the shipping cost change if the demand at the Mount Wellington centre decreases

by 40 crates?

(g) The shipping company has introduced a new requirement that if any shipping is to occur between hub

B and Takapuna, at least 60 crates must be shipped. There is also a fixed cost of $10 for any shipping to occur on this route, on top of the per-crate cost. How will the LP formulation (i.e. mathematical formulation, not Excel) of the problem change as a result of this new requirement?

The LP uses the following syntax: The variables xij = the number of crate to be shipped from hub i to centre j , i ∈ {A, B, C}, j ∈ {M, O, E, A, T}.

Ensure that you define any new variables introduced. You are not required to give the full formulation – only show the changes required.

Question 2: Monitoring Soda Water Inventory (10 marks)

To increase revenue, and to pair with noodles in a combination meal, one of the distribution centres is

planning on also offering their new studying supplement called KFuel, which requires a certain amount of carbonated water. The carbonated water is used in the production of KFuel, but can also be sold directly to customers. The centre buys soda water each week at the market price. It needs enough soda water to

meet the requirements for the customers’ orders of KFuel it has received for that week. It can also sell upto

an additional 25 litres of soda water to customers in a week. This would sell at $4.25 per litre in the first two weeks, and $4.50 per litre in the last two weeks. The distribution centre has refrigerated containers that can store up to 200 litres at a cost of $0.15 per litre per week. However, this cost increases to $0.20 per litre per week at the end of week 1 (start of week 2), due to the deterioration of one of the fridges.

The table below gives the manager’s estimates of the market price and the amount of soda water required for KFuel production each week.

	Market Price	Litres Required for KFuel Orders
Week 1	$1.20	55
Week 2	$1.50	45
Week 3	$1.00	50
Week 4	$0.92	75

The centre currently has 100 litres of soda water in stock and the manager would like to have 60 litres at the end of week 4. The manager wants to maximise the profit from buying and selling soda water while meeting the requirements for the KFuel orders.

(a) Draw a diagram showing a transshipment formulation of this problem. Your diagram should follow the standard transshipment diagram representation and not include any equations.

You may draw this diagram by hand or on a computer. However, the diagram from the Transshipment Solver on Canvas is NOT acceptable and will score ZERO.

(b) Enter this problem into Excel and solve it. Hand in a screenshot or printout of the resulting Excel spreadsheet, showing the data entry table and the optimal values.

This question part is worth 3 marks.

• To score full marks, you must solve this with the SUMIF() formulation as discussed in Lecture 11B. You must submit screenshots of both the solution and the formula view.

You must also submit your Excel Spreadsheet for Question 2 if you use the SUMIF() formulation.

• Alternatively, for a maximum of 1.5/3 marks, you may use the Transshipment Solver (or the

simplified version for Mac) given on Canvas.

You do not need to submit an Excel Spreadsheet for Question 2 if you use the provided Trans-

shipment Solver.

• If you choose to do the SUMIF() formulation, you should still use Super Supply and a Super Demand nodes for this question. The Tegel Chicken example is available on Canvas to show you how you might set this problem up – you cannot view / select any of the cells in this; this is intentional.

(c) Due to supplier issues, the soda water delivered to the centre in Weeks 3 and 4 must go through a different company. This company can only supply a total of 80 litres of carbonated water in these two weeks. Show how your diagrammatic formulation in part (a) will change to account for this scenario. You do not need to solve this problem in Excel.

Question 3: LP Odds and Ends – Canvas Quiz (10 marks)

Revision is best done early and often, so we will start now... This Canvas Quiz covers a number of concepts that do not easily make for a full assignment question.

This quiz covers concepts from the whole LP topic, and you will receive a random set of ten questions each time. You have three attempts at the quiz, with each attempt timed at 90 minutes. We will take the best mark from your three attempts.

The quiz opens on Thursday 24 March at 12 midday, and is due at 11:30pm on Wednesday 6 April,

30 minutes before the written parts of the assignment is due. You will not be able to attempt the quiz after the due time and all ongoing attempts will be automatically submitted.

You will be able to see your score immediately after submitting the quiz, but the answers will not be shown until after the due time.

Please do not discuss quiz questions on Ed Discussion or any other forum.

We reserve the right to award NEGATIVE marks for quiz questions publically posted to Ed Discussion or any other forum.

Question 4: Starting a Student Club (25 marks)

The Statistics and Engineering Science Students’Association (SESSA) is a brand-new student club, whose

executive members are planning an “Intro Night” to attempt to gain new members. However, due to the

uncertainty in turnout for the event, SESSA is analysing the effect of ordering different quantities of pizza (the only food ever served at student club events). Three decisions are being considered:

D1: order no pizza

D2: order a normal quantity of pizza

D3: order a huge quantity of pizza

It is estimated that a ordering a normal quantity of pizza will increase the cost of the event by $90, while ordering a huge quantity of pizza will increase the cost by $170.

The turnout for the intro night is uncertain, and could depend on factors beyond SESSA’s control, such as global oil prices and the current assignment workload of Statistics and Engineering Science students. SESSA’s operations research team has modelled three scenarios:

S1: low student turnout (20 students)

S2: normal student turnout (70 students)

S3: high student turnout (150 students)

SESSA expects a portion of the students in attendance to sign up for the club; SESSA will be paid $10 by their sponsors for each new member who signs up.

The proportion of students who sign up is based on the turnout, as well as the amount of pizza available. The table below indicates the proportion of attendees who will sign up, for each combination of pizza quantity and turnout:

Pizza Quantity Provided

none normal huge

low turnout 0.3 0.5 0.6

normal turnout 0.2 0.4 0.5

high turnout 0.1 0.3 0.4

(a) Construct a payoff matrix for this situation, with the rows being the decisions and the columns being the turnout scenarios. Note that the payoff is the sponsorship money earned less the cost of the pizza provided.

(b) What is the best decision if SESSA is optimistic? Explain why.

(d) What is the best decision if SESSA wishes to minimise their maximum regret? Show all working.

Suppose that, based on preliminary analysis by SESSA, it is estimated that there is a 0.2 probability of a low turnout, a 0.5 probability of a normal turnout and a 0.3 probability of a high turnout.

(e) Draw and label a decision tree for this situation, and compute the best decision, assuming that SESSA is risk-neutral.

(f) How much (on averge) would it be worth to SESSA to know the turnout in advance of making the

initial decision about how much pizza to order? Show all working. What is this concept called?

There is a chance that‘Operations Research Scientist’ (ORS) will be announced as the new‘Coolest Job of the 21st Century’ (deservedly taking over from Data Scientist). The new‘Coolest Job’will be announced the day before the SESSA Intro Night. If ORS wins, this will result in a huge boost in popularity for STATS/ENGSCI 255 and SESSA might expect a greater turnout to their Intro Night.

An industry insider advises SESSA that there’s a probability of 0.2 that ORS will win, and a probability of 0.8 that something else will claim the title; given this, the (conditional) probability distribution for the three turnout scenarios is given in the table below.

New Coolest Job

ORS something else

low turnout normal turnout high turnout

0 ?

0.4 ?

0.6 ?

(g) Given that the chance of this announcement is already factored into the overall probability, given

earlier, what is the probability of each turnout if something else (not ORS) is the new Coolest Job? These probabilities are missing in the table above.

(h) Assuming risk-neutrality, how much would SESSA be willing to pay to know what the new ‘Coolest

Job’ is, prior to making a decision about their pizza order?

Suppose that SESSA risk-averse, with utility function: U (π) = √3 π + 30, where π is the payoff in $.

(i) Compute SESSA’s expected utility for each pizza decision. Which decision should SESSA choose, and why?

(j) What is the CEV associated with the decision determined in (i)? Show your working.