Downstream supply chain Optimisation for Coca Cola Ltd Industry

This report aims to optimize the downstream supply chain of ACCBC, a subsidiary of Coca Cola in UAE, using linear programming technique. The report includes literature review, problem statement, research methodology, data collection, and key findings. The problem faced by ACCBC and its logistic company is listed, and the shipping cost for different cities is provided. The report also discusses the Mission 2020 of Coca Cola and its impact on the industry.

Downstream supply chain Optimisation
1 | P a g e
Logistics and Supply Chain: An
analysis of the logistics functions
within Coca Cola Ltd Industry

Downstream supply chain Optimisation
ABSTRACT
The role played by Coca-Cola industries has a great impact on communities globally, this is due
to the reason that they provide largest seller of beverage and still selling all around the world, in
this condition it is necessary to optimize this organization in every possible way. In this report
we have tried to optimize the downstream supply chain of a company which is named as
ACCBC, which is subsidiary of Coca-Cola company in UAE. After defining the problem, we
have tried to optimize and integrate the all modes of transport with the help of linear
programming technique using excel solver. We have shown that integrating various mode of
transport can reduce the shipment cost to some extent. Various literature review is given to
support our findings,
2 | P a g e

Downstream supply chain Optimisation
Contents
Introduction......................................................................................................................................4
Mission 2020...............................................................................................................................4
Literature review..............................................................................................................................5
Background..................................................................................................................................5
Present Scenario...........................................................................................................................6
Problem Statement...........................................................................................................................8
Research methodology.....................................................................................................................9
Research Method.........................................................................................................................9
Data Collection..........................................................................................................................10
Data analysis and reflection of key findings..................................................................................14
Reduction of CO2 emission in this optimisation............................................................................18
Recommendation from the analysis...............................................................................................18
Conclusion.....................................................................................................................................18
References......................................................................................................................................19
3 | P a g e

Downstream supply chain Optimisation
Introduction
The Case deals with Coca-Cola Company, A Company with largest and oldest manufacturer and
retailer of beverage in the world. Producing and marketing more than 75 products from its
portfolio all over the world. Headquarter of this company is situated at Atlanta, Georgia. But the
products of this company coming to our hand are made and bottle somewhere around the
consumer, i.e. its manufacturing and bottling plant is situated all around the world. It is very
important fact about this company is that, this company and its major subsidiary companies
produces only concentrated syrup, this syrup is being sold to various local Coca-Cola franchises.
These local franchises mixed this concentrated syrup with filtered water and sweetener and pack
them into Cans, and bottles, this product is sold and distributed to different merchandiser and
then finally to retail store, vending machine, restaurant to provide it to consumers (Garduño,
2017).
Mission 2020
Coca-Cola industries has given a significant impression on changing the face of the world in last
few decades, not due to selling the product all over world, but also due to its polite social
accountability policy. One of the key action incorporated in their policy is it vision and action
towards reducing carbon footprint by 2020. Its 2020 sustainable goals programme is shifting this
companies toward zero carbon emission goals achieving to the year 2020. This programme has
breakup on six different parameters, and each one upscaling towards excellence. Its use of
agricultural product by 100 to 2020, is achieved almost 50% in last one and half year, its giving
back to society and community is reached back to 2% as the target of 1% for the same period.
The subsidiaries companies which is also known as bottlers as making according to guidance and
parameters given by Coca-Cola companies, and its percentage is around 80%, as per data of
2017 sustainability report. One of the main parameter in this policy is reduction of carbon
footprint, which is scaling up to 13 % and its target to reduce the carbon footprint up to 2020 is
25%. Its women empowerment program is counting the empowered women all around world is
1.7 million, it has also targeted as 5 million by the year 2020 (Garduño, 2017).
As discussed earlier the subsidiaries of Coca-Cola industries are also know as bottlers. There are
about 70 big subsidiaries company is working all over the globe. Some of them are highly
advanced as per technology and quality is concern, and some are inching towards as per
guidance given by its parent’s industries. Aujan Coca-Cola bottling company short form ACCBC
is one of them who is packing the beverage and supplying all around United Arab Emirates local
market. Half of the stakes has been acquired by the Coco-cola company in 2012 and provided
Coca-Cola bottling technology this company (Andrew Wearne, 2014).
As far as beverage industry is concerned, the ACCBC supply chain is distributed among two
distinct areas, the upstream and downstream supply chains. The procurement part of beverage
industries, such as water, sweetener and chemicals, power for running the plant, management of
demand, exploration, and inbound logistics of raw material from remote area to the plant, all
4 | P a g e

Downstream supply chain Optimisation
comes under upstream supply chain. The down steam of supply chain consists of outbound
logistics, of finished concentrated syrup, forecasting of market, delivery of concentrated syrup to
the bottlers all over the world. The problem and solution are available in both the areas equally,
but my project in concerned about outbound logistics of the ACCBC limited.
Since product of beverage company is not having very complex process, this can be
accommodating in one plant activity, but a company like Coca-Cola who is having worldwide
presence, then it is a long process and time consuming also. As discussed above sweetener,
chemical and water are primarily the raw material for beverage industries. The process starts
with distillation which process water for various kind of chemical mixing including sweetener.
The processed water further goes to mixing with other chemical as raw material for packed in a
large container for shipping operation. This is the second stage of Coca-Cola process in which
we are getting various types of beverage in our hand, For the case of Coca-Cola it may be Coke,
Fanta, Sprite and so many other products under the brand of same company.
Literature review
Background
The best way to optimise the any supply chain system mathematical optimisation model, this is
because of the reason that, every system of supply chain is has some unique characteristics in
itself, and designing a commercial information system is difficult which can cover all the area of
supply chain for any industries. Most of the industries are procuring customised information
system according to their need. And second thing is that, basic of this system starts from
quantifying the problem and their solution according to our need of optimisation, which need
mathematics, number of crunchy data and its interpretation to achieve optimisation (Beaven,
2014).
Supply chain is much complex what we can think of, as per Tom Blackstock, the vice presidents
of supply chain operations at Coca-Cola North America statements, if you are in the middle of
industrial process, the supply chain of any organisation is looks like a uprooted tree, in which
root system is representing the different supplier of the organisation and the branches of the
uprooted tree is representing network of customer. The look of the tree depends upon various
types organisation is different, A retailer chain will see the customer next to the stem, or back
square, the different company who is supplying the material to the retailer will be just back of
this square box, these are nothing but tier in supply chain network (Joseph Geunes, 2009).
Figure 1- Supply Chain Structure (Sources: (Lambert, 2008))
5 | P a g e

Downstream supply chain Optimisation
Supply chain is a complex task, but its management is more complex and challenging, managing
all supplier who is just behind you and serving all the customer according to their needs which
out to the forward from your side is overwhelming task, most of the supply chain manager
always try to manage their supply chain to the point of consumption, this main reason behind this
viewpoint is that those who have strong relationship with end customer, they have power in their
supply chain. The same theory fits best with the Coca-Cola Company, most of the acquisition
and merger happened in this company is based on strong relationship with customer and their
demand fulfilment (Iyan, 2016).
Present Scenario
For this paper we have gone through several research paper like, (Joseph Geunes, 2009),
(Michael Talmadge, 2016), (Štefan Kudláč, 2017), etc., all these has pioneer work in
mathematical modelling in various industries. The main aspect of all these literature reveals that
linear programming model best tools which can be employed for optimization problem. Stefan
states that the strategy of logistical planning can be best suited with linear programming model;
we can include the type of bulk transportation for solving the problem of distribution. The model
given by Li is based on nonlinear programming and several petroleum refineries and obtained
very effective result for integrated planning network. The formulation given by Neiro and Pinto
had given a very wide spectrum of refineries production with the help of mixed integer and non-
linear programming model which also includes the optimisation process in pipeline
transportation. The analysis given by Michael enhance the levels in further level, they have
analysed whole beverage industries in three tiers and accumulated the result for overall
optimisation for any particular organisation. Instead of several analyses with the help of linear
programming, some other researchers have also given mathematical model on the basis of fuzzy
6 | P a g e

