Result: 100 %

Quiz 1

Question 1

Q: You are asked to display a trend for each product by year. What should you do?
A: Modify the chart to have the product categories as Legend and years as horizontal axis.

Question 2

Q: You have collected the following data and are considering using a pie chart to display it.
A: The pie chart will only show one of the year’s data.

Question 3

Q: You have collected the following data and want to use a chart to display it.
A: Line chart

Question 4

Q: You are asked to display the percentage of sales by category for each year in a column chart. Which of the following chart types should you use?
A: C

Lab 1

Question 1

Q: What does the Yearly total sales look like?
A: Similar to the yearly bikes revenue

Question 2

Q: What percentage of the total revenue comes from the Bikes category?
A: 72%

Question 3

Q: Rank the Countries from the highest to lowest revenue. (Highest in Zone 1, Lowest in Zone 6)
A: Zone 1 : United States
A: Zone 2 : Australia
A: Zone 3 : United Kingdom
A: Zone 4 : Germany
A: Zone 5 : France
A: Zone 6 : Canada

Quiz 2

Question 1

Q: What is the difference between a table and a range in Excel? Select four that apply.
A: In tables, you can add many types of totals for each column without writing any formulas.
A: Tables are formatted with alternate colors by default.
A: Formulas in tables refer to other columns by name and not by regular Excel reference.
A: Formulas in table columns are automatically applied to new rows.

Question 2

Q: If you filter the table to show only sales in United Kingdom, what will be calculated in the Total row?
A: The totals will recalculate according to the filtered rows.

Question 3

Q: You are only interested in the data for male customers in France. After setting the appropriate filters, answer the following questions.
A: How many rows are displayed? : 2573
Q: What is the total revenue for the displayed rows? Only provide whole number. Do not provide symbols or any decimals places.
A: 1770366
Q: What is the average revenue? Only provide numbers, no currency symbols, and report to two decimal points.
A: 688.06

Lab 2

Question 1

Q: What is the total revenue for all the sales in the United States?
A: 27975547

Question 2

Q: What is the total revenue for all the sales in the month of December?
A: 9086931

Question 3

Q: What is the total revenue for all the sales for Young Adults Age Group?
A: 30655614

Question 4

Q: What is the total revenue for all the bikes with frame size 62 for the customer age group Seniors?
A: 12452

Question 5

Q: What is the total profit for United States sales in the month of October 2015, for customer age group Adults?
A: 138419

Quiz 3

Question 1

Q: You have the following Pivot Table fields. You need to calculate the number of sales by age. Drag the appropriate field names and aggregated field names to the appropriate Pivot Table Areas (Filters, Rows, Columns, or Values) to perform the task
A: Filters: ×
A: Columns: ×
A: Rows: Customer Age
A: Values: Count of Customer Age

Question 2

Q: You have the following Pivot Table fields. Your manager asks you to build a pivot table that shows revenue by state and country. How would you arrange the field names and aggregated field names to the appropriate Pivot Table Areas (Filters, Rows, Columns, or Values)?
A: Filters: ×
A: Columns: ×
A: Rows: Country, State
A: Values: Sum od Revenue

Question 3

Q: You have a pivot that is based on an Excel table. You need to add new rows to the Excel table and want to be able to see the values for these new rows in the PivotTable. What should you do?
A: Add rows anywhere in the table, and refresh the pivot table.

Question 4

Q: Using the data provided, create a pivot with “Country “as rows, “Month” as columns, and “Revenue” as values. Drag “Years” to the filter area. What does the value 286,779 in the first summarized field of the pivot table (uppermost left value) represent?
A: The aggregated value for January in France in 2015 and 2016.

Question 5

Q: a. How much revenue was raised through the purchase of bikes by women? Provide a whole number without currency symbols or decimal places.
A: 5728781
Q: b. How much revenue was raised through clothing purchased by men? Provide a whole number without currency symbols or decimal places.
A: 1817262

