Daily Dynamic Target Calculation

sunil1982
sunil1982 used Ask the Experts™
on
Hi ,

I have a Service Level Target for one of my team,. It has a monthly Target.

But I want to calculate the target dynamically based on the past performance.
For an example for the first 5 Days of operation if the team achieved less than the target, then we optimize the daily target in order to catch p the monthly target achievement.

I have attached the sample file, but the target is actually the monthly figure, I am looking forward to get a formula to calculate the dynamic daily targets.
Target-For.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
The following formula will calculate your dynamic daily target based on past data:
=(DAY(EOMONTH(B1,0))*B3-SUM($A2:A2))/(DAY(EOMONTH(B1,0))-DAY(B1)+1)

You may copy this formula across.

Note that the formula will predict values greater than 100% when it becomes impossible make your monthly target achievement. If you don't want to display values greater than 100%, then use:
=MIN(100,(DAY(EOMONTH(B1,0))*B3-SUM($A2:A2))/(DAY(EOMONTH(B1,0))-DAY(B1)+1))
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
Sample file using the suggested formula
Target-ForQ28378206.xlsx

Author

Commented:
It is a great solution, Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial