Data Exercise, Week 6

For this week’s data exercise, you’ll get a break from R and get used to a new tool.

The learning goals for this week are:

The first step is to download and save your own copy of the oil sands model posted on eClass. Make sure you put it in the same directory as your other R work since we may eventually use it with R.

Modifying the Excel Oil Sands Model

For the first part of this exercise, I would like to see you do three things in the Excel model:

  • Add oil price and exchange rate assumptions from the Sproule price deck to your Excel model;
  • Calculate after-tax cash flows for the oil sands project;
  • Calculate the NPV10 and the IRR for the oil sands project;

Adding commodity prices

Using the latest Sproule Price Deck, you should add five elements to your Excel:

  • the WTI price forecast in USD
  • the NIT price forecast
  • the USD/CAD exchange rate
  • the long-term percentage discount between heavy and light oil (WCS vs Edmonton Light)
  • the long-term premium between Edmonton condensate (diluent) prices (check the Natural Gas Liquids tab on the Sproule deck)

You can also add a long term AESO electricity price of $76.50 (from the NGX Fin FF, FP for AESO Flat, (CA/MWh), Alberta forward prices which you can see using the drop down menu here).

You’ll have to calculate the percentage premium for condensate and the discount for WCS. Look at how it’s used in the model and reverse engineer the calculation.

Hint: use paste special, with values only transposed to paste from the Sproule deck into the Excel model. You should be able to do this in sheets as well, although perhaps in two steps.

Calculating after-tax cash flows

Create a new row (or if you prefer, rows) at the bottom of your Excel. You want to get the sum of revenues net of operating and capital costs, financing charges (just the interest payments), royalties, and taxes. You might find it useful to make a summary table with a line for each of these and then a total.

Note: there’s a reason why I didn’t include financing charges in my operating or capital cost calculations earlier in the sheet: they are not allowable costs for the purposes of the royalty regime. They are deductible against taxes.

Calculating IRR and NPV10

Calculate the IRR of the after-tax cash flows and the project’s after tax NPV10 value at the Sproule prices.

Sensitivity

Do the same calculations as above for the two CER scenarios (Current and Evolving Policies). Exchange rates are in the macro indicators file, but be careful because they are in $CA/$US, and the model is coded in $US/$CA. You already know where to find the benchmark (WTI, WCS, and NIT) prices.

Hints: The CER does not have separate condensate or diluent price series, so just use the same percentage premium you calculated from Sproule applied to CER’s WTI in Canadian dollars. Also, I’m reminding you again to be careful because the CER exchange rates are reported in $CA/$US, and the model is coded in $US/$CA.