Please answer all the questions highlighted in yellow, with supporting evidences such as screenshots. Answering the questions requires creating PivotTables or charts and reasoning why you use some certain features of visual encoding properties. DELIVERABLE Electronic file: 646_yourname_assignment3.doc Electronic file: 646_yourname_assignment3.xlsx (your messy working excel file) Submit all files through Assignment link before next Monday. Late submission penalty will be applied. OBJECTIVE To understand dataset and analytics tasks using Microsoft Excel Pivot Tables and Pivot Chart. ACTIVITIES Create a pivot table Create crosstabs Filter, sort, rank Aggregate Create charts DATA REQUIRED • Assignment 3 Dataset.xlsx SCENARIO You are an accountant working for a company called Global Bike Inc. You have been assigned to assist the strategic planning team with profitability analysis in the wholesale division of the company. (The internet sales will be handled by a different team.) Your IT team has pulled transactional data from 2007 through 2011 for you to analyze. The data are stored in an Excel file called Excercise1.xlsx. Everyone on the team agrees that you are the best person to do the initial exploration of the data set. Although you are free to do your own analysis, at the very least, you need to answer the questions in the following section so that you can report back to the team.
USING EXCEL PIVOT TABLES
The most common tool used to slice, dice, roll up, and drill down data is a spreadsheet. Typically slicing and dicing is done by creating a pivot table which creates what is called a cross tabulated structure or crosstab. The crosstab displays aggregated and summarized data based on which way you sort the columns and rows. The pivot table can be easily transposed by swapping the axes; hence the name, pivot. We will now use Microsoft Excel pivot tables to answer some business questions related to your assigned profitability analysis. The step-by-step instructions will guide you through the use and manipulation of pivot tables. 1. Open the data file Assignment 3 Dataset.xlsx in Microsoft Excel 2. You should see the spreadsheet in Figure 1
#ASSIGNMENT #EXCEL #PIVOT #TABLES
