week 3 excel

1

WEEK # 3 - EXERCISES CHAPTER # 2

Exercise Ch2-1: Level 1 – Binding Data Analysis 1 A separate processing line assembles the binding mechanisms for the TZ-Blazer skis. A new machine was recently installed on this line to automate the assembly for several components that had previously been done by hand. Installed about a month ago, the new machine seems to be working smoothly. The QC group wants to determine if the bindings made on this new automated machine meet the same high standards as when they were assembled by hand. The QC group tests ski bindings in two ways:

• Minimum pressure at which the binding mechanism automatically unlocks measured in pounds (lbs.). Bindings that unlock at less than 10 lbs. of pressure are rejected. • Temperature at which the materials of construction fail, given that the cold temperatures affect the binding performance, measured in degrees Celsius (°C). The lower the failure temperature, the better the ski binding. The maximum allowable failure temperature is – 60°C. To avoid any damage to the ski, failure is determined at the point at which the flexibility of the materials falls below a specified level.

The QC group has the historical values for these quality attributes, as shown in Table 2.6

The group has entered the production data for the past four days in an Excel workbook named Binding1.xlsx on a worksheet named BindingData.

In these steps, your task is to compare the mean, median, mode, and standard deviation of the current values with the historical values. Complete the following: 1. Open the workbook named StartfileBinding1.xlsx attached with the data of the assignment, and then save the file as 2-1-QC-Binding-Data-Analysis-1-Your-Name.xlxs. 2. Modify the temperature data so that all values are precisely stored to the nearest tenth of a degree and are displayed with one decimal place. 3. In rows below the data in columns D and E, calculate the mean, median, mode, and standard deviation for the pressure and temperature. Display pressure values in thousandths and temperature values in tenths. Make sure to label all of your calculations. 4. Use the Freeze Pane feature to display the column titles and the values calculated in Step 3 simultaneously. 5. On a new worksheet named Compare, enter the historical values for pressure and temperature (see Table 2.6), using a format similar to that shown in Figure 2.14.

2

WEEK # 3 - EXERCISES CHAPTER # 2

6. On the Compare worksheet, show the values calculated for mean, median, mode, and standard deviation by referencing the values you calculated in Step 3. 7. Calculate the difference between the current values and the historical values for pressure and temperature. (Show positive values if there is an increase from historical values.) 8. Calculate the percent difference between the current values and the historical values for pressure and temperature. 9. In an area below the analysis, list your conclusions about the effect of the new production machine on the quality of the bindings produced, based on current pressure and failure temperature data versus historical data. 10. Format the Compare worksheet so that it is easy to read and includes a title. Use cell shading and/or colors to differentiate the pressure values from the temperature values. Format the values using an appropriate number of decimal places to display the necessary information. Use percentage formats where indicated. 11. Add your name and date at the end of the exercise 12. Save and close the 2-1-QC-Binding-Data-Analysis-1-Your-Name.xlxs workbook.

3

WEEK # 3 - EXERCISES CHAPTER # 2

Exercise Ch2-2: Level 1 – Analyzing Sales for Crèmes Ice Cream Judd Hemming is the eastern regional marketing manager for Crèmes Ice Cream. Each quarter, he completes two separate analyses: an analysis comparing ice cream flavor sales volumes from all regional locations with the same quarter sales volumes from the previous year, and an analysis comparing total sales in dollars, including mean, median, mode, and standard deviation, of sales by store. Sales by Flavor in Gallons The first analysis, sales by flavor, compares the total quantities sold in gallons. The data collected provides for each flavor the number of pints, gallons, and 10-gallon tubs sold for all stores. Pints and gallons are sold directly to the public, whereas 10-gallon tubs are used for in-store sales of cones, cups, and specialty items such as sundaes and banana splits. To eliminate any impact of pricing changes or special promotions, Judd uses the ice cream volumes in gallons to compare sales by flavor. Judd has asked for your help this quarter in completing this analysis. He created two workbook files:

- StartFileCreme.xlsx, which contains the current quarter’s sales on a worksheet named Flavors

- StartFileHCreme.xlsx, which contains the corresponding historical quarterly data for the previous year on a worksheet named HFlavors.

For convenience, the flavors in both data sets are in identical order except for two new flavors introduced this year, which appear at the bottom of the current data set. Keep in mind the following conversions when analyzing this data:

• There are 8 pints per gallon. • Each tub holds 10 gallons.

