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]

Is the Profit Goal 15% of total invoice or 15% of total cost?

 

[OrangeYoda]

The way I've been thinking about this it would be Total Costs + 15% = Total Invoice
I'm open to other ways of thinking that get me to the same or similar answer however.

 

[BadgerRoad]

I have the algebra done, I think. Let me put it into a spreadsheet.

 

[BadgerRoad]

Book1
	A	B	C
1
2	Parts cost		$ 100.00
3	Labor	10%	$ 31.19
4	Labor 2		$ 40.00
5	Overhead		$ 100.00
6	Hrs on site	2
7	Profit Goal	15%	$ 40.68
8	Total Invoice		$ 311.86
Sheet1

Cell Formulas
Range	Formula
C3	C3	=0.1*C8
C4	C4	=B6*20
C5	C5	=B6*50
C7	C7	=(C2+C3+C4+C5)*B7
C8	C8	=(1+B7)*(C2+C4+C5)/(1-B3*(1+B7))

 

[OrangeYoda]

I have the algebra done, I think. Let me put it into a spreadsheet.

I very much appreciate you trying this for me. I've thrown every bit of Excel magic I know to use at this one. Might take a look at the Solver if this doesn't work. It's been some years since I've needed to use that function though, and I'm not sure it'll be the right tool for this job anyway.

 

[BadgerRoad]

Take a look at the spreadsheet I posted and see if it looks correct.

 

[OrangeYoda]

I have your example queued up next to mine currently and trying to figure it out. I've got a different output, but that may have been me. I'm I'm "debugging" currently.

Assuming this works, this is a much simpler solution than I was trying. You've replaced a paragraph long formula with some simple helper columns.

Edit: Meant to add real results pic. as well.
Edit 2: Typos updated and it works very well. Thank you!

 

[OrangeYoda]

Take a look at the spreadsheet I posted and see if it looks correct.

Update. This works remarkable well. Especially considering how simple it is. You've taken a book-style formula and made it a pamphlet.

I simply can't thank you enough!

 

[OrangeYoda]

One question. What is the purpose of the "1"s in your formula? I've taken them out and put them back in to see the effect, but can't wrap my head around their operational purpose.