SLA violation alerts

[asim]

Hi,

I am trying to figure out a couple of linked items for weekly dat
input.

1/ Method to highlight whether a Service Level is forecast t
violate/breach the target at the end of the month, based on the dat
which has already been entered.
2/ If the SLA has not been breached - what is the max value that can b
entered to ensure the SLA is not breached over the remaining weeks i
the month.

Weekly data input will be averaged out at the end of the month to ge
an overall monthly figure

i.e.
Target is for month is 90%
January has 4 weeks (1, 2, 3, 4)
wk 1 actual is 89
wk 2 actual is 99
wk 3 ? - what is max that can be achieved in this week to ensure th
target is not violated before wk4?

I do hope this is something you can help me with.

Thanks in advance.
Asi

 

[Guest]

WK3=3*TARGET-(WK1+WK2)

WK4=4*TARGET-(WK1+WK2+WK3)

HTH

 

[asim]

Hi Toppers, thanks for this.

I have put your formula into a spreadsheet, its useful for a week to
week view.

Is there a similar formula for an end of month view. i.e. in the
attachment you can enter actual data for week 1, 2, etc, and while this
is being entered a month to date figure will automatically calculate the
minimum needed in the next week to ensure the target is achieved.

Hope you can assist again.


+-------------------------------------------------------------------+
|Filename: SLA violation example.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4902 |
+-------------------------------------------------------------------+

 

[Guest]

Put this in C4 and copy down to C7:

=IF(ISBLANK(B4),(4*$B$1-$B$9)/(4-COUNTA($B$4:$B$7)),B4)

If have assumed the month-to-date is a total of B4:B7

-------------------------------------------------------------------------------------

If Month-to-date is the AVERAGE, then put the formula below B9:


=IF(COUNTA($B$4:$B$7)=0,0,AVERAGE($B$4:$B$7))

and this formula in C$ and copy down:

=IF(AND(ISBLANK(B4),COUNTA($B$4:$B$7)<>0),(4*$B$1-COUNTA($B$4:$B$7)*$B$9)/(4-COUNTA($B$4:$B$7)),B4)


HTH

 

[asim]

Tommy, Pure Brilliance! - thankyou ever so much - just what I needed.

How would I change the forumla if I was working towards a Target which
is in the opposite direction of the algorithm you have provided, i.e.
The target should not achieve more than 90%, the absolute target is
92%.

Therefore, anything greater than 90% is bad, but 92% is a no-no?

Is this a simple change?

I have attached the spreadsheet - the data set in columns G-K, is where
the query is.


Thanks again.


+-------------------------------------------------------------------+
|Filename: SLA violation example.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4903 |
+-------------------------------------------------------------------+

 

[Guest]

The logic is the same - exactly as you have it in your example with the
Minimum/Maximum for Target.

It does not matter whether the target is up/down - the calculation works out
what is required to reach a stated figure. So if you do not want to exceed
10%, then your next 3 week average must be no more than 13% [or 19% maximum].

It is up to you on how you interpret the data.

Or have I misunderstood your requirement?

HTH

 

[asim]

Toppers, you are absoutely correct, it can be used for upward/downward
measures - thankyou again for your help with my query.