Problem set 10

Due by 11:59 PM on Monday, December 2, 2019

Important instructions: You should use Excel for this assignment. Show as much work as possible. Submit a PDF of your answers and the Excel file you used to do your calculations. Make sure your Excel file is well organized and clearly labeled.


The old Sweetums factory in Pawnee, Indiana has become derelict and infested with raccoons. Pawnee City Council has approved a motion to develop the site and has received proposals for three different projects.

You must analyze the costs and benefits of each of these projects and rank the three options as first, second, and third choices. Each project has different costs and benefits (specified below), and each entails different distributional consequences (not specified below; you’ll need to think of those).

Use a discount rate of 3% and calculate costs and benefits for a 20-year period. This example is adapted from Dan Wheatley’s CBA Builder project and used under a Creative Commons license.

Option 1: Job training center

One proposal is to transform and renovate the old building into a job training center. The training center is popular among the City Council because it will be used to train local youth and help them start up their own businesses. In particular, council members hope the training center will make Pawnee more economically competitive with its richer neighbors, like Eagleton.

Costs Benefits
Construction (one-time cost; occurs in year 0) $2,800,000 Training benefits and job creation (annual amount; begins in year 2) $250,000
Staffing (annual amount; begins in year 1) $200,000 Multiplier effect on local economy (total divided evenly across years 2–20) $5,200,000
Building maintenance (annual amount; begins in year 1) $10,000

Option 2: Luxury apartments

The second option under consideration is to convert the factory into luxury urban apartments and sell them to local developers. This option requires some major structural changes to the building, but uses the existing outer shell. This option is popular with the City Council because it creates substantial short term revenues (and could attract richer residents away from neighboring Eagleton).

Costs Benefits
Construction (one-time cost; occurs in year 0) $4,200,000 Income received from sale of apartments (annual amount; occurs in years 1–3). Note: the exact value depends on the speed and value of sales—Pawnee economists estimate that the standard deviation for this estimate is roughly $500,000. $2,350,000
Advertising and real estate agent costs (annual amount; occurs in years 0–3) $40,000 Multiplier effect on local economy (total divided evenly across years 2–20) $1,500,000

Option 3: New industrial units

The third option is to demolish the current structure and build eight industrial units on the site, which will then be rented to local business. This idea is popular with the City Council because it is seen as the cheapest option and should create jobs in the city. However, it is likely to result in substantial congestion, noise, and air pollution (and push potential residents towards Eagleton).

Costs Benefits
Demolition (one-time cost; occurs in year 0) $200,000 Income from business rentals (annual amount; begins in year 1) $270,000
Construction (one-time cost; occurs in year 0) $2,000,000 Job creation (annual amount; occurs in years 1–5) $300,000
Maintenance (annual amount; begins in year 1) $100,000 Multiplier effect on local economy (total divided evenly across years 2–20) $2,200,000


Do the following:

  1. Download this Excel template for CBA with lots of information already filled in for you:

  2. Calculate the NPV and BCR for each project and rank them according to NPV. Which project should be chosen given the costs and benefits provided?

  3. What other impacts (i.e. costs or benefits) should be considered for each project? How could you potentially monetize these?

  4. How does your ranking change if the city only receives $1.7 million in sales revenues in years 1 –3?

  5. Do the project rankings change if the discount rate is 2%? 4%? 5%?

  6. What if the estimates for one or more of the costs or benefits are incorrect? Perform some sensitivity analysis and check how robust your ranking is to construction cost estimates and multiplier effect benefit estimates.

  7. What is your final recommendation to the Pawnee City Council? How confident are you in this recommendation? Why?