Maritime Finance and Business Modelling

INFORMATION

You  are required to  complete  all  of the following  questions.    Your  responses  to  the  written
questions  in  sections  1B,  2C  and  3A,  must  be supported  and  justified  by  referring  to
appropriate  textbooks  and  other  academic  sources  such  as  journal  papers,  conference
papers  or  information  from  academic  websites  ending  in  .ac.uk  or  .edu  for  example.    All
spreadsheet work should be printed out and attached to your coursework.

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
  r
2

  The Y Axis Intercept
  The Slope
  Residuals
  Standard Error of Estimate

MBM5212 €“ Maritime Finance and Business Modelling  2

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
  r
2

  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 €“ Chagen, Zeebrugge,
Bilbao, Le Havre and Vigo.

The  container  loads  from Chagen 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 Chagen;  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 Chagen 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.