Reviewing the Parts of the Model

The liquid pump spreadsheet models a design effort with two basic goals: meeting the flow rate target as closely and consistently as possible and minimizing cost. The following sections describe six main parts of the model, indicated by numbers in Figure 175, Fluid Pump Model:

Known Flow Rate Formula

The Known Flow Rate formula specifies the relevant variables discussed earlier: piston radius, piston stroke length, backflow rate, and motor speed.

Flow Rate Variable Assumptions

The flow rate variable assumptions are Crystal Ball assumptions that allow each of the flow rate variables, listed under the Known Flow Rate formula (1), to be included in the Monte Carlo simulation.

To see how these are defined:

Select cell K18 and click the Define Assumption button.
Optional: Look at K19, K20, and K21.

Each assumption is defined as a normal distribution with a mean equal to the Nominal Initial Value and a standard deviation equal to the StDev Initial Value. The Mean and Standard Deviation parameters are not entered directly. Instead, they are entered as cell references to the Initial Value cells.

Note:

In this model, whenever you see the word "nominal," it means approximate, designed, or theoretical, as opposed to the actual measured value.

Flow Rate and Cost Forecasts

The flow rate and cost forecasts are Crystal Ball forecasts, the output of the Monte Carlo simulation.

The Flow Rate Forecast is based on the Known Flow Rate formula (1).

To view it:

Select cell K23.
The formula in that cell is the Known Flow Rate formula (a function of the flow rate variables) expressed in Microsoft Excel format.
Click the Define Forecast button.
Notice that the values in cells E24, E25, and E23 are displayed in the Define Forecast dialog as the process capability lower specification limit (LSL), upper specification limit (USL), and target value, respectively.
They are defined as cell references. To demonstrate this, click in one of the text boxes. The related cell reference is displayed. (Note: If you don’t see these text boxes in the Define Forecast dialog, process capability features have not been activated in the Run Preferences dialog.)
The Total Cost Forecast is the sum of the Component Cost Function column values in the upper table. To view it:
Select cell K25.
Notice its formula is simply the sum of the component costs in cells J18 through J21. No target or limits have been established for this forecast.

Flow Rate Target and Limits

The flow rate target and upper and lower specification limits (4) are values defined by the food processing plant in the Define phase of the DFSS process. The previous section shows how they can be used to set the Target, LSL, and USL for the Flow Rate Forecast in cell K23.

Cost Coefficients

The cost coefficients table contains values used to calculate the component cost functions, summed to yield the Total Cost Forecast (3).

To see how these are used to calculate the component cost functions:

Select cell J18.
This formula uses the Base Cost Coefficient of the piston, the initial value of the piston radius, the "Tolerance" Cost Coefficient of the piston, and the initial value of the standard deviation. The cost is directly proportional to the radius squared due to raw material usage and inversely proportional to the standard deviation squared (a common method to relate tolerance to cost).
Optional: View the contents of cells J19, J20, and J21.
These formulas are also similarly derived from cost coefficients or nominal costs relevant to each particular flow rate variable.

Motor and Backflow Valve Cost Options

The motor and backflow valve cost options are two sets of nine options each provided by the motor and backflow valve vendors. Different performance specifications have different costs associated with them.