Natural Resource and Energy Economics Problem
Note: This problem set was significantly shortened due to the cancelled classes. You should have the knowledge to complete both of these problems.
Please do your write-up in Word, pasting relevant Excel tables or graphs into your document. Do not turn in Excel worksheets. Make your HW look as professional as possible.
1. Suppose the inverse demand function for a depletable resource is given by P = 10 - Q and the marginal cost of supplying it is $2.00. Assume the discount rate is 10%.
a) If 10 units are to be allocated between two periods, in a dynamic efficient allocation, how much would be allocated in each period?
b) What would the efficient price be in the two periods?
c) What would the marginal user cost be in each period?
d) If there were 20 units available, what would the marginal user cost be in each period?
2. The goal of this problem is to develop a more complex cash flow model using Excel. Your goal is to create an Excel sheet that gives you the same answers as the attached.
Assume initially:
- Costs of $10,000 in year 1, growing at 3% per year.
- Income (revenue) of $9000 in year 1, increasing at 6% each year
- 10-year project, with payments due on January 31 of each year
- Discount rate = 10%
a) Calculate the project's NPV using the XNPV formula (calculates NPV that includes dates of actual costs and income).
b) Calculate the project's IRR using the XIRR formula (uses dates as above)
c) Create a 1-way data table varying discount rate
d) Create a 2-way data table varying cost growth rates on vertical axis and revenue growth rates on the horizontal axes. Use conditional formatting to cases in which NPV>0. Explain what this table shows you about the financial viability of this project. Be specific.
e) Using the goal seeking function (under What-if Analysis), find the discount rate at which the NPV equals zero.
f) Prepare a 2-way data table that calculates the IRR, varying costs and revenues. Use conditional formatting to highlight IRR's greater than 20%.
g) Using your original assumptions above, how does the NPV and IRR change if you assume all payments and costs happen on December 1 of each year? Why did they change?
Your basic model should look something like this. You might want to use color coding to highlight assumptions.
Attachment:- Assignment File.rar