Business Excel Homework

PROJECT OPTION til:

Data file: DeliveryTimes-5518.xlsx

NOTE: Use a = 0.05 as the significance level

Scenario:
A manufacturer called Lukey Industries is facing a choice between two suppliers for a part:
Jellystone Manufacturing and Wallston Incorporated. The timing of delivery of these parts is a
primary concern, because the parts are needed at a crucial bottleneck in the manufacturer’s
process. In choosing a supplier, Lukey Industries has a couple of criteria in mind: speed and
reliability of deliveries. First, Lukey would like to choose the supplier with the fastest delivery
time. However, if both Jellystone and Wallston have similar delivery times, then Lukey would
like to choose the one with the most consistent delivery times.
Lukey Industries has hired you as a consultant to perform a statistical analysis and prepare a
report to help them choose the best supplier.
To compare Jellystone Manufacturing and Wallston, Inc., you take two random, independent
samples of deliveries of the part, and measure the time it takes from order to delivery. There
are 150 deliveries total: 78 from Jellystone and 72 from Wallston. The delivery times, in hours,
are recorded in the attached Excel data file: DeliveryTimes-5518.xlsx
A Note on Formatting: Your report should have three main headings:

Introduction: Here you should explain what the analysis is and why you are performing
it. A few sentences are usually enough.

Analysis: Here you should present your statistical analysis, including any requested
hypotheses, tables, and interpretations or decisions. All technical material should go
here.

Conclusion: Here you should briefly summarize in plain language what your statistical
analysis showed, and make your recommendation based upon it. If follow-up questions
are asked in the directions, the answers to those should go here.

Directions:

1) When performing and presenting any statistical analysis, it is important for your audience to
understand the characteristics of your data. These characteristics are summarized using
descriptive statistics that measure central tendency and dispersion for each supplier. In this
case, those descriptive statistics should be: mean, median, variance, standard deviation,
minimum, maximum, and the sample size for each sample (Jellystone and Wallston).

In Excel: The descriptive statistics may be calculated using the Descriptive Statistics
procedure in Excel’s Data Analysis tool OR using the appropriate Excel functions. If you
use the Descriptive Statistics procedure, leave all the statistics in place in the Excel
sheet, and only include the requested values in the report. If you use Excel functions,
please leave them in place in the cells of your data sheet.

In the Report:

Include a single table with the numerical descriptive statistics in the rows and a
column for each supplier (Jellystone and Wallston). Only report the specific
descriptives requested in the directions above.

Comment on the similarities and differences between the two suppliers. Note:
numbers can be similar without being equal.

2) One concern when choosing a supplier is whether the average length of time to delivery is
different between the two suppliers. To test this, you should use the methods of Chapter 10 to
decide whether there is a difference between the population mean delivery times for Jellystone
and Wallston. Since you are using Chapter 10 methods, it will help greatly if you re-read the
relevant handouts.
In Excel: You must decide on the appropriate test for the difference between the two
population means.

In section 10.1, we learned a hypothesis test for the difference between two
population means when the population standard deviations are known; that
procedure is the z-Test: Two Sample for Means in Excel’s Data Analysis tool.

In section 10.2, we learned a hypothesis test for the difference between two
population means when the population standard deviations are unknown; that
procedure is the t-Test: Two-Sample Assuming Unequal Variances in Excel’s Data
Analysis tool.

Choose the appropriate test for this particular analysis and complete the analysis in
Excel (Note: the key question to answer is: are the standard deviations that you have
here characteristics of the populations, and known prior to sampling? or are they
sample standard deviations?). Step-by-step directions for each of these tests are at the
end of the respective sections in the book, and you can also look back at the MindTap
Excel activities for Chapter 10 to review the video tutorials.

In the Report

Include a brief description of the purpose of this test. Indicate which test you
chose to perform and explain your decision.

State your hypotheses.

Show the results of the test in a table. Only include the p-value and/or critical
value that you are actually using in the analysis.

Report the correct p-value for your hypotheses and indicate whether you reject
or fail to reject the null. You may choose to use the critical value approach for
this step, but whatever you decide to do, be sure to state the basis for rejecting
or failing to reject the null hypothesis.

Interpret the results of your hypothesis test in terms of the original question: do
your results indicate that there is a difference between the population mean
delivery times for the two suppliers, or not? If so, which one is faster?

3) Lukey Industries’ second concern is the variability of completion times: if neither supplier is
faster, then they would like to choose the more consistent and reliable supplier. (Remember, if
the variance of one supplier is lower, then that supplier has more consistent delivery times).
Test whether the variances of the delivery times for Jellystone and Wallston are different by

performing an equality of variances test, which we learned in section 11.2. The step-by-step
directions for Excel are at the end of that section, and you can review the Chapter 11 MindTap
Excel activities for video tutorials. Reviewing the Chapter 11 handouts on hypothesis tests for
two population variances will help a great deal.

0 In Excel: The test to compare two population variances is the F-Test Two-Sample for
Variances in the Excel Data Analysis tool. CAREFUL: This test automatically reports a
one-tailed p-value, so you must ask yourself: are you performing a one-tailed or a two-
tailed test? and act accordingly. Excel doesn’t know what you are doing or how you have
formulated your hypotheses, so be careful to use the results appropriately.

In the Report: Include a brief description of the purpose of this test.

State your hypotheses.

Show your results in a table. Only include the correct p-value and/or critical
value for this analysis in the table.

Report the correct p-value for your hypotheses and indicate whether you reject
or fail to reject the null. You may choose to use the critical value approach for
this step, but whatever you decide to do, be sure to state your basis for rejecting
or failing to reject the null hypothesis

Now that your test has shown whether or not the variances are different, look at
the sample variances to determine which method has the lower variance.
Interpret the results of your hypothesis test in terms of the original question: do
your results indicate that one supplier has a lower variance than the other?
Which supplier has the lower variance, if either of them do?

4) In the Report: Make a recommendation about which supplier Lukey Industries should
choose: Jellystone Manufacturing or Wallston Incorporated. This recommendation should refer
to the evidence from your statistical analysis.

What other steps might Lukey Industries take before making a decision about which supplier to
adopt? What other variables could be measured that might be helpful in making this decision?
Why? (Feel free to be creative here – there are many right answers)

Last Updated on February 11, 2019