Hi,

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

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

Weekly data input will be averaged out at the end of the month to get
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 the
target is not violated before wk4?

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

Thanks in advance.
Asim


--
asim
------------------------------------------------------------------------
asim's Profile: http://www.excelforum.com/member.php...o&userid=35530
View this thread: http://www.excelforum.com/showthread...hreadid=552972

WK3=3*TARGET-(WK1+WK2)

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

HTH

"asim" wrote:


Hi,

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

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

Weekly data input will be averaged out at the end of the month to get
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 the
target is not violated before wk4?

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

Thanks in advance.
Asim


--
asim
------------------------------------------------------------------------
asim's Profile: http://www.excelforum.com/member.php...o&userid=35530
View this thread: http://www.excelforum.com/showthread...hreadid=552972

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 |
+-------------------------------------------------------------------+

--
asim
------------------------------------------------------------------------
asim's Profile: http://www.excelforum.com/member.php...o&userid=35530
View this thread: http://www.excelforum.com/showthread...hreadid=552972

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" wrote:


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 |
+-------------------------------------------------------------------+

--
asim
------------------------------------------------------------------------
asim's Profile: http://www.excelforum.com/member.php...o&userid=35530
View this thread: http://www.excelforum.com/showthread...hreadid=552972

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 |
+-------------------------------------------------------------------+

--
asim
------------------------------------------------------------------------
asim's Profile: http://www.excelforum.com/member.php...o&userid=35530
View this thread: http://www.excelforum.com/showthread...hreadid=552972

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" wrote:


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 |
+-------------------------------------------------------------------+

--
asim
------------------------------------------------------------------------
asim's Profile: http://www.excelforum.com/member.php...o&userid=35530
View this thread: http://www.excelforum.com/showthread...hreadid=552972

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


--
asim
------------------------------------------------------------------------
asim's Profile: http://www.excelforum.com/member.php...o&userid=35530
View this thread: http://www.excelforum.com/showthread...hreadid=552972