Downstream supply chain Optimisation
logics, stochastic demands and discrete analysis. To maintain connectivity between productions
plant to warehouses and warehouses to customer mixed is best analysed by Persson and Göthe-
Lundgren, with the help of inequality as a constraint in the linear programming. All of the above
researches have given their analysis irrespective of environmental consideration and pollution
point of view, but (Ba-Shammakh, 2009) clearly states that, review of energy and power
consumed by these refineries section including CO2 generation is must things to do, like other
subject where inclusion of environmental aspect is necessary, analysis is also not a different from
it, we must have some environmental related analysis to this project also (Yihua Zhong, 2013).
As discussed earlier, the present study is about shipping and despatch process of product from
ACCBC refineries, which also known as bottling of Coca-Cola, is since the ACCBC is partially
state-owned company, not listed in share market and does provide much data to analyse the
current situation.
Figure 2- Supply chain in for ACCBC
There ACCBC industries consists of two bottling unit and the current capacity of refining
including both the plant 800 pallets per day, in which 75 % of the product is going local five
cities. The major despatches are happening through Truck, Mini Truck and Pickup. The rest 25%
is being consumed in their rest of the cites in UAE. The project is based on these domestic
production and consumption of beverage from these five cities.
7 | P a g e

Downstream supply chain Optimisation
The product going to domestic market from plant is Petrol, Diesel and Octane petrol which is
also known as High speed petrol. There are five big depots for oil reserve which is used to store
the processed liquid and ready to go to the customer
Problem Statement
In recent years the supply chain operation for ACCBC is being performed smoothly, and not
making any supply issue news from international market. But Last two years we heard lot about
domestic distribution system. A place where oil and gas are being produced, is feeling scarcity in
beverage some places and in other places, no space to keep the Coca-Cola Product. The vehicle
which is owned by shipping and logistic company is transferring the material as usual. But due to
Figure 3-Problem with Aujan
ban opposed on Qatar, most of the companies who are hailed from Qatar are forced to leave the
UAE area, results that there is loss of some big transporter who has given their vehicle to
shipping and logistic company for transportation of product to domestic market. Due to this
reason some mismanagement has occurred within the company.
8 | P a g e

Downstream supply chain Optimisation
Research methodology
Research Method
The problem faced by ACCB spends and its logistic company is listed below.
ACCBC spent around 5 million AED by Truck to ship their Coca-Cola product to different
depots within the country, from 2 different Plant to five different Consumers in different cities
Dubai, Al-ain, Sharjah, Fujairah, Ajman. The name of specific customer in not given because of
various list of customers. Each Plant has a limited supply and each Consumers a certain demand.
Data Collection
Since the given company is not listed in equity market, we have lack of primary data in this
report because this company does not provide much data about themselves, almost all the
analysis done the secondary data only. We have presented the optimisation of outbound supply
chain with the help of mixed integer linear programming based on excel solver.
The shipping cost of five different cities are listed below
Table 1- Shipping Cost /barrel by rail (Sources: (ling, 2017)
Destination\Plant location AD Plant AL-Ruwais Plant Demand
Dubai 1212 1840 136
Al-ain 966 2020 90
Sharjah 2289 722 102
Fujairah 1503 961 67
Ajman 1728 245 77
The current shipping cost by Truck, Minitruck and Pickup for different city is as follows for
ACCBC
Table 2- Shipping cost by different means of transport (Sources: (ling, 2017)
Destination\
Total Cost Truck Mini
Truck
Picku
p
Dubai 3,051.
84 2371.2
1285.
2
Al-ain 2,985.
60 1333.8 850.5
Sharjah 3,011.
28 2074.8 963.9
9 | P a g e

Downstream supply chain Optimisation
Fujairah 2,464.
68 1185.6
633.1
5
Ajman 1,972.
68 1926.6
727.6
5
Total 13,486
.08
8,892.0
0
4,460.
40
Which is around 27000 AED per day
The analysis of above given data in difficult unless and until we bring it some mathematical
model. The above data only depicts that the current shipping cost for five different sites in UAE
is around 0.8 million AED on daily basis.
Based on the above data, we have selected the constrains
1) The variables are the number of products to ship from each plant to the Consumers. These are
given the
name Products shipped in worksheet Trailer
2) The logical constraint is
Products shipped >= 0 via the Assume Non-Negative option
The other two constraints are
Total received >= Demand
Total shipped <= Capacity
3) The objective is to minimize cost. This is given the name Total cost.
This is a transportation problem in its simplest form. Still, this type of model is widely used to
save many thousands of dollars each year. In worksheet tanker we will consider a 2-level
transportation, and in worksheet Small tanker we expand this to
a multi-product, 2-level transportation problem.
After arranging all the data and running the solver, we get the following result.
Table 3-Answer report for Truck Shipment
Cost of shipping (AED per pallet)
Destinations
Dubai Al-ain Sharjah Fujairah Ajman
AD Plant 15.24 17.28 17.16 25.44 18.36
AL-Rewais Plant 41.52 41.76 43.56 61.68 45.36
10 | P a g e

Downstream supply chain Optimisation
As per optimisation of Truck shipment according to demand and capacity the result show that,
total shipment can happen in Just AED 12854, which quite less than AED 13486 actual expenses
by Truck.
Now in similar way we must check the scenario of Tanker shipment from depot to cities and
from plant to directly to cities. The cost for tanker is as follows.
Table 4-Shipment cost by mini Truck (Sources: (ling, 2017)
Destination\Plant
location UAE Plant AL-Ruwais Plant
Depot 1 7.28 11.25
Depot 2 7.28 4.37
Depot 3 14.56 7.28
Depot 4 2.91 2.91
And shipment cost by tanker for depot to cites area as follows.
Table 5-SHipment cost by tanker (Sources: (ling, 2017))
Destination\Plant location UAE Plant AL-Ruwais Plant
Dubai 15.24
41.52
Al-ain 17.28 41.76
Sharjah 17.16 43.56
Fujairah 25.44 61.68
Ajman 18.36 45.36
In order to Minimize the costs of shipping goods from factories to Depots and Consumers, and
Depots to Consumers, while not exceeding the supply available from each factory or the capacity
of each Depot, and meeting the demand from each Consumer, we have to arrange the data in
excel sheet.
1) The variables are the number of products to ship from the factories to the Depots, the factories
to the Consumers, and the Depots to the Consumers. These are defined in worksheet Mini
Truck as given below
Factory to Depot, Factory to Consumer, Depot Consumer.
11 | P a g e

Downstream supply chain Optimisation
2) The logical constraints are all defined via the Assume Non-Negative option:
Factory to Depot >= 0
Factory to Consumer >= 0
Depot Consumer >= 0
The other constraints are
Total from factory <= Factory capacity
Total to Consumer >= Demand
Total to Depot <= Depot capacity
Total to Depot = Total from Depot
3) The objective is to minimize cost, given by Total cost.
the last constraint must be an '=', because otherwise products would start piling up at the Depot.
It would be possible to make this a multi-period model where storage at the Depots would be
possible and even desired, if transportation prices would fluctuate during the different time
periods. In worksheet Transport3 we will look at a multi-product situation.
After running the solver, the following result we obtained.
Table 6-Optimised result for Mini truck
Dubai Al-ain Sharjah Fujairah Ajman Total
Depot 1 0 0 130 10 0 140
Depot 2 0 90 0 70 0 160
Depot 3 140 0 0 0 10 150
Depot 4 0 0 10 0 120 130
Total 160 90 140 80 130
Demands 160 90 140 80 130
This result shows the overall shipment cost through tanker is AED 4985/day, which is quite less
that AED 8982. The sensitivity report is kept at the annexure of this report.
In the similar fashion, we must optimise the Pickup truck scenarios.
Table 7-Shipment cost of Pickup Truck from Plant to depot (Sources: (ling, 2017))
UAE plant Al-ruwais Plant
12 | P a g e

