• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula to Calculate % achievement against target for daily planning in a factory.

Preet Inder

New Member
Hi, there is a situation where i am stuck at calculating %achievement vs target on daily basis. Please see the example below:

Product A and B are received from a Supplier on dates 1,2,3 June ( received qty / target qty )

Total Requirement of Product A: 150 and Product B 200
Received: Product A: 145 and Product B: 180

123
A (recvd qty / target qty )25/5060/5060/50
B (recvd qty / target qty )0/10090/090/100

So, on day 1 for Product A % achievement is 50% & on day 2 it is 100% ( not more than 100% as the plan was for 50 pcs only )
So, this is how we calculate:

Actual Calculation ( how it is supposed to be done )
1 June2 June 3 June
Product A50 %100 %100%
Product B0090%
Average: 25%50%95%

Avg. Achievement is 56.66%

Now if i try to apply formula and calculate in excel by totaling the received qty against targeted qty the % achievement for each day comes out to be



123
16.66 %300%100%



Avg: 138.88% ( WHICH IS WRONG )

Please guide how should I apply the formula

Thanks in Advance.
 
Preet Inder
If You calculate max(received,planned) / planned
and You're compare it to received / planned ...
of course those could be same ... if if if and if.
You gave value (WHICH IS WRONG)
... but what would be 'correct' value?
 
First of all, thank you for replying. & Sorry didnt really understand your reply, but if you want to know the correct value: it is 56.66%

The algo to get to this value is explained in the post above. Manually it is easy for two items and three days, but for 50 items and full month it is cumbersome.

How we calculate:

1
23
A (recvd qty / target qty )25/5060/5060/50
B (recvd qty / target qty )0/10090/090/100

So, on day 1 for Product A % achievement is 50% & on day 2 it is 100% ( not more than 100% as the plan was for 50 pcs only )
So, this is how we calculate:

Actual Calculation ( how it is supposed to be done )
1 June2 June3 June
Product A50 %100 %100%
Product B0090%
Average:25%50%95%

Avg. Achievement is 56.66%

Thanks
 
Hi Preet,

Are you looking for a VBA Solution to this problem? Attached is quick way to achieve what you trying to do using some Excel formulas.

Thanks

Mofi
 

Attachments

  • % Calculations.xlsx
    10.5 KB · Views: 45
Preet Inder
Your or anybody result depends which values do You use ...
Now, You 'forget' two lines and
after that You used previous values which gives for You ... hmm? ... Your named 'correct' values.
I could find out Your 56,66% ... but is it the most correct value ... who knows?
Without You even sample file and clear details - what would You need to get?
It's a challenge.
 
It is not correct to calculate the average achievement as an average of item achievements.
For a meaningful overall average each percentage has to be weighted with the volume / target quantity.
That is the same as taking the total eligible receipts and dividing by the total target value.
 
prabhakardl

Moderator Notes:
You should reread Forum Rules:

pls reply through email to p r a b h a k a r . d l @ g m a i l . c o m

  • Please Don't post your Phone Number or Address and be careful posting your email address. Spam robots do trawl forums collecting this info.
 
Last edited by a moderator:
Back
Top