2306Afe Quantitative Methods For Business Assessment Answer

1. Your company needs to acquire an optimal mix of oil crude types from a group of supplying nations. We assume that the demand (in Millions of Barrels per year) for oil crude types will be 5 Light Crude; 5 Intermediate Crude; 0.5 Heavy Crude in the first year. Furthermore, as your country is growing rapidly you assume that the demand for all petroleum types will grow at a rate of 1.5%/year. The four countries that can supply your oil demand have the following assumed prices and available supply.

Each ship can carry 1 million barrels of oil. Assume the price for:
Light Crude is $45/barrel in year 1 and increases at a rate of 1.6% per year for 20 years.
Intermediate Crude is $35/Barrel in year 1 increases at a rate of 1.7% per year for 20 years.
Heavy Crude is $20/Barrel in year 1 increases at a rate of 1.8% per year for 20 years.
Your linear programme should decide how many ships you should purchase full of oil from each country in order to minimize your total cost of supply for each year for the 20 year forecast.

2. After assuming the least cost mix of supplies has now been decided upon from question 1, you must now build you Oil Empire by obtaining a refinery. Using your superior knowledge of Net Present Value and Internal Rates of Return, you must now devise a plan to purchase a $10 Million refinery. Assuming that your refinery can take the above oil supply (Light, Intermediate and Heavy Crudes), you now need to make some Diesel and Gasoline. The refinery uses 3/4 of the oil purchased to make Gasoline and the remaining 1/4 to make Diesel.
To find the potential Total Revenue for each year over the 20 years forecasted demand state the output from your refinery and total revenue
?Using a Base price of $1.8/L for Gasoline and $1.85/L Diesel
?Assuming the price of both fuels increase at a rate of 1.9%/year
?Assume there are 157L of oil per barrel
Using the Total Revenue from Gasoline and Diesel Sales, your costs of obtaining the oil and a $0.1/L refinery cost, establish the NPV of the project assuming a discount rate of 10%. Then calculate the IRR of the project.

3.For the following sub-scenarios write a short analysis on:
?Discuss how/if this project would be profitable at a range of NPV values 6% to 12%
?Discuss how the Capital Cost effects the profitability of the project
?Discuss how if the price of Gasoline only increased at a rate of 1.2% how profitability would change.
?Discuss how if the maximum supply from Saudi Arabia dropped by 30% would affect profitability.
?Discuss if the number of ships from Australia could be increased at a shipping cost of $2.5 would affect your profitability.

The table below summarises the optimal cost of purchasing crude oil products for supply to the Fredonia state in the next 20 years

Year	Country	Number of ships	Total cost incurred
1	Saudi Arabia	6
	USA	5
	Australia	1	$481,300,000.00
2	Saudi Arabia	6
	USA	5
	Australia	1	$488,624,750.00
3	Saudi Arabia	7
	USA	5	$502,140,936.25
4	Saudi Arabia	7
	USA	5	$509,772,091.85
5	Saudi Arabia	7
	USA	5	$517,529,783.73
6	Saudi Arabia	7
	USA	5	$525,416,113.16
7	Saudi Arabia	7
	USA	5	$533,433,216.40
8	Saudi Arabia	7
	USA	5	$541,583,265.20
9	Saudi Arabia	6
	Dubai	1
	USA	5
	Australia	1	$580,884,070.91
10	Saudi Arabia	5
	USA	5
	Australia	3	$589,320,247.09
11	Saudi Arabia	7
	USA	5	$566,853,348.15
12	Saudi Arabia	7
	USA	5	$575,557,628.43
13	Saudi Arabia	7
	USA	5	$584,406,267.13
14	Saudi Arabia	4
	Dubai	1
	USA	5
	Australia	2	$566,514,092.12
15	Saudi Arabia	4
	USA	6
	Australia	2	$578,475,346.31
16	Saudi Arabia	5
	USA	5
	Australia	1	$549,557,209.64
17	Saudi Arabia	5
	USA	5
	Australia	1	$558,094,003.10
18	Saudi Arabia	6
	USA	5	$573,861,050.27
19	Saudi Arabia	2
	USA	6
	Australia	3	$550,425,147.55
20	Saudi Arabia	5
	USA	5	$533,019,327.11

2. A table summary of revenue and expenses generated from the oil supply