Downstream supply chain Optimisation
Petrol Diesel Octane Petrol Diesel Octane
Depot 1 0.48 0.96 0.72 1.44 1.20 1.34
Depot 2 0.48 0.72 1.20 0.29 0.77 0.86
Depot 3 0.96 1.20 0.96 0.48 0.96 0.91
Depot 4 0.19 1.20 0.77 0.19 0.72 1.06
Table 8-Shipment cost from Plant to cities. (Sources: (ling, 2017) )
Dubai Al-ain Sharjah Fujairah Ajman
UAE plant
Cok
e 10.56 13.45 9.60 11.52 10.00
Fant
a 9.60 11.52 7.68 10.56 10.40
Spri
te 11.14 11.52 8.64 10.76 9.40
Al-ruwais Plant
Cok
e 11.52 13.45 13.45 9.60 8.00
Fant
a 8.64 11.33 8.45 9.60 8.40
Spri
te 9.41 10.56 9.03 10.95 9.80
Table 9-Shipment cost from Depot to cities. (Sources: (ling, 2017))
Depot 1 Depot 2 Depot 3 Depot 4
Coke
Fant
a
Sprit
e
Cok
e
Fant
a
Sprit
e
Cok
e
Fant
a
Sprit
e
Cok
e
Fant
a
Sprit
e
Dubai 5.65
3.7
6
4.7
1
3.7
6
4.7
1
4.1
4
3.7
6
3.3
9
4.7
1
9.4
1
6.5
9
5.6
5
Al-ain 3.01
3.3
9
2.6
4
1.8
8
3.7
6
4.1
4
5.6
5
5.0
8
4.5
2
5.6
5
4.8
9
4.1
4
Sharja
h 1.88
4.5
2
4.1
4
1.8
8
3.7
6
3.3
9
7.5
3
5.4
6
6.5
9
2.2
6
2.6
4
5.6
5
Fujair
ah 5.65
4.8
9
3.0
1
3.7
6
3.3
9
5.2
7
7.5
3
6.7
8
6.4
0
5.6
5
4.7
1
4.1
4
Ajman
12.0
0
8.4
0
6.4
0
2.0
0
6.0
0
7.0
0
2.0
0
4.0
0
3.4
0
2.0
0
4.4
0
3.6
0
To find the lowest cost for 3 different goods from Plant to Depots and Consumers, and Depots to
Consumers, while not exceeding the supply available from each factory or the capacity of each
Depot, and meeting the demand from each Consumer. We must arrange the data in excel sheet as
given in Pickup worksheet.
The lowest cost for Pickup is based builds on model Truck worksheet. We must minimise the
cost of shipping, but this time there are 3 products to distribute. The way ACCBC should
distribute the product is as follows
The solution to the problem is identical to the one in truck. Notice that we have used the 'Insert
Name Define' command to extend the model to a multiproduct problem. This way the variables
13 | P a g e

Downstream supply chain Optimisation
and constraints are still the same as in Tanker. The small tanker delivers the same result as three
separate models for the three products. There will be times however, that there are constraints
that apply to more than one product. In that case it would not be desirable to have three different
models and maybe even impossible. For an extension of this model, where the number of
products made in the factories depends on the demand and distribution rather than being
constant, see the worksheet outbound supply chain in this workbook.
Data analysis and reflection of key findings
After running the excel solver we get the following result.
Table 10-Potmized result for Pickup Truck shipment
Dubai Al-ain Sharjah Fujairah Ajman Total
Depot 1 Coke 0 0 0 0 0 0
Fanta 0 0 0 0 0 0
Sprite 0 0 0 0 0 0
Depot 2 Coke 36 36 0 0 0 72
Fanta 0 0 0 0 0 0
Sprite 0 0 0 0 0 0
Depot 3 Coke 0 0 0 0 0 0
Fanta 0 0 0 0 0 0
Sprite 0 0 0 0 0 0
Depot 4 Coke 0 0 41 0 31 72
Fanta 0 0 0 0 0 0
Sprite 0 0 0 0 0 0
Total Coke 54 36 41 27 31
Fanta 54 36 41 27 31
Sprite 27 18 20 13 15
Demands Coke 54 36 41 27 31
Fanta 54 36 41 27 31
Sprite 27 18 20 13 15
The total cost of small tanker calculated as AED 3984, which is higher that present cost incurred
by ACCBC which AED 4460.
Despite these solutions we have, integrate all the scenario and find out the best possible result
from production to logistic cost i.e. outbound supply chain cost.
14 | P a g e

Downstream supply chain Optimisation
Now it can be analysed by Minimize the costs of producing 3 different goods, and shipping them
from plant to Depots and cities, and Depots to cities, while not exceeding the supply available
from each factory or the capacity of each Depot, and meeting the demand from each city.
In this scenario ACCBC must minimize the cost of shipping three different products from
factories to Depots and cities and from Depots to cities. The production of each product at each
plant depends on the distribution. How many products should each factory produce and how
should the products be distributed to minimize total cost while meeting demand?
Now we must extend the transportation model as seen in the Pickup Truck worksheet. This time
the factories do not produce a fixed amount. The amounts produced are now variables.
1) The variables are the number of products to make in the factories, the number of products to
ship from factories to Depots, factories to cities, and Depots to cities. In worksheet Outbound
supply chain worksheet these are given the names Products made, Factory to Depot, Factory to
cities, and Depot to cities.
2) The logical constraints are all defined via the Assume Non-Negative option:
Products made >= 0
Factory to Depot >= 0
Factory to Cities >= 0
Depot to Cities >= 0
The other constraints are
Total from factory <= Factory capacity
Total to cities >= Demand
Total to Depot <= Depot capacity
Total to Depot = Total from Depot
3) The objective is to minimize cost. This is defined in the worksheet as Total cost.
This is one of the more complex models in this series of examples. If the number of products,
factories and Depots becomes large, the number of variables in a model like this one becomes
very large. Also bear in mind the degree of coordination between business units that may be
needed to implement the optimal solution. For these reasons, some users prefer to split problems
like this one into a set of smaller, simpler models.
15 | P a g e

Downstream supply chain Optimisation
After running the solver, we got the following result.
Table 11- Optimised No of pallet shipped
Number of pallet
shipped
Depot
1
Depot
2
Depot
3
Depot
4 Total
UAE
Plant
Cok
e 0 0 0 72 72
Fant
a 0 0 0 0 0
Sprit
e 0 0 0 0 0
Al-
ruwais
Cok
e 0 72 0 0 72
Fant
a 0 0 0 0 0
Sprit
e 0 0 0 0 0
Total
Cok
e 0 72 0 72
Fant
a 0 0 0 0
Sprit
e 0 0 0 0
Capacit
y
Cok
e 88 72 88 72
Fant
a 88 72 88 72
Sprit
e 44 36 44 36
Dubai Al-ain
Sharja
h
Fujair
ah
Ajma
n
Tot
al
UAE
Plant
Cok
e 18 0 0 0 0 18
Fant
a 0 0 41 0 0 41
Sprit
e 0 0 20 13 15 49
Al-
ruwais
Cok
e 0 0 0 26 0 26
Fant
a 54 36 0 27 31 148
Sprit
e 27 18 0 0 0 45
From the above optimised report total pallet shipped for Abu Dhabi Plant is 180 number of
pallets, which consists of 90 pallets of Coke, 41 pallets of Fanta and 49 pallets of Sprite. In the
same way From Al-ruwais plant, the total number of barrels shipped will be 292 pallets solver
report further indicated that no necessary to shift the petrol from Al-ruwais plant. The total
break-up of barrels and its cost is as follows.
Table 12- Breakup of shipment and their different cost
Cost of shipping (AED per Pallet)
Destinations
Dep
ot 1 Depot 2 Depot 3 Depot 4
16 | P a g e

Downstream supply chain Optimisation
UAE Plant
Cok
e 7.68 7.68 15.37 3.07
Fant
a
15.3
7 11.52 19.21 19.21
Spri
te
11.5
2 19.21 15.37 12.29
Al-ruwais
Cok
e
23.0
5 4.61 7.68 3.07
Fant
a
19.2
1 12.29 15.37 11.52
Spri
te
21.5
1 13.83 14.60 16.90
Dub
ai Al-ain Sharjah Fujairah Ajman
UAE Plant
Cok
e
10.5
6 13.45 9.60 11.52 10.00
Fant
a 9.60 11.52 7.68 10.56 10.40
Spri
te
11.1
4 11.52 8.64 10.76 9.40
Al-ruwais
Cok
e
11.5
2 13.45 13.45 9.60 8.00
Fant
a 8.64 11.33 8.45 9.60 8.40
Spri
te 9.41 10.56 9.03 10.95 9.80
Dub
ai Al-ain Sharjah Fujairah Ajman
Depot 1
Cok
e 5.65 3.01 1.88 5.65 12.00
Fant
a 3.76 3.39 4.52 4.89 8.40
Spri
te 4.71 2.64 4.14 3.01 6.40
Depot 2
Cok
e 3.76 1.88 1.88 3.76 2.00
Fant
a 4.71 3.76 3.76 3.39 6.00
Spri
te 4.14 4.14 3.39 5.27 7.00
Depot 3
Cok
e 3.76 5.65 7.53 7.53 2.00
Fant
a 3.39 5.08 5.46 6.78 4.00
Spri
te 4.71 4.52 6.59 6.40 3.40
Depot 4
Cok
e 9.41 5.65 2.26 5.65 2.00
Fant
a 6.59 4.89 2.64 4.71 4.40
Spri
te 5.65 4.14 5.65 4.14 3.60
Here we can that, what is the power of integrating the supply chain can perform, the total
shipment cost including Truck, Mini Truck and Pickup includes and cost around AED 3980 per
day which is quite less than total cost of individual means of transport which AED 21823. Which
is around 18% of total cost of individual transport. The total saving occurs by this optimisation
technique is AED 17843 on per day basis. The annual cost reduction is around AED 6.5 million.
It’s a huge saving.
17 | P a g e

