+ Reply to Thread
Results 1 to 11 of 11

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

  1. #1
    Registered User
    Join Date
    06-06-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    15

    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
    Attached Files Attached Files
    Last edited by parikshitpathak; 07-11-2018 at 09:17 AM. Reason: Uploaded the updated excel sheet.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    17,630

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

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


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    06-06-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    15
    Kindly use the file that has V1 added at the end of file name.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    17,630

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

    Please remove the attachment that is not required. Thanks.

  5. #5
    Registered User
    Join Date
    06-06-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    15

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

    Quote Originally Posted by AliGW View Post
    Please remove the attachment that is not required. Thanks.
    Sorry for the inconvenience. I have removed the unwanted file from the message.

    Thanks for the help in advance..

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    22,915

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

    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: copy to clipboard
    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.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  7. #7
    Registered User
    Join Date
    06-06-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    15

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

    Thanks Richard for the help. I will try out the formula given by you and see if that solves my query. Thanks in advance for the time and solution. !!


    Cheers

    PP

  8. #8
    Registered User
    Join Date
    06-06-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    15

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

    Hi Richard,

    I tried to apply the formula by making some changes. It gives me "FALSE" in the cell where it needs to show how many more jobs needs to be delivered with
    quality to achieve the target. I am attaching the excel sheet with the formulas updated. Can you please check and help me understand and give me a solution for that?

    The data has been updated in the row number 4. Formulas in the column I and K.

    Thanks in advance..


    Cheers

    PP
    Attached Files Attached Files

  9. #9
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    22,915

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

    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.

  10. #10
    Registered User
    Join Date
    06-06-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    15

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

    PP[/QUOTE]
    Hi Richard,

    I made changes to the formula to try to split the message and number under 2 different columns. Sorry about that.

    The Column I should display the number of jobs to delivered with quality if the target of 95% can be achieved assuming the number of jobs that are to be received as per the column G, will be delivered with 100% quality and column K should display "Quality can be achieved" or "Quality cannot be achieved"

    If even after delivering the remaining number of jobs as per column G with 100% quality if the target of 95% cannot be achieved then the column K should say "Quality will be missed".

    The objective of column I is to get the number and predict if I can afford any errors or not. Currently the column I displays the number of jobs remaining to be received and not the number of jobs to be delivered without errors.

    I hope i am not complicating.

    Thanks

    PP

  11. #11
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    22,915

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

    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%

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1