What were the total sales to australian customers

Assignment Help Computer Engineering

Reference no: EM131965347

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.

Reference no: EM131965347

Questions Cloud

Calculate the net present value of the loan : Calculate the net present value of the loan excluding flotation costs. (Do not round intermediate calculations and round your answer to 2 decimal places.

Discuss the grading system for hypertension : Discuss the grading system for hypertension, Outlining the modifiable and non-modifiable features of the disease and their primary management.

How much will the condo be worth : Dan and Jia are newlyweds and have just purchased a condominium for $70,000. Since the condo is very small, they hope to move into a single-family house.

Conflict using thomas-kilmann tool : Describe a situation when you managed a conflict using Thomas-Kilmann Tool and Win-wIn tool (Win-Win) - actively seeking the solution that satisfies the needs o

What were the total sales to australian customers : 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 did we sell most?

What are the metrics to measure of starbuck : What are the metrics to measure of Starbuck, the process and how it is performing.

Calculate the price of each bond at the end of every year : Cynthia Phelps is a recent retiree who is interesed in investing some of her savings into corporate bonds. Her financial planner, Jamie Sheridan, has suggested.

Create summary report on an information systems : In this assignment, create summary report on an Information Systems technology current event that has impacted a business decision

Present an evidence-based education plan for both : Present an evidence-based education plan for both Katrina and Trevor. The Patient Education Plan should include their cardiac medications.

User Account

All Pages