Downstream supply chain Optimisation
Reduction of CO2 emission in this optimisation
The reduction in CO2 emission is depends upon how the move of vehicle happened for
transportation, before optimisation the calculation show that, the CO2 emission due from trailer
to the five cities is around 10 ton/day, in which, only 5 ton emission is due to trailer, 3.5 ton due
to mini truck and 1.5 ton due to Pickup truck, After optimisation technique implementation in
which 144 Pallet in transferred to depot and then from depot to other cities, rest of the 328 pallet
material is directly sent from ACCBC plant situated in Abu Dhabi and AL-Runways to the five
cities, this causes reduction in CO2 emission by 65% and total CO2 emission is after optimisation
is 3.31 ton (Mcfarland, 2017).
Recommendation from the analysis
We have seen that small decision analysis can gives a huge difference in cost saving and
optimisation of our resources according to the requirement. This analysis is done on the excel
sheet and this package has limitation to provide constraints and criteria which cannot be fulfilled
in real time optimisation techniques. A company like subsidiaries of Coca-Cola can run very
high integrity of commercial package and which can optimise the given situation with thousands
of constraints and further the input criterial can also be given in thousand ways. Some of the
commercial package which is available to optimise the enterprise resources are AIMMS,
ALGLIB, ANSYS, COMSOL, IOSO, LINDO, MATLAB, and SmartDO etc. based on the above
result we can recommend that integrated mode of transport is best for the current situation, if it
not possible to apply all the recommendation given by calculation then at least apply as much as
possible, this will reduce the cost to some extent for organisation. Another thing is that the
production from Al-ruwais plant can be directly shift toward the export zone, so that profit can
be maximised. The domestic market can be able to be handle by Abu Dhabi plant, this decision
can make economical for company and cost saving is directly related to increase profit margin
for the company (Dorothy Leab, 2017).
A company like ACCBC should also look for some alternate means of transport for their
domestic operation like knapsack, A kind of Truck which has three compartments in it. It means
that it can fill and carry four different product and transport it to required place. For small
customer which require multiproduct in small quantity can be fulfilled easily by this transport
medium (overall 3rd party logistics at Origin Logistics Pvt. Ltd, 2015).
Conclusion
The problem stated in this report is of very unique kind and important because it arises due to
conflict between two countries and loss arise due to empowering some legislative protocol to
each other. ACCBC is just an example who faced such challenges, this can happen anywhere in
this world. During transporter person hijacked in Iraq. After getting out form this problem the
18 | P a g e

Downstream supply chain Optimisation
transporter company has left the operation in Iraq and Iraqi industries has faced the similar
problem. In this report, we have picked the problem by doing extensive analysis of the
companies like ACCBC, we have lack of primary data in this report because this company does
not provide much data about themselves, almost all the analysis done the secondary data only.
We have presented the optimisation of outbound supply chain with the help of mixed integer
linear programming based on excel solver. The results from the analysis gives only the overall
view of only outbound supply chain. This can be integrated from start to finish of the product
line and further to the customer end. The optimisation technique with the help of linear
programming is also helpful in doing inventory management for any organisation. Optimisation
techniques can be adopted in various ways in an organisation even maintenance and quality,
which has the primary candidate for this tool. Even in same field as downstream supply chain,
we can analyse in some different way like mass balance, demand, capacities, service levels, and
OPEC quota. One of the remarkable finding in my project is that we have assessed the trade-off
among different means of transport in outbound supply chain.
In the future work, will try to exploit the fact by some different and higher-level software
package to find more accurate result. The same analysis can be done in three sections of inbound
process and outbound supply chain analysis. A company like ACCBC which is first bottlers of
Coca-Cola in gulf region, is still lacking benchmarking in logistic and supply chain integration.
To achieve this, they must dig and explore more data not Coke.
References
Aggarwal, P. (2014). Supply Chain Management of Locally-Grown Organic Food: A Leap
Toward Sustainable Development. Cognizant journal, 1(1), 1-11.
Andrew Wearne, M. B. (2014). Australian Case Study Coca-Cola Amatil. Aon Corporation
Australia, 1-23.
Barker, J. A. (2014). From the Depths of Despair to the Promise of Presence (1st ed.). New
York: Eisenbrauns.
19 | P a g e

Downstream supply chain Optimisation
Barua, J. J. (2010). Challenges facing supply chian management in oil marketting companies in
kenya. Scool of businees adminitration journal, 1(1), 1-65.
Ba-Shammakh, M. (2009). An Optimization Approach for Integrating Planning and CO2
Mitigation in the Power and Refinery Sectors. University of Waterloo, 1-202.
Beaven, I. (2014). CDP 2014 Investor CDP 2014 Information Request. Coca-cola Journal, 1-82.
Bhushan Sudhakar, C. S. (2016). Logistics and Supply Chain Management. Puducherry:
Puducherry University Press.
Ceradini, G. (2014). Coca-Cola HBC. Libelle SystemCopy, 1-2.
Choudhary, A. (2014). Analysis and design of Supply chain model for a specific organisation.
The Macrotheme Review, 1-36.
delmer, J. (2014). Refreshing Its Supply Chain Operations. Jda Journal, 1-3.
Dorothy Leab, B. S. (2017). National logistics working groups: A landscape analysis study.
Health Care journal, 35(1), 1-10.
Garduño, J. (2017). Coca-Cola Sustainability Report 2017. Public Affairs & Communications, 1-
17.
Iyan, C. (2016). 2016 Sustainability Report. Coca-Cola Journal, 1.
Jaber, D. S. (2015). ADNOC group sustainability report. Adnoc journal, 1-11.
Jianhua Dai, S. P. (2017). Mitigation of Bullwhip Effect in Supply Chain Inventory Management
Model. Manufacturing and Management, 1(1), 1-6.
Jorge Garduño, I. K. (2017). Coca-Cola Sustainability Repor t 2017. Coca-Cola Journal, 1-17.
Joseph Geunes, P. P. (2009). Supply chian optimisation. Applied Optimization techniques, 98(1),
1-418.
K Kumarasiri, S. A. (2015). Effective Logistics Process for the Soft Drink Industry. Ucsc
Journal, 1-10.
Lambert, D. M. (2008). An Executive Summary of Supply Chian Management: Process,
partnership,Performance. Supply Chain Management Institute, 1-24.
Leary, Z. (2013). The essential guide to doing your research project (2nd ed.). Los Angeles:
Sage Publisher.
ling, R. (2017). Investment guide to UAE. Framework for investments journal, 1(1), 1-48.
20 | P a g e

Downstream supply chain Optimisation
Matveeva, K. (2015). Exploring current issue in the supply chian management in the oil and gas
industries. Aplied science journal, 1(1), 1-36.
MBA project anlysis. (2017). Australian Business Institute, 1(1), 1-8.
Mcfarland, B. (2017, 12 09). https://carbonfund.org/how-we-calculate/. Retrieved from
Carbonfund.org Foundation: https://carbonfund.org/how-we-calculate/
Michael Talmadge, L. B. (2016). Optimizing Biorefinery Design and Operations via Linear
Programming Models. Symposium on Thermal and Catalytic Sciences, 1-1.
Muhtar Kent, G. P. (2012). United States Security Exchange Commission. Annual Report
Journal, 1-176.
Nsikan Paul Akpan, O. C. (2017). Karmarkar’s Approach for Solving Linear Programming
Problem for Profit Maximization in Production Industries: NBC Port-Harcourt Plant.
American Journal of Statistics and Probability, 1-8.
O’Leary. (2014). Introduction to literature review. Literature review, 1(1), 1-9.
overall 3rd party logistics at Origin Logistics Pvt. Ltd. (2015). IBMR journal, 1(1), 1-69.
Panos Pardalos, D.-Z. D. (2009). Optimisation and logistic challenges in the enterprize. Springer
Optimization and Its Applications, 1(1), 1-430.
Purvankara, D. (2007). srategic Analysis of Coca-Cola Company. Simon Fraser University
Journal, 1-116.
Raed Hussaian, B. K. (2006). Supply Chain Management in the Petroleum Industry: Challenges
and Opportunities. International Journal of Global Logistics & Supply Chain
Management, 1(2), 90-97.
Sanjeewa, R. (2014). Implementation of Cross dockng system to Coca-Cola beverages Sri
Lanka. Kotelawala Defence Journal, 1-9.
Sharada Vadali, S. C. (2016). Buyer-Supplier Transport Access Measures for Industry Clusters.
Texas A&M University System, 1(1), 1-11.
Štefan Kudláč, J. M. (2017). Using the Saaty Method and the FMEA Method for Evaluation of
Constraints in Logistics Chain. Transportation Science and Technology, 1(1), 1-7.
Szpilko, D. (2017). Tourism Supply Chain – Overview of Selected Literature. Procedia
Engineering, 182(1), 1-7.
TamioYoshimatsu. (2017). Coca-Cola Bottlers Japan. Earnings Presentation, 1-34.
21 | P a g e

