|
Using Spreadsheets in RENO
ReliaSoft's
RENO
software tool is a platform for building and running
analyses for probabilistic or deterministic scenarios
using an intuitive flowchart modeling approach and
simulation. RENO can be thought of as a "visual
spreadsheet" that allows you to graphically create and
manipulate data and equations. However, RENO
also goes a step
further and provides the ability to manipulate internal
Excel®-compatible spreadsheets from the flowcharts. In
this article we will present an example using these
spreadsheets.
Example
A company manufactures hinges
that are
made up of four components, where components A, B and C
must fit inside component D when the unit is assembled. The next
figure shows a schematic of the hinge's assembly.
The manufacturer wants to determine the percentage of
hinges that would fall out of specifications.
Specifically, the manufacturer wants to estimate the
probability that the size of (A+B+C) will be greater than
the size of the space within D. For
that purpose, the manufacturer collects data about the
dimensions of each of the manufactured components and
determines the part dimensions as follows:
- A – Normal distribution with mean = 2 and std = .02
- B - Normal distribution with mean = 2 and std = .02
- C - Normal distribution with mean = 30 and std = .2
- D - Normal distribution with mean = 34.5 and std = .5
We could approach this problem in RENO in many ways.
The main idea is to generate four random variables for
A, B, C and D and determine whether the sum of A+B+C
exceeds the dimensions of D. We would repeat this
simulation a number of times and determine
the number of times we would have a hinge that cannot be
assembled. To do this, we could use RENO’s flowchart
capabilities exclusively. An example of this can be
found
here [1]. We could also set up this problem in a
spreadsheet and use the flowcharting capabilities of
RENO to update the spreadsheet for each simulation.
This spreadsheet-based approach may be more familiar for
some users and it may make it easier to expand the
model, for example if you need to add a large number of
additional parts to the analysis
The first step would be to set up the spreadsheet for
this model as shown next:
The cells with a gray
background are user input values such as the mean and
standard deviation of the hinge components. The cells
with a yellow background are cells that contain equations.
Cell D9 contains the following equation:
=IF((D4+D5+D6)>D7,1,0)
The value in cell D9 will be 1 when the hinge is out of
spec (i.e. when the sum of the cells D4, D5 and D6 is greater
than the value in cell D7). It will be zero otherwise.
Cell D13 contains the following equation:
=IF(B1>0,D12/B1,0)
The value in
cell D13 is the ratio obtained by dividing the out of spec count
found in cell D12 by the number of simulations in cell
B1. If the number of simulations is zero, then the value
of this cell is 0. Note that this cell has been
formatted as a percentage.
We are now ready to
build a flowchart that will:
- Write the number of
simulations run in cell B1.
- Write four
random variables for A, B, C and D in cells D4, D5, D6
and D7 respectively.
- Update the out of spec count in
cell D12.
- Repeat N number of simulations.
The
flowchart is shown next.
Step 1: Use a block to write
the number of simulations that have been run in cell B1, as
shown next.
The function
SET_NUMBER(Spreadsheet Name, Sheet Index, Column, Row,
New Value) used above is a reserved keyword in RENO that sets
the spreadsheet cell at the specified Column and Row to
the specified New Value. In
this case it is setting cell B1 in Spreadsheet1 to SIMS_TOTAL.
SIMS_TOTAL is another reserved keyword in RENO that evaluates to
the total number of simulations defined during a run.
RENO's reserved keywords, definitions and predefined
functions can be accessed though the Function Wizard.
The Function Wizard also can be used to build equations
for definitions and standard
blocks. We can use the Function Wizard to build the equation for
"Block 1:
Initialize" as shown next.
Step 2: Use a
block to draw four random variables using the parameters
entered by the user in Spreadsheet1 and then to write the
variables
to cells D4, D5, D6 and D7 respectively.
The function SET_NUMBER is used again here to write the
values NormalRV(1), NormalRV(2), NormalRV(3) and
NormalRV(4) to cells D4, D5, D6 and D7 respectively. The function NormalRV
is a Definition created by the user that
takes one variable (the row where the parameters can be
found), and returns a normal random variable using those
parameters.
NormalRV uses the intrinsic
function NORMINV(probability, mean, standard_dev), which
returns the inverse of the normal cumulative
distribution for the specified mean and standard
deviation.
Remember that in order to draw a
random number that follows a normal distribution, we
need the parameters of the distribution and a uniform
random number that will be used as the probability. The
parameters of the distribution are obtained from the
user inputs in the second and third columns of
Spreadsheet1 in the row passed to the function NormalRV.
The probability is generated by the user-created Random Variable
Definition shown
next.
Note that RENO will initialize (draw
a value for) URN once at the beginning of each
simulation. From there on, it will be a constant. Therefore,
in the NormalRV function, we use the intrinsic function
RESET_RV(Random Variable), which resets the value of the
specified Random Variable (URN) by drawing a new value. In this manner, we ensure
that a new random variable is returned each time NormalRV is
called.
Step 3: Use a Block that will add the
value in cell D9 to the cell in D12 after each
simulation.
Step 4: Specify the number of
simulations in the Simulation Console, as
shown next.
When the simulation is complete,
the results are displayed in Spreadsheet1, as shown
next. Note that the values of A, B, C and D are the
random values used in the last simulation (the 1,000th
simulation).
Based on 1,000 simulations,
A+B+C will exceed D 17.7% of the time.
References
[1]
"Example R-3 - Hinge Assembly Variability," Internet:
http://www.ReliaSoft.com/reno/examples/renoexr3/index.htm,
[Aug. 6, 2009].
[2]
Wittwer, J.W., "Stochastic Model Example: Tolerance Stack-Up,"
Internet:
http://vertex42.com/ExcelArticles/mc/StochasticModel.html,
June 1, 2004 [Aug. 6, 2009].
|
