What is the price when i have margin, cost and discount

[PHolt]

Hi,

Hope somebody can help with the below formula, the 1st, 3rd and 4th columns we fill in. I have set up formula's to work out the next 3.

I'm try to work out the list price, starting at the cost but incorparating the discount and making a 22% margin. The first table works final until you look at the 2nd table were the Margin% is wrong.
A B C D E F G

[TABLE="width: 500, align: left"]
<TBODY>[TR]
[TD]Costs[/TD]
[TD]List Price[/TD]
[TD]Discount%[/TD]
[TD]Margin%[/TD]
[TD]Margin£[/TD]
[TD]List Price[/TD]
[TD]Sell Price[/TD]
[/TR]
[TR]
[TD]£29.94[/TD]
[TD]=G2[/TD]
[TD]35%[/TD]
[TD]22%[/TD]
[TD]=A2*D2[/TD]
[TD]=((D2*A2)+A2)/(1-C2)*1[/TD]
[TD]=B2-(B2*C2)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]£56.20[/TD]
[TD][/TD]
[TD][/TD]
[TD]£6.59[/TD]
[TD]£56.20[/TD]
[TD]£36.53[/TD]
[/TR]
</TBODY>[/TABLE]








Based on the figures these formula generate, it is checked in another table

V W X Y

[TABLE="width: 500"]
<TBODY>[TR]
[TD]List Price[/TD]
[TD]Less 35%[/TD]
[TD]Margin£[/TD]
[TD]Margin%[/TD]
[/TR]
[TR]
[TD]£56.20[/TD]
[TD]=V9-(V9*35%)[/TD]
[TD]=W9-A2[/TD]
[TD]=X9/W9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]£36.53[/TD]
[TD]£6.59[/TD]
[TD]18%[/TD]
[/TR]
</TBODY>[/TABLE]


Can someone please have a look and see where I have gone wrong, as I would need the margin%(Y2) in the second table to show in this case 22%.


Thanks for all your help
Pam

 

[Stiuart_W]

It looks like youve calculated the list price based on achieving a margin of 22%. You've then discounted that by 22% so clearly you won't have a final marging of 22%. it will be less than that (as you have calculated)

 

[PHolt]

But how would I fixed this formula so the last one is 22%?

 

[Stiuart_W]

Assumptions:-

1) List price is the item sale price BEFORE discount
2) Sell price is the list price LESS the discount
3) You want to achieve a margin of 22% (or whatever is in D2) based on the SELL PRICE (you haven't been very clear on that)

If Above is correct then


Cell G2 (Sell price) = A2(D2+1)
Cell F2 (List Price) = G2/(1-C2)

Hope this helps

 

[PHolt]

Hi,

This gives me the same results, highligted in Red is the table that is used to check the figures.


[TABLE="class: cms_table"]
<TBODY>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[/TR]
[TR]
[TD]Costs
[/TD]
[TD]List Price
[/TD]
[TD]Discount
[/TD]
[TD]Margin%
[/TD]
[TD]Margin£
[/TD]
[TD]List Price
[/TD]
[TD]Sell Price
[/TD]
[TD]Less 35%
[/TD]
[TD]Margin£
[/TD]
[TD]Margin%
[/TD]
[/TR]
[TR]
[TD]£29.94
[/TD]
[TD]=F2
[/TD]
[TD]35%
[/TD]
[TD]22%
[/TD]
[TD]=A2*D2
[/TD]
[TD]=G2/(1-C2)
[/TD]
[TD]=A2*(D2+1)
[/TD]
[TD]=B2-(B2*35%)
[/TD]
[TD]=H2-A2
[/TD]
[TD]=I2/H2
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]£56.20
[/TD]
[TD][/TD]
[TD][/TD]
[TD]£6.59
[/TD]
[TD]£56.20
[/TD]
[TD]£36.53
[/TD]
[TD]£36.53
[/TD]
[TD]£6.59
[/TD]
[TD]18.03%
[/TD]
[/TR]
</TBODY>[/TABLE]

The margin in Red needs to show 22%, I understand how I have got this figure of 18.03%, but I can't see how to change the formula so the margin in column J states 22%

Thanks

 

[Stiuart_W]

J2 should be =I2/A2

 

[PHolt]

Thank you