Problems

1. Contoso, Ltd. produces microchips. Five types of defects (labeled 1–5) have been known to occur. Chips are manufactured by two operators (A and B) using four machines (1–4). You are given data about a sample of defective chips, including the type of defect, the operator, machine number, and day of the week the defect occurred. Use this data to chart a course of action that would lead, as quickly as possible, to improved product quality. You should use the PivotTable Wizard to “stratify” the defects with respect to type of defect, day of the week, machine used, and operator working. You might even want to break down the data by machine, operator, and so on. Assume that each operator and machine made an equal number of products. You’ll find this data in the file Contoso.xlsx.

2. You own a fast food restaurant and have done some market research in an attempt to better understand your customers. For a random sample of customers, you are given the income, gender, and number of days per week that residents go out for fast food. Use this information to determine how gender and income influence the frequency with which a person goes out to eat fast food. The data is in the file Macdonalds.xlsx.

3. Students at the School of Fine Art apply to study either English or Science. You have been assigned to determine whether the School of Fine Art discriminates against women in admitting students to the school of their choice. You are given the following data on the School of Fine Art’s students:

   • Female or male

   • Major applied for: English (Eng) or Science (Sci)

   • Admit? Yes or No

   Assuming that women are as equally qualified for each major as the men, does this data indicate that the college discriminated against women? Ensure you use all available information! The data is in the file Finearts.xlsx.

4. You have been assigned to evaluate the quality of care given to heart attack patients at Emergency Room (ER) and Chicago Hope (CH). For the last month you are given the following patient data:

   • Hospital (ER or CH).

   • Risk category (high or low). High-risk people are less likely to survive than low-risk people.

   • Patient outcome (live or die).

   Use this data to determine which hospital is doing a better job of caring for heart attack patients. Hint: Use all the data! The data is in the file Hospital.xlsx.

5. You are given the monthly level of the Dow Jones Index for the years 1947–1992. Does this data indicate any unusual seasonal patterns in stock returns? Hint: You can extract the month (January, February, and so on) by using the formula TEXT(A4,"mmm") copied to any column. The data is in the file Dow.xlsx.

6. The file Makeupdb.xlsx contains information about the sales of makeup products. For each transaction, you are given the following information:

   • Name of salesperson

   • Date of sale

   • Product sold

   • Units sold

   • Transaction revenue

   Create a PivotTable to compile the following information:

   • The number of sales transactions for each salesperson.

   • For each salesperson, the total revenue by product.

   • Using your answer to the previous question, create a function that always yields Jen’s lipstick sales.

   • Total revenue generated by each salesperson broken down by location.

   • Total revenue by salesperson and year. (Hint: You will need to group the data by year.)

7. For the years 1985–1992, you are given monthly interest rates on bonds that pay money one year after the day they’re bought. It’s often suggested that interest rates are more volatile-tend to change more-when interest rates are high. Does the data in the file Intratevol-volatility.xlsx support this statement? Hint: PivotTables can display standard deviations.

8. For our grocery example, prepare a chart that summarizes the trend over time of the sales at each store.

9. For our grocery example, create a calculate field that computes an average per unit price received for each product.

10. For our grocery example, create a PivotChart that summarizes the sales of each product at each store for the years 2005 and 2006.

11. For the data in the file Calcitemdata.xlsx, create calculated fields that summarize sales of dessert (cakes+puddings) and fruits (apples+grapes).

12. In the chip PivotTable example, create a PivotTable that summarizes monthly sales of Chips 1 and 3 in France and the U.S.

13. In the customer PivotTable example, show the top 15 customers in one table and the bottom 5 customers in another table.

14. The file Ptablepartsdata.xlsx contains sales of various parts. Each part code begins with either Part (for computer part) or Comp (for computer). Create a PivotTable that only shows sales of Parts. (Hint: Use a Labels Filter.)

15. For the data in Problem 14, summarize the total sales of parts and computers.