A company (we'll call them Potent Brewing Co.) has hired you to do some calculations on the retirement plans for their employees
The data they sent you is on the sheets Participant and 2020 Contributions
They have hired you to calculate and send them the fields on the Deliverable sheet for each employee
Details on the Deliverable fields:
- Full Name: Last Name, First Name
- Current Age: calculated by the Date of Birth
- Retirement Date: 65 years after their date of birth, unless they're already over 65, in which case it is two years after their next birth date
- 2020 Total Contributions - 401k: sum of the employee's 401k contributions in 2020. If they did not contribute then the total is $0.
- 2020 Total Contributions - Roth: sum of the employee's Roth contributions in 2020. If they did not contribute then the total is $0.
- Eligible for Bonus: Potent Brewing Co. encourages their employees to retire by giving them a bonus if they save at least $20k a year. If employee contributed at least $20k then 1, else 0.
- Full Name: To obtain Last Name and First Name merged together I have used CONCAT combined with VLOOKUP formula
- Current Age: To obtain current age calculated by the Date of Birth I have used combination of DATEDIF(MATCH(TODAY)))
- Retirement Date: To calculate retirement date I have used combined IF,TODAY, EDATE, INDEX and MATCH
- 2020 Total Contributions - 401k: To calculate sum of the employee's 401k contributions I have used SUMIFS formula
- 2020 Total Contributions - Roth: To calculate sum of the employee's Roth contributions i have used SUMIFS formula.
- Eligible for Bonus: To calculate if the employee is eligible for bonus I have used IF combined with SUM formula