Book: Succeeding in Business with Microsoft Excel 2013

Chapter 2 page 101

Steps To Success: Level 1

A separate processing line assembles the binding mechanisms for the TZBlazer 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 tempera-tures 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 60C. To avoid any damage to the ski, failure is determined at the point at which the exibility of the materials falls below a specied level.

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

Table 2.6: Historical data for bindings

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 Binding1.xlsx located in the Chapter 2 folder, and then save the file as QC Binding Data Analysis 1.xlsx .

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.

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 pres-sure 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. Save and close the QC Binding Data Analysis 1.xlsx workbook.