MSIN0042 Decision Science

Online Examination Paper 2023/24

Copyright Note to students: Copyright of this assessment brief is with UCL and the module leader(s).  If this brief draws upon work by third parties (e.g. Case Study publishers) such third parties also hold copyright. It must not be copied, reproduced, transferred, distributed, leased, licensed or shared with any other individual(s) and/or organisations, including web-based organisations, without permission of the copyright holder(s) at any point in time.

Academic Misconduct: Academic Misconduct is defined as any action or attempted action that may result in a student obtaining an unfair academic advantage. Academic misconduct includes plagiarism, obtaining help from/sharing work with others be they individuals and/or organisations or any other form. of cheating. Definitions and penalties for Academic Misconduct are outlined in the Academic Manual.

•     It is forbidden to:

o  Communicate or collaborate with other students or any other third parties in relation to this assessment.

o  Discuss or share assessment content with other students or third parties.

o  Copy or attempt to copy the work of another student(s).

• Such actions will be referred to the Academic Misconduct Panel, the penalties of which may include exclusion from UCL.

Referencing: In an exam no external research would normally take place and you would demonstrate your learning from the module and communicate your learning in your own words and, where appropriate, through calculations. This applies to this online assessment. If you were to make direct use of somebody else’s work, either by memorising it or copying it word for word, you must reference and provide full citation for ALL sources used.  This includes any direct quotes and paraphrased text.  If in doubt, reference it. Further guidance on referencing is to be found here: https://library-guides.ucl.ac.uk/referencing-plagiarism Failure to cite references correctly may result in your work being referred to the Academic Misconduct Panel.

SECTION A

This section consists of TWO (2) questions and is worth THIRTY (30) marks.

The VictorySphere Company  produces footballs. VictorySphere  must  decide  how many footballs to produce each month. The company has decided to use a 3-month planning horizon. The forecasted monthly demands for the next 3 months are 15,000, 30,000, and 28,000. VictorySphere wants to meet these demands on time, knowing that it currently has 10,000 footballs in inventory and that it can use a given month’s production and leftover inventory from previous months to help meet the demand for that month. (For simplicity, we assume that production occurs during the month, and demand  occurs  at  the  end  of  the  month.)  During  each  month  there  is  enough production capacity to produce up to 25,000 footballs. The forecasted production costs per football for the next 3 months are £12.40, £12.80, and £13.00, respectively. The holding cost per football held in inventory at the end of any month is figured at 5% of the production cost for that month. (This cost includes the cost of storage and also the cost of money tied up in inventory.) The selling price for footballs is not considered relevant to the production decision because VictorySphere will satisfy all customer demand  exactly  when  it  occurs—at  whatever  the  selling  price  is.  Therefore, VictorySphere wants to determine the production schedule that minimizes the total production and holding costs.

Let Q1, Q2, Q3 represent the quantity of footballs produced in month 1, 2, 3.  The unit is in thousands.

Let I1, I2, I3 denote the corresponding end-of-month inventories (after demand has been satisfied), also measured in thousands.

Question A.1 [15 marks]

Suppose that you are the business analyst for VictorySphere and want to determine the production plan so the total cost is minimized. Clearly write down the objective function and constraints based on variables defined above.

Question A.2 [15 marks]

Suppose that you have completed the model and find the optimal solution using Excel solver. Below, you will see the excel file with the solution, the excel file with the formulas and the sensitivity analysis report.

Constraints

(a) Reading from the Excel solution, what are the optimal production quantities for  each month? What are the ending inventories for each month? What is the optimal total cost.            [7 marks]

(b) There are 3 lines in sensitivity analysis. List the constraint the 2nd line refers to.   You cannot get credit if you list more than one constraint. Is that constraint binding?        [4 marks]

(c) Suppose the inventory at the end of Month 3 is required to be 10,000 footballs for future use. Use the sensitivity analysis table to find the new optimal total cost.          [4 marks]

SECTION B

This section consists of THREE (3) questions and is worth THIRTY (30) marks.

