Cost Benefit Analysis Spreadsheet and Report Assignment
Part 1 must be submitted as a Microsoft Excel file.
Part 2 must be submitted as a Word file.
Part 1
Part 1 must be submitted as a Microsoft Excel file.
Prepare a spreadsheet to conduct a Benefit: Cost Analysis (BCA) of a project that is being considered for funding. (Details of the project are provided below.) The spreadsheet should contain at least the base-case numbers for the BCA, but it needs to be possible to change all of the input numbers. Assume that you are going to hand over the spreadsheet to other users in your organisation, so the spreadsheet needs to clear and transparent for them to use, with helpful labels and supporting text. Use appropriate measures to protect the spreadsheet from damage and errors.
You can assume that the users know about BCA.
You may utilise the information presented in the workshops, but you should make your spreadsheet from scratch, rather than just editing the spreadsheets
Part 2
Part 2 must be submitted as a Word file
Submit both files.
Provide a report describing and discussing the results of the BCA.
The report should not include a full descriiption of the project or the numerical assumptions that go into the BCA. Instead, the task is to provide just the section of a full report that focuses on the results. You can assume that the other sections have already been written and are available to the decision makers.
The report on results should include the following:
• The base-case results for the relevant decision criteria.
• An explanation for decision makers of why those decision criteria are relevant and how to interpret them.
• The distribution of benefits and costs amongst stakeholder groups.
• Comments on what the distributional results might imply about possible changes to the funding arrangements.
• Sensitivity analysis results that provide insights into the robustness of the base- case results.
• Comments on the robustness of the base-case results and what this might mean for decision making.
The word limit for part 2 is 1200 words. Figures, tables, references and appendices are not included in the word count, just the main test and headings.
The project to evaluate in Assignment 1
You work for a local government (L G) that is trying to decide whether to fund a project to restore an urban drain to a natural stream. The project is similar to the Banister Creek example that is included in Topic 1 of the course.
At this stage, this is the only project under consideration, so the decision makers within the LG need to make a yes/no decision about it. The LG expects to be able to obtain matching funds from the State Government if it goes ahead with the project. Given that state support, the LG has sufficient funds to fund its half of the project, so you do not need to account for a funding constraint.
However, there is a chance that the LG might soon decide to consider a number of similar projects in different locations. If so, there it would not have sufficient funds to do them all and it will be necessary to prioritise them. Your results from this BCA would be compared to BCAs for the other potential projects.
Your report should provide information relevant to these two different decision contexts, and advice to the decision makers on which decision criteria to use in the two cases.
The current project
The proposal is to restore a 200-metre section of Costello Creek in suburban Perth.
The restoration work would involve giving the creek a more natural shape, with meanders, riffles, fringing sedges, gentle sloping banks, and thick vegetation on the banks. The earth works involved to reshape the existing steep banks would be substantial, and erosion control matting would be used to stabilise the stream banks.
Anticipated benefits include improved amenity for local residents, improved water quality in the river that the creek joins, and reduced flow velocity of the stream during high-rainfall events to an acceptable level for public safety.
Without-project scenario: current conditions would continue indefinitely, with existing lack of amenity, poor water quality and safety risk. The surrounding suburb is well- established, so we do not expect a significant change in local population, with no significant demographic changes.
With-project scenario: The benefits are easily attributed to the project. They clearly would not have arisen without the project.
Time
Start year for analysis (year zero): 2021 Length of analysis: 40
Real discount rate: 4%, constant over time
Quantifying benefits
There are four stakeholder groups who would receive benefits or incur costs: local government, state government, local community, general community of Perth.
Most dollar values given below have been inflated or deflated to 2021. The exception is the benefit estimate quoted from Mazur and Bennett (2009), which is in 2008 dollars.
Local amenity. The benefits to local residents are expected to be similar (in percentage terms) to those estimated by Polyakov et al. (2017) for the restoration of Banister Creek. There are 250 properties that are close enough to the proposed restoration site to capture these benefits. The average value of houses in the suburb in 2021 (without the project) is $585,000.
Assume that the increase in house price resulting from the project occurs suddenly and fully in 2027.
The allocation of these benefits amongst the four stakeholders is 95% to local community, 5% to local government (via higher rates).
Water quality. The drain contributes nutrients to the Canning River. They largely come from surface water flows and would be reduced by around 90% once the vegetation was fully established (by 2034).
There is no existing study of non-market benefits from river water-quality improvements in Perth. It is suggested to use Mazur and Bennett (2009) as a starting point for estimating the benefits. For households in Sydney, they provide an estimate of the household willingness to pay per year for improving the health of waterways in the Lachlan catchment (current status is that 85% of the waterway has poor water quality). Their estimate of benefit is $0.35 per household per year per km of river with improved water quality, in 2008 dollars. You should adjust the value to allow for inflation, and for differences in average income between Perth and Sydney. Also, assume that they magnitude of the benefit from the project is equivalent to 0.5 km of river with improved water quality. Assume that this benefit is obtained (on average) by all households in Perth and that there are 760,000 households in Perth.
Assume that the benefit increases linearly starting in 2025 (the benefit is zero in 2024 and non-zero in 2025) and reaching its maximum level in 2034.
Improved public safety. Project will reduce the small existing probability of a fatality in the drain during flood conditions. Children sometimes play in the existing drain.
The new creek will have less steep banks so people can get out more easily.
Annual probability of one person drowning occurring in the drain without the project: 0.5%. (Assume zero probability of more than one person drowning.)
Annual probability of a drowning with the project: 0.1%. The benefit starts suddenly and fully in 2025, when the main earthworks are completed.
Use the value of a statistical life recommended by the Office of Best Practice Regulation (2020).
Attribute all of this benefit to local residents.
Behaviour change
Most of the benefits arise from the project creating new physical structures, so they mostly do not depend on behaviour. The planting of new vegetation is expected to be done by local volunteers, and there is a 20% risk that that might not happen.
However, if it did not occur, the costs would be picked up by the LG, so there would be no loss of benefits. (The increased cost to the LG would equal the costs attributed to volunteer labour below.)
There is a behavioural element in the reduced risk of drowning. There is judged to be a 10% chance that local children will take greater risks during a flood as a result of perceived greater safety. This has already been factored into the safety-benefit numbers given above: a reduction from 0.5% to 0.1% in the annual risk of a drowning.
Project risk
We are confident that maintenance funding will be fully funded by the LG until at least 2035. After that, there is a 20% risk that maintenance funding would be ceased. If that happened, the amenity benefits would decline over time (but the water- quality and safety-related benefits would not). For simplicity, assume that the amenity benefit would be lost suddenly and completely after 2040 (with 20% probability). Note that the benefits stop 5 years after maintenance costs cease to be paid.
Hint: Remember that the change in property values in 2027 reflects an increase in annual benefits each year after that. To calculate the effect of this risk, you could convert the change in asset value into a change in annual value, using the PMT function in Excel.
There is a risk of a severe storm and flood damaging the project before the vegetation is fully established. Assume that there is a 3% probability of this occurring, and that if it did occur, all benefits of the project would be lost.
Costs
Who bears costs 2022 2023 2024 2025 2026
Project personnel LG/State government
(50:50) $150,000 $150,000 $150,000 $150,000 $75,000
Overheads LG/State government
(50:50) $75,000 $75,000 $75,000 $75,000 $38,000
Earthworks LG/State government (50:50) $150,800 $15,200 $26,000 $254,000 $85,500
Earthworks personnel LG/State government
(50:50) $30,000 $10,000 $15,000 $100,000 $50,000
Contract design LG $50,000
Approvals LG $25,000
Planting material LG/State government (50:50) $30,000 $10,000 $10,000
Local volunteer
labour Local
residents $25,000 $8,000 $8,000
Maintenance costs (borne by LG alone): $20,000 in most years commencing in 2027, but $35,000 in 2035, 2045 and 2055.
Funds that are subject to the excess burden of taxation: assume that all state funds incur the excess burden but that LG funds are from rates based on property values and do not. Volunteer labour does not either.
For the purposes of calculating the constrained BCR in the second decision context, assume that only LG costs between 2022 and 2026 are constrained.
Marginal excess burden for state funds: 20%
Sensitivity Analysis
Conduct a sensitivity analysis of your BCA. You can choose to do single-variable, multiple variable or Monte Carlo sensitivity analysis.
Here is information about the 75% confidence intervals for the input variables. This information was elicited in workshop with relevant experts.
Variable Low level High level
Average house price -10% +10%
Increase in house price Use range indicated by Polyakov et al. (2017) Use range indicated by Polyakov et al. (2017)
Timing of commencement of amenity benefits 2025 2030
Water quality benefit per household -50% +50%
Number of households in Perth -3% +3%
Timing of water quality benefits Commence two years earlier Commence three years later
Reduction in probability of drowning -50% +20%
Risk of loss of maintenance funding -50% (i.e. probability = 10% instead of 20%) +50% (i.e. probability = 30% instead of 20%)
Risk of storm damage -66% (i.e. probability = 1%
instead of 3%) +66% (i.e. probability = 5%
instead of 3%)
Up-front costs (2022-2026) -5% +50%
Maintenance costs (all costs after 2026) -5% +30%
Discount rate 2% 6%
