Forum Discussion
Production cost calculator with variable price depending on quantity
Hello!
I am currently studying logistics. I recently got a project to create a production calculator. It is supposed to work on the principle that:
-The factory can produce up to 9 batches of the product.
-Until the fifth batch, each subsequent batch is 10% cheaper to produce.
-6 to 9 batches are 50% cheaper to produce.
That is, the first product has a 10% lower cost of production. So when producing two products, the first products will have a 10% lower production cost and the second products will have a 20% lower production cost.
I hope I have explained the situation in an understandable way. My problem is that I can't do it with Excel. I cannot correctly use the IF function to create this calculator.
So I am asking for help on how to do this because I have not found the answer anywhere else. Is it even possible in Excel?
- = IF(batch<6, 11 - batch, 5) / 10
4 Replies
The accepted IF formula works well for the current 1–9 batch requirement. If the pricing rules become more complex, though, I’d probably avoid adding more nested IF statements.
A small lookup table for quantity ranges and their corresponding discount could make the calculator much easier to maintain. Then the formula only needs to find the appropriate tier, so changing the pricing rules later wouldn’t require rewriting the calculation.
For example, the table could contain minimum quantity, maximum quantity, and discount percentage. This also makes it easier to extend the calculator beyond 9 batches or add different pricing rules for different products.
- = IF(batch<6, 11 - batch, 5) / 10
- It works!
Sorry for the delay, but I waited for the professor to review the project. The project went perfectly.
Thank you for your help, Rajesh-S too.
- Share some sample data with us ,, help to understand the data format!!
