ST2200/MS2020 - Excel Assignment 2025: ColaFizz
ST2200/MS2020 – Excel Assignment 2025
|
Background A soft drinks company, named “ColaFizz”, has recently changed the type of bottle used for their drink. They previously used plastic bottles, and are now using cardboard bottles, which are friendlier for the environment. The company are interested in monitoring the weight of the new cardboard bottles that they are producing. Also, they are using two different machines to manufacture these cardboard bottles, and they wish for the bottles produced by these machines to be consistent with each other. The unique selling point for their product is the extra high levels of fizz (carbon dioxide bubbles) in the soft drink. When using the plastic bottles for their product, it was accepted that 10% of the bottles had a fault in how they were sealed, resulting in reduced fizziness for those bottles. There is a |
|
possibility that this proportion may have changed since moving to cardboard bottles. You are given data on 400 cardboard bottles which have been randomly sampled from the factory process line. |
The data are stored as ColaFizz.xlsx on Canvas.
|
Column |
Variable Name |
Description |
|
A |
Bottle_number |
Unique identifier code for each bottle |
|
B |
Seal_broken |
Indicates if each bottle has a faulty seal, resulting in reduced
fizziness. |
|
C |
Machine_type |
Indicates which of the two machines each bottle was made by.
0 = Machine A |
|
D |
Manufacture_time |
The length of time (in seconds) taken to manufacture each bottle |
|
E |
Bottle_weight |
The weight of each bottle (in grams). |
|
Data for Bottle_weight: very important! There are two sheets in the excel workbook provided to you. Go to the “All Data” sheet. Note that the column “Bottle_weight” is blank. Use the following procedure to find the Bottle_weight data |
|
for your assignment. Go to the sheet named “Bottle_weight Data”. This contains 100 different possible columns of data for Bottle_weight, just one of which is to be used for your assignment. Looking across the first row on this sheet, find the column that corresponds with the last two digits of your student number. For example, if your student number is 12345600, then your last two digits are 00, and your column of data is then column B. If your student number is 12345678, then your last two digits are 78, and in that case column CB contains your column of Bottle_weight data, as shown in this screenshot: |
1
|
Back on sheet “All Data”, in cell E2, type the below formula (depending on your specific column) to link through the data you need from the sheet “Bottle_weight Data”. Example formulas, for cell E2, on sheet “All Data”: |
|
if the last two digits of your student number are 78, type: ='Bottle_weight Data'!CB2 Then copy this formula to all cells in the “Bottle_weight” column, from E3 to E401. Save the file to your memory stick or other reliable backup storage. |
Tasks
The management of ColaFizz have given you the following tasks for you to investigate and report on in your assignment.
-
Is the mean bottle weight from Machine A the same as Machine B? Test the null hypothesis that the mean bottle weights from Machine A and Machine B are the same, against the alternative hypothesis that they are different.
-
When ColaFizz was using plastic bottles, the average proportion of bottles with a faulty seal was 10%. If the proportion of cardboard bottles with a faulty seal is different to 10%, then ColaFizz would like to be aware of this. Use an appropriate hypothesis test to investigate if this proportion has changed.
-
Is there a relationship between the length of time to manufacture a bottle, and the weight of a bottle? Use appropriate graphical and numerical analyses to advise ColaFizz. (Consider Manufacture_time as the X variable, and Bottle_weight as the Y variable).
Using a simple linear regression model, calculate the prediction for Bottle_weight when Manufacture_time is 15 seconds.
2
Hints:
-
When reporting the results of a hypothesis test, you should: 1. state the null and alternative hypotheses, 2. present appropriate descriptive statistics as background, 3. present the output of the statistical tests from Excel, possibly edited to contain the most relevant details, 4. use the p- value to accept/reject the null hypothesis and 5. state your conclusions clearly.
-
Use a 5% level of significance in all hypothesis tests.
-
The Excel practical assignments in the course notes are a helpful resource for the tasks in this
assignment.
Failure to comply with the following instructions may result in losing all continuous assessment credit (20%) for this assignment. Use the following checklists to ensure you follow the instructions.
Relevant output from Excel should be copied to Microsoft Word to produce your report. (Marks will be awarded for the quality of the report presentation.)
Save your report as a Word (.doc or .docx) or pdf file – no other format will be accepted. Include your answers to the tasks in your file.
