Final Exam
Due: Thursday, Feb 23rd @11:59pm
Total possible points: 30% (out of 100%)

Instruction:

• Chapters for the midterm exam is from 1 to 5.

• Please visit the class website for downloading data files and hint (week 6 row).

• You must use ONLY Microsoft Excel for this exam and turn in ONLY ONE Excel file.

• Please use "textbox" function from "Insert" for explaining your answers.

• Please use "sheets (I also call "tap") (bottom of Excel screen)" for labeling your answers

• You need to rename each sheet and make sure that renames make sense.

• For example, first sheet can be renamed as "Q 1" or "Q2-a."

• You can create more sheets by simply clicking "insert Worksheet" or Shift + F11. 

• Your Excel midterm file must include all the required charts, tables, calculation and functions as well as explanation. Other wise, you will receive zero point.

Please click the following link to download the Excel data file for the questions 1, 2, 3, and 4

1. Calculate maximum, minimum, range, # of classes, and class width. Make sure to show all the necessary calculations using functions and formulae. Otherwise, no point will be given. Then, develop a frequency distribution, relative frequency distribution, and histogram. Use the "Dollar Claim Amount" column (5% points).

2. Calculate followings using the "Dollar Claim Amount" column and than explain primary usage of each one .  Make sure to show all the necessary calculations using functions and formulae (5% points).

   1. Measures of central location (mean, mode, median, 67th percentiles, box & whisker plots)

   2. Measures of variation (range, inter-quartile range, standard deviation)

   3. Using the mean and standard deviation together: coefficient of variation

3. Develop a joint frequency distribution as well as a joint relative frequency distribution for the variables "Plant Location" and "Complaint Code" using the "Pivot Table" function (3% points).

4. The midterm data file company makes backup alarms for machinery like forklift and commercial trucks. When a customer returns one of the alarms under warranty, the quality manager logs data on the product. Answer for following questions and CLICK HERE to see the HINT for this question  (5% points):

   1. What is the probability the product was made at the Salt Lake City Plant?

   2. What is the probability the reason for the return was due to a wiring problem?

   3. What is the joint probability the returned item was from the Salt Lake City plant and had a wiring related problem?

   4. What is the probability that a returned item was made on the day shifted at the Salt Lake City plant and had a malfunction problem?

   5. If an item was returned, what is the most likely profile for the item, including plant location, shift, and cause of problem?

5. Use the binomial distribution in which n = 6 and p = 0.3 to calculate the following probabilities. You must use Excel's binomial distribution function (4%):

   1. X is at most 1.

   2. X is at least 2.

   3. X is more that 5.

   4. X is less than 6.

6. The answer for exercise 5-41 question (a) on page 211 is following: If 5 or more confirmed guests do not show then no guests will be sent to another hotel. Therefore, we find the probability of 5 or more successes, where a success is defined as a confirmed reservation that does not show. In this case n = 25, p = 0.15 and x = number of no shows. The P(x > 5) = 1 – P( < 4) = 1 - 0.6821 = 0.3179. Here are answers for question (b) 0.2174 (c) 0.25374. They are correct answers and your job is to prove those answers (b) and (c). That is, I want you to show and explain "Proof Processes" for getting those correct answers (just like the question (a)) using Excel (4% points).

7. Solve the exercise question 5-44 on page 211(4% points).