You now have just one more data category to incorporate in the Resource Calculation spreadsheet: Weighted Average Standard Times. This is shown as:
STw, or Standard Time (Weighted Avg)
It is very important to use the weighted average, rather than just a simple average, in order to calculate labor and machinery resources accurately. The weighting factor is each product’s volume. If, for example, the times for a few very high-volume products are especially high, the simple average time could be misleadingly low.
Work one process at a time. For each product using that process, take the VOL for that product times the Standard Time in minutes. Then, sum the results for all products using that process. As you might think, that sum is called the Sum Product, and you will find a row for it on the Resource Calculation spreadsheet just below Takt Time. We recommend the fo
llowing Excel function for making this calculation; this function will go in the cell on the Sum Product row, looking at the range of Standard Times in that process column – in the example below (Times in Cells D5 through D13), and the fixed product VOLs – example, (Cells $C$5 through $C$13). Use appropriate cells and ranges. Note that if a given product does not use the process (i.e. zero volume), it is not included.
Next, divide by the “Throughput by Process” for that process (which is three rows above). So, the Weighted Average Standard Time, for a process, is
STANDARD TIME (Weighted) = Sum PRODUCT / Throughput by Process
STw = ∑ (Volume x ST) / ∑ Volume.
Review your formulas and calculations for Sum Product and for Standard Time (Weighted). Be sure to save your work!