Associate a salary increase to an employee and use to calculate monthly project staffing costs

[automation monkey]

I have a project scheduling spreadsheet that I also use to create a P&L per project. At the moment it works with a fixed salary, however, salaries change with annual pay rises so I'd like to try and find a way to factor this in to my data so it stays accurate.

I have a number of sheets working together to create the project P&L. The first is a staff details sheet which has employee details including an associated.

I'd like to first find the best way to represent salary associated with a period of time. Maybe a column with salary start date and the salary columns associated with each. So, for example, After a year with us, Bob would have a second entry with a new date added to the salary start date and entries in salary and salary monthly. Unfortunately this method will end up with duplicate entries for employment start date, end date, status etc. The other way could be to have the salary start date column and salary column have multiple entries seperated with commas - this might prove problematic with how I'm using this data as follows.

I assign each employee to different projects on a Schedule sheet as follows :

I then pull the data in to per project sheets using

=FILTER(FILTER(AllStaffProjectAllocationTbl,AllStaffProjectAllocationTbl[Project Code Name]=B2), {1,0,0,1,1,1,0,0,0,1,0,0,0,1,0,0,0,0})

to generate the data as follows :

I then create a P&L on the same sheet that looks like this :

The per month salary costs are calculated through the following function in L7 and sequenced for the duration of the project

=MMULT(SEQUENCE(1,ROWS($A$18#),1,0),($L$1#>=OFFSET($A$18#,0,3,,1))*($L$1#<=OFFSET($A$18#,0,4,,1))*OFFSET($A$18#,0,5,,1))

So, however the salary details sheet incoporates the salary changes throughout an employees employment I will want to factor them in to the Salary Costs row on the Project P&L and likely need to somehow incorporate them in to the Resource sheet and the per project resource summary that's generated from it.
Does anyone have any ideas on how I can approach this? I can share a simplified version of the spreadsheet too if it helps - I'm just not sure how I do that on here as it relies on multiple sheets.

 

[automation monkey]

I'm thinking that it might make sense to extract the salary information from the staff details sheet and create a staff salary sheet as follows:

With multiple entries denoting when a salary increase happens. I'd then remove the salary information from the schedule summary sheet (it's not needed on here really - just included to copy through to the per project sheet), and then remove the salary from the per project sheet resource summary i.e. column F in the example above and then look to solve the problem of incorporating the correct salary from the new staff salary sheet in the formula in L7 in the Project P&L -

=MMULT(SEQUENCE(1,ROWS($A$18#),1,0),($L$1#>=OFFSET($A$18#,0,3,,1))*($L$1#<=OFFSET($A$18#,0,4,,1))*OFFSET($A$18#,0,5,,1))

What are the thoughts on this approach and does anyone know how I would modify the formula to work with potentially multiple salaries for the given project duration?