How to arrive at Sales price calculation

[Amit gupta]

Hello

I am stuck up with a problem where I need help on how to calculate sales price for a product I wish to sell. I have a product cost , shipping cost (fixed), profit (% on sales price), Vat is 20% of sales price (i.e. 1/6th of sales price) and amazon category fees which is 20% on sales price in my case ( on rare occasion it varies to 8% or 12% as well).

The main challenge is that while calculating Vat and Amazon category fees individually we require sales price including category fees while calculating vat and similarly we require sales price including vat while calculating Amazon category fees. I am selling my stuff on mere idea basis and at times I loose money on sales due to my wrong way of sales price calculation.

I would really appreciate if any one can help me with a concrete and best solution to the query stated above.

I would prefer a simple excel formula than opting for macros / vba code / or any other mode.

Thanks in advance in anticipation to get prompt and positive response.

Best Regards
Amit

 

[joeu2004]

I need help on how to calculate sales price for a product I wish to sell. I have a product cost , shipping cost (fixed), profit (% on sales price), Vat is 20% of sales price (i.e. 1/6th of sales price) and amazon category fees which is 20% on sales price in my case ( on rare occasion it varies to 8% or 12% as well).

I don't agree with calculating %profit based on total sales price, especially if VAT is included.

Be that as it may, the following is the formula that you seek:

=ROUNDUP((B1+B2) / (1-B3-B4-INDEX(B6:B8,B5)), 2)

where:

[TABLE="class: grid, width: 190"]
<tbody>[TR]
[TD]
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]cost
[/TD]
[TD="align: right"]100.00
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]shipping[/TD]
[TD="align: right"]10.00[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]%profit[/TD]
[TD="align: right"]15.00%[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]%vat[/TD]
[TD="align: right"]20.00%[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]amazon cat[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]%amazon1[/TD]
[TD="align: right"]20.00%[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]%amazon2[/TD]
[TD="align: right"]8.00%[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]%amazon3[/TD]
[TD="align: right"]12.00%[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]sales price[/TD]
[TD="align: right"]244.45[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]-shipping
[/TD]
[TD="align: right"]-10.00[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]-cost
[/TD]
[TD="align: right"]-100.00[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]-vat
[/TD]
[TD="align: right"]-48.89[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]-amazon
[/TD]
[TD="align: right"]-48.89[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]profit[/TD]
[TD="align: right"]36.67[/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]%profit[/TD]
[TD="align: right"]15.00%[/TD]
[/TR]
</tbody>[/TABLE]

B10: =ROUNDUP((B1+B2)/(1-B3-B4-INDEX(B6:B8,B5)),2)
B11: =-B2
B12: =-B1
B13: =-ROUND(B10*B4,2)
B14: =-ROUND(B10*INDEX(B6:B8,B5),2)
B15: =SUM(B10:B14)
B16: =B15/B10

In B10, it is debatable whether to use ROUND or ROUNDUP.

B11:B16 check the result in B10. B16 should be about the same as B3.

FYI, the following is the derivation of the formula in B10:

tot = cost + shipping + tot*prof% + tot*vat% + tot*amazon%
So:
tot - tot*prof% - tot*vat% - tot*amazon% = cost + shipping
tot*(1 - prof% - vat% - amazon%) = cost + shipping
tot = (cost + shipping) / (1 - prof% - vat% - amazon%)