logo

Cost Benefit Analysis Template

   

Added on  2019-09-20

5 Pages3544 Words337 Views
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 ishigher 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 insurancewill 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).

End of preview

Want to access all the pages? Upload your documents or become a member.