Downstream supply chain Optimisation
Thapa, G. B. (2016). Basics of Informed Logistics in Just-in-Time Production Sequencing and
Supply Chain Systems. Journal of the Institute of Engineering,, 9(1), 1-11.
The project report:Summarising and presenting data. (2017). research process journal, 1(1), 1-6.
Thomas Poulsena, R. L. (2017). supply chain for the green transformation. The case of offshore
wind logistics. Renewable and Sustainable Energy Reviews, 1(1), 1-14.
Tim, M. (2015). World's Largest Coca-cola bottelers. Warehouse and logistic journal, 1-1.
Vaniel, S. (2016). automatic warehouse for Coca-Cola Hellenic Bottling Company. System
Logostic Journal, 1-2.
Waleed Abduljabbar, R. T. (2014). case study of petroleum transportation logistics: A decision
support system based on simulation and stochastic optimal control. African Journal of
Business Management, 6(11), 1-18.
Weedon, V. (2015). Resistant floor for beverage factory. Builder Solution Journal, 1-2.
Yakuzi, L. (2016). Taking Up the Challenge of Transformation. Integration journal, 1-4.
Yihua Zhong, D. C. (2013). The Theory of Multi-criteria and Multiple Constraint-level Linear
Programming for Oilfield Development. Information technology and Quantitative
management, 1-8.
Yin, R. K. (2014). Introduction to research methodology. Research methodology, 1(1), 1-8.
ANNEXTURE
Microsoft Excel 16.0
Answer Report
Worksheet:
[661124.xls]OutBound
Supply Chian
Report Created:
09/12/2017 22:19:35
Result: Solver found a
solution. All
22 | P a g e

Downstream supply chain Optimisation
Constraints and
optimality conditions
are satisfied.
Solver Engine
Engine: Simplex LP
Solution Time: 0.218
Seconds.
Iterations: 53
Subproblems: 0
Solver Options
Max Time 1000 sec,
Iterations 1000, Precision
0.00000001
Max Subproblems 1000,
Max Integer Sols 1000,
Integer Tolerance 5%,
Assume NonNegative
Objective Cell (Min)
Cell
Nam
e
Ori
gin
al
Val
ue
Fin
al
Val
ue
$C$102
Total
_cos
t
382,
334.
80
382,
334.
80
Variable Cells
Cell
Nam
e
Ori
gin
al
Val
ue
Fin
al
Val
ue
Int
eg
er
$B$13
UAE
Plant
Coke
90.4
0
90.4
0
Co
nti
n
$C$13
UAE
Plant
Fant
a
40.8
0
40.8
0
Co
nti
n
$D$13
UAE
Plant
Sprit
e
49.2
0
49.2
0
Co
nti
n
$B$14
Al-
ruwa
is
Coke
98.4
0
98.4
0
Co
nti
n
$C$14 Al-
ruwa
148.
00
148.
00
Co
nti
23 | P a g e

Downstream supply chain Optimisation
is
Fant
a n
$D$14
Al-
ruwa
is
Sprit
e
45.2
0
45.2
0
Co
nti
n
$C$65
Coke
Dub
ai 18 18
Co
nti
n
$D$65
Coke
Al-
ain 0 0
Co
nti
n
$E$65
Coke
Shar
zah 0 0
Co
nti
n
$F$65
Coke
Fujai
rah 0 0
Co
nti
n
$G$65
Coke
Ajma
n 0 0
Co
nti
n
$C$66
Fant
a
Dub
ai 0 0
Co
nti
n
$D$66
Fant
a Al-
ain 0 0
Co
nti
n
$E$66
Fant
a
Shar
zah 41 41
Co
nti
n
$F$66
Fant
a
Fujai
rah 0 0
Co
nti
n
$G$66
Fant
a
Ajma
n 0 0
Co
nti
n
$C$67
Sprit
e
Dub
ai 0 0
Co
nti
n
$D$67
Sprit
e Al-
ain 0 0
Co
nti
n
$E$67
Sprit
e
Shar
zah 20 20
Co
nti
n
$F$67 Sprit 13 13 Co
24 | P a g e

Downstream supply chain Optimisation
e
Fujai
rah
nti
n
$G$67
Sprit
e
Ajma
n 15 15
Co
nti
n
$C$68
Coke
Dub
ai 0 0
Co
nti
n
$D$68
Coke
Al-
ain 0 0
Co
nti
n
$E$68
Coke
Shar
zah 0 0
Co
nti
n
$F$68
Coke
Fujai
rah 26 26
Co
nti
n
$G$68
Coke
Ajma
n 0 0
Co
nti
n
$C$69
Fant
a
Dub
ai 54 54
Co
nti
n
$D$69
Fant
a Al-
ain 36 36
Co
nti
n
$E$69
Fant
a
Shar
zah 0 0
Co
nti
n
$F$69
Fant
a
Fujai
rah 27 27
Co
nti
n
$G$69
Fant
a
Ajma
n 31 31
Co
nti
n
$C$70
Sprit
e
Dub
ai 27 27
Co
nti
n
$D$70
Sprit
e Al-
ain 18 18
Co
nti
n
$E$70
Sprit
e
Shar
zah 0 0
Co
nti
n
$F$70 Sprit
e
0 0 Co
nti
25 | P a g e

Downstream supply chain Optimisation
Fujai
rah n
$G$70
Sprit
e
Ajma
n 0 0
Co
nti
n
$C$51
Coke
Dep
ot 1 0 0
Co
nti
n
$D$51
Coke
Dep
ot 2 0 0
Co
nti
n
$E$51
Coke
Dep
ot 3 0 0
Co
nti
n
$F$51
Coke
Dep
ot 4 72 72
Co
nti
n
$C$52
Fant
a
Dep
ot 1 0 0
Co
nti
n
$D$52
Fant
a
Dep
ot 2 0 0
Co
nti
n
$E$52
Fant
a
Dep
ot 3 0 0
Co
nti
n
$F$52
Fant
a
Dep
ot 4 0 0
Co
nti
n
$C$53
Sprit
e
Dep
ot 1 0 0
Co
nti
n
$D$53
Sprit
e
Dep
ot 2 0 0
Co
nti
n
$E$53
Sprit
e
Dep
ot 3 0 0
Co
nti
n
$F$53
Sprit
e
Dep
ot 4 0 0
Co
nti
n
$C$54
Coke
Dep
ot 1 0 0
Co
nti
n
$D$54 Coke 72 72 Co
26 | P a g e

Downstream supply chain Optimisation
Dep
ot 2
nti
n
$E$54
Coke
Dep
ot 3 0 0
Co
nti
n
$F$54
Coke
Dep
ot 4 0 0
Co
nti
n
$C$55
Fant
a
Dep
ot 1 0 0
Co
nti
n
$D$55
Fant
a
Dep
ot 2 0 0
Co
nti
n
$E$55
Fant
a
Dep
ot 3 0 0
Co
nti
n
$F$55
Fant
a
Dep
ot 4 0 0
Co
nti
n
$C$56
Sprit
e
Dep
ot 1 0 0
Co
nti
n
$D$56
Sprit
e
Dep
ot 2 0 0
Co
nti
n
$E$56
Sprit
e
Dep
ot 3 0 0
Co
nti
n
$F$56
Sprit
e
Dep
ot 4 0 0
Co
nti
n
$C$81
Coke
Dub
ai 0 0
Co
nti
n
$D$81
Coke
Al-
ain 0 0
Co
nti
n
$E$81
Coke
Shar
zah 0 0
Co
nti
n
$F$81
Coke
Fujai
rah 0 0
Co
nti
n
$G$81 Coke
Ajma
0 0 Co
nti
27 | P a g e

