Homework answers / question archive / For example

For example

MS Excel

Share With

For example. suppose that the cell 85 con-tains the number 7, then INDIRECT ( "A" Et (B5+1 ) ) refers to cell A8. In order to understand this evaluation. first observe that the ampersand (&) concatenates (or adds) two strings, before the concatenation occurs, the numerical value of B5+1 is converted to a string; thus, the two strings "A" and " 8 " are combined to form the address AS. An Excel simulation usually involves building a table similar to Table 2.1; thus, we start our spreadsheet with the following two rows. 
1 
2 
A 1 Hour 2 0  F # at WS 3 0 
B 
C D 
# at WS I 5 
Time-1 Remaining 
# at WS 2 0 
E Time-2 Remaining 0 

Time-3 Remaining 0 
#atWS4 0 
Remaining 0 
Cumulative Completed 0 
K L M N 
1 Entity # 2 0 
Finish Time 0 
Start Time 0 
Cycle Time 0 
The key difference between the Excel table and Table 2.1 is the meaning of Columns C, E, G, and I. The spreadsheet will maintain the time remaining for pro-cessing instead of the time that has already been used. In order to build the future rows, we use the following formulas in row 3. 
Column A Column B Column C Column D Column E Column F Column G Column H Column I Column J Column K Column L 
=A2+1 =B2-(C2.1)+(I2=1) =IF(B3=0,0, IF(C2<=1,1,C2-1)) =D2- (E2=1) + (C2=1) =IF(D3=0,0, IF(E2<=1, IF(RAND()<0.5,1,3),E2-1)) =F2- (G2=1) + (E2=1) =IF(F3=0,0, IF(G2<=1,1,G2-1)) =H2-(I2.1)+(G2=1) .IF(H3=0,0, IF(I2<=1,1,I2-1)) =J2+(I2=1) =K2+1 =OFFSET($A$1,MATCH(K3,$J$2:$J$1000,0),0) 
Introduction to Factory Models Column M =OFFSET ( SAS' , MATCH (K3-$B$2, $J$2:$J$1000,0) , 0) Column N =L3 -M3 Once the formulas are entered, the range of cells A3 :N1000 should be high-lighted and then the "copy down" feature (or <ctrl> -D) used to extend the table down. Do not be concerned that several entries in the L, M, and N columns con-tarn number errors (i.e., #1,1/ A); these are expected and should be ignored. One of the keys to understanding the above formulas is to recognize that a job undergoing processing will leave the work station whenever the time remaining at that worksta-tion equals I. We also use the fact that when a boolean expression is used within a mathematical expression, it will return the value I when true and return 0 when it evaluates to false. Because the RAND function is a "volatile" function, it is recomputed whenever the F9 key is pressed, so if you would like to see different realizations of the simulation, press F9. The final step in the simulation is to report the average throughput rate (th) and the average cycle time CT. To do this, place the word Throughput in cell Pl, and put =J1000 / A1000 in the P2 cell. Remember, the row 3 formulas were copied down to row 1000; thus, the value in cell A1000 represents the total time for the simulation and the value in cell J1000 is the total number of jobs processed through the simulation. In other words, the P2 cell equals the total output divided by the total time, which is the average throughput rate. In cell P3, place the word CycleTime and in the P4 cell place 
=AVERAGE(INDIRECT("N" & (B2+2) & .:N. & (J1000+2))) which yields the average of the individual cycle times. To understand this formula, remember that the value of cell B2 is equal to 5 and is the initial work-in-process. The value in cell J1000 varies depending on the random outcome of the simulation. To illustrate the formula, suppose that 425 entities were processed (i.e., the value of J1000 is 425), then the INDIRECT function will reference "07 :N427" which contain cycle times. (Other cells within column N will likely contain number errors.) The reason for using the INDIRECT function is so that when the WIP level is changed, the CT formula will be changed to include or exclude the appropriate .11s. A final suggestion can be made with respect to the throughput rate. The rate is biased towards the low side because the initial few hours are not representative of steady-state conditions. Therefore, the formula = ( J1000 -J48 ) / ( A1000 -A48 ) would give a better estimate of the long-run average value. The choice of consider-ing the first two days as comprising the transient period of operation is somewhat arbitrary and can be studied further by developing graphs of the average values if so desired.