Need Help with Formula for Predictive and Actionable Reporting

Six Sigma – iSixSigma Forums Operations Finance Need Help with Formula for Predictive and Actionable Reporting

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
  • #249892


    I am trying to create a formula that will calculate number of completed products needed in order to reach preset SLAs.  The variables are the issue as the incoming and outgoing changes hourly as is the backlog when the SLA is unmet.  Any expertise available to assist?


    Michael Parent

    More details will help make this solution more accurate.

    Here’s something I came up with in Excel.

    You have a daily SLA for finished products, 10,000 units.

    You have a backlog of 2,000 units.

    You need to calculate how many units need to be produced per hour. Assuming a 24/7 production schedule, would yield: (10,000+2,000)units/24hrs = 500 units/hr

    Further you could look at things like your capacity. Given the # of Machines and cycle time, how many units can be produced each minute? each hour? [20] Machines *[1.5min] = p13.3units] per minute (Capacity)

    13.3 units/min* 60min/hour = 800 units per hour (Capacity)

    You can then compare this MAXIMUM capacity to the hourly demand, to get a % capacity demanded.

    (500 units demanded/hour) /(800 units per hour (capacity)) = 63% of capacity will be used to make SLA.


    Finally, (though not in my screenshot) You can adjust the MAXIMUM capacity for planned and unplanned downtime causes. This in turn will give you a closer forecast of real output. Managers can use this to make decisions about shutting machines down for maintenance, etc.

    isixsigma capacity


    I hope this helps,

    Good luck!


    • This reply was modified 1 week, 4 days ago by Michael Parent. Reason: Better explanation of formulas
Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.