Downstream supply chain Optimisation
n n
$C$82
Fant
a
Dub
ai 0 0
Co
nti
n
$D$82
Fant
a Al-
ain 0 0
Co
nti
n
$E$82
Fant
a
Shar
zah 0 0
Co
nti
n
$F$82
Fant
a
Fujai
rah 0 0
Co
nti
n
$G$82
Fant
a
Ajma
n 0 0
Co
nti
n
$C$83
Sprit
e
Dub
ai 0 0
Co
nti
n
$D$83
Sprit
e Al-
ain 0 0
Co
nti
n
$E$83
Sprit
e
Shar
zah 0 0
Co
nti
n
$F$83
Sprit
e
Fujai
rah 0 0
Co
nti
n
$G$83
Sprit
e
Ajma
n 0 0
Co
nti
n
$C$84
Coke
Dub
ai 36 36
Co
nti
n
$D$84
Coke
Al-
ain 36 36
Co
nti
n
$E$84
Coke
Shar
zah 0 0
Co
nti
n
$F$84
Coke
Fujai
rah 0 0
Co
nti
n
$G$84
Coke
Ajma
n 0 0
Co
nti
n
28 | P a g e

Downstream supply chain Optimisation
$C$85
Fant
a
Dub
ai 0 0
Co
nti
n
$D$85
Fant
a Al-
ain 0 0
Co
nti
n
$E$85
Fant
a
Shar
zah 0 0
Co
nti
n
$F$85
Fant
a
Fujai
rah 0 0
Co
nti
n
$G$85
Fant
a
Ajma
n 0 0
Co
nti
n
$C$86
Sprit
e
Dub
ai 0 0
Co
nti
n
$D$86
Sprit
e Al-
ain 0 0
Co
nti
n
$E$86
Sprit
e
Shar
zah 0 0
Co
nti
n
$F$86
Sprit
e
Fujai
rah 0 0
Co
nti
n
$G$86
Sprit
e
Ajma
n 0 0
Co
nti
n
$C$87
Coke
Dub
ai 0 0
Co
nti
n
$D$87
Coke
Al-
ain 0 0
Co
nti
n
$E$87
Coke
Shar
zah 0 0
Co
nti
n
$F$87
Coke
Fujai
rah 0 0
Co
nti
n
$G$87
Coke
Ajma
n 0 0
Co
nti
n
$C$88 Fant 0 0 Co
29 | P a g e

Downstream supply chain Optimisation
a
Dub
ai
nti
n
$D$88
Fant
a Al-
ain 0 0
Co
nti
n
$E$88
Fant
a
Shar
zah 0 0
Co
nti
n
$F$88
Fant
a
Fujai
rah 0 0
Co
nti
n
$G$88
Fant
a
Ajma
n 0 0
Co
nti
n
$C$89
Sprit
e
Dub
ai 0 0
Co
nti
n
$D$89
Sprit
e Al-
ain 0 0
Co
nti
n
$E$89
Sprit
e
Shar
zah 0 0
Co
nti
n
$F$89
Sprit
e
Fujai
rah 0 0
Co
nti
n
$G$89
Sprit
e
Ajma
n 0 0
Co
nti
n
$C$90
Coke
Dub
ai 0 0
Co
nti
n
$D$90
Coke
Al-
ain 0 0
Co
nti
n
$E$90
Coke
Shar
zah 41 41
Co
nti
n
$F$90
Coke
Fujai
rah 0 0
Co
nti
n
$G$90
Coke
Ajma
n 31 31
Co
nti
n
$C$91 Fant
a
0 0 Co
nti
30 | P a g e

Downstream supply chain Optimisation
Dub
ai n
$D$91
Fant
a Al-
ain 0 0
Co
nti
n
$E$91
Fant
a
Shar
zah 0 0
Co
nti
n
$F$91
Fant
a
Fujai
rah 0 0
Co
nti
n
$G$91
Fant
a
Ajma
n 0 0
Co
nti
n
$C$92
Sprit
e
Dub
ai 0 0
Co
nti
n
$D$92
Sprit
e Al-
ain 0 0
Co
nti
n
$E$92
Sprit
e
Shar
zah 0 0
Co
nti
n
$F$92
Sprit
e
Fujai
rah 0 0
Co
nti
n
$G$92
Sprit
e
Ajma
n 0 0
Co
nti
n
Constraints
Cell
Nam
e
Cell
Val
ue
For
mul
a
St
at
us
S
l
a
c
k
$G$73
Coke
Ajma
n 90
$G$
73<
=$H
$73
Bin
din
g 0
$G$74
Fant
a
Ajma
n 41
$G$
74<
=$H
$74
Bin
din
g 0
$G$75 Sprit
e
49 $G$
75<
Bin
din
0
31 | P a g e

Downstream supply chain Optimisation
Ajma
n
=$H
$75 g
$G$76
Coke
Ajma
n 98
$G$
76<
=$H
$76
Bin
din
g 0
$G$77
Fant
a
Ajma
n 148
$G$
77<
=$H
$77
Bin
din
g 0
$G$78
Sprit
e
Ajma
n 45
$G$
78<
=$H
$78
Bin
din
g 0
$C$93
Coke
Dub
ai 54
$C$
93>
=$C
$96
Bin
din
g 0
$D$93
Coke
Al-
ain 36
$D$
93>
=$D
$96
Bin
din
g 0
$E$93
Coke
Shar
zah 41
$E$
93>
=$E
$96
Bin
din
g 0
$F$93
Coke
Fujai
rah 27
$F$
93>
=$F
$96
Bin
din
g 0
$G$93
Coke
Ajma
n 31
$G$
93>
=$G
$96
Bin
din
g 0
$C$94
Fant
a
Dub
ai 54
$C$
94>
=$C
$97
Bin
din
g 0
$D$94
Fant
a Al-
ain 36
$D$
94>
=$D
$97
Bin
din
g 0
$E$94
Fant
a
Shar
zah 41
$E$
94>
=$E
$97
Bin
din
g 0
$F$94
Fant
a
Fujai
rah 27
$F$
94>
=$F
$97
Bin
din
g 0
$G$94
Fant
a
Ajma
n 31
$G$
94>
=$G
$97
Bin
din
g 0
32 | P a g e

Downstream supply chain Optimisation
$C$95
Sprit
e
Dub
ai 27
$C$
95>
=$C
$98
Bin
din
g 0
$D$95
Sprit
e Al-
ain 18
$D$
95>
=$D
$98
Bin
din
g 0
$E$95
Sprit
e
Shar
zah 20
$E$
95>
=$E
$98
Bin
din
g 0
$F$95
Sprit
e
Fujai
rah 13
$F$
95>
=$F
$98
Bin
din
g 0
$G$95
Sprit
e
Ajma
n 15
$G$
95>
=$G
$98
Bin
din
g 0
$C$57
Coke
Dep
ot 1 0
$C$
57<
=$C
$60
Not
Bin
din
g
8
8
$D$57
Coke
Dep
ot 2 72
$D$
57<
=$D
$60
Bin
din
g 0
$E$57
Coke
Dep
ot 3 0
$E$
57<
=$E
$60
Not
Bin
din
g
8
8
$F$57
Coke
Dep
ot 4 72
$F$
57<
=$F
$60
Bin
din
g 0
$C$58
Fant
a
Dep
ot 1 0
$C$
58<
=$C
$61
Not
Bin
din
g
8
8
$D$58
Fant
a
Dep
ot 2 0
$D$
58<
=$D
$61
Not
Bin
din
g
7
2
$E$58
Fant
a
Dep
ot 3 0
$E$
58<
=$E
$61
Not
Bin
din
g
8
8
$F$58
Fant
a
Dep
ot 4 0
$F$
58<
=$F
$61
Not
Bin
din
g
7
2
$C$59 Sprit
e
0 $C$
59<
Not
Bin
4
4
33 | P a g e

