Special thanks to Santiago Bolivar
Result: 100 %

Quiz 5

Question 1

Q: You have a sales table that contains “Quantity,” “Unit Price,” and “Unit Cost.” How would you add the following calculations to be used in a PivotTable?
Q: Total Cost
A: Add a calculated column in the table (Total Cost = Unit Cost * Quantity).
Q: Total Revenue
A: Add a calculated column in the table (Total Revenue = Unit Price * Quantity).
Q: Total Profit
A: Add a Pivot Table calculated field (Total Profit = Revenue - Cost).
A: Add a calculated column in the table (Total Profit = Revenue - Cost).
Q: Profit Margin
A: Add a PivotTable calculated field: Profit Margin = (Revenue - Cost) / Revenue.

Question 2

Q: Conditional formatting (Green – Yellow – Red Color Scale) was applied on the above PivotTable. What can you do in order to improve usability of the formatting?
A: Modify the percentiles for maximum and minimum values.

Lab 5

Question 1

Q: Which Sub Category sold the most quantity?
A: A. Tires and Tubes

Question 2

Q: Which Sub Category has the most revenue?
A: B. Road Bikes

Question 3

Q: What is the total margin for Australia in the year 2016?
A: C. 33.74%

Question 4

Q: Using the same filters, which category has the lowest margin?
A: C. Clothing

Question 5

Q: Which Sub Category has the lowest margin?
A: A. Caps

Question 6

Q: Which product has the least margin?
A: D. Short-Sleeve Classic Jersey, M

Quiz 6

Question 1

Q: What is the main advantage of referencing cells in a Pivot Table with the GetPivotData() function rather than by regular cell reference?
A: It returns the same values even if the layout of the data changes.

Question 2

Q: What are the disadvantages of referencing cells in a Pivot Table with the GetPivotData() function and not by regular cell reference? Choose three that apply.
A: The function is not adjusted when it is dragged across cells.
A: It is very complicated to edit the function.
A: It is very complicated to write the function manually.

Question 3

Q: What are the advantages of creating aggregates using SUMIFS() rather than using a Pivot Table? Choose three that apply.
A: SUMIFS() formulas can be arranged in flexible formats in the grid.
A: SUMIFS() formulas' comparison operators are not restricted to equality.
A: SUMIFS() formulas do not require refreshes to reflect changes in the data.

Question 4

Q: What are the disadvantages of creating aggregates using SUMIFS() rather than using a Pivot Table? Choose three that apply.
A: A range with SUMIFS formulas is much more difficult to change if you need to include more or fewer fields.
A: Pivot Tables shrink and expand dynamically based on the data.
A: On a report based on SUMIFS() formulas, you are unable to implement simple drill downs and drill ups.

Lab 6

Question 1

Q: Without applying any filter, which year does the Accessories category have negative growth?
A: C. 2015

Question 2

Q: Filter the report for Youth Age Group. Which two years do the Accessories category have negative growth?
A: B. 2014
A: D. 2016

Question 3

Q: Without applying any filter, which two years do the Bikes category have negative growth?
A: B. 2014
A: D. 2016

Question 4

Q: Filter the report for Australia. Which year do the Bikes category have the highest growth?
A: C. 2015

Question 5

Q: Keep the Australia filter. In the year that Bikes sales have the highest growth (previous question), which Sub Category of Bikes has the highest growth?
A: A. Mountain Bikes

Quiz 7

Question 1

Q: How would you modify the following formula to include age ranges in the following report?
=SUMIFS(SalesTable[Revenue],SalesTable[Customer Gender],B$4,SalesTable[Country],$A5)
A: =SUMIFS(SalesTable[Revenue],SalesTable[Customer Gender],B$4,SalesTable[Country],$A5,SalesTable[Customer Age],">="&$B$1,SalesTable[Customer Age],"

Question 2

Q: What is the main purpose of using the Treemap and Sunburst charts to display data? Select the best answer.
A: To show the relative size at different levels of a hierarchy.

Question 3

Q: You want to create a SUMIFS() formula that references a specific cell in your spreadsheet as you apply it to a range of data containing multiple columns. What should you do?
A: Use a mix of absolute and relative reference in your formula.

Lab 7

Question 1

Q: Explore the sales composition of Bikes category for each Age Group. Which Age Group does the composition (rank of sales) differ than the rest?
A: D. Seniors

Question 2

Q: Now explore the sales composition of Bikes category for each Age Group, for the Male customers. Which Age Group does the composition differ than the rest?
A: E. The composition are the same across Age Group for Male customers.

Question 3

P: Clear all filters. Now, filter for the year 2016 and Germany. Rank the sales from the highest to lowest for the Clothing category.
A: Jerseys

Question 4

P: Keep the filter settings and add filter by Male customers. Rank the sales from the highest to lowest for the Clothing category.
A: Jerseys

Question 5

P: Keep the filter settings and add filter by Youth Age Group. Rank the sales from the highest to lowest for the Clothing category.
A: Jerseys

Quiz 8

Question

Q: You want combine data in two ranges/tables in a single pivot. What should you do? Choose two that apply.
A: Click ‘More Tables…’ from the pivot field list, and create relationships between the different ranges.
A: Combine the different ranges into one range using VLOOKUP formulas.

Lab 8

Question 1

Q: For those customers who bought bikes, what are the top three (bought the most quantity) customer profiles (marital status and number of children)?
A: A. Single without child
A: C. Married with one child
A: D. Married with two children

Question 2

P: For those customers who bought bikes, what are the top three (bought the most quantity) income brackets? (Rank from highest to Lowest)
A: 40000

Question 3

Q: For those customers who bought bikes, what are the top two (bought the most quantity) education levels?
A: B. Partial College
A: C. Bachelors

Question 4

Q: What is the percentage of the customers who bought Bikes and are house owners?
A: D. 68.72%