Thread: Minimum Pricing Formula - Please Help if you can with Excel

Hi,

I am trying to put together a one-cell excel formula so that I can calculate a minimum selling price for a given item after all expenses, commissions, and taxes(vat) are paid.

My costs are:

Item purchase price excluding VAT@15%
0.83p per item
14.35% comissions of the gross selling price
1.5% comissions of the gross selling price
15% vat of the gross selling price

My formula needs to factor in a minimum of .35 pence NET profit after all is paid.

Can anyone help? Any help on this would be very much appreciated!

Originally Posted by SamK

Hi,

I am trying to put together a one-cell excel formula so that I can calculate a minimum selling price for a given item after all expenses, commissions, and taxes(vat) are paid.

My costs are:

Item purchase price excluding VAT@15%
0.83p per item
14.35% comissions of the gross selling price
1.5% comissions of the gross selling price
15% vat of the gross selling price

My formula needs to factor in a minimum of .35 pence NET profit after all is paid.

Can anyone help? Any help on this would be very much appreciated!

Code:

=0.83E2/(14.35+1.5+15) + 0.35

The E2 means (from 10^-2 on the bottom) and is in to make the percentages decimals. Without the 0.35 would be the net selling price without profit so adding that number means you'll get 0.35p profit after "tax" (ie: the commission and VAT). That should give you a 0.35p profit each time, I'm not sure how you'd change it to get a variable amount I suppose a percentage of net value might work for a profit. Dividing by them shows the total cost
The 0.83 is the original buying price.

If you need cell referencing let me know. Personally I would put the original buying price in a cell and refer to it (it will be B3 in the following example), same with the profit margin (B4 in the following example)

Code:

=$B$3*1E-2/(14.35+1.5+15) + $B$4

You can make it moreso using absolute cell referencing but I'll post it as an attachment.

Hello,

Thank you very much for your reply and help. In my haste to get some help I forgot to mention that the items cost price varies and then we start adding on top of that vat +.75+.08 (thats where the .83 comes from) and then all the rest.

So it should be Item price+VAT, then .75 for shipping+0.08 handling, then 14.35% Commissions on the gross selling price, then 1.5% commissions on the gross selling price again, then finally another 15%vat to be taken into consideration of the gross selling price. Absolute net profit has to be a minimum of .35p.

Could I please be cheeky and ask you to post me the formula as it would appear in a single cell? Also does this take into account the output vat on the gross amount?

Once again I cannot stress enough how much this is helping me, so your help is very much appreciated.

Kind Regards,

Sam

Originally Posted by SamK

Hello,

Thank you very much for your reply and help. In my haste to get some help I forgot to mention that the items cost price varies and then we start adding on top of that vat +.75+.08 (thats where the .83 comes from) and then all the rest.

So it should be Item price+VAT, then .75 for shipping+0.08 handling, then 14.35% Commissions on the gross selling price, then 1.5% commissions on the gross selling price again, then finally another 15%vat to be taken into consideration of the gross selling price. Absolute net profit has to be a minimum of .35p.

Could I please be cheeky and ask you to post me the formula as it would appear in a single cell? Also does this take into account the output vat on the gross amount?

Once again I cannot stress enough how much this is helping me, so your help is very much appreciated.

Kind Regards,

Sam

Your post is quite hard to follow I'm afraid - did you mean that you start off with a product that costs n pence (inc VAT) to buy and then you have to add 0.75 shipping and 0.08 handling (these two are constants?)

From that you add the 14.5% commission and the 1.5% commission and finally the VAT at 15%. Also those three are cumulative so the 1.5% acts upon the 14.5% already added too
As this will be the total cost an extra 0.35 must be added to ensure your profit.

Is that right?

If so (and assuming n is in cell A8)
Part 1 (cost to buy):

Code:

k =(A8*(1+0.15) + 0.08 + 0.75)

Purchase VAT
Shipping and Handling

Note I've used k to make it simpler in the next steps:

Part 2 (selling costs)

Code:

k+0.145k + 0.015(k+0.145k) + 0.15(0.015(k+0.145k)) + 0.35

14.5% commission
1.5% commission
15% VAT on selling
0.35p profit margin

Reintroducing our expression for k:

Code:

(A8*(1+0.15) + 0.08 + 0.75)+0.145(A8*(1+0.15) + 0.08 + 0.75) + 0.015((A8*(1+0.15) + 0.08 + 0.75)+0.145(A8*(1+0.15) + 0.08 + 0.75)) + 0.15(0.015((A8*(1+0.15) + 0.08 + 0.75)+0.145(A8*(1+0.15) + 0.08 + 0.75))) + 0.35

I think that's right but you may want to check for yourself when subbing in your expression for k

Originally Posted by e^(i*pi)

Your post is quite hard to follow I'm afraid - did you mean that you start off with a product that costs n pence (inc VAT) to buy and then you have to add 0.75 shipping and 0.08 handling (these two are constants?)

- Yes. n can be any amount example £16.87. The 0.75 and 0.08 are constants.

From that you add the 14.5% commission and the 1.5% commission and finally the VAT at 15%. Also those three are cumulative so the 1.5% acts upon the 14.5% already added too
As this will be the total cost an extra 0.35 must be added to ensure your profit.

Is that right?

Again correct. The only thing is its 14.35% instead of 14.5

If so (and assuming n is in cell A8)
Part 1 (cost to buy):

Code:

k =(A8*(1+0.15) + 0.08 + 0.75)

Purchase VAT
Shipping and Handling - Correct

Note I've used k to make it simpler in the next steps:

Part 2 (selling costs)

Code:

k+0.145k + 0.015(k+0.145k) + 0.15(0.015(k+0.145k)) + 0.35

14.5% commission
1.5% commission
15% VAT on selling
0.35p profit margin

Reintroducing our expression for k:

Code:

(A8*(1+0.15) + 0.08 + 0.75)+0.145(A8*(1+0.15) + 0.08 + 0.75) + 0.015((A8*(1+0.15) + 0.08 + 0.75)+0.145(A8*(1+0.15) + 0.08 + 0.75)) + 0.15(0.015((A8*(1+0.15) + 0.08 + 0.75)+0.145(A8*(1+0.15) + 0.08 + 0.75))) + 0.35

I think that's right but you may want to check for yourself when subbing in your expression for k[/quote]

I have checked this with an example of n=22.31 - (this is from 19.40 excl. vat). It gives me a result of 31.20 as a minimum selling price.

Am I right to break it down like this:

15.85% of 31.20 = 4.94
15% of 31.20 = 4.68

4.68 + 4.94 + 0.75 + 0.08 = 10.45
31.20 - 10.45 = 20.75
20.75 - 22.31 = -1.56

Have I done something wrong with the above breakdown?

I'm sorry if anything is not clear. Please feel free to PM me if you would like to chat.

Thank you once again for your time.

Regards,

Sam