+ Reply to Thread
Results 1 to 11 of 11

Discount Calculation

  1. #1
    Registered User
    Join Date
    10-11-2013
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    2

    Discount Calculation

    Good day.
    I have a customer who is demanding an additional 8% discount so he can make extra margin without increasing his current selling price. I would like to calculate how much additional stock he should purchase for me to break even with this additional 8% discount. It is not as easy as working on the wholesale price less 8% and the increasing the number of units at the new discounted price to give you the same value. To set the rules please see below example.

    Cost is 100.00
    Markup is 50%
    Wholesale is this 150.00
    8% discount is -12.00
    8% discount final amount is 138.00

    Profit is 50.00 but after discount is 38.00.

    So how would you calculate this with an easy formula to show how many unit should be purchased to get to the same unit profit?

    I hope I have made it clear what I am looking for. Not the simple:

    20 * 50.00 = 1000.00 in profit thus 26.5 *38.00 will give the same profit in unit of 1000.00.

    I am looking for the calculation that will bring me the break even point of making the additional 8% discount worth it.

    I look forward to some creative thinking.

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,596

    Re: Discount Calculation

    As best as I can tell, to offset an 8% discount you need to multiply the result by 1.086956522 to offset it. Beyond that I didn't get into the rest of the detail.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    Re: Discount Calculation

    Upload a workbook with some sample data to help us to understand your requirement.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  4. #4
    Registered User
    Join Date
    09-28-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    53

    Re: Discount Calculation

    Perhaps my business and math skills are rusty, but it's impossible to both offer an 8% discount per unit and make the same margin on a per-unit basis. It's a mathematical impossibility. You can only determine the break-even point of how many extra units you have to sell to make the same per-order margin, which is exactly the "simple" calculation you did above.

    If you're talking how much extra he needs to purchase for a given order size, all you do is subtract the original order size from the new order size (in your simple calculation above) which would be 26.5 - 20 = 6.5 extra units for you to make the same profit.

    I'm not quite sure what you're looking for.

  5. #5
    Valued Forum Contributor ratcat's Avatar
    Join Date
    03-07-2008
    Location
    Rural NSW, Australia
    MS-Off Ver
    Vista 2007
    Posts
    1,111

    Re: Discount Calculation

    G'day

    Once a pond a time I did make a fancy discount tool in excel like the one your asking for. Can't find the disc

    But I tested the brain cells and came up with this.....
    Attached Files Attached Files

  6. #6
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,546

    Re: Discount Calculation

    sorry wrong formula
    Last edited by samba_ravi; 10-12-2013 at 08:09 AM.

  7. #7
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,546

    Re: Discount Calculation

    units to be sold = present selling units * markup % / (markup % -(1+markup %)*discount%)

  8. #8
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,546

    Re: Discount Calculation

    as per your example = 20*50%/(50%-(1+50%)*8%)

  9. #9
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,963

    Re: Discount Calculation

    I think it is:
    50/38* qty
    Attached Files Attached Files
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  10. #10
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    Re: Discount Calculation

    Goal Seek would be a great tool to achieve this task.

    See the attachment.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    10-11-2013
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Discount Calculation

    Thank you for all the feedback. I have worked on the attached which I think is easier from a sales perspective as it gives the increase in a percentage.

    Similar to the options given.

    Thanks once again



    Working.xlsxWorking.xlsx

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VAT and Discount Calculation
    By daisan in forum Excel General
    Replies: 5
    Last Post: 03-03-2008, 10:18 AM
  2. Discount calculation
    By N Harkawat in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2005, 06:05 AM
  3. Discount calculation
    By N Harkawat in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 04:05 AM
  4. Discount calculation
    By Kerri in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  5. [SOLVED] Discount calculation
    By Kerri in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1