open the microsoft excel workbook interactive


Open the Microsoft Excel workbook Interactive model, Economics.xls.

On the first tab you will see a figure similar to Figure 3.2. As before, the white cells are cells you can change by entering different numbers. You can experiment with the crude price, the operating cost, the quantity of each product, and its selling price. The exception is the quantity of residual fuel oil, which is calculated to match the total quantity of crude input (we are ignoring "refinery gain"; see the footnote in the Weekly Notes). As you change the quantities of the other products the fuel oil quantity will automatically adjust.

Near the bottom you will see the outputs, in particular the "Margin," which is calculated using the equation in the Weekly Notes. As with last week's model, clicking on the "Reset to Base Values" button restores the inputs to their starting point.

We will treat this "Distillation Refinery" as if it were the marginal global refinery. Economic theory says the marginal producer in any market will be one that operates at zero profit (more correctly, zero cash flow); other lower-cost producers will operate with positive profit, but the highest cost "marginal" producer operates at zero profit. The marginal producer has the last unit of production needed to match supply with demand: if the marginal producer is operating at positive profit, an even higher cost producer will enter and provide additional supply. When supply matches demand, any producer with lower cost will be producing; any producer with higher cost will be shut down.

1. Reset to base values. What crude price will cause this refinery to have a zero margin?

2.Now suppose the refiner decides to consider running different, heavier crude. The distilled fractions from this crude ("yields") are shown below. For this marginal refiner to decide to switch, what is the maximum price for the heavier crude that would cause the refiner to be indifferent between the two crudes?

833_Interactive model.png

3. Does the heavy crude have lower or higher value from the base crude? Why?

4. If this is the global marginal refiner, what is the crude price differential between these two crudes?

5. Do the same analysis comparing the base crude to lighter crude, whose yields are shown below. What is the break-even price that will make the refiner indifferent between these two crudes?

1599_Interactive model1.png

6. Does the lighter crude have lower or higher value than the base crude? Why?

7. If this is the global marginal refiner, what is the crude price differential between these two crudes?

8. Reset to base values. Now change the gasoline price from $105/Barrel to $110/Barrel. What is the crude price that creates a zero margin? Is it higher or lower than the crude price you found in question 1?

9. Reset to base values. Now change the residual fuel price from $40/Barrel to $45/Barrel. What is the zero-margin break-even crude price now? Based on the results you found in questions 8 and 9, how would you characterise the relationship between product prices and crude prices?

10. Reset to base values. Now suppose the product prices change as shown in the table below. Re-do the analysis of heavy and light crudes to determine their relative prices. What are the crude price differentials now? How do they compare to those you found before?

139_Interactive model2.png

 

11. Based on your answers in question 10, how would you characterise the influence of product prices on crude price differentials?

12. Move to the Conversion Investment tab. In this analysis, we assume for simplicity that product prices, crude prices and operating costs stay fixed for the life of the refinery (a real analysis would treat this differently, of course). Again, the white cells are cells you can change. There are a few more inputs here than in the Distillation Refinery tab. You still see the same input cell for the crude price and the same cells for the product quantities out of distillation. What is new here are a cell inside the Conversion block to accommodate the capital cost and a cell for the conversion unit's operating cost. Also, the light product prices on the right are now input as deltas to the gasoline price.

There are a few other inputs you can change, although it will not be necessary for purposes of this assignment. These are the capital spend profile, the discount rate, the depreciation rate, and the corporate income tax rate. (For those financial aficionados among you, we are assuming an exponential depreciation rate and are not distinguishing "book" tax from "tax" tax.)

Note that the conversion unit redistributes product yields from distillation in favour of light products (column R).

Note: A lower pane on this tab has been "frozen" so you can scroll down to see the financials all the way to the NPV calculation. If your screen resolution gives you problems with this, "unfreeze" the panes and just split the screen. To avoid problems you may have with this, the NPV is also shown in row 3 (cell F3).

Enter for the crude price the number you calculated in question 1 (this refinery is no longer the marginal refinery, which sets the crude price, but a more efficient one). (Note: To permit the model to be able to repeatedly reset to base values for this series of questions, the correct value has already been input, which gives away the answer to question 1, and also this one, but ...consider it a bonus. Not sure how many of you will have looked ahead to this question anyway...).

A subtlety is that to calculate the value of the conversion investment, we must only account for the incremental revenue gains that come from the investment and the incremental costs it incurs. We need to calculate the NPV with the conversion investment and subtract out the NPV without it. By setting the crude price to make the distillation component realise zero margin, we accomplish the same thing (you can look at the spreadsheet logic to see that this can be done either way).

The question: What is the (incremental) NPV of this investment in conversion capacity?  

13. What is the maximum capital cost of the conversion unit for which a refiner should proceed with the investment?

14. Reset to base values. Change the price delta for Jet Fuel & Kerosene from ($5) to zero. What is the maximum viable capital cost now?

15. What does this tell you about the significance of estimating future product prices?

16. Reset to base values. Now put in the distillation product yields (column J) for heavy crude as given in question 2. Also enter the price of heavy crude you calculated in question 2. What is the maximum viable capital cost now?

17. What does this tell you about the refiner's choice of crude type given an investment in conversion capacity?

18. Reset to base values. Now enter the distillation product yields for light crude as given in question 5. Also put in the price of heavy crude you calculated in question 5. What is the maximum viable capital cost now?

19. Given this analysis, what can you say about how future expectations of light versus heavy crude supply might affect refiner investment decisions?

20. In a real investment decision like this, there are many uncertainties.  What inputs to this model should properly be considered uncertainties if you were contemplating doing a risk analysis?

Download:- Economics.xls

Solution Preview :

Prepared by a verified Expert
Microeconomics: open the microsoft excel workbook interactive
Reference No:- TGS0486142

Now Priced at $70 (50% Discount)

Recommended (90%)

Rated (4.3/5)