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 One Drive


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 iterval 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



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 running these experiments entering the uncoded matrix 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.


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:

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

The result of the regression analysis:

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:



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.

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:
Sheet Factorial: Using Excel Data Analysis/ Regression:

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:

Histogram for d and confidence interval for its average:



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


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. 

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


Conclusions

  •  Process simulation could be a useful alternative, when it is not possible to use an actual catapult.
  • We can use an Excel simulator. 
  • Group work and discussion is still possible remotely, running the simulator as a shared file in the cloud such as One Drive, while discussing via Zoom, Meet, or Teams.
  • 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_with_Excel.xlsm fro
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 running a full factorial experi
Read more