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 Canvas for Assignment 2:

• a single PDF file containing all your written answers and Excel screenshots to the Assignment 2 dropbox.

• a single XLSX file containing all your Excel worksheets with Solver models to the separate As- signment 2 Excel file dropbox.

We primarily mark your PDF submission, but you will receive ZERO marks for Excel components if you do not submit your Excel file.

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.

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:  Mixing Muesli Again (15 marks)

Max’s muesli has proved to be popular with their friends and they are scaling up their production to sell. Max wants to produce four flavours of muesli each with slightly different ingredients: nut, seed, berry, and natural. Max has also managed to obtain three benchtop mixers of various types (1, 2, and 3). Because of

the different ingredients in each type of muesli they each take a different amount of time to mix in each of the mixers. The table below give the time (in minutes) to mix one batch of each type in each mixer:

Max wants to know which types of muesli should be mixed in each mixer to minimise the total time spent mixing by the mixers to make one batch of each type.  As there are only three mixers Max will mix one batch by hand which does not contribute to the mixing time of the mixers.

(a) Formulate the above problem as a balanced assignment problem using summation (Σ) notation.

Define any variables and sets that you use.

(b) Enter the problem from part (a) into Excel in the format prescribed in Sections 9-10 of the Section A

Notes 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.

(c) Give a brief (1-2 sentences) non-technical interpretation of Excel’s answer to Max.

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) Suppose Max would like to make an additional batch of the berry type to keep for themself. Explain the changes required to the formulation in (a) to account for this change and ensure that it is still a balanced assignment problem. You may assume that Max will mix the two batches not mixed in mixers by hand.

(e) By how much will the total time spent mixing by the mixers change if the time it takes to mix the

seed muesli on mixer 3 increases by 0.3 minutes? 

Question 2:  Shipping Logs   (10 marks)

A logging company has harvested 500 tons of logs in Coromandel (Co), 600 in Gisborne (Gi), and 300 in Turangi (Tu).  The logs need to be transported from these locations to either Kawerau (Ka) or Te Kuiti (TK) to be processed. Finally they are sent to either Tauranga (Ta), Auckland (Au), or Whang¯arei (Wh) to be sold.

The logs can be sold for $1200 per ton in Tauranga, $1400 in Auckland and $1800 in Whang¯arei; up to 600 tons of logs can be sold in Tauranga, 800 in Auckland, and 500 in Whang¯arei.

The costs for shipping one ton of logs between the locations are given in the tables below:

Origin    Destination 

Ka         TK

Co Gi Tu

200

155

125

165

286

100

The logging company wants to know how many logs to ship from each harvest location to processing location, and from the processing locations to the sale locations to maximise the amount that they make from selling the logs.

(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, submitting 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.

• You may use the Transshipment Solver (or the simplified version for Mac) given on Canvas – you will earn a maximum of 1.5 marks for this part if you do this.

• You may solve this with the SUMIF() formulation as discussed in Lecture 11B – you will qualify for all 3 marks for this part. You must submit both the solution and the formula view.

• If you choose to do the SUMIF() formulation, you should still use a Super Supply and a Super Demand node for this question.  A spreadsheet showing 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) Suppose the plant at Kawerau can only process a maximum of 500 tons of logs.  Show how your network diagram would be modified to take this into account.  You do not need to solve this problem in Excel.

(d) To maintain their relationship with customers in Whangarei the logging company would like to sell at least 100 tons of logs in Whangarei. Describe how your network diagram would be modified to take this into account. 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 60 minutes. We will take the best mark from your three attempts.

The quiz opens on Thursday 23 March at 6:30pm, and is due at 11:30pm on Wednesday 5 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 Piazza or any other forum.

We reserve the right to award NEGATIVE marks for quiz questions posted to Piazza.

Question 4:  Cooking Channel Production   (25 marks)

The Cooking Channel is planning on launching a new show and has decided on a format, but is not sure how much budget they should give it. They are trying to decide between a high budget production, which would cost $7.1M, a medium budget production costing $2.8M, and a low budget production of $1.2M.

As part of all of the productions they will run an advertisement campaign which can have three levels of success: it either goes viral, it has an normal result, or it flops.  The amount of money (in millions of dollars $M) that the show is expected to make given the budget and advertisement outcome is given in the table below:

Production Budget

(a) Construct a payoff matrix for this decision, with the rows being the budget levels and the columns the success of the advertisement campaign. The payoff is the amount the show makes less the cost of production.

(b) What is the best decision if The Cooking Channel is optimistic? Explain why.

(c) What is the best decision if The Cooking Channel is pessimistic? Explain why.

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

Suppose that, based on previous experience with similar shows, it is estimated that there is a 0.15 proba- bility of a viral outcome, a 0.65 probability of a normal outcome and a 0.2 probability of a flop.

(e) Draw and label a decision tree for this situation, and compute the best decision, assuming that The Cooking Channel is risk-neutral. (All values should assume units of millions of dollars $M.)

(f) How much would it be worth to The Cooking Channel to know the outcome of the advertisement

campaign in advance of making the initial decision about the show’s production budget?  Show all working. What is this concept called?