Spreadsheet: 883953d1732767340-salary-forecasting-for-different-employee-scenarios-salary-forecasting_27-nov-2024-v2
Sheet: P10 SFT

 |  |  |  |  |  | IMPORTANT: Dates are in MM/DD/YYYY format
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Workings for Pro-rate salary based on start and end dates of employment
 |  |  |  |  |  |  |  | 2024-11-01 00:00:00 |  | Anniversary Increment |  |  | CBA |  |  |  | Monthly Salary
Employee Name | Start date of employment or acting | End date of employment, acting or retiring | Annual Salary Beginning of FY | Monthly Salary | Classification | Step | Step Anniversary Date |  | Forecast for remainder of FY | Next Step Annual  Salary | Monthly Salary | Pro-rated [NAME] on  Anniversary Date | Effective Date | Incremented Salary | Monthly  Salary | Pro-rated  [NAME] in  December /June | 2024-04-01 00:00:00 | 2024-05-01 00:00:00 | 2024-06-01 00:00:00 | 2024-07-01 00:00:00 | 2024-08-01 00:00:00 | 2024-09-01 00:00:00 | 2024-10-01 00:00:00 | 2024-11-01 00:00:00 | 2024-12-01 00:00:00 | 2025-01-01 00:00:00 | 2025-02-01 00:00:00 | 2025-03-01 00:00:00 |  |  | 2024-04-01 00:00:00 | 2024-05-01 00:00:00 | 2024-06-01 00:00:00 | 2024-07-01 00:00:00 | 2024-08-01 00:00:00 | 2024-09-01 00:00:00 | 2024-10-01 00:00:00 | 2024-11-01 00:00:00 | 2024-12-01 00:00:00 | 2025-01-01 00:00:00 | 2025-02-01 00:00:00 | 2025-03-01 00:00:00
Emp. 1.a (acting Nov 4 - Feb 28) | 2024-11-04 00:00:00 | 2025-02-28 00:00:00 | 2000 | 500 | IT-04 | 3 | 2024-11-04 00:00:00
Emp. 2 | 2020-01-01 00:00:00 |  |  |  | IT-03 | 8 | 2024-04-01 00:00:00
Emp. 3 retiring (April 1 to Oct 15) | 2020-01-01 00:00:00 | 2024-10-15 00:00:00 |  |  | IT-03 | 4 |  | 59325 | 0 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Start Date Adj
Emp. 1 | 2020-01-01 00:00:00 |  |  |  | IT-03 | 6 | 2024-03-14 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | End Date Adj
Emp. 4 | 2024-08-29 00:00:00 |  |  |  | IT-02 | 5 | 2024-08-29 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | When both Start Date & End Date falls on the same month
Emp. 5 | 2020-01-01 00:00:00 |  |  |  | AS-05 | 3 | 2024-01-04 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Employee in employment during the month (1=yes)
Emp 6 | 2024-06-08 00:00:00 | 2024-09-08 00:00:00 |  |  | IT-02 | 5 | 2024-10-06 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | [NAME]
Emp. 6a | 2024-09-09 00:00:00 | 2025-01-08 00:00:00 |  |  | IT-03 | 5 | 2024-10-06 00:00:00
Emp. 6b | 2025-01-09 00:00:00 |  |  |  | IT-03 | 5 | 2024-10-06 00:00:00
Anniversary Increment
Level | Eff. date | Step 1 | Step 2 | Step 3 | Step 4 | Step 5 | Step 6 | Step 7 | Step 8 | Step 9 |  | Possible Scenarios
IT-01 | 2024-04-01 00:00:00 | 67831 | 70339 | 72842 | 75332 | 77820 | 80309 | 82794 | 87411 | 87411 |  | 1-Employee 1 is an IT03 but will replace (acting) his supervisor for 4 months during the year (1a)  - depending on the dates, I'd like to know how much I paid him so far and the forecast for the remainder of FY (depending on I5)
IT-02 | 2024-04-01 00:00:00 | 83961 | 86649 | 89333 | 92020 | 94708 | 97393 | 100078 | 102763 | 102763 |  | 2-Employee 2 is an IT-03 at the last step
IT-03 | 2024-04-01 00:00:00 | 99108 | 102522 | 105935 | 109351 | 112763 | 116172 | 119584 | 123137 | 123137 |  | 3-Employee 3 is retiring this year, She was with us on April 1st and her last day was Oct. 16, 2024
IT-04 | 2024-04-01 00:00:00 | 113478 | 117391 | 121304 | 125216 | 129127 | 133041 | 136951 | 141249 | 141249
IT-05 | 2024-04-01 00:00:00 | 130636 | 135507 | 140374 | 145245 | 150118 | 154990 | 159860 | 164731 | 170237 |  | 4- Employee 4 is an employee that joined our team on August 29, 2024 - I'd like to be able to calculate how much salary I've paid her and forecast for the remainder of fiscal year.
[NAME] | 2024-04-01 00:00:00 | 60424 | 62721 | 65104 |  |  |  |  |  |  |  | 5-Employee 5 is NOT an IT (classification).  He's an AS.  (different pay, different amount of steps etc..)  I've added the CBA dates in the tables
AS-02 | 2024-04-01 00:00:00 | 67330 | 69888 | 72544 |  |  |  |  |  |  |  | 6-Employee 6 is a complicated case.   He joined our team on June 8th, 2024 (IT-02) then was acting for his supervisor from September 9th to January 8th.  He will go back in his IT02 role for  the remainder of the fiscal OR may be extended his acting IT03 until March 31st.
AS-03 | 2024-04-01 00:00:00 | 72171 | 74910 | 77622
AS-04 | 2024-04-01 00:00:00 | 78834 | 81829 | 85187
AS-05 | 2024-04-01 00:00:00 | 94113 | 97689 | 101750
Collective Bargaining Agreement Increment
Level | Eff. date | Step 1 | Step 2 | Step 3 | Step 4 | Step 5 | Step 6 | Step 7 | Step 8 | Step 9
IT-01 | 2024-12-22 00:00:00 | 69361 | 71925 | 74485 | 77031 | 79574 | 82120 | 84661 | 89382 | 89382
IT-02 | 2024-12-22 00:00:00 | 85854 | 88603 | 91348 | 94095 | 96844 | 99589 | 102335 | 105080 | 105080
IT-03 | 2024-12-22 00:00:00 | 101343 | 104833 | 108324 | 111817 | 115306 | 118791 | 122281 | 125914 | 125914
IT-04 | 2024-12-22 00:00:00 | 116037 | 120038 | 124039 | 128039 | 132039 | 136041 | 140039 | 144434 | 144434
IT-05 | 2024-12-22 00:00:00 | 133582 | 138563 | 143539 | 148520 | 153503 | 158485 | 163465 | 168446 | 174076
[NAME] | 2024-06-21 00:00:00 | 61786 | 64135 | 66572
AS-02 | 2024-06-21 00:00:00 | 68849 | 71464 | 74180
AS-03 | 2024-06-21 00:00:00 | 73798 | 76599 | 79511
AS-04 | 2024-06-21 00:00:00 | 80612 | 83675 | 87108
AS-05 | 2024-06-21 00:00:00 | 96235 | 99892 | 104044