Formula to predict the error free delivery from the expected volumes per week

Hi All,

I am trying to formulate my weekly quality scores from the weekly volumes that are expected from the clients. My weekly volumes from each client differ. I need to find and predict if I will be able to achieve the quality targets based on the already received volumes and remaining/expected volumes for the week/month. I am attaching the excel sheet which will help to explain the requirement in details.

The idea is to identify how many more jobs from the remaining volumes can we afford to have errors to achieve the quality of 95% against the total volume.

Example :

Weekly Volume Expected as per the trend : 140
Already received volumes : 10
Jobs with Error : 3
Error Free Ads : 7
Remaining jobs for the week : 130
Current Quality %age : 70%
How many more jobs we need to deliver from remaining 130 jobs to achieve 95% and how many can we afford to have error and not impact the quality target of 95%?


It would be a great help if I can get this fix at your earliest..

Thanks in advance to all the experts in the group.

The updated excel (QualityTargetCalculator_V1.xlsx) has the cells highlighted in Yellow where i need the help in formulas to automate. Kindly ignore the other excel.

Cheers

PP

Why are there two files attached? Which one should we use? Please remove the attachment that is not needed to avoid confusion.

Please remove the attachment that is not required. Thanks.

Since your new jobs could be either a quality job or one with errors it's not possible to give an answer. If we assume all the remaining jobs were to be quality jobs then in I4

Formula:

Please Login or Register  to view this content.

This will return the number of remaining jobs which must be quality jobs in order that the Target % is achieved.

Where the resulting formula number is less than the remaining jobs it means that if all the remaining jobs were quality jobs then the overall % will be above the target %.

Obviously if some of te remaining jobs were to be jobs with errors then the maths will need to change and you'd need to give some advice as to how any new formula should split the remaining jobs between quality & error jobs. I suppose one way would be to assume the same ratio of quality:error jobs for the future jobs as for the actual jobs.

You have not used the formula I gave you and neither have you commented on the matter of whether future jobs are quality jobs or one with errors.

Unless you're changing the basic requirement with the caveat I stated I don't understand why you are trying to change my formula or what you expect the answer to be. My formula gives you the number of jobs needed to deliver the target % if it's possible and tells you if it's not.

I believe that's what my formula gives you. Just use my formula but change the text 'not possible' to 'quality will be missed

In the case of your I4 it will return 22.75 which means you need that many jobs in order to achieve 95%. With I5 it's not possible since 33 additional quality jobs will only give 94%