Question 1

You decide to start your analysis by examining how throughput time changes as utilization increases. Open the HW3 Excel file. Click on the tab labeled Questions 1. In the spreadsheet for Question 1, a coefficient of variation (CV) of 1.0 is used to determine the Interarrival time and total processing time (including setup) variance. The formulas to determine the Throughput Time have already been entered for you.

1a. (12 points) Identify the formulas in the green cells B9-B14. B8 is the level of description needed for each cell.

Cell	Description	Formula (Description and Numbers)
B8	Batch Processing Time	Set-up Time + (Batch Size * Processing Time/Part)
		35 + (400 * 3) = 1,235 minutes per batch
B9	Interarrival Time
B10	Arrival rate (batches/week)
B11	Capacity (batches/week)
B12	Capacity (parts/week)
B13	Throughput Rate (batches/week)
B14	Throughput Rate (parts/week)

1b. (10 points) Plot the Throughput Time as a line graph (created using the scatter chart in Excel) that has Utilization on the horizontal axis and Throughput Time on the vertical axis.

Question 1 Spreadsheet CV 4,800.00 0.85 3.00 Available time per machine (minutes/week) Required Utilization Processing Time per Part (minutes) Batch Size (parts) Setup Time per Batch (minutes) Total Process Time (minutes/batch) Interarrival Time (minutes/batch) Arrival rate (batches/week) Capacity (batches/week) Capacity (parts/week) Throughput Rate (batches/week) Throughput Rate (parts/week) Queue Time (minutes) Throughput Time (minutes) Variance for Total Process Time (minutes/batch) WIP in Queue (batches) WIP in Queue (parts) Total WIP (batches) Total WIP (parts) 4,800.00 0.60 3.00 400 35.00 1,235.00 2,058.33 2.33 3.89 1,554.66 2.33 932.79 1,852.50 3,087.50 0.07 0.90 360.00 1.50 600.00 4,800.00 0.65 3.00 400 35.00 1,235.00 1,900.00 2.53 3.89 1,554.66 2.53 1,010.53 2,293.57 3,528.57 0.07 4,800.00 0.70 3.00 400 35.00 1,235.00 1,764.29 2.72 3.89 1,554.66 2.72 1,088.26 2,881.67 4,116.67 0.07 1.63 653.33 2.63 1,053.33 4,800.00 0.75 3.00 400 35.00 1,235.00 1,646.67 2.91 3.89 1,554.66 2.91 1,165.99 3,705.00 4,940.00 0.07 2.25 900.00 3.25 1,300.00 4,800.00 0.80 3.00 400 35.00 1,235.00 1,543.75 3.11 3.89 1,554.66 3.11 1,243.72 4,940.00 6,175.00 0.07 3.20 1,280.00 4.20 1,680.00 400 35.00 1,235.00 1,452.94 3.30 3.89 1,554.66 3.30 1.321.46 6,998.33 8,233.33 0.07 4.82 1,926.67 4,800.00 0.90 3.00 400 35.00 1,235.00 1.372.22 3.50 3.89 1,554.66 3.50 1,399.19 11,115.00 12,350.00 0.07 8.10 3,240.00 9.10 3,640.00 4,800.00 0.95 3.00 400 35.00 1,235.00 1,300.00 3.69 3.89 1,554.66 3.69 1.476.92 23,465.00 24,700.00 0.07 18.05 7,220.00 19.05 7,620.00 1.21 482.86 2.21 882.86 5.82 2,326.67 Question 1 Spreadsheet CV 4,800.00 0.85 3.00 Available time per machine (minutes/week) Required Utilization Processing Time per Part (minutes) Batch Size (parts) Setup Time per Batch (minutes) Total Process Time (minutes/batch) Interarrival Time (minutes/batch) Arrival rate (batches/week) Capacity (batches/week) Capacity (parts/week) Throughput Rate (batches/week) Throughput Rate (parts/week) Queue Time (minutes) Throughput Time (minutes) Variance for Total Process Time (minutes/batch) WIP in Queue (batches) WIP in Queue (parts) Total WIP (batches) Total WIP (parts) 4,800.00 0.60 3.00 400 35.00 1,235.00 2,058.33 2.33 3.89 1,554.66 2.33 932.79 1,852.50 3,087.50 0.07 0.90 360.00 1.50 600.00 4,800.00 0.65 3.00 400 35.00 1,235.00 1,900.00 2.53 3.89 1,554.66 2.53 1,010.53 2,293.57 3,528.57 0.07 4,800.00 0.70 3.00 400 35.00 1,235.00 1,764.29 2.72 3.89 1,554.66 2.72 1,088.26 2,881.67 4,116.67 0.07 1.63 653.33 2.63 1,053.33 4,800.00 0.75 3.00 400 35.00 1,235.00 1,646.67 2.91 3.89 1,554.66 2.91 1,165.99 3,705.00 4,940.00 0.07 2.25 900.00 3.25 1,300.00 4,800.00 0.80 3.00 400 35.00 1,235.00 1,543.75 3.11 3.89 1,554.66 3.11 1,243.72 4,940.00 6,175.00 0.07 3.20 1,280.00 4.20 1,680.00 400 35.00 1,235.00 1,452.94 3.30 3.89 1,554.66 3.30 1.321.46 6,998.33 8,233.33 0.07 4.82 1,926.67 4,800.00 0.90 3.00 400 35.00 1,235.00 1.372.22 3.50 3.89 1,554.66 3.50 1,399.19 11,115.00 12,350.00 0.07 8.10 3,240.00 9.10 3,640.00 4,800.00 0.95 3.00 400 35.00 1,235.00 1,300.00 3.69 3.89 1,554.66 3.69 1.476.92 23,465.00 24,700.00 0.07 18.05 7,220.00 19.05 7,620.00 1.21 482.86 2.21 882.86 5.82 2,326.67