Queuing Analysis

#### Optimization Models

Drive/Structure

Models

Subassemblies

 Process Flow Analysis - Economic and Requirements Analysis

The Process Economics command from the OM_IE menu produces a Project worksheet which computes a variety of results for the system of processes defined in the workbook. It accepts economic data in terms of costs of resources and raw materials and sales quantities for products. It then computes total profit for the system. It also computes resource requirements for specified production amounts, and tables of resource and raw material usage by products. The program estimates machine and raw material requirements and constructs a from-to chart showing the part flows between machines. The latter is useful for a plant-layout analysis. This worksheet also provides the data source for mathematical programming models that can be used to optimize product mix and identify bottlenecks.
 Select the Process Economics command from the OM_IE menu to perform the analysis.

The program constructs the Project worksheet. Only one Project worksheet can be included in a workbook. The Project worksheet computes the economic effects of decisions concerning the manufacturing system. For this sheet, the add-in requires economic data such as unit revenues for products, raw material costs, machine investment costs and operating costs. When first created, some of these numbers are filled in with arbitrary values.

The worksheet shows tables of resource and raw material usage by products and estimates machine and raw material requirements for given sales quantities. The requirements are driven by the numbers in the Sales per Week column. The initial values for sales are taken from the flow out (or flow in for push systems) process entries.

In the following we discuss each portion of the worksheet. White cells require data from the user. Yellow areas are the results of formulas inserted by the add-in and should not be changed. Green areas are results from computer computations. In some instances these can be changed by the user, but they will be replaced by computations controlled by buttons and commands.

The sets of products, resources and raw materials that appear on this worksheet are those defined in the current workbook. The three products A, B and C have data entered as shown in the previous pages of this discussion.

Product Information

 Range Title Explanation E3:E5 Product These cells hold the names of the processes. We associate the output of each product with the process. The name of the product is the name of the process. These names are compiled from the process names in the workbook. They should not be changed. F3:F5 Unit Revenue This is the revenue realized per unit of sales for each products. G3:G5 Min Sales This is the minimum number of units sold per week for each product. H3:H5 Max Sales This is the maximum number of units sold per week for each product. I3:I5 Sales This is the number of units sold per week for each product. The user may experiment with these values. The specific values shown here are those provided by the flow out entries for the products. When a mathematical programming model is constructed and run the computed results will replace these entries. In that case the range will be colored yellow. J3:J5 Unit Operating/Raw Material Cost This is the sum of the unit operating cost for the resources used by the process and the cost of the raw materials expended for a unit of product. K3:K5 Unit Profit This is a computed number that is the unit revenue minus the raw material and resource costs for a unit of product.

Resource Availability Information

 Range Title Explanation E9:E12 Resource These cells hold the names of the resources. The add-in compiles a list of resources from those named in the Resource Type columns of the processes. General resources also are included in this list. The list is compiled by the add-in and should not be changed. F9:F12 Number Used This is the actual number of resources (machines) used of each type. G9:G12 Max Number This is the maximum number of resources (machines) that can be used. H9HI12 Unit hours/week This is the number of hours each machine type is available during a week. The number 40 indicates one 8-hour shift per day and a five day week. Two shifts per day would be indicated with 80 in this cell. I9:I12 % Available This is the percent of time each machine is available for productive activities. The number might be less than 100% due to downtime for maintenance, worker break times, etc. J9:J12 % Maximum Utilization This is the percentage of the available time that the machine is to be active. This number might be set less than 100% to allow for statistical variability. K9:K12 Unit Available hours per week This is a computed field that indicates the maximum number of hours one unit of a machine type can be used during a week. It is the product of the previous three columns. L9:L12 Max. Available hours per week These entries multiply the Max. Number by the Unit Availability. M9:M12 Resource Available hours per week These entries multiply the Number Used by the Unit Availability. N9:N12 Resource Used hours per week This is the numbers of hours used on each resource type for the specified level of sales (I3:I5). For the example, the time used on Run-In is more than the amount available. This is clearly a bottleneck resource. When the time used is greater than the time available, the sales quantities are not feasible. O9:O12 Utilization This is the proportion of the time available that the resource is used. It is the entry in row N divided by the amount in row M.