Year	Gasoline revenue	Diesel revenue	Refinery cost	Cost of purchases	Net Cashflow
0					-$10,000,000.00
1	$2,225,475,000.00	$762,431,250.00	$164,850,000.00	$481,300,000.00	$2,341,756,250.00
2	$2,301,775,410.38	$788,571,205.41	$167,322,750.00	$488,624,750.00	$2,434,399,115.78
3	$2,380,691,780.32	$815,607,369.18	$169,832,591.25	$502,140,936.25	$2,524,325,622.00
4	$2,462,313,798.01	$843,570,467.84	$172,380,080.12	$509,772,091.85	$2,623,732,093.88
5	$2,546,734,226.57	$872,492,281.33	$174,965,781.32	$517,529,783.73	$2,726,730,942.85
6	$2,634,049,009.53	$902,405,679.19	$177,590,268.04	$525,416,113.16	$2,833,448,307.52
7	$2,724,357,379.82	$933,344,657.90	$180,254,122.06	$533,433,216.40	$2,944,014,699.26
8	$2,817,761,972.59	$965,344,379.50	$182,957,933.89	$541,583,265.20	$3,058,565,153.00
9	$2,914,368,941.82	$998,441,211.55	$185,702,302.90	$580,884,070.91	$3,146,223,779.56
10	$3,014,288,080.99	$1,032,672,768.49	$188,487,837.44	$589,320,247.09	$3,269,152,764.94
11	$3,117,632,947.85	$1,068,077,954.35	$191,315,155.01	$566,853,348.15	$3,427,542,399.05
12	$3,224,520,993.46	$1,104,697,007.02	$194,184,882.33	$575,557,628.43	$3,559,475,489.72
13	$3,335,073,695.72	$1,142,571,543.91	$197,097,655.57	$584,406,267.13	$3,696,141,316.94
14	$3,449,416,697.38	$1,181,744,609.29	$200,054,120.40	$566,514,092.12	$3,864,593,094.15
15	$3,567,679,948.85	$1,222,260,723.22	$203,054,932.20	$578,475,346.31	$4,008,410,393.56
16	$3,689,997,855.90	$1,264,165,932.11	$206,100,756.19	$549,557,209.64	$4,198,505,822.18
17	$3,816,509,432.39	$1,307,507,861.10	$209,192,267.53	$558,094,003.10	$4,356,731,022.85
18	$3,947,358,458.28	$1,352,335,768.11	$212,330,151.54	$573,861,050.27	$4,513,503,024.58
19	$4,082,693,643.02	$1,398,700,599.92	$215,515,103.82	$550,425,147.55	$4,715,453,991.58
20	$4,222,668,794.57	$1,446,655,049.99	$218,747,830.37	$533,019,327.11	$4,917,556,687.08

At a discount rate of 10%, the Net Present Value (NPV) is given as

NPV	$26,116,989,970.34
While the is
IRR	23419.58%

3. At 6% the NPV is $36,796,298,366.68, this value is greater than 0 hence the project is profitable.

On the other hand, at 12% the NPV is obtained as $22,455,372,302.21 which is also greater than 0. This means the project will be profitable will remain profitable when the rate of discount fluctuates between 6% and 12%

• Increase in the cost of capital decreases the NPV while a decrease increases the NPV. The initial capital outlay is negatively proportionate to the profitability of the project.
• In cases where the price of gasoline only increases by 1.2% NPV will drop to $25,966,112,240.31. This indicates a fall in the level of profitability by 0.5777%.
• The maximum number of ships that the firm will need from Saudi Arabia in a single year will be 7. A drop in the maximum number of ships available from Saudi Arabia from 20 to 14 still allows the firm to obtain 7 ships. This means there will be no impact on profitability as the firm will not need to review its crude oil supply structure.
• The increase in shipment cost from Austrlaia from $ 1.8 to $ 2.5 per barrel will increase the cost of purchases which in turn will leads to a drop in the profits of the refinery products. 

References

Allen-Zhu, Z. & Orecchia, L., 2015. Using Optimization to Break the Epsilon Barrier: A Faster and Simpler Width-Independent Algorithm for Solving Positive Linear Programs in Parallel. s.l.:ACM-SIAM Symposium.

Gerard, S. & AGhosh, D., 2010. Networks in; Springer, Text and Computer Exercises in Network Optimization, s.l.: Springer.

Sierksma, G. & Zwols, Y., 2015. Linear and Integer Optimization. Third ed. s.l.:CRC Press.