So I am working on an excel project for class. I am having trouble getting started. If someone could help get me started, I'm
sure I can figure out the rest. I have attached what I have started on (I think I am doing it wrong) and the project outline.
Once again if I could have some help getting started, that would be great!!!
Who does not like cookies? While cookie preparation is more of an art, we can use science to help polish this art and bake
better cookies. Maggie is fond of cookies and every chance she gets, she bakes some. Recently, she has been using a new
recipe to bake chocolate chip cookies. Unfortunately, she is not satisfied with their quality, but she does not want to give
up. She is pretty confident about the quality and quantity of all but two ingredients that are used in preparing the cookies,
sugar and salt. She has been using different brands of sugar and salt, she has changed the quantity used, but still she is
not happy with the results.
As an engineer, Maggie wants to build a decision support system to help improve the recipe. She baked cookies using the
following four different combinations of sugar and salt: 1 cup of white sugar and no salt; 1 cup of white sugar and 1
teaspoon of salt; 1 cup of regular sugar and no salt; 1 cup of regular sugar and 1 teaspoon of salt. She had her friends try
the cookies and evaluate their taste on a scale of 1 to 10, with 1 being the worst and 10 the best. Maggie used the following
model to collect and analyze the data from the surveys:
In order to identify how sugar or salt (individually) or their interaction influences the taste of cookies, we will perform
an ANOVA analysis. Below we describe the main steps of this analysis:
1. Decide on the total number of replications.
2. In the problem description we identified four types of experiments to be performed (Experiment 1: use 1 cup of white sugar
and no salt; etc). Number the experiments from one to four. For each replication, randomly generate a sequence of experiments
to be performed. For example, during Replication 1, we first bake cookies using Recipe 1, then we bake again using Recipes 4,
2, and 3. During Replication 2, we bake cookies using Recipes 4, then 3, 2 and 1; etc. Assign a code to each batch of cookies
baked in each experiment of each replication performed.
3. Build a spreadsheet that presents the feedback from the surveys for each batch of cookies prepared.
4. Use the data analysis tools in Excel to run a Two Factor ANOVA with replicates.
5. Use the results from the ANOVA analysis to identify the F-Statistic and degrees of freedom for the two factors and their
interaction. Use the degrees of freedom and the user-defined confidence level to determine the significance of each factor
and their interaction to the taste of chocolate chip cookies.
6. Graph the average responses for each factor combination. The significant interactions are the ones that lack parallelism
of the lines.
7. For the significant factors, perform the Tukey or Fischer LSD tests.
8. Check the adequacy of the model using the normal probability plots, the run order plots, etc.
The results of
the data analyses enable us to identify the factors that influence the taste of cookies. One can choose to repeat the
procedure using different levels of the factors that are of concern. For more details about experimental design and ANOVA
analysis, see Montgomery (1997).
1. Build a spreadsheet that presents the following information about each batch of cookies baked: batch number, replication
number, and experiment number.
2. Build a spreadsheet that presents the results of the survey.
1. Build a welcome form.
2. Build a form that allows the user to update the data used to perform the ANOVA analyses. For this purpose use the
a. A combo box that enables the user to choose the number of levels for each factor. In the problem description we identified
two factors to be observed during the experiments. For each factor we identified two levels (e.g., no salt or 1 teaspoon of
salt). Excel can only handle ANOVA analysis with two factors, but there is no limit on the number of levels for each factor.
b. A text box where the user can type in the total number of replications.
c. Command buttons that, when clicked on, open the Excel spreadsheets and allow the user to add/update/delete the data on
d. A frame that has two option buttons. The option buttons enable the user to choose the type of test (Tukey or Fischer LSD)
to be performed.
e. A command button that, when clicked on, performs the ANOVA analysis and presents the corresponding results.
Design a logo for this project. Insert this logo in the forms created above. Pick a background color and a font color for the
forms created. Include the following in the forms created: record navigation command buttons, record operations command
buttons, and form operations command buttons as needed.
1. Report the results from the ANOVA analysis in a tabular form.
2. Present the factors and iterations that have been identified as significant for this experiment. For each factor (or
iteration) report the corresponding p value.
3. Graph the average responses for each factor combination.
4. Prepare box plots and scatter diagrams using the data collected for each factor.
5. Present the factors that have most impacted the taste of the chocolate chip cookies. Use the results from the ANOVA
analysis and the Tukey and Fischer LSD tests to identify the corresponding optimal levels.