Question 6

Q: Drill down into the data for the cell that represents the revenue for bike sales in Germany, 2015. A new worksheet is created with a table in it. What is the content of this table?
A: The table contains all rows in SalesTable corresponding to 2015, Bikes and Germany

Question 7

Q: a. Total revenue for sales of bikes in the country that sold the most in 2016
A: 2257187
Q: b. Number of sales transactions for bikes in the US in 2016
A: 1415
Q: c. Average number of clothing items purchased in the US during 2016 rounded to the nearest whole number
A: 2
Q: d. The largest revenue registered in a single transaction in the Germany during 2015
A: 3089
Q: e. The largest revenue registered in a single transaction during 2015
A: 3224

Lab 3A

Question 1

Q: Which year did the company start selling Touring Bikes?
A: 2013

Question 2

Q: Rank the States for Germany, from the highest to lowest revenue.
A: Zone 1: Hessen
A: Zone 2: Saarland
A: Zone 3: Nordrhein-Westfalen
A: Zone 4: Hamburg
A: Zone 5: Bayern
A: Zone 6: Brandenburg

Question 3

Q: What about for the year 2013?
A: Zone 1: Saarland
A: Zone 2: Hessen
A: Zone 3: Nordrhein-Westfalen
A: Zone 4: Hamburg
A: Zone 5: Bayern
A: Zone 6: Brandenburg

Question 4

Q: Which frame size sold the most?
A: 42

Question 5

Q: Which Age Group has the lowest revenue?
A: Seniors

Lab 3B

Question 1

Q: Which country’s trend is the most different when compared to the other countries?
A: B. Australia

Question 2

Q: Which year does the Bikes category have the least sales?
A: D. 2014

Question 3

Q: What is the proportion of sales for Young Adults?
A: C. 35.95%

Question 4

Q: Which frame size sold the least?
A: B. 54

Quiz 4

Question 1

Q: Using the data provided, create a pivot with “Country” as rows, “Product Category” as columns, and “Revenue” as values. Slice months by “April.” What does the grand total for revenue (2,200,490) represent?
A: Total combined revenue for April in 2015 and 2016

Question 2

Q: Which objects can be sliced by slicers? Choose 3 that apply.
A: Pivot Charts
A: Pivot Tables
A: Tables

Question 3

Q: When would you use both a slicer and a filter based on the same field?
A: When you want to search items and apply the results on multiple PivotTables

Question 4

Q: Why are Bike Racks, Fenders and Gloves at the bottom of the Sub Category slicer in a lighter color?
A: During 2015, there were no sales for these sub categories.

Question 5

Q: What will happen if you click "Bike Racks"?
A: All Pivot Tables connected to both year and sub category slicers will be blank.

Lab 4A

Question 1

Q: A quick glance on the Yearly Sales by Country shows that Australia has an unusual trend compared to the other countries. Which year does Australia have the least sales?
A: A. 2011

Question 2

Q: What is the percentage of Australia sales (of total sales) in the year that it has the least sales (previous question)?
A: D. 28%

Question 3

Q: Let's filter the charts by Australia using the Country slicer. What might be the cause of this trend?
A: B. The sharp fluctuations of bicycle sales.

Question 4

Q: For all the years that has all the three categories, which year does the Bikes has the least proportion?
A: B. 2014

Question 5

Q: What is the percentage of Bikes sales (of total sales) in that year?
A: B. 59%

Lab 4B

Question 1

Q: Which Statement best describes the proportion of sales by customer gender?
A: C: Sales are pretty much evenly split between male and female customers.

Question 1 (old)

Q: Which Bike's frame size is more popular for each Gender? Place the Frame size into the appropriate box.
A: Zone 1: 42
A: Zone 2: 38

Question 2

Q: In Australia, which Age Group has more sales revenue to females than to males? (Select two that apply)
A: Young Adults (25-34)
A: Adults (35-64)