Move Order Workflow - Excel - Excel by lba17920

VIEWS: 0 PAGES: 10

Move Order Workflow document sample

More Info
									                          Welcome



            Tools for Managers


 Workflow Analysis
        Professor Stephen R. Lawrence
    College of Business and Administration
Graduate School of Business and Administration
             University of Colorado
               Boulder, CO 80303

                 303/492-4351
       Stephen.Lawrence@Colorado.Edu




                           Page 1
                                                     Help



                                            Workflow Analysis

                                 Instructions and Help
           Throughout this workbook, cells in which you are to enter data are shaded in light green.
           Cells which show calculated results are shaded in light yellow with red type.
           Other informational cells are shown in white with black type.

Purpose: The purpose of this workbook is to allow the easy estimation of job or customer flowtime
         distributions when a number of processing workcenters are visited. The worst-case,
         most-likely, and best-case flowtimes for each visited workcenter are solicited and the
         flowtime distribution (mean and variance) through the workcenter is modeled as a
          triangular distribution. The means and variances across all visited workcenters
         are summed, and total job flowtime is modeled as a Gamma distribution. This
         distribution is then used to estimate required job leadtimes for several performance
         objectives, and is used to construct managerially useful leadtime trade-off curves.

  Step 1: Move to worksheet "Flowtimes" and delete any old data in green-shaded cells.

  Step 2: Enter "Customer Data" (this information is used for reference only).

  Step 3: Enter "Customer Service" data:
           The "Service Level" is the percentage of jobs which you desire to be on-time (e.g., 90%).
           "Expense if Tardy" is the approximate cost of a tardy job (including penalities, shipping, etc.)

  Step 4: Under the "Routing" heading, enter the names of the workcenters or stations which
          the job or customer visits during its processing.

  Step 5: For each of the workcenters defined in Step 4, enter the "best-case," "most-likely,"
          and "worst-case" estimates of how long the job or customer will take to move
          through the workcenter under "normal" operating conditions.

Results: The "Leadtime Results" section shows the required leadtimes for three objectives.
         "Leadtime" is defined as the time between now and the job due-date or promise-date.
         "Expected Tardy Cost" is the expected probability of being tardy multiplied by the tardy cost.
         "Expected Service Level" is the probability that the job will be completed on time.

           The three performance objectives are:
            1. Minimize Absolute Lateness: Minimize the expected time that a job misses its due-date,
                        whether it be early or late.
            2. Minimize Squared Deviation: Minimize the square of the deviation of a job's
                       completion from its due-date: penalizes jobs with large deviations more
                       heavily than jobs with small deviations.
            3. Meet Target Service Level: Meet the stated service level goal, such as "90% of all jobs
                       will be on time." How early jobs are does not matter.

 Charts: Densities -- shows the triangular and Gamma flowtime density functions
         Trade-Off Curves -- shows the trade-off between service level, expected tardy-costs, and leadtime.




                                                    Page 2
                Help




and leadtime.




                Page 3
                       862d32b7-4e24-4073-92c6-153cd18b7888.xls

                                                                                13-Apr-11
                                WORKFLOW ESTIMATOR
                        Customer Data                           Customer Service
                Customer: ABC Corp                            Service Target:    90%
                 Order No: 1234                             Expense if Tardy: $1,000
               Product No: XYZ                                   Time Units:    Days

                                                  Routing
       Flowtime      Wctr 3   Wctr 1   Wctr 2       na        na      na       na
       Best-Case        2       5        1
       Most-Likely      3       8        3
       Worst-Case      10      20       40
       Mean           5.00    11.00    14.67        0.00     0.00     0.00    0.00
       Variance       3.17    10.50    80.39        0.00     0.00     0.00    0.00

                                                 LEADTIME RESULTS
                                                      Expected      Expected
       Objective                  Leadtime           Tardy Cost   Service Level
Minimize Absolute Deviation     29.08 Days             $537          50.00%
Minimize Squared Deviation      30.67 Days             $500          52.79%
Meet Target Service Level       43.34 Days             $100          90.00%




                                        Page 4
              862d32b7-4e24-4073-92c6-153cd18b7888.xls

