Customizable Spreadsheet Template: Operations Process Flow Analysis

The model is a dynamic (time-dependent) simulation of a manufacturing or business process flow with many stages. For example, you can model manufacturing of products in a factory, processing paperwork in a business flow, and delivering finished products in a distribution system.

The model is basically quite simple.

1. Break the process into stages. Draw a diagram that represents each stage as a box, and each flow of work units from one stage to another as an arrow from one box to another.
2. For each stage, specify the scrap rate (percentage of work units that must be scrapped); and the fraction of good output that goes to each stage for further processing.
3. Enter work units into one or more of the stages in each time period. Watch the units flow through the process stages until they are done.
4. For each process stage, record the amount of material, labor, overhead, and fixed equipment costs that are expended processing work units.
5. Examine reports that show at what stage the cost was greatest and why, and the cost of output units.

The flow of units being processed is described by transition rates between processing stages and yield rates that can vary with time.

• The units can transition from a stage to any other stage, including rework stages. The flow is not restricted to a straight line, and it can even include loops.
• The model tracks input units, yield percent, output units, scrap units and scrap percent for each process stage in each time period.
• Each process has capacity limits determined by the number of pieces of processing equipment.
• Transition rates and process yield rates can vary with time.
• You can inject purchased input units into any stage in the flow.

Each work unit accumulates cost as it proceeds through the process.

• Reports costs and cost per unit for inputs, units processed, and good units out for each stage in each time period.
• Allocates cost of scrapped units (less salvage value) to good output units at each processing stage.
• Shows costs with and without breakout of material, labor, allocated capital expense, and overhead.

The Advanced version has additional features.

• Tracks sales and operating results for one or more products produced.
• You can sell the output of any process stage.
• Sales of each finished product are driven by market demand and limited by available finished units.
• Operating reports include revenue, units sold, prices, cost of goods, and gross margin.
• The model supports multiple scenarios in which process parameters (such as process transition rates, process costs, and yields) vary across scenarios.

Here are some examples of where we have used process flow analysis of this type.

• Model the manufacturing processes and costs for incandescent, fluorescent and high-intensity discharge lamps.
• Analyze the impact of automating some processes, increasing machine speeds, reducing scrap rates.
• Simulate manufacturing function for new products before manufacturing begins, to help translate technical information into cust projections, and to identify places where design changes, process improvements or new processes are needed.
• Model processing of travel tickets in the back office of a global travel agency.
• Figure out where and why most of the cost and errors are generated in the current process, to identify opportunities for reducing costs and improving efficiency.
• Evaluate the impact on costs of a new highly automated computer system.
• Analyze manufacturing costs of narrow-body commercial aircraft.
• Compare costs of processes and overhead versus ten years earlier to identify candidates for cost reductions.
• Evaluate the cost impact of combining similar (but not identical) subassemblies in the same manufacturing shop, by estimating scale economies and the 'cost of complexity' of making a wider range of assemblies in one shop.

To understand the model, we suggest exploring the flow of work units before the flow of costs. The flow of units is simpler because it is self-contained, whereas the cost flow is built on top of the units flow.