Excel Assignment 1 – Displaying and Describing Data | Complete Solution

Excel Assignment 1 – Displaying and Describing Data

The staff at Bread & Butter Bakery is having difficulty with producing the right amount of bread. They have never examined the demand for specific types of bread and know (from experience) that the demand changes depending on the day of the week. To better estimate their supply needs, Chris Herrin (Founder and Chef) collects data on bread loaf sales over a three week period. Although he is able to organize his data into a nice spreadsheet, he is not very familiar with data analysis or how to make sense of his data. He approaches you (knowing about your newly developed expertise in statistics) for help with answering specific questions.
Open the data file “Bakery” (available in the description of this assignment on Canvas). Use this data set to answer the following questions using Microsoft Excel. Be sure to save this document (with answers typed and figures pasted from Excel when requested) and your Excel Workbook; both documents will be submitted on Canvas for your Excel Assignment. The Excel workbook is worth 10% of your grade (each figure must accompany a pivot table and be on a separate worksheet/tab that is clearly labeled, all calculations must use pivot tables or excel functions).
Use the video tutorial (available in the description of this assignment on Canvas) for step-by-step instructions on how to use Microsoft Excel for completion of this assignment.

Visual Display of Frequencies of Bread Sales (be sure to create each figure in a separate worksheet/tab in Excel, label each tab according to the contents):
1. Create a joint frequency bar graph for total bread sales on each day; make sure axes are labeled and there is a key for each bar color (paste below; 2 points).
a. Which day appears to have the highest overall sales (1 point)?
b. Which type of bread appears to be most popular (1 point)?

2. Create a pie chart for percentage of white bread sold on each day; make sure the percentages are presented within each slice and there is a key for each slice color (paste below; 2 points).
a. Which day has the highest percentage of white bread sales (1 point)?
b. Which day has the lowest percentage of white bread sales (1 point)?

3. Create a pie chart for percentage of wheat bread sold on each day; make sure the percentages are presented within each slice and there is a key for each slice color (paste below; 2 points).
a. Which day has the highest percentage of wheat bread sales (1 point)?
b. Which day has the lowest percentage of wheat bread sales (1 point)?

4. Examine the two pie charts (from #2 & #3) and the joint frequency bar graph (from #1). Which presentation of information (bar graph or pie charts) provides the most useful information for determining supply needs of white versus wheat bread each day of the week? Explain based on the type of information presented in the pie charts versus the bar graph (2 points).
5. Create a pie chart for percentage of each type of bread sold on Sundays; make sure the percentages are presented within each slice and there is a key for each slice color (paste below; 2 points).
a. Which type of bread had the highest percentage of sales on Sunday (1 point)?
b. Which type of bread had the lowest percentage of sales on Sunday (1 point)?

6. Create a pie chart for percentage of each type of bread sold on Saturdays; make sure the percentages are presented within each slice and there is a key for each slice color (paste below; 2 points).
a. Which type of bread had the highest percentage of sales on Saturday (1 point)?
b. Which type of bread had the lowest percentage of sales on Saturday (1 point)?

7. Examine the two pie charts (from #5 & #6) and the joint frequency bar graph (from #1). Which presentation of information (bar graph or pie charts) presents the most accurate illustration of the overall sales/demand for each bread type on Saturday versus Sunday? Explain based on the type of information presented in the pie charts versus the bar graph (2 points).

Mean and Standard Deviation of Bread Sales (create a single pivot table in a new spreadsheet/tab with all the values needed to answer the following questions, label the tab accordingly; also round answers typed below to the thousandths place)
8. What are the mean sales per day for each type of bread (3 points)?
a. White    
b. Wheat    
c. Multigrain    
d. Black    
e. Cinnamon Raisin    
f. Sour Dough French    
g. Light Oat

9. What is the mean number of sales for cinnamon raisin bread on Saturday (1 point)?

10. Why should Chris estimate his daily bread demand based on the means for each day over the three week data collection period instead of the sums (2 points)?

11. What is the standard deviation for wheat bread (1 point)?

12. What is the standard deviation for sour dough bread (1 point)?

13. According to the standard deviations, which type of bread (wheat or sour dough) has more consistent sales (e.g., which estimate of sales is probably going to be more accurate more often? Explain (2 points)

14. What is the coefficient of variation for wheat bread (1 point)?

15. What is the coefficient of variation for sour dough bread (1 point)?

16. According to the coefficients of variation, which type of bread (wheat or sour dough) has more consistent sales (e.g., which estimate of sales is probably going to be more accurate more often)? Explain (2 points)

17. Explain why you had different conclusions based on the standard deviation versus the coefficient of variation (2 points)