Setup and conduct a spreadsheet benefit-cost analysis of several scenarios of proposals that would merge Hagerstown, MD, and Washington County, MD, prosecutorial offices. For this problem, please describe an Excel spreadsheet electronic file (.XLSX/.XLS) and clearly state the answer to each item below, showing work for each scenario below. Organize/enter the data, compute net benefits (NB), and net present value (NPV) to make a recommendation based on 10 years of forecasted impacts and values.

a. In Scenario A, use the assumptions below. Based solely on net present value (NPV), do you recommend the proposal?

• Initial costs are paid immediately (year 0). In future years, costs and benefits should be accounted at "end of the year" (the usual convention).
• Annual benefits are $7 in years 2-4, and $12 in years 5-10.
• Initial costs are $20.
• Annual costs are $10 in years 1-4, and $5 in years 5-10.
• Use no discounting.

b. In Scenario B, apply a discount rate of 0.02 (2%). Recalculate NPV. What do you recommend?

c. In Scenario C, separate everything into 3 groups: Hagerstown and Washington County and the State of Maryland. Recalculate NPV for each group. Using the assumptions below, what does each jurisdiction's leaders recommend?

• Washington County Annual benefits are $4 in years 2-4, and $10 in years 5-10. Initial costs are $12. Annual costs are $6 in years 1-4, and $3 in years 5-10.
• Hagerstown Annual benefits are $3 in years 2-4, and $2 in years 5-10. Initial costs are $8. Annual costs are $4 in years 1-4, and $2 in years 5-10.
• The State includes the Hagerstown and Washington County but has no other costs or benefits.
• Use a discount rate of 0.04 (4%). This is greater than in A & B.

d. In Scenario D, you should update Scenario C. Suppose that an accounting error is found and the Hagerstown's costs for years 0-4 are halved (reduced by 50%). Now, what does each jurisdiction's leaders recommend?

• The Hagerstown's initial costs are $4
• The Hagerstown's annual costs are $2 in years 1-4. (Annual costs in years 5-10 remain $2.)

e. Finally, consider this BCA application (merging Hagerstown and Washington County prosecutorial offices). Assume that the numbers above are very accurate (no uncertainty or forecast error) for what they capture. What concerns, if any, might you have about changes being made to the judicial system solely based on this BCA analysis?

• Be specific. Please write some paragraphs. This does not need to be a long answer, but it should be thoughtful.