Trusted by +2 million users,
assist thousands of students everyday
assist thousands of students everyday
Showing pages 1 to 2 of 5 pages
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 okayto 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 thespreadsheet—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 SummaryBudget 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 fromthe .pdf into the “Salaries & Wages” cell (B7). Enter (or copy and paste) the EmployeeBenefits amount from the .pdf (just underneath “Salaries & Wages”) into the “EmployeeBenefits” cell of the spreadsheet (B8). Finally, enter (or copy and paste) Staff – FTE fromthe .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 – SocialSecurity and Medicare. We keep these two items outside of the analysis because they willhave no “net” effect: i.e., will be paid identically under the pilot benefit structure to the waythey 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 thewithholding rate of .062 (6.2%), so that B9 has:=B7*.062. (Except no period!) [Thiscomputation ignores the earnings maximum for Social Security, which is controversial –especially the Democratic side – that stops FICA tax withholding after an individual’sincome reaches $118,500. We can’t get this level of precision without knowing who inparticular makes more than $118,500. All we know is that the average is less than $118,500for all of the departments, so we assume that no individual is making more. That’s clearly anunrealistic assumption, but we should get a close enough result.] Next, we do a similarcomputation for “Medicare” using the withholding rate of .0145 (1.45%), which give us forB10:=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 thatour analysis will deal with [exclusive of FICA because FICA will continue to be paid by theemployer (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 thenumber of staff (FTE), resulting in cell B13:=B11/B12. This is the amount from whichsavings available to make the business case feasible. The reason that is so is because theexpenditure on benefits, aside from FICA withholding, will be replaced under the pilotprogram 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 tothe 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 tocost 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 step12 – is separated out. Therefore, “Health care-related benefits per employee” equals“Benefits net of FICA per employee,” figured in step 11, minus the “Retirement-relatedbenefits 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. Escalationcomputations are handled by existing formulas within the spreadsheet, which are found incolumns 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) toparticipate in the pilot plan.b)FICA tax withholding (Social Security and Medicare) will continue to be paid asthey are currently and are therefore neither net costs nor net savings (“net” = thedifference between the status quo and the pilot).c)The program is assumed to take 6 months to become operational. During those 6months, all current benefits (including health insurance and retirement) will bewithheld normally.d)The base stipend will be $5,000 per year. This is prorated by 50% to $2,500 forthe 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 accountswhen they sign up (assuming employees will “roll” existing balances into IRAs).f)Human resources has assigned a flat expense of $25,000 per department todocument the pilot program, interview applicants/review applications, set up HRsystem 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 bythe rate of health care escalation. This cost requires special handling—see 16 (a).