Hi all -
I’m trying to build out a monthly salary forecast where Feb 1 is the first day of the fiscal year. I would like to do this in Excel using Excel functions (ie no VBA).
The starting point for this worksheet is a data from an HR system (Columns A through W). Only Column N is relevant as it contains the current annual salary.
To that I have added:
· Column X – Start Date of the employee (which is the hire date)
· Column Y - Permanent Non-FT % - this adjusts the annual salary for employees that do not work a full 5 day week on a permanent basis (eg – they only work 4 days per week or 80%)
· Column Z – Annual Salary in Column N as adjusted by any % Column Y
· Column AA – Effective Date of any Salary Increase
· Column AB – New Salary after salary increase (stated at 100%)
· Column AC – New Salary after salary increase as adjusted by any permanent non-FT % in Column Y
· Column AD – start date for any Leave of Absences (ie a worker who is temporarily off for parental leave – that leave could be a full leave or a partial leave)
· Column AE – return date for any Leave of Absences
· Column AF – this is the expected % of the full-time salary expected to be paid during any Leave of Absences (could be zero % or could be higher if employee just takes a reduced work week)
· Column AH – Termination Date (for employees that resign or are fired)
My main concern is Column AI to Column AT where I need to build formulas to calculate the monthly salary expected to be paid in each given month. I have tried to do this but I am not struggling with the nested ifs and other logical operators. Here is the formula that I currently have in Cell AI8:
=IF(AND(AI$4>=$X8,OR(ISBLANK($AH8),AI$5<=$AH8)),IF(AND(NOT(ISBLANK($AA8)),AI$4>=$AA8),$AC8/12,$Z8/12),0)
It fails on a couple of fronts:
1. Trying to factor in the Leave of Absences so that Columns AI to AT reflect a reduced salary during the period of the leave of absence.
2. Trying to get the prorata salary to be paid in any partial month where a change occurs (ie new hire / salary increase / leave of absence / termination). (Right now I am only getting a salary amount where an employee works the full month.)
I have attached my HTML version of my worksheet. Hopefully I have done this correctly.
I’m not sure if this too complex a question but any help would be appreciated.
| Excel Workbook | |||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | AQ | AR | AS | AT | AU | AV | AW | |||
| 1 | |||||||||||||||||||||||||||||||||||||||||||||||||||
| 2 | |||||||||||||||||||||||||||||||||||||||||||||||||||
| 3 | 28 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 31 | 31 | 28 | 31 | 30 | ||||||||||||||||||||||||||||||||||||
| 4 | 1-Feb-11 | 1-Mar-11 | 1-Apr-11 | 1-May-11 | 1-Jun-11 | 1-Jul-11 | 1-Aug-11 | 1-Sep-11 | 1-Oct-11 | 1-Nov-11 | 1-Dec-11 | 1-Jan-12 | 1-Feb-12 | 1-Mar-12 | 1-Apr-12 | ||||||||||||||||||||||||||||||||||||
| 5 | 28-Feb-11 | 31-Mar-11 | 30-Apr-11 | 31-May-11 | 30-Jun-11 | 31-Jul-11 | 31-Aug-11 | 30-Sep-11 | 31-Oct-11 | 30-Nov-11 | 31-Dec-11 | 31-Jan-12 | 28-Feb-12 | 31-Mar-12 | 30-Apr-12 | ||||||||||||||||||||||||||||||||||||
| 6 | |||||||||||||||||||||||||||||||||||||||||||||||||||
| 7 | ID | First Name | Middle | Last | Title | Location | Full/Part | Reg/Temp | Employee Status | Per Status | Unit | DeptID | Descr | Annual Rt | Currency | Supv ID | Name | Country | Hire Date | Rehire Dt | Company Dt | Service Dt | TERMINATION DT + 1 | Start Date | Permanent Non-FT % | Annual Salary Base Currency | Salary Increase - Effective Date | Salary Increase - New Annual Salary Base Currency | Salary Increase - New Annual Salary Base Currency - Non-FT % Adjusted | Leave of Absence - Start | Leave of Absence - Return | Salary % During Leave of Absence | Salary During Leave of Absence | Term Date - "Enter Here" | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar | Apr | ||
| 8 | 63000 | CAD | 10/15/2002 | 10/15/2002 | 10/15/2002 | 15-Oct-02 | 80% | 50,400 | 1-Apr-11 | 64,260 | 51,408 | 16-May-11 | 16-Jul-11 | 50% | 32,130 | 15-Nov-11 | 4,200.00 | 4,200.00 | 4,284.00 | 4,284.00 | 4,284.00 | 4,284.00 | 4,284.00 | 4,284.00 | 4,284.00 | - | - | - | - | - | - | ||||||||||||||||||||
| 9 | 45000 | CAD | 12/1/2003 | 6/12/2006 | 5/11/1998 | 5/11/1998 | 12-Jun-06 | 45,000 | 1-Apr-11 | 45,900 | 45,900 | 16-May-11 | 16-Jul-11 | 50% | 22,950 | 15-Nov-11 | 3,750.00 | 3,750.00 | 3,825.00 | 3,825.00 | 3,825.00 | 3,825.00 | 3,825.00 | 3,825.00 | 3,825.00 | - | - | - | - | - | - | ||||||||||||||||||||
| 10 | 48000 | CAD | 3/5/2011 | 3/5/2011 | 3/5/2011 | 5-Mar-11 | 48,000 | 1-Apr-11 | 48,960 | 48,960 | 0% | - | - | - | 4,080.00 | 4,080.00 | 4,080.00 | 4,080.00 | 4,080.00 | 4,080.00 | 4,080.00 | 4,080.00 | 4,080.00 | 4,080.00 | 4,080.00 | 4,080.00 | 4,080.00 | ||||||||||||||||||||||||
Base Salary only | |||||||||||||||||||||||||||||||||||||||||||||||||||
| Excel Workbook | |||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | AQ | AR | AS | AT | AU | AV | AW | |||
| 1 | |||||||||||||||||||||||||||||||||||||||||||||||||||
| 2 | |||||||||||||||||||||||||||||||||||||||||||||||||||
| 3 | 28 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 31 | 31 | 28 | 31 | 30 | ||||||||||||||||||||||||||||||||||||
| 4 | 1-Feb-11 | 1-Mar-11 | 1-Apr-11 | 1-May-11 | 1-Jun-11 | 1-Jul-11 | 1-Aug-11 | 1-Sep-11 | 1-Oct-11 | 1-Nov-11 | 1-Dec-11 | 1-Jan-12 | 1-Feb-12 | 1-Mar-12 | 1-Apr-12 | ||||||||||||||||||||||||||||||||||||
| 5 | 28-Feb-11 | 31-Mar-11 | 30-Apr-11 | 31-May-11 | 30-Jun-11 | 31-Jul-11 | 31-Aug-11 | 30-Sep-11 | 31-Oct-11 | 30-Nov-11 | 31-Dec-11 | 31-Jan-12 | 28-Feb-12 | 31-Mar-12 | 30-Apr-12 | ||||||||||||||||||||||||||||||||||||
| 6 | |||||||||||||||||||||||||||||||||||||||||||||||||||
| 7 | ID | First Name | Middle | Last | Title | Location | Full/Part | Reg/Temp | Employee Status | Per Status | Unit | DeptID | Descr | Annual Rt | Currency | Supv ID | Name | Country | Hire Date | Rehire Dt | Company Dt | Service Dt | TERMINATION DT + 1 | Start Date | Permanent Non-FT % | Annual Salary Base Currency | Salary Increase - Effective Date | Salary Increase - New Annual Salary Base Currency | Salary Increase - New Annual Salary Base Currency - Non-FT % Adjusted | Leave of Absence - Start | Leave of Absence - Return | Salary % During Leave of Absence | Salary During Leave of Absence | Term Date - "Enter Here" | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar | Apr | ||
| 8 | 63000 | CAD | 10/15/2002 | 10/15/2002 | 10/15/2002 | 15-Oct-02 | 80% | 50,400 | 1-Apr-11 | 64,260 | 51,408 | 16-May-11 | 16-Jul-11 | 50% | 32,130 | 15-Nov-11 | 4,200.00 | 4,200.00 | 4,284.00 | 4,284.00 | 4,284.00 | 4,284.00 | 4,284.00 | 4,284.00 | 4,284.00 | - | - | - | - | - | - | ||||||||||||||||||||
| 9 | 45000 | CAD | 12/1/2003 | 6/12/2006 | 5/11/1998 | 5/11/1998 | 12-Jun-06 | 45,000 | 1-Apr-11 | 45,900 | 45,900 | 16-May-11 | 16-Jul-11 | 50% | 22,950 | 15-Nov-11 | 3,750.00 | 3,750.00 | 3,825.00 | 3,825.00 | 3,825.00 | 3,825.00 | 3,825.00 | 3,825.00 | 3,825.00 | - | - | - | - | - | - | ||||||||||||||||||||
| 10 | 48000 | CAD | 3/5/2011 | 3/5/2011 | 3/5/2011 | 5-Mar-11 | 48,000 | 1-Apr-11 | 48,960 | 48,960 | 0% | - | - | - | 4,080.00 | 4,080.00 | 4,080.00 | 4,080.00 | 4,080.00 | 4,080.00 | 4,080.00 | 4,080.00 | 4,080.00 | 4,080.00 | 4,080.00 | 4,080.00 | 4,080.00 | ||||||||||||||||||||||||
Base Salary only | |||||||||||||||||||||||||||||||||||||||||||||||||||
