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

Costs	List Price	Discount%	Margin%	Margin£	List Price	Sell Price
£29.94	=G2	35%	22%	=A2*D2	=((D2*A2)+A2)/(1-C2)*1	=B2-(B2*C2)
	£56.20			£6.59	£56.20	£36.53

<TBODY>
</TBODY>

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

V W X Y

List Price	Less 35%	Margin£	Margin%
£56.20	=V9-(V9*35%)	=W9-A2	=X9/W9
	£36.53	£6.59	18%

<TBODY>
</TBODY>

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.

A	B	C	D	E	F	G	H	I	J
Costs	List Price	Discount	Margin%	Margin£	List Price	Sell Price	Less 35%	Margin£	Margin%
£29.94	=F2	35%	22%	=A2*D2	=G2/(1-C2)	=A2*(D2+1)	=B2-(B2*35%)	=H2-A2	=I2/H2
	£56.20			£6.59	£56.20	£36.53	£36.53	£6.59	18.03%

<TBODY>
</TBODY>

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