Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Math Help - Confusing pricing problem for MS Excel automation.

  1. #1
    Newbie
    Joined
    Jan 2009
    Posts
    7

    Confusing pricing problem for MS Excel automation.

    Hoping a budding mathematician can help with a problem I’ve got. I’ve been playing around with this one in MS Excel and I’m afraid it has me beat.

    Here’s what I’m trying to do
    I start with a Sell Price from which there are fixed costs that I know will be incurred.

    Sell Price 5000

    Cost1 547.50 (Generated from sell price)

    Cost2 800 (Generated from sell price)

    Tax 3% on buy price ?? Will equal 92.19

    Cost Total 1347.50 plus 3% tax on buy price(1439.69)

    Sell less known costs 3652.5 should include the 3% tax on buy price(3560.31)

    Profit Margin (10% on Sell less known costs) ?? Will equal 454.63

    Buy Price ?? Will equal 3073.00

    Essentially I’m trying to work out how to construct an Excel formula to calculate all this for me automatically from the information I know starting at a sell price and working back.
    Is this possible anybody?? 
    Follow Math Help Forum on Facebook and Google+

  2. #2
    Bar0n janvdl's Avatar
    Joined
    Apr 2007
    From
    Meh
    Posts
    1,630
    Thanks
    6
    Quote Originally Posted by intglad View Post
    Hoping a budding mathematician can help with a problem Iíve got. Iíve been playing around with this one in MS Excel and Iím afraid it has me beat.

    Hereís what Iím trying to do
    I start with a Sell Price from which there are fixed costs that I know will be incurred.

    Sell Price 5000

    Cost1 547.50 (Generated from sell price)

    Cost2 800 (Generated from sell price)

    Tax 3% on buy price ?? Will equal 92.19

    Cost Total 1347.50 plus 3% tax on buy price(1439.69)

    Sell less known costs 3652.5 should include the 3% tax on buy price(3560.31)

    Profit Margin (10% on Sell less known costs) ?? Will equal 454.63

    Buy Price ?? Will equal 3073.00

    Essentially Iím trying to work out how to construct an Excel formula to calculate all this for me automatically from the information I know starting at a sell price and working back.
    Is this possible anybody?? 
    How do you generate the costs from the sell price? What's the percentage?
    Follow Math Help Forum on Facebook and Google+

  3. #3
    Newbie
    Joined
    Jan 2009
    Posts
    7
    janvdl,

    They are actually just dollar values that come off a table.
    For the purpose of the problem you could just assume it a fixed amount.
    Follow Math Help Forum on Facebook and Google+

  4. #4
    Bar0n janvdl's Avatar
    Joined
    Apr 2007
    From
    Meh
    Posts
    1,630
    Thanks
    6
    Quote Originally Posted by intglad View Post
    janvdl,

    They are actually just dollar values that come off a table.
    For the purpose of the problem you could just assume it a fixed amount.
    This should be quick. Check back in 15-20 minutes.
    Follow Math Help Forum on Facebook and Google+

  5. #5
    Bar0n janvdl's Avatar
    Joined
    Apr 2007
    From
    Meh
    Posts
    1,630
    Thanks
    6
    Please check your original profit margin, you made a mistake there. It should be 356.03 instead of 454.63

    And I assume the buy price = Selling price - (Known Costs + Tax + Profit)

    See the attachment, and if it works correctly.
    Attached Files Attached Files
    Follow Math Help Forum on Facebook and Google+

  6. #6
    Newbie
    Joined
    Jan 2009
    Posts
    7
    janvdl

    Thank you for that. I tried the spreadsheet and the tax in this case is not fixed but will be based on the buy price. I thought I could just use reciprocal calculations to find the buy price but doesn't work.
    Follow Math Help Forum on Facebook and Google+

  7. #7
    Bar0n janvdl's Avatar
    Joined
    Apr 2007
    From
    Meh
    Posts
    1,630
    Thanks
    6
    Quote Originally Posted by intglad View Post
    janvdl

    Thank you for that. I tried the spreadsheet and the tax in this case is not fixed but will be based on the buy price. I thought I could just use reciprocal calculations to find the buy price but doesn't work.
    Yes you should be able to.

    Since in this case the tax is \frac{3}{100} \times Buy Price ; then the Buy price should be: \frac{100}{3} \times Tax.

    If the tax rate was 4 percent, it would just be \frac{4}{100} and \frac{100}{4}
    Follow Math Help Forum on Facebook and Google+

  8. #8
    Newbie
    Joined
    Jan 2009
    Posts
    7
    janvdl

    It's great trying knock this one off. hehe
    The problem here is that I'm attempting to generate the buy price in order to calculate the tax and hence the profit margin.
    The three elements that won't calculate so far are the tax, buy price and the profit margin.
    Is it possible given the information we have?
    Follow Math Help Forum on Facebook and Google+

  9. #9
    Bar0n janvdl's Avatar
    Joined
    Apr 2007
    From
    Meh
    Posts
    1,630
    Thanks
    6
    Quote Originally Posted by intglad View Post
    janvdl

    It's great trying knock this one off. hehe
    The problem here is that I'm attempting to generate the buy price in order to calculate the tax and hence the profit margin.
    The three elements that won't calculate so far are the tax, buy price and the profit margin.
    Is it possible given the information we have?
    Please give me the exact question as it is on the paper. With only the information you were given, no information that you calculated yourself.
    Follow Math Help Forum on Facebook and Google+

  10. #10
    Newbie
    Joined
    Jan 2009
    Posts
    7
    Known Information.

    Sell Price 5000

    Cost1 547.50 (Generated from sell price)

    Cost2 800 (Generated from sell price)



    Information to be calculated.

    Profit Margin (10% on Sell less known costs including the 3% tax)

    Tax 3% on buy price that will be recorded as a cost

    Buy Price
    Follow Math Help Forum on Facebook and Google+

  11. #11
    Bar0n janvdl's Avatar
    Joined
    Apr 2007
    From
    Meh
    Posts
    1,630
    Thanks
    6
    Quote Originally Posted by intglad View Post
    Known Information.

    Sell Price 5000

    Cost1 547.50 (Generated from sell price)

    Cost2 800 (Generated from sell price)



    Information to be calculated.

    Profit Margin (10% on Sell less known costs including the 3% tax)

    Tax 3% on buy price that will be recorded as a cost

    Buy Price
    The known costs are 1347,50

    So we can construct the following formula from the info given:

    Profit = \frac{10}{100} \left( 5000 - \left( 1347,50 + \frac{3}{100} \left(  Buy \ Price \right) \right) \right)

    I can only simplify it to this:

    Profit = 365,25 - \frac{3}{1000} \left( Buy \ Price \right)

    I could be missing something, but otherwise we'll need more info to calculate this one...
    Follow Math Help Forum on Facebook and Google+

  12. #12
    Bar0n janvdl's Avatar
    Joined
    Apr 2007
    From
    Meh
    Posts
    1,630
    Thanks
    6
    Otherwise they could mean that the tax is in fact included with the profit margin, and not subtracted from it.

    So we can say:

    Profit \ Margin = \frac{10}{100} \left( 5000 - 1347,50 \right) = 365,25

    We at least have the profit margin that way...
    Follow Math Help Forum on Facebook and Google+

  13. #13
    Newbie
    Joined
    Jan 2009
    Posts
    7
    janvdl

    Thanks for helping my friend.
    I've tried nutting it out and felt another piece of information might have been required.
    I'll keep playing around in Excel and will post back if I happen to get it.
    Follow Math Help Forum on Facebook and Google+

  14. #14
    Bar0n janvdl's Avatar
    Joined
    Apr 2007
    From
    Meh
    Posts
    1,630
    Thanks
    6
    Quote Originally Posted by intglad View Post
    janvdl

    Thanks for helping my friend.
    I've tried nutting it out and felt another piece of information might have been required.
    I'll keep playing around in Excel and will post back if I happen to get it.
    Alright. I usually miss simple little things, so just keep an eye open.

    I'm curious, is this a homework problem, or something you are trying to calculate out of interest?
    Follow Math Help Forum on Facebook and Google+

  15. #15
    Newbie
    Joined
    Jan 2009
    Posts
    7
    It's a legitimate business calculation.
    I've been working this stuff semi manually in Excel but always figured it could be completely calculated.
    It would be awesome to figure it out if it is possible because if you have a product that know how much you could sell it for, the calculation to establish a buy price would be a very handy tool indeed.
    Follow Math Help Forum on Facebook and Google+

Page 1 of 2 12 LastLast

Similar Math Help Forum Discussions

  1. Car pricing program in Excel
    Posted in the Business Math Forum
    Replies: 0
    Last Post: April 27th 2011, 04:42 PM
  2. Excel problem
    Posted in the Business Math Forum
    Replies: 3
    Last Post: November 22nd 2010, 11:56 AM
  3. Excel problem
    Posted in the Math Software Forum
    Replies: 0
    Last Post: August 5th 2010, 10:33 AM
  4. extrapolation on european call pricing problem
    Posted in the Advanced Applied Math Forum
    Replies: 0
    Last Post: April 10th 2010, 01:40 PM
  5. Replies: 4
    Last Post: May 19th 2009, 12:16 AM

Search Tags


/mathhelpforum @mathhelpforum