# 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

 1 2 3 A (recvd qty / target qty ) 25/50 60/50 60/50 B (recvd qty / target qty ) 0/100 90/0 90/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 June 2 June 3 June Product A 50 % 100 % 100% Product B 0 0 90% 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

 1 2 3 16.66 % 300% 100%

Avg: 138.88% ( WHICH IS WRONG )

Please guide how should I apply the formula

#### 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 2 3 A (recvd qty / target qty ) 25/50 60/50 60/50 B (recvd qty / target qty ) 0/100 90/0 90/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 June 2 June 3 June Product A 50 % 100 % 100% Product B 0 0 90% 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

• 10.5 KB Views: 2

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