All Chapters Reverse
CHAPTER 16: ANALYZING DATASETS WITH TABLES AND PIVOT TABLES
Instructor’s Manual Problem Set
Solutions can be found in the accompanying Excel files. Note that if you wish to see all of the formulas at
once, you may use the CTRL+` (Control plus grave accent) shortcut key to toggle them on or off.
1. Using the data in the Alphabet Financials.xlsx workbook that was used in the chapter:
a) Convert the balance sheet to a Table that is named Raw_BalanceSheet.
b) Use Get & Transform to convert the balance sheet into a format that is suitable for a pivot table by
transposing the data. Be sure to set the correct data types for each column, and replace errors with a
value of 0. Don’t forget to enter the word “Date” above the dates. Name the query
Transposed_BalanceSheet.
c) Build a pivot table from the data and group the dates by quarters and years.
d) Create a calculated field that shows the total debt ratio, and format the result as a percentage with two
decimal places.
e) Create a pivot chart that shows the debt ratio over time. Has Alphabet’s capital structure changed over
this 6-year period?
2. Using the same workbook:
a) Transpose the income statement using Get & Transform as you did with the balance sheet in problem
1. Name this query Transposed_IncomeStatement.
b) Use Get & Transform -> New Query -> Combine Queries -> Merge to merge the
Transposed_IncomeStatement and Transposed_BalanceSheet queries. Do a Full Outer Join to use all
rows from both queries. Call this query Combined_Financials and load it to a new worksheet.
c) Create a new Pivot Table from the Combined_Financials table. Add a Date field that shows years and
quarters.
d) Create two calculated fields that show the net profit margin (NPM) and return on total assets (ROA).
e) Create a pivot line chart that shows the data from part (d). Place the ROA on a secondary y-axis.
f) Does there appear to be a strong correlation between the two data series? Use the Correl function to
calculate the correlation coefficient.
3. Using the ETF Data.xlsx workbook from the chapter:
a) Use Get & Transform on the data to create a query that shows average Beta by ETF Struct. To do this,
use the Group By button to group the data by ETF Struct and then create a new column called Avg
Beta. Also create a second aggregation that shows the count of each type of fund. Name the query
Avg_Beta_by_Structure and load it to a new worksheet.
102
,103 Chapter 16: Analyzing Datasets with tables and pivot tables
Instructor’s Manual Problem Set
b) What is the average beta for each type of fund, and how many of each are in the dataset?
c) Create another query, called Average Returns by Category, that shows the number of funds in each
category and the average of the 1 year, 3 year, and 5 year returns by category.
d) Return to the data and create a pivot table that shows the same results as you got in part (a).
e) Return to the data and create a pivot table that shows the same results as you got in part (c).
f) Add a Timeline and use it to filter the pivot table so that it shows the operating profit margin from
2000 to the present.
,104
Chapter 16: Analyzing Datasets with tables and pivot tables
Instructor’s Manual Problem Set
4. Go to
https://www.microsoft.com/en-us/Investor/earnings/trended/yearly-income-statements.aspx
and download Microsoft’s annual income statements from 1995 to the most recent year
available. There is a direct link to the Excel (Trended Historical Financials) file on that page.
a) Reformat the worksheet so that it is suitable for a pivot table. This will require changing the year
headings to actual dates, deleting any blank rows, and then transposing the data.
b) Create a pivot table that shows Operating Profit Margin for each year. This will require you to create
a calculated field.
c) Add a pivot chart that displays the results from part (b) over time. Do you notice a trend in
Microsoft’s profitability?
, 105 Chapter 16: Analyzing Datasets with tables and pivot tables
Instructor’s Manual Problem Set
Chapter 16: Multiple Choice Questions
1. _________: Any field in this area will appear above the pivot table and will be used to
filter the report.
a) Report Filter
b) Column Labels
c) Row Labels
d) Values
e) None of the above
Answer: a
2. The dropdown buttons change to indicate when a filter or sort has been applied to the
field. Which of the following icons represents "No Filter or Sort"?
a)
f)
g)
h)
i)
Answer: a
3. The dropdown buttons change to indicate when a filter or sort has been applied to the
field. Which of the following icons represents "Filter Applied"?
CHAPTER 16: ANALYZING DATASETS WITH TABLES AND PIVOT TABLES
Instructor’s Manual Problem Set
Solutions can be found in the accompanying Excel files. Note that if you wish to see all of the formulas at
once, you may use the CTRL+` (Control plus grave accent) shortcut key to toggle them on or off.
1. Using the data in the Alphabet Financials.xlsx workbook that was used in the chapter:
a) Convert the balance sheet to a Table that is named Raw_BalanceSheet.
b) Use Get & Transform to convert the balance sheet into a format that is suitable for a pivot table by
transposing the data. Be sure to set the correct data types for each column, and replace errors with a
value of 0. Don’t forget to enter the word “Date” above the dates. Name the query
Transposed_BalanceSheet.
c) Build a pivot table from the data and group the dates by quarters and years.
d) Create a calculated field that shows the total debt ratio, and format the result as a percentage with two
decimal places.
e) Create a pivot chart that shows the debt ratio over time. Has Alphabet’s capital structure changed over
this 6-year period?
2. Using the same workbook:
a) Transpose the income statement using Get & Transform as you did with the balance sheet in problem
1. Name this query Transposed_IncomeStatement.
b) Use Get & Transform -> New Query -> Combine Queries -> Merge to merge the
Transposed_IncomeStatement and Transposed_BalanceSheet queries. Do a Full Outer Join to use all
rows from both queries. Call this query Combined_Financials and load it to a new worksheet.
c) Create a new Pivot Table from the Combined_Financials table. Add a Date field that shows years and
quarters.
d) Create two calculated fields that show the net profit margin (NPM) and return on total assets (ROA).
e) Create a pivot line chart that shows the data from part (d). Place the ROA on a secondary y-axis.
f) Does there appear to be a strong correlation between the two data series? Use the Correl function to
calculate the correlation coefficient.
3. Using the ETF Data.xlsx workbook from the chapter:
a) Use Get & Transform on the data to create a query that shows average Beta by ETF Struct. To do this,
use the Group By button to group the data by ETF Struct and then create a new column called Avg
Beta. Also create a second aggregation that shows the count of each type of fund. Name the query
Avg_Beta_by_Structure and load it to a new worksheet.
102
,103 Chapter 16: Analyzing Datasets with tables and pivot tables
Instructor’s Manual Problem Set
b) What is the average beta for each type of fund, and how many of each are in the dataset?
c) Create another query, called Average Returns by Category, that shows the number of funds in each
category and the average of the 1 year, 3 year, and 5 year returns by category.
d) Return to the data and create a pivot table that shows the same results as you got in part (a).
e) Return to the data and create a pivot table that shows the same results as you got in part (c).
f) Add a Timeline and use it to filter the pivot table so that it shows the operating profit margin from
2000 to the present.
,104
Chapter 16: Analyzing Datasets with tables and pivot tables
Instructor’s Manual Problem Set
4. Go to
https://www.microsoft.com/en-us/Investor/earnings/trended/yearly-income-statements.aspx
and download Microsoft’s annual income statements from 1995 to the most recent year
available. There is a direct link to the Excel (Trended Historical Financials) file on that page.
a) Reformat the worksheet so that it is suitable for a pivot table. This will require changing the year
headings to actual dates, deleting any blank rows, and then transposing the data.
b) Create a pivot table that shows Operating Profit Margin for each year. This will require you to create
a calculated field.
c) Add a pivot chart that displays the results from part (b) over time. Do you notice a trend in
Microsoft’s profitability?
, 105 Chapter 16: Analyzing Datasets with tables and pivot tables
Instructor’s Manual Problem Set
Chapter 16: Multiple Choice Questions
1. _________: Any field in this area will appear above the pivot table and will be used to
filter the report.
a) Report Filter
b) Column Labels
c) Row Labels
d) Values
e) None of the above
Answer: a
2. The dropdown buttons change to indicate when a filter or sort has been applied to the
field. Which of the following icons represents "No Filter or Sort"?
a)
f)
g)
h)
i)
Answer: a
3. The dropdown buttons change to indicate when a filter or sort has been applied to the
field. Which of the following icons represents "Filter Applied"?
