Results 1 to 5 of 5

Math Help - Minimum Pricing Formula - Please Help if you can with Excel

  1. #1
    Newbie
    Joined
    May 2009
    Posts
    3

    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!
    Follow Math Help Forum on Facebook and Google+

  2. #2
    -1
    e^(i*pi)'s Avatar
    Joined
    Feb 2009
    From
    West Midlands, England
    Posts
    3,053
    Thanks
    1
    Quote Originally Posted by SamK View Post
    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 10^2 (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.
    Last edited by e^(i*pi); May 17th 2009 at 12:15 PM.
    Follow Math Help Forum on Facebook and Google+

  3. #3
    Newbie
    Joined
    May 2009
    Posts
    3
    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
    Last edited by SamK; May 17th 2009 at 01:15 PM.
    Follow Math Help Forum on Facebook and Google+

  4. #4
    -1
    e^(i*pi)'s Avatar
    Joined
    Feb 2009
    From
    West Midlands, England
    Posts
    3,053
    Thanks
    1
    Quote Originally Posted by SamK View Post
    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
    Follow Math Help Forum on Facebook and Google+

  5. #5
    Newbie
    Joined
    May 2009
    Posts
    3
    Quote Originally Posted by e^(i*pi) View Post
    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
    Follow Math Help Forum on Facebook and Google+

Similar Math Help Forum Discussions

  1. Car pricing program in Excel
    Posted in the Business Math Forum
    Replies: 0
    Last Post: April 27th 2011, 03:42 PM
  2. Finding a formula for a compeitive retail pricing
    Posted in the Advanced Algebra Forum
    Replies: 1
    Last Post: June 8th 2010, 03:48 AM
  3. need help with excel formula
    Posted in the Business Math Forum
    Replies: 5
    Last Post: August 31st 2009, 05:50 AM
  4. Looking for a Keyword ranking formula on a Data set in excel
    Posted in the Advanced Statistics Forum
    Replies: 0
    Last Post: April 7th 2009, 09:27 AM
  5. Confusing pricing problem for MS Excel automation.
    Posted in the Business Math Forum
    Replies: 15
    Last Post: January 22nd 2009, 06:04 PM

Search Tags


/mathhelpforum @mathhelpforum