The Concrete for Earth (C4E) is a company that has developed an environmentally- friendly,  and  structurally  strong  concrete.  Its  concrete  absorbs  carbon  in  the atmosphere as it dries initially, and also over time as it reacts with the air. As a result, the use of this concrete brings net carbon neutrality to many construction projects (or may even make them carbon negative, generating carbon credits).

The  UK’s  Department for Transport  is  leading the effort to decarbonize the  UK’s infrastructure. In its recent call to build a series of new aqueducts to transport fresh water around the greater Manchester region, it has announced that it will exclusively consider environmentally sustainable concrete and started the competitive bidding process for concrete suppliers. The bid that presents the lowest price will win.

C4E is considering whether to make a bid on the Manchester aqueduct project. It will cost the company £300,000 to prepare a bid, and if it wins the bid, it will cost the company an estimated £11m to produce the amount of concrete necessary for the project. However, there is uncertainty about each of these estimates. C4E assesses that the cost to complete the project can be as low as £9m but can be as high as £15m. The cost of bid can be as low as £250k but can also go as high as £450k.

More importantly, C4E is aware of 2 competitors who have developed a similar carbon absorbing concrete product available for mass production. It knows that these two companies will compete in the bid.   Since  C4E  has  the  superior technology  and manufacturing process, it believes that its bid will likely be lower than its competitors’ . In particular, it believes that the competitors’ bid will be, independently of the others, most likely 30% above C4E’s bid, although it is possible that they may also bid as low as 10% below C4E’s bid and up to 80% above C4E’s bid.

Question B.1 [10 marks]

(a) State the key uncertainties and express them as you would using SimVoi with appropriate parameter values.         [6 marks]

(b) Let Y denote the bid amount by C4E. Using the expression of the uncertainties, state the condition for C4E winning the bid.   [4 marks]

Question B.2 [10 marks]

Suppose you want to perform. simulation analysis using Simvoi.

(a) In  the  output  cell  in  Simvoi,  state  the  expression  for  C4E’s  profit  using  the uncertainty expressions above. (Hint: you can use the IF function.)        [3 marks]

(b) Describe the steps you would take to get a sense of the probability of winning the bid using Simvoi. [3 marks]

(c) Instead of having 2 identical and independent competitors, suppose that you had 5  identical  and   independent  competitors.  How  would   probability  of  winning change? Explain.            [4 marks]

Question B.3 [10 marks]

The company C4E has come to you to determine what would be the appropriate bid amount to maximize its expected profit. It is considering either a bid value of Y1=£12m or Y2=£11m. You have performed simulation analysis using the bid values using SimVoi, which produced the following outputs.

(a) Which curve corresponds to the bid Y1 = £12m and Y2=£11m? Explain.            [4 mark]

(b) Between Y1 and Y2, which would you recommend as the bid amount (in other words, which bid has higher expected profit)? Under your recommendation, how likely is it for C4E to make profit greater than £1m? State the probability.           [3 mark]

(c) C4E has revealed that it is considering a bid amount of Y3=£13m. Based on all of the above questions, would you recommend Y3? If so, why? If not, why not?           [3 marks]

SECTION C

This section consists of THREE (3) questions and is worth FORTY (40) marks.

You need to source a product and want to buy it at a cheaper price. There are two markets that offer this product. The price in Market 1 has a 50% chance of being £40 and a 50% chance of being £60. Independently, the price in Market 2 has a 50% chance of being £30 and a 50% chance of being £70.

Question C.1 [10 marks]

You can only find out the price after deciding which market to buy from. Draw the decision tree and determine the optimal decision.

Question C.2 [10 marks]

If you can find the prices of both markets before making a purchase, what is the expected price you will pay? What is the value of the full price information?

Question C.3 [20 marks]

Suppose you can obtain the price information from only one market. In that case, from which market should you quote the price. Draw the decision tree to analyse it. Find the value of partial information using only the price information from Market 1. Repeat the calculation for Market 2.