Resource Space Information

 Range Title Explanation E16:E19 Resource These cells hold the names of the resources. The add-in compiles a list of resources from those named in the Resource Type columns of the processes. General resources also are included in this list. The list is compiled by the add-in and should not be changed. F16:F19 Space per Unit This is space measured in some squared space dimension. The data is useful for a plant layout analysis. G16:G19 Operating Cost per hour This is the cost to operate the resource per hour. H16:H19 Investment Cost per week This is the investment cost per week. It is used when the number of machines is to be optimized. The time dimension for the operating cost and investment cost are the same. I16:I19 Space Required This is computed by multiplying the Space per Unit by the Number Used (F9:F12). J16:J19 Operating Cost per Week This is computed by multiplying the Operating Cost per Hour by the Resource Used (N9:N12). K16:K19 Investment Cost per Week This is computed by multiplying the Investment Cost per Week by the  Number Used (F9:F12).

Raw Material
Information

 Range Title Explanation E24:E26 Material These cells hold the names of the materials. The add-in compiles a list of materials from those named in the Raw Materials Type columns of the processes. General raw materials also are included in this list. The list is compiled by the add-in and should not be changed. F24:F26 Unit Cost This is the unit cost for purchasing the raw materials. G24:G26 Maximum Available This is the maximum number of units of raw materials available per week. H24:H26 Used This is the numbers of units used for each raw material type for the specified level of sales (I3:I5). I24:I26 Cost This is computed by multiplying the Space per Unit by the Number Used (F9:F12).

Resource Requirements

This information is produced entirely by the computer from the process definitions. No data is entered by the user. The entries from the Unit Resource Use matrix come from the unit time columns of the process definitions. When a process uses a resource more than once, the unit times are added. The entries from the Unit Resource Flow matrix come from the unit flow columns of the process definitions. When a process uses a resource more than once, the unit flows are added.

 Range Title Explanation F31:H34 Unit Resource Use This is the number of hours required for on each resource for each unit of finished good for a process. F35:H35 Resource Operating cost per unit This is operating cost associated with each resource type per unit of product. It is included in the unit profit computation. K31:M34 Unit Resource Flow This is the number of arrivals at each resource for each unit of finished good for a process.

Material Requirements

This information is produced entirely by the computer from the process definitions. No data is entered by the user. The entries come from the raw material amount and the unit flow columns of the process results. When a process uses a resource more than once, the results are added.

 Range Title Explanation F40:H42 Material used per unit of product This is the number of units of raw material required for per unit of flow out for each process. F43:H43 Material cost per unit This is material cost associated with per unit of product. It is included in the unit profit computation.

From-To
Matrix

The from-to matrix accumulates flows between adjacent resources in the process definitions. The matrix is useful for plant layout analyses. In order for an entry to appear there must be resources defined for each of two adjacent operations. If adjacent operations have the same name, the flows are not accumulated. Thus we have zero flows on the diagonal. The flow is the sum of the flows for all three products. The Run-in resource is specified for the Run-in operation and flow enters this operation from both the SMT and TH Lines. We see these entries in the column labeled RunIn.

Summary Information

Information on the upper left corner of the Project worksheet shows the components of net income for the sales volumes specified.

Computation
Buttons

Below the summary information, we see four buttons that control the computations of the add-in.

The top button fills the resource and raw material usage matrices on the Project worksheet. These matrices are filled when the worksheet is first constructed, but when the user changes the definitions of the projects the entries should be recomputed. If the sets of products, resources or raw materials has changed, it is necessary to completely reconstruct the Project worksheet. Economic data previously entered is transferred to the new worksheet.

The second button creates a worksheet defining one of three mathematical programming models that can be used for product mix, raw material and resource decisions. The models are discussed elsewhere in these pages.

The third button refreshes the LP/IP models that are in the workbook. These models depend on the economic and process flow data and when this data is changed, the models should be refreshed.

The fourth button fills the From-To matrix. Again this only necessary when the data changes.

The bottom button calls the Facility Layout add-in and places the space and flow data into the layout data ranges.

Operations Management / Industrial Engineering
Internet
by Paul A. Jensen