Downstream supply chain Optimisation
Dep
ot 1
=$C
$62
din
g
$D$59
Sprit
e
Dep
ot 2 0
$D$
59<
=$D
$62
Not
Bin
din
g
3
6
$E$59
Sprit
e
Dep
ot 3 0
$E$
59<
=$E
$62
Not
Bin
din
g
4
4
$F$59
Sprit
e
Dep
ot 4 0
$F$
59<
=$F
$62
Not
Bin
din
g
3
6
$C$57
Coke
Dep
ot 1 0
$C$
57=
$H$
81
Bin
din
g 0
$C$58
Fant
a
Dep
ot 1 0
$C$
58=
$H$
82
Bin
din
g 0
$C$59
Sprit
e
Dep
ot 1 0
$C$
59=
$H$
83
Bin
din
g 0
$D$57
Coke
Dep
ot 2 72
$D$
57=
$H$
84
Bin
din
g 0
$D$58
Fant
a
Dep
ot 2 0
$D$
58=
$H$
85
Bin
din
g 0
$D$59
Sprit
e
Dep
ot 2 0
$D$
59=
$H$
86
Bin
din
g 0
$E$57
Coke
Dep
ot 3 0
$E$
57=
$H$
87
Bin
din
g 0
$E$58
Fant
a
Dep
ot 3 0
$E$
58=
$H$
88
Bin
din
g 0
$E$59
Sprit
e
Dep
ot 3 0
$E$
59=
$H$
89
Bin
din
g 0
$F$57
Coke
Dep
ot 4 72
$F$
57=
$H$
90
Bin
din
g 0
34 | P a g e

Downstream supply chain Optimisation
$F$58
Fant
a
Dep
ot 4 0
$F$
58=
$H$
91
Bin
din
g 0
$F$59
Sprit
e
Dep
ot 4 0
$F$
59=
$H$
92
Bin
din
g 0
Sensitivity report
Microsoft Excel 16.0 Sensitivity Report
Worksheet: [661124.xls]OutBound Supply Chian
Report Created: 09/12/2017 22:19:39
Variable Cells
Fin
al Reduced
Objectiv
e
Allowab
le
Allowabl
e
Cell Name
Val
ue Cost
Coefficie
nt
Increas
e
Decreas
e
$B$
13
UAE Plant
Coke 90.4 0 806 0 0.9604
$C$
13
UAE Plant
Fanta 40.8 0 780 0.76832 0.19208
$D$
13
UAE Plant
Sprite 49.2 0 836 0.19208 0.76832
$B$
14 Al-ruwais Coke 98.4 0 806 0.9604 0
$C$
14
Al-ruwais
Fanta 148 0 780 0.19208 0.76832
$D$
14
Al-ruwais
Sprite 45.2 0 836 0.76832 0.19208
$C$
65 Coke Dubai 18.4 0 10.5644
0.88356
8 0.9604
$D$
65 Coke Al-ain 0 4.763584 13.4456 1E+30 4.763584
$E$6
5 Coke Sharzah 0
3.388291
2 9.604 1E+30
3.388291
2
$F$6
5 Coke Fujairah 0 1.9208 11.5248 1E+30 1.9208
$G$
65 Coke Ajman 0 4.043152 10 1E+30 4.043152
$C$
66 Fanta Dubai 0 0.9604 9.604 1E+30 0.9604
$D$
66 Fanta Al-ain 0 0.19208 11.5248 1E+30 0.19208
35 | P a g e

Downstream supply chain Optimisation
$E$6
6 Fanta Sharzah 40.8 0 7.6832 0.76832 787.6832
$F$6
6 Fanta Fujairah 0 0.9604 10.5644 1E+30 0.9604
$G$
66 Fanta Ajman 0 2 10.4 1E+30 2
$C$
67 Sprite Dubai 0 1.72872 11.14064 1E+30 1.72872
$D$
67 Sprite Al-ain 0 0.9604 11.5248 1E+30 0.9604
$E$6
7 Sprite Sharzah 20.4 0 8.6436 0.38416 844.6436
$F$6
7 Sprite Fujairah 13.4 0 10.75648 0.19208
846.7564
8
$G$
67 Sprite Ajman 15.4 0 9.4 0.4 845.4
$C$
68 Coke Dubai 0 0.9604 11.5248 1E+30 0.9604
$D$
68 Coke Al-ain 0 4.763584 13.4456 1E+30 4.763584
$E$6
8 Coke Sharzah 0
7.229891
2 13.4456 1E+30
7.229891
2
$F$6
8 Coke Fujairah 26.4 0 9.604 0.9604 0.883568
$G$
68 Coke Ajman 0 2.043152 8 1E+30 2.043152
$C$
69 Fanta Dubai 54.4 0 8.6436 0.9604 788.6436
$D$
69 Fanta Al-ain 36 0 11.33272 0.19208
791.3327
2
$E$6
9 Fanta Sharzah 0 0.76832 8.45152 1E+30 0.76832
$F$6
9 Fanta Fujairah 26.8 0 9.604 0.9604 789.604
$G$
69 Fanta Ajman 30.8 0 8.4 2 788.4
$C$
70 Sprite Dubai 27.2 0 9.41192 1.72872
845.4119
2
$D$
70 Sprite Al-ain 18 0 10.5644 0.9604 846.5644
$E$7
0 Sprite Sharzah 0 0.38416 9.02776 1E+30 0.38416
$F$7
0 Sprite Fujairah 0 0.19208 10.94856 1E+30 0.19208
$G$
70 Sprite Ajman 0 0.4 9.8 1E+30 0.4
$C$
51 Coke Depot 1 0 0 7.6832 15.3664
2.012998
4
$D$
51 Coke Depot 2 0 3.07328 7.6832 1E+30 3.07328
$E$5
1 Coke Depot 3 0 7.6832 15.3664 1E+30 7.6832
$F$5
1 Coke Depot 4 72 0 3.07328 0 1E+30
36 | P a g e

Downstream supply chain Optimisation
$C$
52 Fanta Depot 1 0 0 15.3664 3.8416
7.421971
2
$D$
52 Fanta Depot 2 0 0 11.5248 0.76832 3.956848
$E$5
2 Fanta Depot 3 0 3.8416 19.208 1E+30 3.8416
$F$5
2 Fanta Depot 4 0 7.6832 19.208 1E+30 7.6832
$C$
53 Sprite Depot 1 0 0 11.5248 9.98816
3.595737
6
$D$
53 Sprite Depot 2 0 5.37824 19.208 1E+30 5.37824
$E$5
3 Sprite Depot 3 0 0.76832 15.3664 1E+30 0.76832
$F$5
3 Sprite Depot 4 0 0 12.29312 4.60992
5.677884
8
$C$
54 Coke Depot 1 0 15.3664 23.0496 1E+30 15.3664
$D$
54 Coke Depot 2 72 0 4.60992
2.18971
2 1E+30
$E$5
4 Coke Depot 3 0 0 7.6832 7.6832 0.883568
$F$5
4 Coke Depot 4 0 0 3.07328 1E+30 0
$C$
55 Fanta Depot 1 0 3.8416 19.208 1E+30 3.8416
$D$
55 Fanta Depot 2 0 0.76832 12.29312 1E+30 0.76832
$E$5
5 Fanta Depot 3 0 0 15.3664 3.8416
9.116116
8
$F$5
5 Fanta Depot 4 0 0 11.5248 7.6832
5.086278
4
$C$
56 Sprite Depot 1 0 9.98816 21.51296 1E+30 9.98816
$D$
56 Sprite Depot 2 0 0 13.82976 5.37824
7.406604
8
$E$5
6 Sprite Depot 3 0 0 14.59808 0.76832
8.551401
6
$F$5
6 Sprite Depot 4 0 4.60992 16.90304 1E+30 4.60992
$C$
81 Coke Dubai 0 2.765952 5.647152 1E+30 2.765952
$D$
81 Coke Al-ain 0
2.012998
4
3.011814
4 1E+30
2.012998
4
$E$8
1 Coke Sharzah 0
3.349875
2 1.882384 1E+30
3.349875
2
$F$8
1 Coke Fujairah 0 3.726352 5.647152 1E+30 3.726352
$G$
81 Coke Ajman 0
13.72635
2 12 1E+30
13.72635
2
$C$
82 Fanta Dubai 0
10.48756
8 3.764768 1E+30
10.48756
8
$D$
82 Fanta Al-ain 0
7.421971
2
3.388291
2 1E+30
7.421971
2
37 | P a g e

