ENTERPRISE ACCOUNTING SYSTEMS ASSIGNMENT
Adventure Works Cycles is a large multinational manufacturing company. The company manufactures and sells metal and composite bicycles to North American, European and Asian commercial markets. While its base operation is in Bothell, Washington with 290 employees, several regional sales teams are located throughout their market base.
Adventure Works Cycles management team has asked you to help create a dashboard for them to provide better insights into their sales information and allow them to drill into the results real time versus requesting reports.
The objective of this assignment is to use your Excel skills to develop a dashboard with various Adventure Works data.
You will need to utilize Microsoft Power Pivot within Excel 2016 to complete this assignment. This assignment will need to be completed on a Windows version of Excel 2016.
You will submit your completed dashboard to Blackboard. Please save this file as your netid and Excel Dashboard SPR 2018 e.g. mkupch1.Excel Dashboard 2018.xlsx
I. Data Loading
1. Download off Blackboard and Import the following CSV files into a Power Pivot data model:
a. FactInternetSales
b. DimCustomer
c. DimGeography
d. DimProduct
e. DimProductSubCategory
f. DimSalesTerritory
2. Create a Date Table that covers the minimum and maximum dates within the Order Date field in the FactInternetSales Table
II. Relationship Creation
Create the following relationships between tables:
1. DimCustomer(GeographyKey) Many to One DimGeography (GeographyKey)
2. DimProduct (ProductSubCategoryKey) Many to One DimProductSubcategory (ProductSubcategoryKey)
3. FactInternetSales (CustomerKey) Many to One DimCustomer (CustomerKey)
4. FactInternetSales (ProductKey) Many to One DimProduct (ProductKey)
5. FactInternetSales (SalesTerritoryKey) Many to One DimSalesTerritory (SalesTerritoryKey)
6. FactInternetSales (Order Date) Many to One Calendar(Date)
III. Measurements
Create the following Measures from the FactInternetSales and DimCustomer tables:
Table: FactInternetSales
1. Total Sales Amount (TotalSalesAmt)
• Format: Currency (0 Decimal)
2. Total Product Count (TotalProductCount)
• Format: Whole Number
3. Year to Date Total Sales Amount (YTDSalesAmt)
• Format: Currency (0 Decimal)
4. Year over Year Growth Sales Amount (Current Year Sales - Prior Year Sales) (YOYSalesGrowth)
• Format: Currency (0 Decimal)
5. Year over Year Percentage Sales Growth
• Format: Number (Percentage) 6.Prior Year Sales Amount (PYSalesAmt)
• Format: Currency (0 Decimal) 7.Order Count
• Format: Whole Number (OrderCount) Table: DimCustomer
8. Total Customer Count (TotalCustomerCount)
• Format: Whole Number
Note: You can reference the DAX Function Reference to identify the appropriate DAX functions
IV. Calculated Columns
Create the following calculated columns in the DimCustomer table:
1. Column Name: Age
Description: Calculate the age of the customer based on the difference between their BirthDate and April 30, 2018
2. Column Name: Age_Group
Description: Place each customer based on their Age as calculated above, into the following age groups:
• 30-40
• 41-50
• 51-60
• 61-70
• 71-80
• 81-90
• 91 and Over
Create the following calculated column in the Calendar table:
3. Column Name: Quarter
Description: Create a column that calculates the fiscal quarter that the Date column belongs to as follows:
• 01/01/ - 03/31 - Q1
• 04/01/ - 06/30 - Q2
• 07/01 - 09/30 - Q3
• 10/01 - 12/31 - Q4
V. Graphs
Create the following Graphs:
1. Clustered Bar Graph that shows the Total Sales Amount by SalesTerritoryRegion
• Graph Name: Territory Sales
• Format Axis Number: Category Accounting (0 Decimal)
• Chart Title: Total Sales by Territory
• Size Chart: Height 3" x Width 6"
• Location: C6-H18
2. Pie Chart that shows the Total Sales Amount by Gender
• Graph Name: Total Sales by Gender
• Chart Style: 8
• Chart Title: Total Sales by Gender
• Size Chart: Height 3" x Width 3"
• Location: C20-E33
3. Custom Combination that shows Total Sales by AgeGroup and Count of the number of Customers in that AgeGroup
• Graph Name: Sales by Age
• Clustered Column: Sales Amount
• Line with Markers: Customer Count
• Chart Title: Total Sales Amount and Number of Customers by Age Group
• Format Axis Number: Category Accounting (0 Decimal)
• Size Chart: Height 3" X Width 5.75"
• Location: O6-S18
4. Donut chart showing number of customers by YearlyIncome
• Graph Name: Yearly Income
• Chart Style: 5
• Chart Title: Total Customers by Yearly Income
• Size Chart: Height 3" x 3.5" Width
• Location: F20-H33
VI. Pivot Tables
Note: Make sure to uncheck "autofit column widths on update" for all Pivot Tables
1. Create a pivot table in cell P21 that shows the following:
1. TotalSalesAmt (measure) and Total Order Count by EnglishProductSubcategory then by EnglishProductName
2. PivotTable Name: Sales by Product
2. Create a pivot table in cell I7 that shows the following:
1. Total Sales by Year, Quarter and Month
2. YTD Total Sales by Year, Quarter and Month
3. Prior Year Sales by Amount by Year, Quarter and Month
4. Year over Year Sales Growth by Sales Amount by Year, Quarter and Month
5. Year over Year Percentage Growth by Year, Quarter and Month
6. PivotTable Name: Sales Summary
3. Create a pivot table in cell D3 that shows the following:
1. Total Sales Measure
2. PivotTable Name: Total Sales
4. Create a pivot table in cell F3 that shows the following:
1. Total Customer Count Measure
2. PivotTable Name: Total Customers
5. Create a pivot table in cell H3 that shows the following:
1. Total Product Count Measure
2. PivotTable Name: Total Products
6. Create a pivot table in cell J3 that shows the following:
1. Total Order Count Measure
2. PivotTable Name: Total Orders
VII. Conditional Formatting
1. Add a blue data bar to the Total Sales Amount column created in step 2 above
2. Add a red data bar to the YTD Total Sales column created in step 2 above
VIII. Slicers
Note: All slicers should be connected to every Pivot Table and Pivot Charts via Report Connections
1. Create a slicer for SalesTerritoryCountry
a. Change the number of columns from 1 to 2
2. Create a slicer for Age Group
a. Change the number of columns from 1 to 3
3. Create a slicer for EnglishProductSubcategory
a. Change the number of columns from 1 to 2
4. Create a Timeline Filter by Order Date
5. Create a slicer by YearlyIncome
a. Change the number of columns from 1 to 3
IX. Questions
Use the dashboard that you just created to answer the following questions below?
1. What were the total sales to Australian customers who were 30-40 years old from 2011- 2014? Which product sold the best and in which quarter and year did we sell the most?
2. In Q3 2014 which product did we sell the most of? Which age group purchased the most? Which country purchased the least and how much?
3. What were the total sales for Road Bikes and Mountain Bikes in Q3 and Q4 2012? What were the total sales of Road Bikes to customers with an Yearly Income of $20,000?
X. Presentation
1. Add a title in cells A1 through C4 which includes a logo of UIC and Accounting 494 on one line and your name dashboard on the bottom
2. Make sure that all your slicers line up, graphs and pivot tables line up nicely
3. Numbers should be formatted appropriately i.e. numbers should be formatted as numbers with commas with no decimals, numbers that represent currency should be formatted as Accounting with no decimals.