Share via

Excel Volume Pricing: Model Based on Lowest Possible Value

Anonymous
2019-03-06T21:25:17+00:00

Hi - 

I need to create a volume based pricing model in Excel. As the volume increases, the cost/unit should decrease, but the cost/unit should never go below a certain value (e.g. $.0002). 

Essentially, I need to have one column with a list of volumes and another column that will calculate the cost/unit at each volume. Again, the cost/unit should decrease exponentially until lowest possible cost/unit is reached (e.g $0.0002). 

Here's what I imagine this to look like. Note, there should be a new row until the maximum volume and/or minimum cost is reached (e.g. a row for 900,000, 10,000,000, etc.).

Volume Cost/Unit Overall Cost
100****000

Let me know if you need any more information. Happy to provide as needed. Thanks.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-03-08T10:58:37+00:00

    Hi Katie,

    We welcome you to share the sample file. To protect your privacy, you can remove personal information in it then upload to your OneDrive, and share the related link on the forum.

    Regards,

    Eric

    Was this answer helpful?

    0 comments
  2. Anonymous
    2019-03-06T23:59:16+00:00

    Apologies for the confusion, the values in the table above were meant to illustrate what the table should look like. These values are sample values only and are incorrect.

    The values in COLUMN A (Product Volume) should increase according to the logic below:

    IF(

    A3 < 1000000, B3 + 100000,

    IF(

    A3 < 5000000, B3 + 250000,

    IF(

    A3 < 10000000, B2 + 1000000,

    IF(

    A3 < 20000000, 1500000,

    IF(

    A3 >= 20000000, 2000000,

    ""

    )))))

    Was this answer helpful?

    0 comments
  3. Ashish Mathur 101.9K Reputation points Volunteer Moderator
    2019-03-06T23:20:45+00:00

    Hi,

    What should be the logic for increasing the Product Volume?  Why does it increase from 100,000 to 400,000 and not 100,000 to 250,000?

    Was this answer helpful?

    0 comments
  4. Anonymous
    2019-03-06T23:17:24+00:00

    Thanks, Jeovany. I've added a screenshot with sample data below.

    For more context, the values in the first table (first and last values) will always be known values.

    In the second table, a new row should be added in the Product Volume column until the maximum volume (250M) is reached. (In the screenshot below, we are missing all values from 11.7M to 250M).

    The Cost/Unit should continue decrease at an exponential rate until the minimum Cost/Unit (0.002) is reached.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2019-03-06T22:24:30+00:00

    Hi Katie

    I would like to help you but

    Could you please post more information or details of your goal.

    A screenshot of your data will help, Also post the results expected.

    Remove any confidential / sensitive data.

    Do let me know if you require any further help on this. Will be glad to help you.

    Thanks

    Regards

    Jeovany CV

    Was this answer helpful?

    0 comments