+ Reply to Thread
Results 1 to 6 of 6

Automatic calculation of cheapest unit price with bulk discounts

  1. #1
    Registered User
    Join Date
    04-07-2011
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    11

    Automatic calculation of cheapest unit price with bulk discounts

    Hi,

    Firstly sorry about the title - sometimes it's difficult trying to explain what you want.

    In my sample file I have three columns with prices for 2 products (Columns BDF). What I would like excel to automatically calculate is whether it is cheaper to buy more of the product for the decreased price or to stick with the quantity required.

    For example I have already worked out manually (text in red) that it is cheaper to buy 500 of Product A at the second price (90p) than buying 451 of Product A at the first price (1).

    Is there a way to get excel to calculate this automatically? I would like to use this on a spreadsheet that has 30 items on it and 6 different price breaks. Prices are going up all the time so I don't fancy having to recalculate everytime there is a price increase. i can add as many columns as Excel 2007 will allow.

    Any help greatly appreciated - even if it's a resounding NO it can't be done!!

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Automatic calculation of cheapest unit price with bulk discounts

    Hi

    Where do you nominate what the minimum quantity for each product you require?

    rylo

  3. #3
    Registered User
    Join Date
    04-07-2011
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Automatic calculation of cheapest unit price with bulk discounts

    Hi rylo,

    The minimum quantity will be 200 as there are no price breaks before this. This would be the case for 22/30 of the products.

    The minimum ordering quantity is dealt with in a different cell in a different part of th workbook. If a qty under 200 is entered then the figure is rounded up to 200.

    Thanks
    Last edited by whirlwind1; 04-13-2011 at 05:06 AM. Reason: Expanded on explanation

  4. #4
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Automatic calculation of cheapest unit price with bulk discounts

    Hi

    Guess I asked the wrong question. Where is the quantity required to be entered. You have mentioned 451in your example for Product A, but where is this required amount entered? Is it the same for every product?

    rylo

  5. #5
    Registered User
    Join Date
    04-07-2011
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Automatic calculation of cheapest unit price with bulk discounts

    Hi Rylo,

    The example I have given isn't exactly the sheet I need it for. The quantity in question isn't the number required of a product, the quantity is for square metres of corrugated board. This is calculated by multimplying 2 numbers together so the quantity isn't so much entered as worked out. I currently have a 2.8mb excel file which serves us as a costing sheet and an orders file also with various other admin sheets added along the way.

    For the purposes of this example the quantity can be entered into cell A5 (I will change the forumla to point to the correct cell when I put it into my work file. This formula will be used 60 times in each worksheet as there is a possibility that is the maximum number of instances this calculation will be required.

    Thanks

  6. #6
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Automatic calculation of cheapest unit price with bulk discounts

    Hi

    Is this what you are trying to achieve?

    rylo
    Attached Files Attached Files
    Last edited by rylo; 04-18-2011 at 08:21 PM. Reason: attach file

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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