CO5124:03 Data Analysis and Decision Modelling Decision Modelling Assignment Weighting – 20% Multi-Period Oil Blending Background Information Brisneyland Oil Refinery needs crude oil to make petrol. Your company has won the consulting contract to choose where to buy oil for the next 3 months. The aim is to minimise cost and also obey some constraints. Objective To minimize cost, where should we buy oil? The oil refinery is located next to storage tanks. This means we can unload oil from ships, and store it before we refine the oil into petrol. So we could buy cheaper oil in an early month, and store it until a later month. Data Below are the forward contract prices for oil. (A forward contract is a contract to buy oil today, but the oil will be delivered in the future). Also listed are the shipping distances. Kind of oil Cost Feb 2018 Cost Mar 2018 Cost Apr 2018 Shipping Distance (nautical miles) West Texas intermediate $63.50 $65.00 $66.00 9,283 Long Beach medium $65.50 $67.50 $68.00 6,287 Brent light sweet $69.50 $71.00 $73.50 12,090 Maracaibo heavy $54.50 $57.50 $61.50 4,680 Aceh medium $63.50 $64.00 $68.50 3,828 Dharhan light sweet $67.00 $69.50 $70.50 7,453 Oil can only be delivered in big ships, called oil tankers. Tankers come in a variety of sizes. When you order oil, it only comes in a tanker full of the stuff, and you cannot buy a fraction of a tanker. Also, the bigger the oil tanker, the cheaper the transport cost. Kind of oil tanker Capacity (barrels) Cost per barrel per nautical mile Panamax 700,000 $0.0011 Suezmax 1,200,000 $0.0009 TI Class 2,650,000 $0.0005 For example, if you order a Panamax tanker full of West Texas intermediate oil, the ship will travel 9,283 nautical miles from Texas to Brisbane, at a cost of $0.0011 per nautical mile per barrel, so that the transport cost is 9,283 x $0.0011 x 700,000 = $9,309,300 just for transport costs. The oil itself costs another $63.50 per barrel for February delivery, so add another 700,000 x $63.50 = $48,650,000 for the oil. So the total cost of this one ship in February will be $48,650,000 + $9,309,300 = $57,959,300. Why canâ€t we just buy the cheapest oil? Because we need to make petrol. Petrol is made by mixing chemicals extracted from the oil, and octane what we need the most. Heavier oil is cheaper, but has less octane, and so makes less petrol. This table shows how much octane is in the different types of oil. Kind of oil Octane Sweet light (or light sweet) 24% Intermediate or Medium 19% Heavy 16% Constraints a) The refinery runs continuously, and each month it refines 8,000,000 barrels of oil. b) At the beginning of February, the tanker farm will be empty. So for February, you need to buy at least 8,000,000 barrels of oil for refining into petrol. In the months after that, you can buy more or less oil, so long as each month there is 8,000,000 barrels available (either from last monthâ€s storage, or from this monthâ€s new oil). c) For storage, we store up to 9,000,000 barrels of oil (but you could always build more storage space). The amount of oil in storage cannot be negative. d) At the end of the 3 months, the storage should contain at least 1,000,000 barrels. e) Due to sanctions, we can only obtain one ship of oil (of any size) from Maracaibo per month. But maybe you could negotiate with Donald Trump and get around oil sanctions (NASA did, so maybe you can too). f) For other kinds of oil besides Maracaibo, there is a monthly maximum of 4 ships (of any size). Again, maybe you could get more ships, if you paid a higher price. g) Due to shallow oceans near Britain and Texas, they canâ€t use the biggest tankers: • Brent oil can only use tankers of size Suezmax or Panamax. • West Texas oil can only use tankers of size Panamax. h) We refine the oil to make petrol, and thatâ€s limited by the amount of octane we can get from the oil. Each month, we need at least 1,500,000 barrels of octane. Requirements and Assessment Criteria Each student is required to submit a Microsoft Excel file, which should include at least two versions of the problem: the original problem as described above, plus a modified problem to try and improve the outcome in some creative way (e.g., by changing a constraint, or finding a new source of oil, or something criminal, etc.) This assessment is an individual assignment. The purpose of this assignment is to apply appropriate decision modelling to solve a business problem. The activities involve developing decision models to represent a business problem and implementing that decision model using Microsoft Excel and Solver. Students are required to mark up their Excel spreadsheet to: • comprehensively explain and critically analyse the relationship between the business issues and practice, and solving using appropriate and accurate decision models; • correctly and critically evaluate, analyse, and discuss all models/options. Draw critical justified conclusions and present recommendations with supportin…