Posted: September 18th, 2017

Statistics

All of your work should be contained in ONE Excel spreadsheet (though you are welcome to use a separate tab for each question). Put all of your details in the Excel file. You should be using the formulas in Excel, too. It’s extremely important for you to learn to use the many features of Excel. Thus I cannot consider anything submitted in a Word document.

Basic Business Math

1A. A new yarn shop wants to apportion their investment money ($150,000) for advertising, building upgrades, and education in the ratio of 7:8:10. How much money does each category get apportioned?

1B. CatCo has a new line of kitten starter kits. The basic kit features a sandbox,

sand, scooper, three cans of kitten food and catnip. The exotic kit features the premium self-cleaning sandbox with all natural sand, 10 cases of kitten food, a living catnip plant, and a scratching post tower. The basic kit costs $80 and the exotic kit costs $200. Kitten lovers bought 10 times as many basic kits than exotic kits last month. And last month, both types of starter kit had total sales of $16,000 (this is the total for both items). How many basic kits did CatCo sell? How many exotic kits did CatCo sell?

2. Create an Excel spreadsheet that can be used to calculate your grade in this class. The spreadsheet should include the weights of each graded assignment, your grade in each assignment, and your final grade. To use this for your benefit, please design it so that it can be used to calculate your interim grade before you have all the grades. (An interim grade should consider only the grades you have earned at any point in the course and should not assume you will receive a zero on the remaining assignments.)

Use Excel’s built-in functions to build the calculator so that Excel will automatically calculate your grade as you enter your grades during the class. You do not need to enter any grades; just enter the functions.

Use a new worksheet called “Grade Calculator” in the same Excel file that you are submitting for this assignment.

After this week you will receive the solution from your professor and you will be able to use our solution for this class.

Hint: Please enter sample scores to test your grade calculator. (If it has no functionality – and is merely a list of assignments – it is not a calculator, and you have a lot more work to do.) I’ll be checking the “interim” grade feature as well.

You should remove your sample scores prior to submitting your homework, but note that your professor will enter sample scores to test your work. Also, your grades in this course will be expressed as percentages (0% to 100%) rather than as points.

3. An essential property of concern for any food company that uses a high-speed

bottle-filling machine to package their product is the weight of the food product in the individual bottles. If the bottles are under filled, two problems arise. First,

customers may not have enough product for their needs. Second, the company

may be in violation of the truth-in-labeling laws. In this example, the label weight on the package indicates that, on average, there are 2.5 ounces of product in a bottle. If the average amount of product in a bottle exceeds the label weight, the company is giving away product. Getting an exact amount of product in a bottle is problematic because of variation in the temperature and humidity inside the factory, differences in the density of the product, and the extremely fast filling operation of the machine (approximately 450 bottles per minute). The following table provides the weight in ounces of a sample of 60

bottles produced in one hour by a single machine:

1. Compute the arithmetic mean and median.

1. Compute the first quartile and third quartile.
2. Compute the range, interquartile range, variance, standard deviation, and coefficient of variation.
3. Interpret the measures of central tendency within the context of this problem. Why should the company producing the bottles be concerned about the central tendency?
4. Interpret the measures of variation within the context of this problem. Why should the company producing the bottles be concerned about variation?

4. A well known apple juice production company maintains records concerning the number of unacceptable containers of apple juice obtained from the filling and capping machines. Based on past data, the probability that a container came from machine I and was nonconforming is 0.03 and the probability that a container came from machine II and was nonconforming is 0.02. These probabilities represent the probability of one container out of the total sample having the specified characteristics. Half the containers are filled on machine I and the other half are filled on machine II.

1. If a filled container of juice is selected at random, what is the probability that it is an acceptable container?
2. If a filled container of juice is selected at random, what is the probability that it was filled on machine II?
3. If a filled container of juice is selected at random, what is the probability that it was filled on machine I and is an acceptable container?