13-Apr-11




      Total
        8
       14
       70
      30.67
      94.06




                               Page 5
                                             Densities



                                     Flowtime Density
          0.05


          0.05


          0.04


          0.04


          0.03
Density




          0.03


          0.02


          0.02


          0.01


          0.01


          0.00
                 0.0   10.0   20.0    30.0     40.0      50.0   60.0   70.0   80.0
                                             Days


                                              Page 6
                                                          Trade-off Curves



                                            Flowtime Trade-off Curves

                       1,200                                                                     120%



                       1,000                                                                     100%
Expected Tardy Costs




                        800                                                                      80%




                                                                                                        Service Level
                        600                                                                      60%



                        400                                                                      40%



                        200                                                                      20%



                          0                                                                       0%
                               0.0   10.0   20.0   30.0     40.0         50.0   60.0   70.0   80.0
                                                            Days


                                                              Page 7
                                     Work



       Flowtime Distribution Calculations
Statistics                         Limits                       Differences
Mean        30.67               Best       8                    LB       6
 Var        94.06              Likely     14                    WL       56
alpha      9.9988              Worst      70                    WB       62
 beta      3.0670
                         Triangular                 Gamma
 Fraction        X        f(X)    F(X)       f(X)       F(X)        X E[cost]
0.000        8.0000   0.00000 0.00000    0.00037    0.00039     8.0000 1,000
0.001        8.0620   0.00033 0.00001    0.00039    0.00041     8.0620 1,000
0.002        8.1240   0.00067 0.00004    0.00041    0.00043     8.1240 1,000
0.003        8.1860   0.00100 0.00009    0.00043    0.00046     8.1860 1,000
0.004        8.2480   0.00133 0.00017    0.00045    0.00049     8.2480 1,000
0.005        8.3100   0.00167 0.00026    0.00047    0.00052     8.3100   999
0.006        8.3720   0.00200 0.00037    0.00049    0.00055     8.3720   999
0.007        8.4340   0.00233 0.00051    0.00052    0.00058     8.4340   999
0.008        8.4960   0.00267 0.00066    0.00054    0.00061     8.4960   999
0.009        8.5580   0.00300 0.00084    0.00057    0.00064     8.5580   999
0.010        8.6200   0.00333 0.00103    0.00059    0.00068     8.6200   999
0.020        9.2400   0.00667 0.00413    0.00090    0.00114     9.2400   999
0.030        9.8600   0.01000 0.00930    0.00133    0.00182     9.8600   998
0.040       10.4800   0.01333 0.01653    0.00187    0.00281    10.4800   997
0.050       11.1000   0.01667 0.02583    0.00257    0.00418    11.1000   996
0.060       11.7200   0.02000 0.03720    0.00342    0.00603    11.7200   994
0.070       12.3400   0.02333 0.05063    0.00445    0.00846    12.3400   992
0.080       12.9600   0.02667 0.06613    0.00565    0.01158    12.9600   988
0.090       13.5800   0.03000 0.08370    0.00702    0.01550    13.5800   985
0.100       14.2000   0.03214 0.10321    0.00858    0.02032    14.2000   980
0.110       14.8200   0.03179 0.12303    0.01029    0.02616    14.8200   974
0.120       15.4400   0.03143 0.14263    0.01216    0.03312    15.4400   967
0.130       16.0600   0.03107 0.16200    0.01416    0.04127    16.0600   959
0.140       16.6800   0.03071 0.18116    0.01626    0.05069    16.6800   949
0.150       17.3000   0.03036 0.20009    0.01845    0.06145    17.3000   939
0.160       17.9200   0.03000 0.21880    0.02069    0.07358    17.9200   926
0.170       18.5400   0.02964 0.23729    0.02296    0.08712    18.5400   913
0.180       19.1600   0.02929 0.25556    0.02522    0.10205    19.1600   898
0.190       19.7800   0.02893 0.27360    0.02744    0.11838    19.7800   882
0.200       20.4000   0.02857 0.29143    0.02960    0.13607    20.4000   864
0.210       21.0200   0.02821 0.30903    0.03166    0.15506    21.0200   845
0.220       21.6400   0.02786 0.32641    0.03360    0.17530    21.6400   825
0.230       22.2600   0.02750 0.34358    0.03539    0.19669    22.2600   803
0.240       22.8800   0.02714 0.36051    0.03702    0.21915    22.8800   781

                                     Page 8
                                  Work

