Six Sigma Virtual Catapult with Excel

 


A wooden catapult has been widely used in Six Sigma education to illustrate multiple factor process variation and optimization.

We will use a catapult simulator with 4 factors to practice:

    • Process variation
    • Linear regression
    • Response surface analysis
    • Process optimization
This will be done using Microsoft Excel Analysis and Solver


Download this Excel simulator Catapult.xlsx from Google Drive and run it in your PC.
Select sheet Catapult


Working Plan

1. What factors affect distance?

2. What values of these factors to achieve maximum distance?

3. Run a full factorial set of experiments with 4 central points

4. Can you detect curvature? Is this linear model valid?

5. Run response surface experiments to find a better model

6. What is the resulting formula to estimate distance as a function of factors?

7. What values give us the maximum distance?

8. Make 10 replicates to estimate the confidence interval of the distance

9. Calculate the angle a to hit a target of 20 with maximum or minimum values of f, m and l


Possible Factors Affecting Range

We have 4 factors that might affect distance d:

f:    spring force

m:   payload weight

a:    release angle

l:     lever length

Experimentation Levels

Based on previous experience we have selected the following experimentation levels for each factor:


Factorial Experiment Design

First we want to know which of these factors affect the range d.

We will start with a full factorial design with the 4 factors adding 4 central points.

See article  Design of Experiments (DOE) with Excel

In sheet Coding  we obtain the uncoded values from the coded design matrix.




Run the Experiments

We will now simulate the running these experiments entering the uncoded matrix (copy the matrix and paste ONLY VALUES) in our simulator in sheet Catapult:


Factorial Analysis



We obtain:




Curvature

The R square value of 0.68 indicates that this linear mathematical model only explains 68% of the variation.

On the other hand the p-values of the four factors below 0.05 indicate that they all have a significant effect on the distance d. 




Plotting distance as a function of angle we see that the central point is out of line with the extremes: the relationship between distance and angle is not linear. 

This invalidates our linear model: we will try a quadratic model with response surface design of experiments.

Response Surface Design

See Response Surface Design of Experiments with Excel

We will run a 4 factor CCD design in order to use the previous experiments already performed.
Sheet Response:


To the 16 full factorial experiments already performed we now add 8 additional star points and 7 central points to complete the response surface experiments.


Response Surface Analysis

We now compute the squares and interactions of factors for the analysis in sheet Response:

We do the regression analysis using d as an output and all factors, interactions and squares as inputs.

The result of the regression analysis in sheet Response:

We now have a good R square value of 91%

We will now remove non-significant squares and interactions (p > 0.05) and run the regression again in sheet Reduced:



The p-values for f and l are above 0.05 but their interactions are below 0.05 so they must remain.

The resulting distance estimate formula is:

d = 23 + 1.3 f -7.9 m +1.4 a -1.6 l -1.5 f*m + 0.3 f*l + 28 m*m 

Maximum Range

We will prepare the cells for Solver to calculate the optimal factor values to achieve maximum distance in sheet Reduced.

We prepare the yellow cells to contain the values of f, m, a, l that Solver will calculate to maximize distance d (R26).

Squares and interactions in column R are calculated with formulas from the yellow cells.

The formula for distance in R26 is = SUMPRODUCT(M17:M25 ; R17:R25):




We will ask Solver to maximize distance in R26 by changing the values in the yellow cells R18:R21.



We run Solver to obtain the values that maximize distance.

The maximum distance  of 59.6 is, therefore, achieved with:

f = 20 (maximum force)

m = 1 (minimum payload)

a = 60.6 degrees release angle

l = 10 (maximum lever length)


 Results Confirmation

We run 10 experiments all with these optimal values in sheet Confirmation:

The resulting confidence interval for the mean of these results is (68,75)

Check for normality of these results in sheet Normality:


The distribution of means follows a normal distribution.

The resulting confidence interval is (68,75) as seen before

With our fixed f, m and l values the relationship between angle and distance in sheet Max a is:


We confirm the maximum distance with an angle of 60º.

Hit Target 20

We now want to calculate the factor values to hit a specific target.

There are many combinations of f, m and l values that will allow us to achieve the target of 20 by changing the angle a. 

In sheet Target 20:

We fix f = 10, m = 2 and l = 10 and use Solver to calculate the angle a to hit the target d = 20



The resulting angle is a = 51.2

Average Confidence Interval

We replicate with this optimal angle in sheet Confirmation 20 and obtain the confidence interval in sheet Normality 20: 


Conclusions

  • The Six Sigma Catapult is a useful device to experiment process variation, Design of Experiments and multiple factor process optimization.
  •  Process simulation could be a useful alternative, when it is not possible to use an actual catapult.
  • The catapult may be simulated with Excel. 
  • Group work and discussion is still possible remotely, discussing with a video call.
  • Design of factorial experiments, and Response Surface analysis, may be done with Excel Data Analysis.
  • Excel Solver may be used to optimize the mathematical model with constraints.

Related Articles 
































Comments

Post a Comment

Popular posts from this blog

Design of Experiments (DOE) with Excel

Image
Design of Experiments (DOE) is a very useful process improvement methodology.  Microsoft Excel has some powerful data analysis tools, which I have, successfully, used for DOE. When it comes to analyzing cause and effect, we want to know what process factors affect our outputs.   Correlation and Regression could give us an indication of the main factors, and the extent they affect the outputs. In some cases, there may be interactions among the different factors, or the mathematical relation between factors and outputs may not be linear. In these cases, it is useful to run a systematic set of experiments, to test all possible combinations of factors, to relate them to the outputs. Factorial Experiment Example We want to minimize process loss, and after some brainstorming among the process specialists, we concluded that 5 factors may affect loss. Based on current factor levels, we have selected the following levels to experiment with:    Download this Excel file DOE_w...
Read more

Excel VSM Simulator

Image
    A still picture of a Value Stream Map may not fully explain the behavior of the Value Stream. In some cases we need to understand its dynamic behavior. To do this we will convert our Excel Value Stream Map into a Monte Carlo Simulator. In Excel Value Stream Map we saw how a complex Value Stream could be mapped using Excel. We saw the importance of mapping feedback loops such as test-repair loops because they may become the bottleneck for the complete value stream. In this situation not all items flow along all branches so we need to calculate the % flowing along each branch. This enabled us to calculate the cycle for each workstation based on the staffing. Finally we were able to calculate the ideal staffing to balance the line: insure no workstation cycle is above the average inter-arrival time (takt time). This we did using Solver. Value Stream Map: Still Picture Vs Dynamic Behavior Mapping the current value stream is a good starting point to understand how it behaves b...
Read more

Response Surface Design Of Experiments with Excel

Image
Design of Experiments is a useful methodology for process improvement. The purpose is to find a relationship between process variables we can control and key process outputs in order to increase process capability . You can use Excel statistical analysis tools, Solver, Pivot charts, etc. to plan and analyse the results of these experiments. The first approach is to look for a linear relationship as shown in:   Excel DOE But in some cases this relationship may not be linear, in which case we will try a quadratic model with Response Surface DOE . We will use an example in this Excel file you can download: Download file   ExcelResponseSurface.xlsm   from OneDrive to your PC. In this example we are trying to maximize process yield acting on the critical factors pH , Temperature and Time . We will run the experiments in the Experiments simulation sheet using coded values.   Code pH Temperature Time -1 2 120 7 1 12 150 15 Factorial Experiments We start by ru...
Read more