Assist with multi-variable equation.

[OrangeYoda]

Our company runs a mix of per hour and commissioned employees, and often have both types of employees on a job. One is the sales person while the other is the installer.

I am working on updating our prices and looking for a better systematic way to drive these prices. I keep getting stuck between the variables represented by Labor-Commision and the Total Invoice Variable as they are both a % and depend on each other.

How can I tackle this issue? Maybe this way of going about it is wrong, but you all have a different method to get there.

Parts Costs	Known. $100
Labor (Commission- %)	10% of total, but must be accounted for in the Total Invoice Calc.
Labor 2 ($/HR) x Hours	Known. 20/hr
Overhead x Hours	Known overhead fee. Call it $50/hr
Hours on site	Known Flat Value. Call it 2 hrs for this example.
Profit Goal	15%
Total Invoice	Solve For this.

 

[BadgerRoad]

The algebra gets a little bit messy but hopefully the following will give you some idea. The equation that you need to solve is:
c8=c2+c3+c4+c5+c7 where these variables are the cells.
c2=c8*b3
then
c8=c8*b3+K where k is a constant which equals c3+c4+c5+c7. I'm ignoring the fact that c7 is also a function of c8 to simplify things.
so
c8-c8*b3=k
c8*(1-b3)=k
c8=k/(1-b3)
That's where the 1 comes from.
Again, this explanation is ignoring the fact that c7 is also a function of c8 which would just make the equation longer and more complicated.
Does that help?