A "Quick Guide to Pivot Tables" is found at the end of this document. Review it BEFORE beginning the questions.
The data for this exercise (structured as a flat file) can be found in an Excel workbook located on Blackboard with this name: Excel Pivot Table Assignment Data. This is NOT the same spreadsheet we used to practice Pivot Tables in class.
These data are actual United States Bureau of Labor Statistics data from the Consumer Expenditure Survey that reports expenditures by households. The full data set can be found at this location
https://www.bls.gov/cex/
You do NOT need to access the site for this assignment, but you may find it useful for other purposes. The data we are providing are aggregate (that is, total) consumer expenditure data of US households, expressed in millions of dollars, organized by the following dimensions:
Expenditure Category Expenditure Subcategory Y ear?Age Bracket?Geographic Region
Each subcategory belongs to one category. In a few cases, a category may have only one subcategory.
Note that "Year" refers to a two-year time period. There are two time periods in the data: 2011-2012 and 2013-2014.
So, for example, one row in the spreadsheet might contain the total 2011-2012 expenditures on cereals and bakery products (a subcategory of food) by households in the under 25 age bracket living in the Midwest.
Your task is to use these data and a pivot table in Excel to answer the questions on the next page. Some of these questions are specific to your IP company and others are not. Hint: Pay careful attention to which years are involved in each question.
Begin by creating a pivot table from the data list in the workbook. Then, for each question, manipulate the pivot table to find the answer. When you have found the answer, make a copy of the sheet with your answer in your workbook. Rename the sheet Question X, where "X" is replaced by the number of the question. So when you are finished, your workbook will contain a sheet for each of the eight questions.
Important: Format your results as dollars without decimal places to make them more readable.
1. Show the total dollar expenditures from 2011-2014 (that is, all years in the dataset) by age bracket and by region. Use one row per age bracket and one column per region.
2. Show the total dollar expenditures for 2011-2012 by age bracket and region. Use one row per age bracket and one column per region.
3. Which age bracket accounted for the MOST expenditures in 2013-2014? ?Which region accounted for the MOST expenditures in 2013-2014?? Highlight the age bracket in BLUE and the region in RED on your sheet. Highlight the labels, not the values.
4. Which combination of age bracket and region accounted for the MOST expenditures in 2011-2012? Highlight the age bracket, region, and value in yellow on your sheet.
5. Consider only the categories of expenditures (not the subcategories). For 2013-2014, which category accounts for the MOST expenditures by Americans and which category accounts for the LEAST expenditures? Highlight the category and value for the MOST in BLUE and for the LEAST in RED.
6. What is happening over time in terms of purchases by the different age groups? Put your answer in a text box on the sheet. You do not need to write in sentences-you can just put 5 bullet points in the text box.
7. Now consider the age bracket of greatest importance for you Consulting Challenge (CC) company. For that age bracket, which region had the HIGHEST and which region had the LOWEST total expenditures for 2013-2014? Highlight the age bracket in yellow. Highlight the region and value with the HIGHEST in BLUE and with the LOWEST in RED
8. Now consider the category or subcategory in the table below that is likely to have great significance for your CC company. Examine the total expenditures for all years by age bracket and by region. What do you observe in these data? (Put your answer of no more than 3 sentences in a text box on your sheet.)
|
 Apple
|
Other entertainment supplies, equipment, and services (Subcategory)
|
|
Facebook
|
Other entertainment supplies, equipment, and services (Subcategory)
|
|
 Chipotle
|
Food Away from Home (Subcategory)
|
|
Whole Foods
|
Other food at home (Subcategory)
|
|
Johnson & Johnson
|
Medical Supplies (Subcategory)
|
|
Tesla
|
Vehicle purchases (Subcategory)
|
Quick Guide to Pivot Tables
Block out the data for your pivot table including the row of column labels. Using the "Insert" tab of the ribbon, click on "Pivot Table," which is found all the way to the left in the "Table" group. Assuming you blocked out the range correctly, the table range shown in the dialog box should be correct (or you could modify it). Put the pivot table in a "New Worksheet."
There are many ways to manipulate a pivot table. When you are new at it, using the lower region of the PivotTable Fields pane on the right of the screen will likely be EASIER than direct manipulation (dragging and dropping in the table itself). If the PivotTable Fields pane disappears, click anywhere in the pivot table to get it back. You can also get it back by clicking on "Field List" in the ribbon. The PivotTable Fields pane has areas for Filters, Columns, Rows, and Values (all explained below). You can drag and drop items (i.e., dimensions such as region or product) to add them to areas, remove them from areas, or move them from one area to another.
Filters-this is the "control" for the entire sheet and will appear at the top of the sheet (it is sometimes called a "report filter" or "page field"). So, for instance, if you just want one year, you would put "Year" there and then select a given year.
Columns-these are the items (dimensions) that will be in columns. If you put more than one item in columns they will be nested (and this can sometimes get messy).
Rows-these are the items (dimensions) that will be in rows. If you put more than one item in rows they will be nested. This tends to be a little less messy than nesting them in columns. Nesting dimensions in rows (or in columns) is a way to "drill-down" into your data.
Values-these are the items (variables) that are contained in the resulting table-e.g., sales in dollars. Moving items (dimensions) between the Filters, Columns, and Rows is essentially slicing and dicing your data.
Important Note: If your version of Excel uses the new pivot table display and you would prefer the "classic" display, you can right click on the pivot table area, select "Pivot Table Options," and on the "Display" tab, check the "Classic Pivot Table Layout" box.
Attachment:- Excel_Pivot_Table_Assignment_Data_insy_2299_fall_2016.xlsx