I have a compliated situation I need to make a workbook for. I need to get some background out first.

We are a cleaning company. We clean holiday parks and our staff are paid by the unit. There are a range of different price bands depending on the unit, for ease lets call them small, medium and large. Occasionally there are refresh (half the price) and deep (double price) cleans. The cleaners work in teams and the unit pay they earn is split between that team.

That is the simple part.

The complex part is that we do not always have the same staff on site each day we are there. The teams are often made up of different people from one day to the next because of this. The teams are sometimes made up of different numbers of people.

I need a spreadsheet that will work out the pay of each person, taking into account the days they have and have not worked, what units they have worked in, and who they have worked with (and therefore split that days money with).

Its a big ask.... can anyone take it on?