We are building 5 year financial model and considering to put 3% salary increment every year based on hire date data.
FOR EXAMPLE
Dept | Annual Salary | Year of service | hire date | Annual Bonus | Taxes | Benefits | Total Burden | oct-20 | Nov-20 |
Support | 60000 | 10.3 | 11/22/2010 | 00 | 7860 | 1423 | 69283 | 4819 | 4819 |
Suppose support person was hire on 11/22/2010 but our model is built from 2020. so we only want to go 12 months back. So support salary is 60000 from NOV 2020 to Nov 2021 and from Dec 2021 support will get 3% increment so his new salary will be $New Annual Salary(Previous Annual salary*0.03+Previous Annual Salary) + new annual salary tax + benefit= New total company burden from Dec 2021 to Nov 2022. we want to distribute Monthly Break down (Dec 2021 to Nov 2022 New total company burden/12) from dec to 2021 to Nov 2022. From Dec 2022 he will get another increment of 3% which will revise his salary to Dec 2021 to Nov 2022 Annual salary (Dec 2021 to Nov 2022 annual salary*0.03%+Dec 2021 to Nov 2022 annual salary)+ Dec 2022 to Nov 2023 Tax + Benifits= Total New Burden from Dec 2022 to Nov 2023 and distribute on monthly base
How can we automize this process so it can pull monthly revenue increment in our model ?
