Cost Benefit Analysis Excel Assignment

This practical assignment focuses on conducting a cost-benefit analysis using an Excel template. It involves inputting personnel expenditure data, calculating benefits, and analyzing costs. The assignment requires using a provided budget document, calculating social security and medicare contributions, and determining net benefits. It also includes separating health insurance and retirement benefits, applying escalation rates, and calculating one-time and recurring costs and savings. The analysis culminates in calculating the Benefit-Cost Ratio and writing a memo with recommendations based on the findings. The assignment also includes considerations for the time value of money and the impact of different escalation rates on costs and benefits.

Once you have opened “Cost Benefit Analysis Template” – an Excel Spreadsheet – and
“Description of cost-benefit analysis (CBA) assignment” – MS Word document…
1) At the top of the sheet is an area entitled “Arapahoe County Personnel-Based Expenditures.”
2) Replace F5, which says “(your dept)” with your assigned department (same as before).
3) We use the same budget document and the same budget information as before. So it’s okay
to pull Salary and Benefits from the spreadsheet you did for Excel 1. But you need FTEs, too.
4) The .pdf file called “Complete 2015 Adopted Budget” has budget data for this area of the
spreadsheet—it’s the same one you used for Excel 1.
5) Adopted budget data can be found on different pages for different departments.
For the Assessor’s Office, it’s in the “Budget Summary” table on page 168 (180 of 399).
For the Clerk & Recorder’s Office… on page 178 (190 of 399).
For Community Resources… on page 188 (200 of 399).
For County Attorney’s Office… on page 200 (212 of 399).
For Finance… on page 213 (225 of 399).
For Human Resources… on page 218 (230 of 399).
For Human Services… on page 222 (234 of 399).
For Information Technology… on page 228 (240 of 399).
For Public Works & Development… on page 243 (255 of 399).
6) Fill in only the Salaries & Wages, Employee Benefits, and FTE cells from the Summary
Budget Table.
7) Use only the 2015 budgeted expenditures: in the column named “2015 Adopted Budget.”
8) Enter (or copy and paste) the last (rightmost) column of data under Salaries & Wages from
the .pdf into the “Salaries & Wages” cell (B7). Enter (or copy and paste) the Employee
Benefits amount from the .pdf (just underneath “Salaries & Wages”) into the “Employee
Benefits” cell of the spreadsheet (B8). Finally, enter (or copy and paste) Staff – FTE from
the .pdf (just above Salaries & Wages) into the “Staff-FTE” cell of the spreadsheet (B12)
[you’re going to compute the cells B9-B11 below]. You’re now finished with the .pdf.
9) Next, we remove some amounts from the Benefits that aren’t part of the analysis – Social
Security and Medicare. We keep these two items outside of the analysis because they will
have no “net” effect: i.e., will be paid identically under the pilot benefit structure to the way
they always have been under the current benefit structure. To compute the “Social Security”
amount (contribution by the employer), multiply the Salaries and Wages in B7 by the
withholding rate of .062 (6.2%), so that B9 has: =B7*.062 . (Except no period!) [This
computation ignores the earnings maximum for Social Security, which is controversial –
especially the Democratic side – that stops FICA tax withholding after an individual’s
income reaches $118,500. We can’t get this level of precision without knowing who in
particular makes more than $118,500. All we know is that the average is less than $118,500
for all of the departments, so we assume that no individual is making more. That’s clearly an
unrealistic assumption, but we should get a close enough result.] Next, we do a similar
computation for “Medicare” using the withholding rate of .0145 (1.45%), which give us for
B10: =B7*.0145. [There is no earnings maximum for Medicare, so this computation is 100%
accurate.]

