ECOS3997 Lab 5 Mining Fund Allocation Model Solutions
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 set of 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 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.
Answer: Initial volume of contamination highest ate site 2, which also has the highest clean up costs and the lowest damage coefficient. Sites 3 , 4 and 5 have similar parameters (but 5 has higher αi and clean up costs of those 3). Site 1 has slowest growth of contaminant, but the highest damage coefficient.
3. Graph the damage functions. Comment.
|
200
0 1 2 3 4 5 6 7 8 9 10 |
All the damage costs are decreasing through time except for site 2 (the orange line). For site 2, 
> 
. When this is the case damage costs will be increasing.
4. Based on damage costs, which site might you want to clean up first? Why? Answer: Site 2 has increasing damages-it might make sense to clean this site,
however damages in t=10 are still larger for site 5 (165. 1463) so this might also be a candidate- however this is considering damage costs alone, we really need to solve the optimisation problem.
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.
Answer: Optimal to clean up sites 1,3 and 4 immediately and leave sites 2 and 5 unrehabilitated. Present value of damage costs = 2769, present value of total costs= 3119. Cost of clean up = 350 ( 30 left over).
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?
Answer: for discount rate 0.02 and 0. 1, the schedule remains the same (PVs change). Discount rate would have to be about 0.2 for schedule to change, so solution is robust to reasonable 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.1 and resolving the model. Would it take much of a revision to parameter value in order to completely change the recommended clean up schedule?
Answer: If the value of βi changes to 0.06, then it becomes optimal to clean up site 2 only in time period t=6. Present value of damages = 6594, present value of total costs= 6968, and K=373. 1077.
2022-04-21