ECOS3997 Lab 5 Mining Fund Allocation Model
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
ECOS3997 Lab 5
Mining Fund Allocation Model
The spreadsheet model used for this tutorial is available on Canvas: MineRehabilitationFund.XLS. This is the spreadsheet you will use to generate your results for the Written Report assignment. Open the file. On the Model workbook/tab is the Excel model which has been set up for the tutorial and the assignment. In the Parameters workbook/tab are the parameters you will use for the assignment (your individual parameters appear in the same tow as your SID).
The tutorial parameters are different to any of the parameters that have been issued for the assignment.
The model used is the one presented in the lecture for Week 5:
Minimise total damage and rehabilitation costs:
=1 =1 =1
subject to:
=1 =1 =1
Where:
,0 : initial volume of contamination from Z at site i in period 0
, +1 =(1 + ) , is the growth of volume (0 < < 1)
, = /, : average contamination at site i in period t
, = (, , , ): damage during t is a function of volume contaminated and average concentration.
: costs of cleaning up site i, K is the present value of total funds available for clean up. , : binary choice variable ( , = 1 if site has been cleaned up, 0 otherwise) , = (1 − , ) (, , , ) for t=1, 2,…T
Funds not spend in t are available to earn interest (they increase by factor (1 + ).
There are 5 abandoned mine sites: Muswellbrook (1), Vales Point(2), Munmorah (3), Liddell (4) and Wallerawang (5)
Assume advice is needed for a 10 year horizon, t=0, 1, 2…, T=9.
The particular damage function with no rehabilitation: , = ,
The parameters for this tutorial are:
parameters
Zi
V i, 0
αi
Ki
βi
site i=1
5 10 0.01 50
1
site i=2
50 80 0.2 500
0.01
site i=3
20 20 0.02 200
0.4
site i=4
10 10 0.02 100
0.5
site i=5
30
30
0.04
300
0.2
The present value of total available funds, K=$380 (in $m) and the discount rate assumed is 0.05.
1. Open the Model tab. The excel formulae have been entered for you. Acquaint yourself with how they represent the parameters and functions of the model.
2. Compare/describe the 5 sites in terms of their initial parameter values.
3. Graph the damage functions. Comment.
4. Based on damage costs, which site might you want to clean up first? Why?
5. Use Solver to determine the optimal clean up schedule. (note you do not have to set up the dialogue box in Solver, this has been done for you). The Solver will take a couple of minutes to find a solution.
6. Report the solution: Optimal total clean up cost, the present value of cleaning up and the present value of any remaining damage costs.
7. Repeat 5, and 6 for a range of discount rates 0.02 and 0.1. Is the solution sensitive to changes in the discount rate?
8. Consider the case where there is debate about the parameter values for βi at Vales Point (site 2). In response to the debate, conduct a preliminary sensitivity analysis by increasing the parameter in steps of 0.01 and resolving the model. Would it take much of a revision to parameter value in order to completely change the recommended
clean up schedule?
2022-04-21