Maritime Finance and Business Modelling
SECTION 1A
(10 marks)
Referring to the data shown in Table 1, develop a model using Microsoft Excel which is capable of calculating a solution to the following:
• Pearson Product Moment Correlation Coefficient
• r2
• The Y Axis Intercept
• The Slope
• Residuals
• Standard Error of Estimate
Table 1: World GDP and world seaborne container trade for years 2000-2013.
World seaborne container trade in tonnes x 1 million World GDP in US dollars x 1 trillion
606.81 32.98
628.14 32.78
691.99 34.00
801.44 38.15
910.75 42.94
1001.26 46.47
1091.21 50.33
1215.28 56.69
1270.75 62.17
1133.03 58.88
1291.02 64.55
1404.34 71.45
1453.39 72.91
1531.74 74.90
Source: GDP data from World Bank and seaborne container trade from Clarksons
Your model needs to be automatically updatable so that if any of the figures for GDP or seaborne trade are changed, then it should also update the figures for:
• Pearson Product Moment Correlation Coefficient
• r2
• The Y Axis Intercept
• The Slope
• Residuals
• Standard Error of Estimate
Note: Your model MUST NOT use the Intercept, Slope, Correl, Pearson, or Steyx formulas within Excel.
• Include a print-out of your model fitted on to one sheet of paper.
• Include a print-out of your model that shows the formulas you have used (fitted on to one sheet of paper).
SECTION 1B
(20 marks) 500 words
• Critically asses the strengths and limitations of using of linear regression as a means of evaluating the relationship between variables.
SECTION 2A
Applying linear programming concepts using Microsoft Excel Solver Add-in to determine maximum potential profit.
(10 marks)
Various warehouses in Middlesbrough have a combined capacity of 200,000 cubic metres and they are being used to unload products from 40 foot intermodal containers that have arrived on short sea shipping services from five European ports – Copenhagen, Zeebrugge, Bilbao, Le Havre and Vigo.
The container loads from Copenhagen cost £4,000 each (including the contents inside, transportation administration and other expenses), and occupy 35 cubic metres each, and are sold at £4,250 each. The corresponding figures for Zeebrugge are £3,200, 40 cubic metres and sold for £3,400. For Bilbao the figures are £4,500, 30 cubic metres, £4,690; for Le Havre the figures are £5,000, 34 cubic metres, and £5,200. For Vigo the figures are
£2,000, 45 cubic metres, and £2,250.
The company unloading the containers has allocated up to ten thousand hours of labour per month for unpacking on this operation. It takes two hours of labour to unpack a container from Copenhagen; one-and-a-half hours for one from Zeebrugge; two hours for one from Bilbao; two hours for one from Le Havre; and one hour for one from Vigo. The products in the containers are in high demand and anything imported can be sold, however there are some restrictions:
• Exactly 100 containers arrive from Copenhagen each month
• A maximum of 1,000 container loads are exported from Bilbao each month
• A maximum of 1,000 container loads are exported from Vigo each month
Task:
You are required to use the Solver add-in within Microsoft Excel to calculate the best quantity of containers to import each month in order to achieve the highest level of profit, subject to any constraints outlined above.
Save a copy of your spreadsheet which shows the optimal solution for the quantity of containers imported each month from the four different origins, together with the total figure for profit.
• Attach a printout to your coursework showing all of the figures your solution has calculated.
• Attach a printout to your coursework that shows the formulas you have used in your spreadsheet.
• Also include a printout of the Answer Report that Excel Solver automatically generates.
SECTION 2B
This question involves applying linear programming concepts using Microsoft Excel Solver Add-in to determine the minimum potential total cost of transportation.
(10 marks)
Metallurgical coal is used in steel production. The inland supply chain of moving coal to steelworks on a daily basis involves different costs per tonne. This is because different suppliers of coal charge different amounts based on the distance involved. The distribution in the supply chain involves seven sources (supply) of coal (denoted by C1,…….,C7) and five steelworks (demand) denoted by S1,…..,S5). The transportation costs in £ per tonne between sea ports (C) to steelworks (S) are shown below in Table 2. The quantities in tonnes available (supply) and required (demand) are also included in the table. The sources of coal originate in different parts of the country and the cost of delivery from different sources to different steelworks therefore varies.
Table 2: Transportation costs (£)
C1 C2 C3 C4 C5 C6 C7 Required (tonnes)
S1 5 9 15 4 7 9 10 14,500
S2 4 6 6 5 4 3 7 9,500
S3 9 12 10 10 5 6 7 16,000
S4 11 5 6 8 9 5 5 7,500
S5 13 10 3 6 7 10 4 11,500
Available (tonnes) 5,000 9,000 10,000 8,000 6,000 12,000 9,000
Task:
Using Excel Solver add-in determine which coal source should deliver to which steelworks with the objective function being to minimize the cost of transportation, while ensuring that all requirements for coal by steelworks are met.
Save a copy of your spreadsheet which shows the optimal solution together with the total figure for the cost of this operation.
• Attach a printout to your coursework showing all of the figures your solution has calculated.
• Attach a printout to your coursework that shows the formulas you have used in your spreadsheet.
• Also include a printout of the Answer Report that Excel Solver automatically generates.
SECTION 2C
(20 Marks) 500 words
• Critically evaluate at least three disadvantages of using linear programming as a decision solving technique in a logistics, transportation or supply chain context.
SECTION 3A
Monte Carlo Simulation (30 marks) 1000 words
• Analyse how Monte Carlo simulation could be used for planning purposes within an organization in any area related to logistics, transportation or supply chains.
• Discuss some of the disadvantages of using Microsoft Excel for performing Monte Carlo simulation.
PLACE THIS ORDER OR A SIMILAR ORDER WITH US TODAY AND GET AN AMAZING DISCOUNT 🙂