An oil company has to transport oil products from three sites (S1, S2, S3), through two refineries (R1, R2) and finally to three markets (C1, C2, C3). The supply (in 1,000 barrels per day) are given in Table 1.

Table 1. (Site supply)

Site Supply

S1 1,500

S2 750

S3 1,200

Table 2 below shows the costs in $1,000 of transporting 1,000 barrels of crude oil between sites and refineries. It also has the costs of refining 1,000 barrels of crude oil at each refinery, and maximum capacities (in 1,000 barrels per day) of each refinery. Table 3 below shows the costs in $1,000 of transporting 1,000 barrels of refined fuel between refineries and customers. It also gives customer demands (in 1,000 barrels per day).

a) Given that all customer demands must be fulfilled, while trying to maintain costs at the lowest possible level, formulate the adequate model to solve the problem.

b) Obtain the solution to the previous problem using Excel.

c) Short report interpreting the obtained solution.

Table 2. Site to refinery costs, refinery costs, and refinery capacities

From/to R1 R2

S1 20 7

S2 12 10

S3 15 8

Refining Costs 7 6

Capacity 1000 1200

Table 3. Refinery to customer costs and customer demands

From/to C1 C2 C3

R1 6 8 5

R2 4 10 7

Demand 500 800 900

Note: Assume there is no waste in the refining process, so that the same quantity that enters goes out.