Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a sliding scale discount | Excel Worksheet Functions | |||
Calculate Sliding Scale Calculation | New Users to Excel | |||
Calculating a sliding scale | Excel Worksheet Functions | |||
Sliding Scale Percentage via IF? | New Users to Excel | |||
Percentage Commission on a sliding scale. | Excel Discussion (Misc queries) |