Project NewCo

Summary OutputFor the next seven $(7)$ questions, you will need the supplemental Excel file found HERE.

You are Analyst at New Regions Capital performing due diligence on Project NewCo, a global retailer with six internal departments. As part of the diligence process, the investment team needs to analyze NewCo$$s headcount and compensation expenses to identify relevant trends in the data, and to do this you need to consolidate and summarize the company$$s employment data for review by the deal team. For this analysis, the team wants to look at i$)$ monthly and quarterly compensation expense broken down by Department and Geography, and ii$)$ monthly and quarterly total headcount.

The Excel file contains two tabs: i$)$ an Output tab where the summary exhibit is being created, and ii$)$ a Headcount Data tab, which contains all of the employee data that has been provided by the company in the data room, and detailed calculations of monthly fully loaded compensation expenses performed by another member of your deal team.

NOTE: You are working only in the yellow shaded cells. The Excel file is already formatted for you. To avoid calculation errors, do not change the formatting in the file.

Step $1$: Review the Output tab to understand what data needs to be pulled in to accurately complete the exhibit.

Step $2$: Review the Headcount Data tab to understand exactly what has been provided by the company and the subsequent analysis that have been performed to calculate monthly fully loaded compensation costs.

Step $3$: On the Output tab, complete the monthly Total Compensation Summary section of the exhibit $($columns F through K$)$ by referencing the Headcount Data tab and summing the appropriate data for the correct period, department $($rows $11-18)$ and geography $($rows $20-24).$

Step $4$: On Output tab, complete the monthly Total Headcount Summary section of the exhibit $($columns F through K$,$ rows $32-36)$ by referencing the Headcount Data tab and counting all new hires and terminations for the correct period.

Step $5$: On the Output tab, complete the quarterly consolidation of the monthly data $($columns N through O$).$

Step $6$: Answer the following questions.

Question $1$ of $7$: How much did NewCo spend in compensation expense within their Research & Development department in March $2020?$

A$)$$$75,667$

B$)$$$99,833$

C$)$$$131,865$

D$)$$$151,108$

Question $2$ of $7$: What percentage of total compensation expense did employees in the General & Administrative department account for in June $2020?$

A$)15\mathrm{.}8\%$

B$)18\mathrm{.}5\%$

C$)20\mathrm{.}8\%$

D$)33\mathrm{.}0\%$

Question $3$ of $7$: How much did NewCo spend in compensation expense for employees in their North region in Q$22020?$

A$)$$$44,500$

B$)$$$1,367,578$

C$)$$$475,000$

D$)$$$1,412,078$

Question $4$ of $7$: What was NewCo$$s total compensation expense in Q$22020?$

A$)1,412,078$

B$)1,375,035$

C$)1,240,078$

D$)1,001,500$

Question $5$ of $7$: How many new employees did NewCo hire in Q$12020?$

A$)0$

B$)1$

C$)2$

D$)3$

E$)4$

F$)5$

Question $6$ of $7$: The term $$net new hires$$ refers to the total number of new employees added to a company$$s payroll, after accounting for any separations such as resignations, terminations, and retirements. How many net new hires did NewCo have in Q$22020?$

A$)0$

B$)1$

C$)2$

D$)3$

E$)4$

F$)5$