Question

Based on the case study below, fill out the Excel file and MUST show math appropriate calculations in the right column.

CASE STUDY DATA

Larson Property Management Company

Supplemental Case Material

Building a Business Case

• The system that Larson has chosen to support their recruiting process costs $15,000 each quarter for use of the application.
• The initial implementation costs to the vendor who will help with project management is $50,000
• Maintenance fees are included in the quarterly licensing fee, but any major changes to the system will cost the company on average an additional $25,000
• The internal recruiting team’s average cost (fully loaded to include benefits, bonus, and retirement) is $52 per hour for each recruiter.
• Hiring managers are making $75 per hour in average fully loaded costs
• Every day a sales position goes unfilled, the average revenue lost per day to the company is $20,000.
• The company plans to implement system training for users the first year that will cost $40,000 to develop and deliver. After the first year, they estimate that ongoing training for new users and for new functionality will cost the company only 37.5% of first year training costs each year
• To take advantage of the new system, the company plans to hire 2 more recruiters that will support the organizations recruiting needs. Each recruiter will cost $95,000 fully loaded.
• The company currently spends $750,000 per year on fees to external search firms to help them find talent. It is estimated by HR that the new system will cut the reliance on external search and save the company 30% on fees the first year, and half their fees for year 2, and after that they think fees will be less than $200,000 a year.
• The efficiencies that the new system will provide allow the company to eliminate three administrative positions; each fully loaded costing $52,000 a year.

Related Information to Consider

• The organization thinks they will be adding the employee referral functionality in year two and the onboarding functionality in year 3.
• The HR recruiting team currently spends 15 hours a week gathering data and pulling together reports that are used to track and measure the recruiting activity. The new system’s reporting capabilities will cut the time to do this data gathering and reporting to a half hour per week.
• Because of the new systems functional capability to route requisitions, candidate files, and interview materials to the hiring managers, they will spend on average, 2 hours less per opening to support this process. There are 40 hiring managers that fill on average 10 requisitions per year.
• New applicant tracking functionality of the new system will allow the company to reassign an HR resource that has been supporting Affirmative Action Reporting to another position that will support workforce planning. It is estimated that the new workforce planning that will take place because of the added attention that results from the reassignment of this resource, along with a sales assessment selection tool that is a part of the new system will effectively cut an average of 10 days out of the time to fill.
• The organization plans to hire 5 sales individuals per year for the next 3 years

Business Case Analysis for Cost Justification on ATS - Assignment 2-1 ... Home View Share Comments AutoSaver A BE Insert Draw Page Layout Calibri (Dedy 11 Biy a x fx J E Formulas A A A Data Review = = General Was Text Marge & Center insert Delete Format E-47-0 Sort & Find & Xu Filter Select S % Barrattinelele Styles Kes Sansitivity Paste D32 3 Business Case Analysis Force is highlighted, show your mach for your answers in this column Year 2 Year 3 Year $60,000 5 Costs 6 System Licensing Fee 7 Implementation Costs 8 Additional syster Module Cost Swem Trainine 10 Recruiters $0 $15,000 $60,000 $15,000 SO 13 Benefits (Savings! 14 Time for Heporting 15 Time of Hiring Managers 16 Search Fee Costs 17 Time to fill Savings 18 Amirtrative Resource Savings $375,000 19 $0 $375,000 $ 21 Cout/Sires Net $60,000 $350,000 $0 Recommerdation highlight correct 23 answer Based on the cost/benefit analysis, iny recommendation whether to purchase 24 the ATS Sheet1 Sheet2 Sheets + E - + + 100%

Answer 1

Below is answer in excel worksheet. I am also providing excel worksheet which shows formula for each cell which will help in understanding the result if there is any issue :

Business Case Analysis
Costs	Year1	Year2	Year3	Basis of working for Highlighted Cells
System Licensing fees	60,000.00	60,000.00	60,000.00
Implementation Costs	50,000.00
Additional System Module Cost		25,000.00	25,000.00	Major changes to system cost 25,000. The addition of module will result into major change. Hence for Employee referral functionality added in year-2 & On boarding functionality in Year-3 it will incur cost of 25000 in Year 2 & Year 3
System Training	40,000.00	15,000.00	15,000.00
Recruiters	190,000.00	190,000.00	190,000.00	Two recruiter with fully loaded cost of 95000 per recruiter
Total Costs	340,000.00	290,000.00	290,000.00
Benefits (Saving)
Time for reporting	39,208.00	39,208.00	39,208.00	14.5 Hour saving per week (15-0.5))*52 Hourly rate of HR team * 52 weeks during the year
Time of hiring manager	60,000.00	60,000.00	60,000.00	40 Manager * 10 Position every year *2 (saving in hours) *75 (Fully loaded cost of Manager per hour)
Search fee Cost	225,000.00	375,000.00	550,000.00	Year 3 Cost will be less than Rs.200,000 hence (750,000-200,000)
Time to fill savings	1,000,000.00	1,000,000.00	1,000,000.00	5 Position in year * 10 (reduction in number of days for recruiting) * 20000 (revenue per day)
Administration Resource Saving	156,000.00	156,000.00	156,000.00	3 Admin Staff * 52000 fully loaded cost of admin resource
Total Benefits	1,480,208.00	1,630,208.00	1,805,208.00
Cost /Saving /Net	1,140,208.00	1,340,208.00	1,515,208.00
Recommendation
Based on cost/benefit analysis my recommendation whether to purchase the ATS	Yes	No

Excel worksheet showing formula :

Business Case Analysis
Costs	Year1	Year2	Year3	Basis of working for Highlighted Cells
System Licensing fees	=15000*4	=15000*4	=15000*4
Implementation Costs	50000
Additional System Module Cost		25000	25000	Major changes to system cost 25,000. The addition of module will result into major change. Hence for Employee referral functionality added in year-2 & Onboarding functionality in Year-3 it will incur cost of 25000 in Year 2 & Year 3
System Training	40000	=B7*37.5%	=B7*37.5%
Recruiters	=95000*2	=95000*2	=95000*2	Two recruiter with fully loaded cost of 95000 per recruiter
Total Costs	=SUM(B4:B8)	=SUM(C4:C8)	=SUM(D4:D8)
Benefits (Saving)
Time for reporting	=(15-0.5)*52*52	=(15-0.5)*52*52	=(15-0.5)*52*52	14.5 Hour saving per week (15-0.5))*52 Hourly rate of HR team * 52 weeks during the year
Time of hiring manager	=40*10*2*75	=40*10*2*75	=40*10*2*75	40 Manager * 10 Position every year *2 (saving in hours) *75 (Fully loaded cost of Manager per hour)
Search fee Cost	=750000*30%	=750000*50%	=750000-200000	Year 3 Cost will be less than Rs.200,000 hence (750,000-200,000)
Time to fill savings	=5*10*20000	=5*10*20000	=5*10*20000	5 Position in year * 10 (reduction in number of days for recruiting) * 20000 (revenue per day)
Administration Resource Saving	=52000*3	=52000*3	=52000*3	3 Admin Staff * 52000 fully loaded cost of admin resource
Total Benefits	=SUM(B12:B16)	=SUM(C12:C16)	=SUM(D12:D16)
Cost /Saving /Net	=B17-B9	=C17-C9	=D17-D9
Recommendation
Based on cost/benefit analysis my recommendation whether to purchase the ATS	Yes	No