10) Subtract the Social Security and Medicare amounts from the Benefits (B8), giving for B11:
=B8-B9-B10. This is the “Benefits net of FICA” – which is the portion of the benefits that
our analysis will deal with [exclusive of FICA because FICA will continue to be paid by the
employer (County), meaning no difference between the pilot and status quo].
11) Convert the amount figured in step 10 into a per-employee amount by dividing by the
number of staff (FTE), resulting in cell B13: =B11/B12. This is the amount from which
savings available to make the business case feasible. The reason that is so is because the
expenditure on benefits, aside from FICA withholding, will be replaced under the pilot
program by a general stipend that the employee chooses how or whether to spend.
12) The amount in (11) must be separated into 2 parts because different escalation rates apply to
the health insurance portion and non-health insurance (primarily retirement-related) benefits.
For the health insurance part of the benefit savings, an escalation rate of 8% applies, which is
higher than the general rate of escalation because health care costs are rising at a faster rate.
The regular escalation rate, which applies to everything except health insurance, equals 3.5%.
The non-health portion, which primarily provides retirement-related benefits, is assumed to
cost the equivalent of 7.5% of “Salaries & Wages per employee.” Therefore, Retirement-
related benefits per employee equal the product of “Salaries & Wages” multiplied by 0.075
(or 7.5%), then divided by “Staff-FTE,” so that cell B14 has: =B7*.075/B12.
13) The health insurance cost is what remains after the retirement-related cost – figured in step
12 – is separated out. Therefore, “Health care-related benefits per employee” equals
“Benefits net of FICA per employee,” figured in step 11, minus the “Retirement-related
benefits per employee,” figured in step 12, so that cell B15 has: =B13-B14.
14) Now, all the information is available to calculate the costs and benefits that make up the cost-
benefit analysis. Only quantities and unit costs must be (can be) entered. Escalation
computations are handled by existing formulas within the spreadsheet, which are found in
columns E through K. Please assign the quantities and units using the following assumptions:
a) All departments are assumed to enlist the maximum number of employees (5) to
participate in the pilot plan.
b) FICA tax withholding (Social Security and Medicare) will continue to be paid as
they are currently and are therefore neither net costs nor net savings (“net” = the
difference between the status quo and the pilot).
c) The program is assumed to take 6 months to become operational. During those 6
months, all current benefits (including health insurance and retirement) will be
withheld normally.
d) The base stipend will be $5,000 per year. This is prorated by 50% to $2,500 for
the first year, when the pilot is only going for the last 6 months.
e) One-time payments of $10,000 will be paid into participants’ retirement accounts
when they sign up (assuming employees will “roll” existing balances into IRAs).
f) Human resources has assigned a flat expense of $25,000 per department to
document the pilot program, interview applicants/review applications, set up HR
system parameters for participants, and monitor initial payroll benefit statements.
g) Under the Affordable Care Act employees not provided access to health insurance
will cost employers $3,000 per uncovered employee, which increases annually by
the rate of health care escalation. This cost requires special handling—see 16 (a).

h) For the pilot, up to 5 employees per department will be eligible to participate.
Only those under 30 are eligible, because employees under 30 may avoid personal
penalties by obtaining catastrophic policies from an ACA exchange (not a cost).
15) First, put the applicable costs into the One-time category, beginning at row 20, if they occur
only once, at the beginning of the pilot. Enter the quantity and the unit cost, for example,
HR’s charge of $25,000 per department has a quantity of 1 because each department only
pays once. Put the description of the cost (e.g., “HR charge to set up pilot”) in A20, the
quantity in B20, and the unit cost in C20. The actual cost (unit cost times quantity) is
automatically computed and shows up in the green area (starting in E20). You do not have to
fill up all the spaces provided with costs.
16) Then, assign the recurring costs (beginning at row 25), which last beyond the initial year. A
recurring cost is any payment by the department that it doesn’t currently pay, but is going to
keep paying—beyond the first year. The recurring cost may have a different first-year value,
which involves prorating the costs (multiplying the computed sums by 0.5 to account for 6
out of 12 months) as described above. Put the description for the recurring cost in A25, the
quantity in B25, the prorated (half-year) unit cost in C25, and the full cost for the operational
(after first year) years in D25. You do not have to fill up all the spaces provided with costs.
a) The Affordable Care Act (ACA) penalty is indicated as a cost in row 26, where it
must go because the penalty will grow at the rate of health care escalation (in B3).
Formulas in F26-J26 reference B3, so each year’s cost will increase by 8% over
the previous year’s. The full penalty (not half) is assessed for the transition year.
17) The cost for the initial year (called “Transition”) will be automatically calculated in the red
area (beginning in E25). The on-going costs for the operational years will be automatically
calculated and appear in the teal or bluish area (starting in F25)—notice these are escalated
by the values of 3.5% and 8.0% in cells B2 and B3, with the latter yielding a faster increase.
18) Now, we are ready to assign the savings. In this example, savings consist of costs which the
department currently pays, but which it will not pay in the future. This is not always the case,
for instance, a new receipt or savings of Other People’s Money (say, the public’s) would also
qualify, but there are none of these types of savings in this case. The following assumptions
apply to the savings calculations:
a) All departments are assumed to enlist the maximum number of employees (5) to
participate in the pilot plan.
b) FICA tax withholding (Social Security and Medicare) will continue to be paid as
they are currently and are therefore neither net costs nor net benefits (“net” = the
difference between the status quo and the pilot).
c) The program is assumed to take 6 months to become operational. During those 6
months, all current benefits (including health insurance and retirement) will be
paid normally. Afterwards (during the remaining 6 months of the Transition Year
and for all Operational Years), the County will no longer make benefit payments
other than FICA taxes for the pilot participants.
d) Different assumptions apply to amounts that the County would have to continue
paying in the future, if the pilot was not implemented. The difference is the rate
of escalation, which is assumed to be much higher for health insurance than for
other (retirement-related) benefits. The annual rate for health insurance cost

