# Cost Benefit Analysis

1
EXCEL EXERCISE #7: Cost-Benefit Analysis
1. Enter the information in the spreadsheet below. Be sure that the information is entered
in the same cells as given, or the formulas will not work. The information is the stream of
costs and benefits (in millions) estimated for a proposed city baseball stadium. Year 0
represents the initial investment while costs for years 1-10 are the maintenance costs
incurred at the end of each year. The benefits are the revenues from sport team contracts
and revenues at the end of each year.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
A B C D E F
Total
Discount
Present
Year
Costs
Benefits
Benefits
Value
0 60 0
1 1 3
2 1 3
3 1 10
4 1 10
5 1.5 12.5
6 1.5 12.5
7 1.5 12.5
8 1.5 15
9 2 15
10 2 15
NPV =
Discount
Rate =
2. Highlight the cell range B4:D14. Open the FORMAT menu, select CELLS... Select
the category CURRENCY, select the format \$1,234.10. Repeat this procedure for the
cell range F4:F16.
3. Highlight cell B18. Open the FORMAT menu, select CELLS... Select the category
PERCENT, select two decimal places.
4. To determine the desirability of the project from an efficiency criteria, first calculate the
Total Benefit for each year of the project. To do this, enter the following formula.
D4: =c4-b4
5. Copy the formula in cell D4 to the cell range D5:D14.
6. We must next discount future costs and benefits to put them into today’s value (i.e.,
find the present value). First, enter the following value for the discount rate (a 10%
discount rate).
Page 1/3

## Cost Benefit Analysis PDF

Favor this template? Just fancy it by voting!
•
•
•
•
•
0.0
Related Forms
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•