0.250   23.5000   0.02679   0.37723   0.03847   0.24256   23.5000   757
0.260   24.1200   0.02643   0.39373   0.03973   0.26682   24.1200   733
0.270   24.7400   0.02607   0.41000   0.04079   0.29179   24.7400   708
0.280   25.3600   0.02571   0.42606   0.04164   0.31735   25.3600   683
0.290   25.9800   0.02536   0.44189   0.04227   0.34337   25.9800   657
0.300   26.6000   0.02500   0.45750   0.04270   0.36973   26.6000   630
0.310   27.2200   0.02464   0.47289   0.04292   0.39628   27.2200   604
0.320   27.8400   0.02429   0.48806   0.04295   0.42291   27.8400   577
0.330   28.4600   0.02393   0.50300   0.04278   0.44950   28.4600   551
0.340   29.0800   0.02357   0.51773   0.04243   0.47592   29.0800   524
0.350   29.7000   0.02321   0.53223   0.04191   0.50207   29.7000   498
0.360   30.3200   0.02286   0.54651   0.04123   0.52785   30.3200   472
0.370   30.9400   0.02250   0.56058   0.04042   0.55317   30.9400   447
0.380   31.5600   0.02214   0.57441   0.03947   0.57795   31.5600   422
0.390   32.1800   0.02179   0.58803   0.03842   0.60210   32.1800   398
0.400   32.8000   0.02143   0.60143   0.03727   0.62557   32.8000   374
0.410   33.4200   0.02107   0.61460   0.03604   0.64829   33.4200   352
0.420   34.0400   0.02071   0.62756   0.03474   0.67024   34.0400   330
0.430   34.6600   0.02036   0.64029   0.03338   0.69136   34.6600   309
0.440   35.2800   0.02000   0.65280   0.03199   0.71163   35.2800   288
0.450   35.9000   0.01964   0.66509   0.03057   0.73102   35.9000   269
0.460   36.5200   0.01929   0.67716   0.02914   0.74953   36.5200   250
0.470   37.1400   0.01893   0.68900   0.02770   0.76715   37.1400   233
0.480   37.7600   0.01857   0.70063   0.02626   0.78388   37.7600   216
0.490   38.3800   0.01821   0.71203   0.02484   0.79972   38.3800   200
0.500   39.0000   0.01786   0.72321   0.02344   0.81469   39.0000   185
0.510   39.6200   0.01750   0.73418   0.02207   0.82880   39.6200   171
0.520   40.2400   0.01714   0.74491   0.02074   0.84207   40.2400   158
0.530   40.8600   0.01679   0.75543   0.01944   0.85452   40.8600   145
0.540   41.4800   0.01643   0.76573   0.01819   0.86619   41.4800   134
0.550   42.1000   0.01607   0.77580   0.01698   0.87709   42.1000   123
0.560   42.7200   0.01571   0.78566   0.01583   0.88725   42.7200   113
0.570   43.3400   0.01536   0.79529   0.01472   0.89672   43.3400   103
0.580   43.9600   0.01500   0.80470   0.01366   0.90552   43.9600    94
0.590   44.5800   0.01464   0.81389   0.01266   0.91368   44.5800    86
0.600   45.2000   0.01429   0.82286   0.01171   0.92123   45.2000    79
0.610   45.8200   0.01393   0.83160   0.01082   0.92821   45.8200    72
0.620   46.4400   0.01357   0.84013   0.00997   0.93465   46.4400    65
0.630   47.0600   0.01321   0.84843   0.00918   0.94059   47.0600    59
0.640   47.6800   0.01286   0.85651   0.00844   0.94605   47.6800    54
0.650   48.3000   0.01250   0.86438   0.00774   0.95107   48.3000    49
0.660   48.9200   0.01214   0.87201   0.00710   0.95566   48.9200    44
0.670   49.5400   0.01179   0.87943   0.00649   0.95987   49.5400    40
0.680   50.1600   0.01143   0.88663   0.00593   0.96372   50.1600    36


                                  Page 9
                                  Work

