Recitation_7_Excel

# Recitation_7_Excel - Excel Modeling Practice Michigan State...

This preview shows pages 1–3. Sign up to view the full content.

Excel Modeling Practice Michigan State University ITM 309 Spring 2011 Excel: Decision Support Systems Managing Data with Excel Recitation #6 and recitation #7: Managing Data with Excel Objective: Be familiar with the concept of using Pivot Tables in Excel to support decision-making Pivot Tables (relates to Part 2 of Assignment #3) Scenario : College pals Harry Winter and Tyler Snow are working on opening a third coffee drive-through stand in East Lansing, MI, called Brewed Awakening . Their first two drive-through stands, Jitters and Bean Scene , have done well in their current locations in Okemos, MI, five miles away. Since Harry and Tyler want to start with low overheads, they need assistance analyzing the data from the past year on the different types of coffee and amounts that they sold from both stands. Harry and Tyler would like a recommendation of the four top sellers to start offering when Brewed Awakening opens. They have provided you with the data file in the CoffeeData tab for you to perform the analysis that will support your recommendation. Instructions: a) To make a pivot table in Excel, highlight the categories you want to compare including the name of the category at the top of each column. b) Click on Data on the Excel toolbar and select Pivot Table and Pivot Chart Report . The PivotTable and Pivot Chart Wizard—Step 1 of 3 dialog box opens, as shown below. c) Choose “Microsoft Excel list or database” and “PivotTable”. Click Next. d) The PivotTable and PivotChart Wizard—Step 2 of 3 dialog box opens. In the Range box, the range should be \$A\$1:\$D\$145 , which defines the data

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
Excel Modeling Practice Michigan State University range to use for the PivotTable. The range must include the column headings in row 1, which will be the names of the fields used for the PivotTable. e) Click Next. This dialog box is used to tell Excel whether to place the PivotTable on an existing or new worksheet. Select New Worksheet . f) The next step is to design the layout of the PivotTable. Click the Layout button. Excel opens the PivotTable and PivotChart Wizard–Layout dialog box, as shown below. g) The fields appear on buttons to the right in the dialog box. The four areas you can define are ROW, COLUMN, DATA, and PAGE. h) You will have to drag the field buttons to the areas to define the layout of the PivotTable. For example, to summarize the values in a field in the body of the table, place the field button in the DATA area. To arrange items in a field in columns with the labels across the top, place the field button in the COLUMN area. To arrange items in a field of rows with labels along the side, place the field button in the ROW area. To show data for one item at a time, one item per page, place the field button in the PAGE area. i)
This is the end of the preview. Sign up to access the rest of the document.

## This note was uploaded on 11/11/2011 for the course ITM 309 taught by Professor Rodammer during the Fall '08 term at Michigan State University.

### Page1 / 9

Recitation_7_Excel - Excel Modeling Practice Michigan State...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online