escalation is assumed to be 8% (it’s already in cell B3). The annual rate for cost
escalation for items other then health insurance is 3.5% (it’s in cell B2).
e) In order to separate the health insurance from the retirement-related benefits, the
“Retirement-related benefits per employee” (i.e., non-health care) are assumed to
cost 7.5% of salary. These are the computations we already did in steps 12 and 13.
19) Finally, enter the recurring savings. [Notice there is no one-time savings. It is not always so,
but is the case in this instance.] “Health care savings” description is already provided because
the special escalation rate of 8% applies (just as it does for the ACA penalty—see 16a) and
references to B3 requires it to be in row 30. Enter the quantity for “Health care savings” in
B31. Enter in C31 the “Transition Yr Unit Savings” (the amount of money saved per person)
in the 6 months that the pilot operates for the first year (1/2 of the operational year savings).
Enter in D31 the “Operational Yr Unit Savings” (the amount of money saved per person) in
the full years after the pilot becomes operational. Next, enter the other savings category in
the space left for it in row 30. You’ll have to make up your own description for these savings
and enter it in A30. Just as above, put the quantity in B30, the transition year unit savings
(amount saved per person during the first 6 months of the pilot) in C30, and the operational
year unit savings in D30. You do not have to fill up all the spaces provided with savings.
20) The first year (called “Transition”) cost will be automatically calculated in the yellow area
(beginning in E30). The on-going costs for the operational years will be automatically
calculated and appear in the light purple area (starting in F30)—notice these are escalated by
the values of 3.5% and 8.0% in cells B2 and B3, with the latter yielding a faster increase.
21) In the next section, entitled “Costs/Benefits adjusted for time value of money,” you do not
make any computations – the discount rate is automatically computed. I’ve copied the
discount rates from the “Present Value of 1” document (just to provide a reference to see
where the discount rates for operational years 1-5 come from). Notice the discount rate is
assumed to be 7%. The computations that you could do to derive these discount factors
yourself are provided in F36 through J36.
22) In the orange area from column E through column J and rows 40-42, the present value of
costs (costs computed in the teal/bluish area) are “discounted” for time value of money by
multiplying by the discount factors (in F35 through J35). You will notice that the amounts in
each cell of row 40 get smaller as time goes by, in contrast to the escalated values above
(teal/bluish area) where the amounts get larger with time. This is because the impact of the
discount effect is more powerful than the escalation (inflation) effect, which is typically the
case (who’s going to lend money at less than the rate of inflation?). You’ll see a similar
effect for the discounted benefits (savings) in row 41. The amounts in rows 40 and 41 go
down over time but at a very gradual rate of decline. This is because substantial components
of the costs and benefits (savings) assume an escalation rate of 8% (greater than the discount
rate of 7%). But the other components of costs and benefits (savings) escalate at only 3.5%,
so on average the escalation rate is lower than the discount rate, which will almost always be
the case for a cost-benefit analysis.
23) Finally, we can compute the Benefit-Cost Ratio. It was necessary to first do the present value
computations that we examined in step 22 because Benefit-Cost Ratio can only be calculated
with the present value of costs and the present value of benefits. Incidentally, if you subtract
instead of dividing, you get Net Present Value (= PV of Benefits – PV of Costs), which is
computed for you in cell K42 (Total NPV = the sum of the yearly NPVs in E42 through J42).

24) The Benefit-Cost Ratio, which is computed for you in cell C43, will have different values for
the various departments. The values are provided as a check for the correct computations:
For the Assessor’s Office… it’s 1.21.
For the Clerk & Recorder’s Office… it’s 1.00.
For Community Resources… it’s 1.23.
For County Attorney’s Office… it’s 1.41.
For Finance… it’s 1.28.
For Human Resources… it’s 1.19.
For Human Services… it’s 1.07.
For Information Technology… it’s 1.35.
For Public Works & Development… it’s 1.34.
25) Now that your computations are correct, you can write the memo. I’m not going back over
the basics – you know the drill! I’m not holding you to two pages, so go a little longer if you
need to. The format will be a little different for a cost-benefit analysis. However, it will start
the same. Your first paragraph will describe the purpose of your analysis (a cost-benefit
analysis of a project to change the way benefits are provided on an experimental [pilot] basis)
and summarize the methods that you employed. The next 3 paragraphs should convey the
following information:
a) Paragraph two: describe the project in enough detail that the analysis you do in
the following paragraphs will make sense to the reader. Make sure to cover the
most important factors (Hint: one is that the health insurance rate of escalation is
assumed to be 8%—far in excess of the escalation rate for everything else). And
remember that we’re not trying to make these employees into contractors (which
violates federal law!), but will continue to pay the employer’s share of FICA tax.
Finally, the pilot is voluntary and targets young people who can purchase health
insurance very affordable (catastrophic coverage), so it shouldn’t be a hardship.
b) Paragraph three: provide the quantitative results of your analysis. Once again,
focus on the key results (Hint: the Benefit-Cost Ratio is one of them!). You can
also give a sense what the important financial effects are – by looking in the result
areas (green, red, yellow, teal, purple, and orange) and seeing what jumps out at
you. For instance, would this pilot be a wise move if it only ran 3 full years
instead of 5? (Try summing the NPV for the Transition Year and Operational
Years 1-3.) What if less than the 5 maximum participants decide to participate?
These are just examples of the questions you might look at… you’re welcome to
make up other questions on your own, but I’m trying to get you to look beyond
the BCR at the underlying numbers that make it up.
c) Paragraph four: make a recommendation for your department proceeding with the
pilot or not. Your recommendation should include quantitative factors that you
developed in the paragraph above, but can examine other (qualitative) issues that
you think are important. Good luck!