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

 |  |  |  |  |  | IMPORTANT: Dates are in MM/DD/YYYY format
 |  |  |  |  |  |  |  | 2024-11-01 00:00:00 |  | [NAME] |  |  | CBA |  |  | Monthly Salary
Employee Name | Start date of acting (dd-mm-yyyy) | End date  of acting or retiring  ([NAME]) | Annual Salary Beginning of FY | Monthly Salary | Classification | Step | Step Anniversary Date  (MM/DD/YYYY) |  | Forecast for remainder of FY | Next Step Annual  Salary | Monthly Salary | Pro-rated [NAME] on  Anniversary Date | Dec. 22nd/June 21st Increment | 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
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 | 11/4/2024 | 500 | 1500
Emp. 2 |  |  |  |  | IT-03 | 8 | 4/1/2024
Emp. 3 retiring (April 1 to Oct 15) | 2024-04-01 00:00:00 | 2024-10-15 00:00:00 |  |  | IT-03 | 4 |  | 59325 | 0
Emp. 1 |  |  |  |  | IT-03 | 6 | 3/14/2025
Emp. 4 | August 29,2024 |  |  |  | IT-02 | 5 | 8/29/2024
Emp. 5 |  |  |  |  | AS-05 | 3 | 4/1/2024
Emp. 6a |  |  | 0 |  | IT-03 | 5 | 10/6/2024 |  |  | 0 |  |  | 0
Emp. 6b |  |  |  |  | IT-02 | 3 | 7/25/2024
Emp 6 |  |  | 0 |  | IT-02 | 5 | 10/6/2024 |  |  | 0 |  |  | 0
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