Also, when calculating values for 10-gallon tubs, Judd has asked you to use the convention of rounding down the values to the nearest whole tub. Sales by Store in Dollars The second analysis you need to complete is to summarize sales in dollars by store and compare the result with the previous year’s sales. The Stores worksheet in the same workbook contains sales by store for the current quarter in dollars rounded to the nearest thousand dollars. You need to calculate some basic statistics for store sales. In the same quarter of the previous year, these values were:

• Mean: $8,817 • Median: $8,000 • Mode: $5,500 • Standard Deviation: $2,920

When writing formulas, be certain to use the most efficient method, including the use of functions as well as relative and absolute cell referencing. Complete the following: 1. Open the workbook named StartFileCreme.xlsx attached with the data of the assignment, and then save it as 2-2-Crème-Current-Sales-Analysis-Your-Name.xlsx. 2. On the Flavors worksheet, for the current quarter, calculate the total number of gallons sold for each favor. Place this calculation in the column adjacent to the data provided (Column E). Above the list of flavors, in a separate area, list and clearly label the conversion values. Remember to round down the tub quantities to the nearest whole tub when completing the calculation.

4

WEEK # 3 - EXERCISES CHAPTER # 2

Total number of gallons sold = #Pints Containers Sold Current Qtr / Pints per Gallon + #Gallons Containers Sold Current Qtr + #Tubs Used Current Qtr * Gallons per Tub Use Absolute reference in the cells yellow highlight

3. Add another column in which to enter the total number of gallons from last year. Copy the values for the total amount of ice cream sold for the corresponding flavor for last year from the StartFileHCreme.xlsx workbook in the Chapter 2 folder into the newly added column of the Flavors worksheet. 4. Calculate the overall total and mean number of gallons sold for all flavors for this quarter and for this quarter last year. (Note: Include all flavors in your calculation, whether or not there are sales for them in a given year.). Use the SUM and AVERAGE Function 5. Calculate for each flavor the percent of total gallons Current this flavor represents compared with total sales for the current quarter of all flavors. Copy this formula to the adjacent column to calculate the percent of total gallons this flavor represents compared with historical total sales. 6. In the two adjacent columns, calculate for each flavor and for the totals the difference and percent difference in sales. Assume a positive value represents an increase in sales. Flavors without sales in the previous year should be left blank. (These cells should be completely empty.) TROUBLESHOOTING: In order to complete this step successfully, pay attention to the order of precedence rules when writing the formula. Percent difference is defined as the difference between the new values minus the old value divided by the old value, so specifying the order in which the operations must occur is critical. 7. Switch to the Stores worksheet. Calculate the total sales, mean, median, mode, and standard deviation for this data set. Label the cells so that they can be easily identified. 8. On the same worksheet, set up a table to analyze the change and percent change of each of these statistical values as compared with the historical values. Based on the changes, explain on the worksheet (just below your analysis) whether the stores are doing better this year, and if sales in stores are more or less likely to vary from mean sales than they did last year. 9. Based on sales to all stores and total gallons sold for the current quarter, what is the price of a gallon of ice cream on average? Write a formula to determine this value and place it below the statistical analysis on the Stores worksheet. 10. Add appropriate worksheet titles and formatting to make the worksheets easy to read. 11. Add your name and date at the end of the exercise 12. Save and close the 2-2-Crème-Current-Sales-Analysis-Your-Name.xlsx workbook.

5

WEEK # 3 - EXERCISES CHAPTER # 2

The solution should be similar to: In the Flavor worksheet:

In the Store worksheet:

6

WEEK # 3 - EXERCISES CHAPTER # 2

Exercise Ch2-3: Level 2 – Binding Data Analysis 2 The QC group wants to extend the QC data analysis to ski binding. The group wants to determine the rankings and examine the tail ends of the data (both high and low values) and the number of bindings that will be rejected. The latest data being analyzed is located in a workbook named StartFileBinding2.xlsx. Your task is to complete this analysis. The criteria for rejecting bindings are based on the following two quality tests:

• Minimum pressure at which the binding mechanism will automatically unlock, measured in pounds (lbs.). The higher the pressure at which the binding unlocks, the better the binding. Bindings that unlock at less than 10 lbs. of pressure are rejected.

• Temperature at which the materials of construction fail, given that cold temperatures affect the binding performance, measured in degrees Celsius (°C). The lower the failure temperature, the better the ski binding. The maximum allowable failure temperature is – 59°C.

