Price markup distribution based on total charge

[johnnyfinfan]

Hello to all, and thanks for allowing me a place to solve my Excel conundrums -

I'm self taught in Excel through trial & error, and reading through forums like this for answers, albeit far from a skilled user. Unfortunately, I can't find anything specific to what I'm trying to accomplish here.

I run an automotive service department, and I've created a parts and labor "calculator" that (hopefully) will make things easier once figured out. It figures gross profit, based on sale price and parts & labor cost, then distributes the profit percentages equally. I'm having a problem figuring out the correct formulas and/or cell content to get the part's charge to stay relative with the part's cost - For example, a $.70 part will come out to $5.00 or more at times. The labor always comes out perfect though, because there is only one labor charge.

I've attached my working file for reference and use. I'm sure there are easier formulas to use for some of this, but I've gone with what I know. Don't hold back if you have any suggestions on making what I already have easier. It is protected, but with no password. Any and all help is greatly appreciated, and if there are any questions please let me know.

Service Menu Pricing Breakdown 5-28-15.xlsx
Thanks!!

John

[Richard Buttrey]

Hi,

I think we need some specific examples. Add some parts #s and quantities and then manually enter the results you expect. Tell us which are the result cells and what calculation you have used to derive the result. No doubt we can then express that in Excel terms.

[johnnyfinfan]

Hi Richard -

I've consolidated the sheet on my file to show all factors needed to get the result. The pricing breakdown has been manually entered to reflect the results I am looking for. My objective is to be able to plug in the parts & labor data for different coupons, and have it automatically calculate the parts & labor profit split (by percentages inputted on sheet) and then distribute the profits to the different parts while keeping the individual part prices relative to their cost.

I felt like I was on the right track when I was trying to figure out a formula based on the percentage of cost:

- $5.00 ea. qt. oil X 5 quarts = $25.00
- $4.00 ea. oil filter X 1 filter = $4.00
- $1.00 ea. washer X 1 washer = $1.00
- Total part cost of $30.00

$25.00/$30.00 - 83.33% of cost is oil, so 83.33% of profit margin is applied to the oil
$4.00/$30.00 - 13.33% of cost is the oil filter, so 13.33% of the profit margin is applied to the oil filter
$1.00/$30.00 - 3.33% of cost is the washer, so 3.33% of the profit margin is applied to the washer

I just can't figure out how to lay out the equation and make it work. Please keep in mind that sometimes there can be only one part used, and other times there can be up to 4 different parts used, all with varying quantities.

Thanks again!

John
Service Pricing Breakdown.xlsx

[mick0005]

Johnny,

Maybe this will work for you. Make sure you test it out thoroughly, as I am not 100% certain I have all your requirements clear.

Note I put in a column in Q just to output the % of the gross pts margin for each part so you can take a look and confirm it.

JohnnyFinFan_Service Pricing Breakdown.xlsx

[johnnyfinfan]

Well Mick, you got me to the finish line with that one!! I did have to make a couple of small modifications to what you had put together, so I didn't have the penny variances here and there (I used the "ROUND" function).

Thanks again to both you and Richard for your help & attention!!!

John

[mick0005]

John,

Just a tip... When you find yourself up against a wall on a formula, my advice would be to attack it in pieces. For example, if you knew how to multiply the cost against the margin, but not how to find the weight of the margin... Plug in a placeholder into the formula first... I.e. X

Then, write another formula to determine the weight, ie 83.3%, which clearly you already knew how to do. The you can take that formula and just drop it in place of "X" in the other formula.

Hope this makes sense. It's easier to solve it in parts rather than a large complex formula all at once.

Mike

[johnnyfinfan]

I've never thought of it that way. Sometimes my mind is so focused on the big equation that I don't try to break it down into smaller parts. In essence, I should treat it like a puzzle.

Great suggestion Mike!!