1. Reset to base values. What crude price will cause 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?

   

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?

   

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?
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?  

1. What is the maximum capital cost of the conversion unit for which a refiner should proceed with the investment?
2. Reset to base values. Change the price delta for Jet Fuel & Kerosene from ($5) to zero. What is the maximum viable capital cost now?  
3. What does this tell you about the significance of estimating future product prices?
4. 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?
5. What does this tell you about the refiner's choice of crude type given an investment in conversion capacity?
6. 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?
7. Given this analysis, what can you say about how future expectations of light versus heavy crude supply might affect refiner investment decisions? 
8. 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?