Creating a band quote calculator

[Stemond]

Hi

I have some excel documents that calculate wages for band members depending on certain criteria and band cost. However I want to reverse it so I put in the criteria, enter the band member hourly rate and works out how much the total quote is. It’s difficult trying to explain without showing you the sheet but essentially I want to enter:

Example:
Band members - 3
Hourly rate - £20
Hours working - 12

Expenses:
Car hire - £30
Fuel - £100
Accommodation - £50

Although that seems a simple formula, the problem is that there’s is always 3 or 4 members, but 1 member has a different hourly rate to the others. The breakdown is

4 members:
1st - 30% after expenses
2nd - 23.3% after
3rd - 23.3%
4th - 23.3%

3 members:
Same as above but the 4th wage is split equally amongst the other 3.

This is where I get confused and can’t work out the formula, I want to base the calculator on the 23.3% figure not 30% but it does still need to Ben account d for in the background. If none of that makes sense I can upload the excel sheet if there’s a way.

Many Thanks

Steve

 

[steve the fish]

Re: Help with creating a band quote calculator

This is a bit unclear. Is the £20 for each band member or total? If its for each member is it based on the lower paid or the higher paid? Are the expenses each or total?

 

[Stemond]

Re: Help with creating a band quote calculator

This is a bit unclear. Is the £20 for each band member or total? If its for each member is it based on the lower paid or the higher paid? Are the expenses each or total?

The £20 is per the 3x 23.3% members, the 30% I’m hoping will be automatically calculated from that. Expenses are total and taken before the hourly rate is taken.

 

[steve the fish]

Re: Help with creating a band quote calculator

Sorry im still not getting it. In the example you have what is the result?

 

[oldbrewer]

Re: Help with creating a band quote calculator

4 members:
1st - 30% after expenses
2nd - 23.3% after
3rd - 23.3% 4 3
4th - 23.3% 1 30 37.77777667
2 23.33333333 31.11111
3 members: 3 23.33333333 31.11111
Same as above but the 4th wage is split equally amongst the other 3. 4 23.33333333 n/a




Example:
Band members - 3
Hourly rate - £20 so for 3 band members
Hours working - 12
3 x 12 x 20 = 720

expenses = 180
Car hire - £30
Fuel - £100
Accommodation - £50 wages = 540


first member 204.000012
second member 167.99994
third member 167.99994
TOTAL 539.999892



is this correct

and in your example how is the hourly rate £20

 

[Stemond]

Re: Help with creating a band quote calculator

Sorry im still not getting it. In the example you have what is the result?

Apologies, here is a screenshot of what I currently use, the 1st is for the 3 piece, 2nd is for a 4 piece. The figures I've given above are just examples, I'm not after an answer, I'm after a formula or spreadsheet to calculate any figures I give it, ive mocked up an example in the 3rd screenshot.

Currently all the fields with red text are what I input, this calculator works perfectly when I input a band net pay and works out the hourly rate. What I want is the reverse of this so I input an hourly pay and it gives me the net pay. But as theres a band member who gets more its confusing me so can't work it out.