Sliding scale Question

[Guest]

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!

 

[Guest]

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.