• 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.
 

vletm

Excel Ninja
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?
 

Preet Inder

New Member
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
 

Mofi454

New Member
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

vletm

Excel Ninja
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.
 

Peter Bartholomew

Well-Known Member
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.
 
Top