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?