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 (This inclass practice maps to Part 2 & 3 of Assignment #3) 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.
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

### What students are saying

• As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

Kiran Temple University Fox School of Business ‘17, Course Hero Intern

• I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

Dana University of Pennsylvania ‘17, Course Hero Intern

• The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

Jill Tulane University ‘16, Course Hero Intern