Complete the following: 1. Open the workbook named StartFileBinding2.xlsx attached with the data of the assignment, and then save the file as 2-3-QC-Binding-Data-Analysis-2-Your-Name.xlxs. 2. Write a formula in cell G3 that can be copied down the column to calculate the relative rankings of each data element for the pressure test. Set up your rankings so that the best ski is given a ranking of 1 (based on the rejection criteria described in the problem). Include an appropriate column heading in cell G2. In a similar way, set up the rankings for the Temperature test in column H so that the best ski is given a ranking of 1. Use RANK.EQ Function 3. In cell D49, write a formula to determine the number of ski bindings that failed to meet the minimum pressure requirements for quality testing. Write the formula so that if any of the data inputs change later, this value will be automatically updated. For easier viewing, use the split feature to divide the worksheet into two sections, with the top containing the title and column headings. 4. In cell E49, write a formula to determine the number of ski bindings that failed to meet the minimum temperature requirements for quality testing. Write the formula so that if any of the data inputs change later, this value will be automatically updated. Use the COUNTIF Function 5. In cell F49, write a formula to determine the total value of the ski bindings that have been rejected. Assume that no binding has failed both tests. Format this cell using the Currency Style. Include the label Rejection Summary in cell A49. Use the SUMIF Function 6. Arrange the worksheet to freeze panes so that rows 1 and 2 are always visible.

7

WEEK # 3 - EXERCISES CHAPTER # 2

7. Set up a separate worksheet named Tail in the workbook, and then create a table on the Tail worksheet listing the five highest and lowest values, as shown in Table 2.11. Be certain to include a title on this new worksheet and format the worksheet so that it is easy to read.

8. Write a formula to calculate the highest pressure value so that it will be automatically updated if the binding data is later revised. Write the formula so that it can be copied down the column to determine the second highest pressure, the third highest pressure, and so on, and across the row to determine the highest temperature. Use the LARGE Function 9. Write a formula to calculate the lowest pressure value so that it will be automatically updated if the binding data is later revised. Write the formula so that it can be copied down the column to determine the second lowest pressure, the third lowest pressure, and so on, and across the row to determine the lowest temperature value. Use the SMALL Function 10. Add your name and date at the end of the exercise 11. Save and close the 2-3-QC-Binding-Data-Analysis-2-Your-Name.xlxs.workbook

8

WEEK # 3 - EXERCISES CHAPTER # 2

Exercise Ch2-4: Level 2- Analyzing Demographic Data for Your Restaurant. You have recently decided to open a restaurant that you plan to name “Select your Restaurant Name”. In your restaurant, you plan to feature specialty desserts, along with fine cuisine. One critical decision you must make is where to locate the restaurant. Right now, you are considering two different locations—one near a large retail area on the fringe of several affluent suburbs (site A), and the other in the downtown district (site B). Before making the decision, you hired a local market research firm to provide you with some demographics of the areas and the specific dining habits of the local population that frequent other restaurants in these areas. You have entered the raw results of this research on several worksheets in the StartFileYourRestaurante.xlsx workbook. Each worksheet (SiteA and SiteB) contains the detailed responses of each of the participants of the study, including questions about their age, their income, and the number of meals and desserts they eat outside of their homes per month. Table 2.15 shows the breakdown of the age categories and corresponding income level closest to the respondent’s own income.

Complete the following: 1. Open the workbook named StartFileYourRestaurante.xlsx attached with the data of the assignment, and then save it as 2-4-YourRestaurante-Demog-Analysis-Your-Name.xlxs. 2. On the SiteA and SiteB worksheets, rank each respondent by the number of meals they eat out per month and the number of desserts they eat out per month, respectively— ranking from most meals and desserts out to the least. Freeze the panes of the window on each worksheet to make the category headings at the top visible at all times. 3. On a separate worksheet named Compare, calculate the difference and percent difference of the mean and standard deviation for the data sets (A and B) for the number of meals and number of desserts per month obtained. On the same worksheet, set up a table to list the four highest number of desserts per month from each data set (A and B) and the ten lowest number of meals eaten out by respondents for each of the data sets (A and B). Below the data, discuss how these values differ between the data sets, and recommend either A or B for further analysis, highlighted in pink.

9

WEEK # 3 - EXERCISES CHAPTER # 2

4. On a separate worksheet named Summary, determine the following for the location you think should be selected: • The total number of respondents

• The total number of respondents with incomes at or above $90,000 • The total number of respondents who eat fewer than three desserts out each month • The total number of meals eaten out per month reported by respondents who earn at least $50,000 • The total number of desserts per month reported by respondents who are in the 31 to 35 or over 35 age categories

5. Include appropriate titles, labels, and formatting so that the worksheets are easy to read. 6. Add your name and date at the end of the exercise 7. Save and close the 2-4-YourRestaurante-Demog-Analysis-Your-Name.xlxs workbook.