This example is based on a real scenario. The numbers used are fictional, and the terms have been generalized.
Problem: Final products quality is diminished due to incoming defects from raw materials. Various products all use the same raw materials, but some product types are more sensitive than others. In sensitive product types, a defect in the raw material will become a defect in the final product. In less sensitive product types, a defect in the raw material may not even show up in the final product. A solution is needed where the cleaner raw materials are reserved for the more sensitive product types without impacting throughput.
The spreadsheet contains data gathered over several months. Raw materials are referred to as “blanks” while final products are referred to as “products”. Table 1 shows the number of blanks with a specific number of defects. For example, there were 27 blanks with no defects. Table 2 shows final product data. The goal is to minimize the overall “hit rate”, the percentage of products with at least 1 defect incoming from blanks. 15 product types are labeled A to O. They are also ranked by defect sensitivity. Product type A is the most defect-sensitive with the most defects at blanks showing up in the final product. The current hit rate to beat is 11.11%.
It is not possible to simply allocate each product type to only one or two bins of blanks. For example, I cannot simply allocate zero-defect blanks to product type A only. This is because the day-to-day availability of blanks may not be equal to the long-term distribution used in this model. If this was done, then it is likely that on some days, blanks with more defects will be discarded while products will be backlogged waiting for a blank that is clean enough. Ideally, the current blanks available on hand should be usable for the current products needed.
Solution: Create a blank grouping system based on defect counts. Each group of blanks can be allocated to a group of product types with a range of defect sensitivity. This model explores a 2-group system and a 3-group system.
Case 1 shows a scenario where group 1 includes only product type A and group 2 includes product types B to O. I calculated an average conversion rate for each group (cell H23 and H24), which is used to calculate the predicted defects converted. Please feel free to select the cells to view the formulas. For this case, the predicted conversion rates are 6.67% and 3.03% for group 1 and 2, respectively. Using these conversion rates, I can predict how many incoming blank defects will be converted to a defect on the final product. The total converted defects is 58. The current status quo has 63 defects converted. We can then use these numbers as a scaling factor for the current hit rate. 58/63 * 11.11% is 10.27%, which is the new predicted hit rate for this case. This is a slight improvement.
Case 2 to 14 does the same exercise iterating where to draw the boundary between group 1 and group 2. To emphasize, the key constraint is that there should be enough blanks for all the products. A plot of case number vs. conversion rate is found on the sheet “2 case summary”.
Case 7 predicts the lowest conversion rate at 9.00%. In this case, group 1 is A to G, and group 2 is H to O. Blanks with less than 4 defects can be used for any group 1 product type. Any blanks with more defects should be used for group 2 product types.
Also note how the conversion rate at the edges approaches the status quo value (i.e. a scenario with no blanks binning). This is a good reality check.
A two-group analysis is still reasonable to do in an excel spreadsheet. However, a three-group analysis has 91 different cases. I did this analysis using a python script to iterate where the 2 limits for a 3-group system are defined. Results are below.
The lowest hit rate of 8.49% occurs with limit1 = 2 and limit2 = 8. This corresponds to:
| Group | Product Types | N products | N defects on blanks allowed |
| 1 | A,B | 119 | 0 to 2 |
| 2 | C,D,E,F,G,H | 236 | 2 to 4 |
| 3 | I,J,K,L,M,O | 167 | 4 to 13 |
3-bin calculation code
import pandas as pd
from pandas import Series, DataFrame
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
log_list=list()
"""
binA: above and including layer at limit1
binB: above and including layer at limit2 and below limit 1
binC: below limit 2
"""
layers_df=pd.read_excel("layers.xls")
blanks_df=pd.read_excel("blanks.xls")
layer_count=len(layers_df)
curr_hitrate=layers_df.sum()['plates_hit']/layers_df.sum()['N_plates']
curr_defects=layers_df.sum()['defects_converted']
scenario_list=list()
for limit1 in range(1,layer_count-1):
for limit2 in range(limit1+1,layer_count):
#here is one scenario with defined limits:
blanks_avail=blanks_df.copy()
layers_df['bin']='default'
layers_df.loc[(layers_df['layer_rank']<=limit1),'bin']='binA'
layers_df.loc[(limit1<layers_df['layer_rank']) & (layers_df['layer_rank'] 0 ):
acquire_blanks=curr_blank_N
blanks_avail.iloc[blank_def_count]['N_plates']=0
elif(remainder == 0):
acquire_blanks=curr_blank_N
blanks_avail.iloc[blank_def_count]['N_plates']=0
else:
acquire_blanks=remainder_pre
blanks_avail.iloc[blank_def_count]['N_plates']=curr_blank_N-acquire_blanks
total_def_count+=blank_def_count*acquire_blanks
blank_def_count+=1
grouped_layers.loc[binX,'pred_def']=total_def_count
grouped_layers.loc[binX,'pred_def_conv']=total_def_count*grouped_layers.loc[binX,'conv_rate']
#now for given limit1 and limit2, sum the group's predicted defects
sum_pred_def_conv=grouped_layers['pred_def_conv'].sum()
pred_hit_rate= sum_pred_def_conv/curr_defects * curr_hitrate
scenario_item=[limit1,limit2,sum_pred_def_conv,pred_hit_rate]
scenario_list.append(scenario_item)
#finally, make a dataframe summarizing everying
semi_final_df=pd.DataFrame(data=scenario_list, columns=['limit1','limit2','predicted_hitbacks','predicted_hitrate'])
semi_final_df.to_csv("final.csv", index=False)
final_df=semi_final_df.pivot(index='limit1',columns='limit2',values='predicted_hitrate')
f, ax = plt.subplots(figsize=(11, 11))
ax=sns.heatmap(final_df,cmap='Spectral',annot=True,robust=True, fmt='.4f')
f.savefig("test.png", dpi=400)
Enzo Borja Mangubat 