0.690   50.7800   0.01107   0.89360   0.00541   0.96724   50.7800   33
0.700   51.4000   0.01071   0.90036   0.00493   0.97045   51.4000   30
0.710   52.0200   0.01036   0.90689   0.00449   0.97336   52.0200   27
0.720   52.6400   0.01000   0.91320   0.00408   0.97602   52.6400   24
0.730   53.2600   0.00964   0.91929   0.00370   0.97843   53.2600   22
0.740   53.8800   0.00929   0.92516   0.00336   0.98062   53.8800   19
0.750   54.5000   0.00893   0.93080   0.00304   0.98260   54.5000   17
0.760   55.1200   0.00857   0.93623   0.00275   0.98440   55.1200   16
0.770   55.7400   0.00821   0.94143   0.00249   0.98602   55.7400   14
0.780   56.3600   0.00786   0.94641   0.00224   0.98748   56.3600   13
0.790   56.9800   0.00750   0.95118   0.00202   0.98880   56.9800   11
0.800   57.6000   0.00714   0.95571   0.00182   0.98999   57.6000   10
0.810   58.2200   0.00679   0.96003   0.00164   0.99107   58.2200    9
0.820   58.8400   0.00643   0.96413   0.00147   0.99203   58.8400    8
0.830   59.4600   0.00607   0.96800   0.00132   0.99289   59.4600    7
0.840   60.0800   0.00571   0.97166   0.00119   0.99367   60.0800    6
0.850   60.7000   0.00536   0.97509   0.00106   0.99437   60.7000    6
0.860   61.3200   0.00500   0.97830   0.00095   0.99499   61.3200    5
0.870   61.9400   0.00464   0.98129   0.00085   0.99555   61.9400    4
0.880   62.5600   0.00429   0.98406   0.00076   0.99605   62.5600    4
0.890   63.1800   0.00393   0.98660   0.00068   0.99649   63.1800    4
0.900   63.8000   0.00357   0.98893   0.00061   0.99689   63.8000    3
0.910   64.4200   0.00321   0.99103   0.00054   0.99724   64.4200    3
0.920   65.0400   0.00286   0.99291   0.00048   0.99756   65.0400    2
0.930   65.6600   0.00250   0.99458   0.00043   0.99784   65.6600    2
0.940   66.2800   0.00214   0.99601   0.00038   0.99809   66.2800    2
0.950   66.9000   0.00179   0.99723   0.00034   0.99831   66.9000    2
0.960   67.5200   0.00143   0.99823   0.00030   0.99851   67.5200    1
0.970   68.1400   0.00107   0.99900   0.00027   0.99869   68.1400    1
0.980   68.7600   0.00071   0.99956   0.00024   0.99884   68.7600    1
0.990   69.3800   0.00036   0.99989   0.00021   0.99898   69.3800    1
0.991   69.4420   0.00032   0.99991   0.00021   0.99899   69.4420    1
0.992   69.5040   0.00029   0.99993   0.00020   0.99900   69.5040    1
0.993   69.5660   0.00025   0.99995   0.00020   0.99902   69.5660    1
0.994   69.6280   0.00021   0.99996   0.00020   0.99903   69.6280    1
0.995   69.6900   0.00018   0.99997   0.00020   0.99904   69.6900    1
0.996   69.7520   0.00014   0.99998   0.00019   0.99905   69.7520    1
0.997   69.8140   0.00011   0.99999   0.00019   0.99907   69.8140    1
0.998   69.8760   0.00007   1.00000   0.00019   0.99908   69.8760    1
0.999   69.9380   0.00004   1.00000   0.00019   0.99909   69.9380    1
1.000   70.0000   0.00000   1.00000   0.00018   0.99910   70.0000    1




                                 Page 10

								
To top