Downstream supply chain Optimisation
$E$8
2 Fanta Sharzah 0
12.20092
16
4.517721
6 1E+30
12.20092
16
$F$8
2 Fanta Fujairah 0
10.65659
84
4.894198
4 1E+30
10.65659
84
$G$
82 Fanta Ajman 0 15.3664 8.4 1E+30 15.3664
$C$
83 Sprite Dubai 0 6.81884 4.70596 1E+30 6.81884
$D$
83 Sprite Al-ain 0
3.595737
6
2.635337
6 1E+30
3.595737
6
$E$8
3 Sprite Sharzah 0
7.022444
8
4.141244
8 1E+30
7.022444
8
$F$8
3 Sprite Fujairah 0
3.780134
4
3.011814
4 1E+30
3.780134
4
$G$
83 Sprite Ajman 0 8.5248 6.4 1E+30 8.5248
$C$
84 Coke Dubai 36 0 3.764768 0.9604 0.921984
$D$
84 Coke Al-ain 36 0 1.882384
0.92198
4
814.6820
16
$E$8
4 Coke Sharzah 0
2.466307
2 1.882384 1E+30
2.466307
2
$F$8
4 Coke Fujairah 0 0.9604 3.764768 1E+30 0.9604
$G$
84 Coke Ajman 0 2.842784 2 1E+30 2.842784
$C$
85 Fanta Dubai 0 7.58716 4.70596 1E+30 7.58716
$D$
85 Fanta Al-ain 0 3.956848 3.764768 1E+30 3.956848
$E$8
5 Fanta Sharzah 0 7.606368 3.764768 1E+30 7.606368
$F$8
5 Fanta Fujairah 0
5.309091
2
3.388291
2 1E+30
5.309091
2
$G$
85 Fanta Ajman 0 9.1248 6 1E+30 9.1248
$C$
86 Sprite Dubai 0
8.559084
8
4.141244
8 1E+30
8.559084
8
$D$
86 Sprite Al-ain 0
7.406604
8
4.141244
8 1E+30
7.406604
8
$E$8
6 Sprite Sharzah 0
8.574451
2
3.388291
2 1E+30
8.574451
2
$F$8
6 Sprite Fujairah 0
8.343955
2
5.270675
2 1E+30
8.343955
2
$G$
86 Sprite Ajman 0 11.42976 7 1E+30 11.42976
$C$
87 Coke Dubai 0 0.883568 3.764768 1E+30 0.883568
$D$
87 Coke Al-ain 0 4.648336 5.647152 1E+30 4.648336
$E$8
7 Coke Sharzah 0
8.997027
2 7.529536 1E+30
8.997027
2
$F$8
7 Coke Fujairah 0 5.608736 7.529536 1E+30 5.608736
38 | P a g e

Downstream supply chain Optimisation
$G$
87 Coke Ajman 0 3.726352 2 1E+30 3.726352
$C$
88 Fanta Dubai 0
10.11109
12
3.388291
2 1E+30
10.11109
12
$D$
88 Fanta Al-ain 0
9.116116
8
5.082436
8 1E+30
9.116116
8
$E$8
8 Fanta Sharzah 0
13.14211
36
5.458913
6 1E+30
13.14211
36
$F$8
8 Fanta Fujairah 0
12.53898
24
6.776582
4 1E+30
12.53898
24
$G$
88 Fanta Ajman 0 10.9664 4 1E+30 10.9664
$C$
89 Sprite Dubai 0 9.89212 4.70596 1E+30 9.89212
$D$
89 Sprite Al-ain 0
8.551401
6
4.517721
6 1E+30
8.551401
6
$E$8
9 Sprite Sharzah 0
12.54282
4 6.588344 1E+30
12.54282
4
$F$8
9 Sprite Fujairah 0
10.24170
56
6.400105
6 1E+30
10.24170
56
$G$
89 Sprite Ajman 0 8.59808 3.4 1E+30 8.59808
$C$
90 Coke Dubai 0 2.804368 9.41192 1E+30 2.804368
$D$
90 Coke Al-ain 0 0.921984 5.647152 1E+30 0.921984
$E$9
0 Coke Sharzah 40.8 0
2.258860
8
2.46630
72
812.2157
088
$F$9
0 Coke Fujairah 0.4 0 5.647152
0.88356
8 2.043152
$G$
90 Coke Ajman 30.8 0 2
2.04315
2
811.9568
48
$C$
91 Fanta Dubai 0 9.469544 6.588344 1E+30 9.469544
$D$
91 Fanta Al-ain 0
5.086278
4
4.894198
4 1E+30
5.086278
4
$E$9
1 Fanta Sharzah 0
6.476937
6
2.635337
6 1E+30
6.476937
6
$F$9
1 Fanta Fujairah 0 6.62676 4.70596 1E+30 6.62676
$G$
91 Fanta Ajman 0 7.5248 4.4 1E+30 7.5248
$C$
92 Sprite Dubai 0 8.528352 5.647152 1E+30 8.528352
$D$
92 Sprite Al-ain 0
5.869964
8
4.141244
8 1E+30
5.869964
8
$E$9
2 Sprite Sharzah 0 9.296672 5.647152 1E+30 9.296672
$F$9
2 Sprite Fujairah 0
5.677884
8
4.141244
8 1E+30
5.677884
8
$G$
92 Sprite Ajman 0 6.49312 3.6 1E+30 6.49312
Constraints
39 | P a g e

Downstream supply chain Optimisation
Fin
al Shadow
Constrai
nt
Allowab
le
Allowabl
e
Cell Name
Val
ue Price
R.H.
Side
Increas
e
Decreas
e
$G$
73 Coke Ajman 90.4 -806 0 90.4 1E+30
$G$
74 Fanta Ajman 40.8 -780 0 40.8 1E+30
$G$
75 Sprite Ajman 49.2 -836 0 49.2 1E+30
$G$
76 Coke Ajman 98.4 -806 0 98.4 1E+30
$G$
77 Fanta Ajman 148 -780 0 148 1E+30
$G$
78 Sprite Ajman 45.2 -836 0 45.2 1E+30
$C$
93 Coke Dubai 54.4 816.5644 54.4 1E+30 18.4
$D$
93 Coke Al-ain 36
814.6820
16 36 36 18.4
$E$9
3 Coke Sharzah 40.8
812.2157
088 40.8 0.4 26.4
$F$9
3 Coke Fujairah 26.8 815.604 26.8 1E+30 26.4
$G$
93 Coke Ajman 30.8
811.9568
48 30.8 0.4 26.4
$C$
94 Fanta Dubai 54.4 788.6436 54.4 1E+30 54.4
$D$
94 Fanta Al-ain 36
791.3327
2 36 1E+30 36
$E$9
4 Fanta Sharzah 40.8 787.6832 40.8 1E+30 40.8
$F$9
4 Fanta Fujairah 26.8 789.604 26.8 1E+30 26.8
$G$
94 Fanta Ajman 30.8 788.4 30.8 1E+30 30.8
$C$
95 Sprite Dubai 27.2
845.4119
2 27.2 1E+30 27.2
$D$
95 Sprite Al-ain 18 846.5644 18 1E+30 18
$E$9
5 Sprite Sharzah 20.4 844.6436 20.4 1E+30 20.4
$F$9
5 Sprite Fujairah 13.4
846.7564
8 13.4 1E+30 13.4
$G$
95 Sprite Ajman 15.4 845.4 15.4 1E+30 15.4
$C$
57 Coke Depot 1 0 0 88 1E+30 88
$D$
57 Coke Depot 2 72 -2.189712 72 18.4 36
$E$5
7 Coke Depot 3 0 0 88 1E+30 88
$F$5
7 Coke Depot 4 72 -0.883568 72 26.4 0.4
40 | P a g e

Downstream supply chain Optimisation
$C$
58 Fanta Depot 1 0 0 88 1E+30 88
$D$
58 Fanta Depot 2 0 0 72 1E+30 72
$E$5
8 Fanta Depot 3 0 0 88 1E+30 88
$F$5
8 Fanta Depot 4 0 0 72 1E+30 72
$C$
59 Sprite Depot 1 0 0 44 1E+30 44
$D$
59 Sprite Depot 2 0 0 36 1E+30 36
$E$5
9 Sprite Depot 3 0 0 44 1E+30 44
$F$5
9 Sprite Depot 4 0 0 36 1E+30 36
$C$
57 Coke Depot 1 0 813.6832 0 88 0
$C$
58 Fanta Depot 1 0 795.3664 0 88 0
$C$
59 Sprite Depot 1 0 847.5248 0 44 0
$D$
57 Coke Depot 2 72
812.7996
32 0 36 18.4
$D$
58 Fanta Depot 2 0 791.5248 0 72 0
$D$
59 Sprite Depot 2 0
849.8297
6 0 36 0
$E$5
7 Coke Depot 3 0 813.6832 0 88 0
$E$5
8 Fanta Depot 3 0 795.3664 0 88 0
$E$5
9 Sprite Depot 3 0
850.5980
8 0 44 0
$F$5
7 Coke Depot 4 72
809.9568
48 0 0.4 26.4
$F$5
8 Fanta Depot 4 0 791.5248 0 72 0
$F$5
9 Sprite Depot 4 0
848.2931
2 0 36 0
41 | P a g e