Sliding scale Question
Hello
My widget factory recieves orders from a distributor for production. My workers dont always produce the ordered amount. My distributor has set up a penalty clause as follows. For up to 2 widgets or 1.5% of order (which ever is greater) he will accept for up to 10 widgets or 7.5% of order (whichever is greater) yeilds a 10% penalty for greater than 10 or 7.5 % yields a 25% penalty. For under production, I pay the penalty X his cost For over production I am paid cost less the penalty. The penalty is applied as follows: If my order is 100 the break points would be 2 and 10 If my production is 85 then the penalty would be 8*90%Cost and 5*75% Cost What formula would I use to calculate this? Thanks! |
Sliding scale Question
Don't use 1 formula. Design you worksheet with multiple columns and then add
up the costs on each row where a row represents and order. Use the following columns A) Quantity Ordered B) Price Each C) Total Price - Column A * B, needed for penality column D) Quantity Delivered E) Penalty for Shortage by up to 2 - Contains IF formula that returns 0 if not (column A - column D) is not 1 or 2. F) Shortage up to 10 - Contains IF formula that returns 0 if not (column A - Column D) is < 3 or 10. G) Shortage over 10 - Contains IF formula that returns 0 if not (column A - Column D) is < 10. H) Over Production - Contains IF formula that returns 0 if not column A - column D is = 0. I) Cost J) Over Production Penalty - Contains IF formula that returns 0 if column A - column D is <0. K) Under Production Penalty - Contains IF formula that returns 0 if column A - column D is = 0. L) Price charged - Total of following columns C, E - H, J, K. Make sure number are added or subtracted properly. Either make the penality a negative number or subtract the penalty when calculating Price Charged. "Sandy" wrote: Hello My widget factory recieves orders from a distributor for production. My workers dont always produce the ordered amount. My distributor has set up a penalty clause as follows. For up to 2 widgets or 1.5% of order (which ever is greater) he will accept for up to 10 widgets or 7.5% of order (whichever is greater) yeilds a 10% penalty for greater than 10 or 7.5 % yields a 25% penalty. For under production, I pay the penalty X his cost For over production I am paid cost less the penalty. The penalty is applied as follows: If my order is 100 the break points would be 2 and 10 If my production is 85 then the penalty would be 8*90%Cost and 5*75% Cost What formula would I use to calculate this? Thanks! |
All times are GMT +1. The time now is 04:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com