Stable profit margin

[enitron]

Hello!!!

I have a problem at work and i was wondering if you could help me out.

I am creating pricelists and i want a formula to help me change the prices automatically in order to have a stable profit margin.

For example the buying price for product 001 is 13.40 in order to have a 30% profit margin i must sell it for 19,10. So i want a formula to check the prices and increase them or decrease them in order to have a 30% profit margin. So if the price was 18 to check that the profit is lower than 30% and change it to 19.10

Is something like that possible????

I have some huge pricelist and it takes me too much time to change every single price.

Thank you in advance!!!!!

[enitron]

Anyone?????

[Sam Capricci]

it is possible but it would be VERY helpful if you could upload a sample with enough information to see what you are working with AND your expected results so people can know when to increase the amount and when to decrease it.

[enitron]

Hi.
Thank you very much for your answer.
I have just upload a test excel sheet.

So i want the Street Price € (including VAT 19%) to changes automatically in order our client margin to be stable at 38% and our margin to be stable at 30%

Do you think we can find anything???
Please help!!!!

Thank you so much

[Pepe Le Mokko]

Anyone?????

Administrative Note:

We don't expect you to bump threads sooner than 24 hours after your last post or more than once a day. Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.

[enitron]

So sorry if i became persistant or roud it wasn't in my intentions!!!
I really appreciate all the help i am getting through this forum and of course i can wait as long as it needs.
Thank you very much for the reminder and really a huge thank you to all the members for the help!!!!!

[enitron]

One correction at my previous post i want our margin to be stable at 30%, the clients margin i have manage to keep it stable at 38%

Thank you very much!!!!

[enitron]

In order to help more, i have attached again an example with the initial prices and how i want to change with a function automatically.
i want the Street Price € (including VAT 19%) to changes automatically in order our margin to be stable at 30%

Thank you soooooooooo much in advance!!!!

[MrShorty]

This feels to me like more of an algebra problem than a programming problem.

Enter 0.3 into Q2
Current formula in Q2 is =(M2-L2)/M2. Solve for M2 and enter that formula into M2 (If you need to refresh your algebra, this might be a useful page: https://www.purplemath.com/modules/solvelin3.htm ).
Current formula in M2 is =N2*0.62. Solve for N2 =M2/0.62 and enter that into N2.
Current formula in N2 is =O2/1.19. Solve for O2 (=N2*1.19) and enter that formula into O2.
Copy those formulas down and you should have a spreadsheet where you can enter 0.3 (or whatever your desired profit margin is) into column Q and the sheet will compute the street price in column O. The hardest part (that I've left to you to do) is solving Q=(M-L)/M for M.

[enitron]

So sorry but i really didn't understand a thing!! My english are rusted.
Can you please attach the test excel with the changes because in my book it doesn't seem to work. Maybe i am doing something wrong.

Thank you very much for your help!!!

[MrShorty]

I did not actually edit your sheet. I just read the existing formulas and saw the algebraic logic that would be needed to get the inverse. I didn't actually do any of it (I figured you'd be able to handle it).

After doing the algebra, what formulas did you end up with in M2, N2, and O2?

[Sam Capricci]

I agree with Mr Shorty, if you want something at a 30% markup just multiply that value by 1.3. Same for 38% markup, X*1.38. I had trouble following your math.

[enitron]

I did not actually edit your sheet. I just read the existing formulas and saw the algebraic logic that would be needed to get the inverse. I didn't actually do any of it (I figured you'd be able to handle it).

After doing the algebra, what formulas did you end up with in M2, N2, and O2?

Ok. Maybe i am not smart enough but i didn't handle it . So please if you could prove that your logic is working by editing my sheet i would be really grateful!

To explain again. I want a formula to change the Street Price € (including VAT 19%) so i will have a stable 30% profit margin. I have a pricelist Street Price € (including VAT 19%) that give me different profit margin, but i want to change it so i will get a 30%.

Thank you very much again for your time.

[Sam Capricci]

So in your workbook in post #8, going by the listed items in columns K and to the right, take Item 1, you list your buying price at 16.00.
In this country we have no VAT so walk us through how you want each item to be priced?
Because my math would be that we take our purchase price, add in costs (labor and fixed costs) to get to the final price which would still be our cost, then multiply that by 1.3 if I wanted a 30% markup.
And please don't take comments as directed at you personally, I doubt anyone on here thinks you aren't smart enough to handle it.

[MrShorty]

I'm still not sure how much of this is an algebra question outside of Excel and how much is Excel. Because we must first solve the algebra problem, let's start there. This is all basic algebra you would have learned back in secondary school, so none of this should be new to you. I cannot upload a sample file from this computer, but the edits should be easy (once we get through the algebra).

1) Enter 0.3 (or whatever you want to use for your profit margin) into Q2.
2) We have to do the algebra to solve for M2 for Q2. From your previous version, Q2=(M2-L2)/M2, so we need to solve this for M2:
Multiply both sides by M2 -> M2*Q2=M2-L2
Move all of the M2 terms to the same side -> M2*Q2-M2=-L2
Factor out M2 on the left side -> M2*(Q2-1)=-L2
Divide both sides by Q2-1 to get the final expression for M2 -> M2=-L2/(Q2-1)
Now we have our formula for M2, enter that formula into M2.
3) From the previous version, M2=N2*0.62. Solve this expression for N2 (N2=M2/0.62). Enter that formula into N2.
4) From the previous version, N2=O2/1.19. Solve this for O2 (O2=N2*1.19). Enter that formula into O2.

That should do it. Which of those edits do you have trouble with? Which of the algebra steps do you have trouble understanding?

[enitron]

OMG!!!! Its working perfectly!!! Thank you so much!!!
Indeed it was an angebra problem and now i understand what you meant by saying "solve". You wanted to solve it as an equation. I didn't understand it in the first place. Of course i can solve simple equations like that.

I can't thank you enough for your help and for your patient to explain to me everything in detail!!!! I am grateful!!!!