Creating a formula calculates time impact on achieveing sales quota

[Khaledity]

Hi,

Please I need a help on the following!

I am a salesman (for instance) and I have a target of 1,000 USD to be
achieved on Jan. 15th with a weight of 30% - I did achieve the target
but on Feb. 1st.

How can I use excel to calculate (consider) the delay in time even if
the target achieved? I believe the weighted score will be affected!

Thanks in advance.

Khaledity

 

[vezerid]

Khaledity,
before this becomes an issue for Excel one must first decide on the
math. How would the weight be dropped with delays in delivery? There
is not a single formula which one must choose. There can be several.

For example, let us say you had 90 days to achieve the target. A delay
of 15 (total 105) is now 1/6 of the target. You could multiply 30%
with 90/105. Or, for example, you might want to give a double weight
in every dealyed day. Thus, the 15 day delay could count for 30 days
and you could multiply 30% with 90/120 (120 = 90 + 2*15).
Both these methods will never reach 0 but will become fairly small
after a point.

Another approach is a linear one. For example, for each day of delay
you can drop the weight by 1% or 2% or whatever.

Thus you have to see what you really want and then we can find the
formula for this.

HTH
Kostis Vezerides