PROBLEMS

Using the data in the Alphabet Financials.xls workbook that was used in the chapter:

1. Convert the balance sheet into a format that is suitable for a pivot table by transposing the data using Get & Transform. Don't forget to enter the word "Date" above the dates.
2. Build a pivot table from the data showing Total Assets by Quarter. Add a pivot chart showing how total assets have grown over time.Create a calculated field that shows the current ratio in the pivot table, and format the numbers with two decimal places.Create a pivot chart that shows the current ratio over time. Has Alphabet's liquidity deteriorated or improved over this period? Insert a trend line into the chart to verify your results.
3. Now, use a Timeline to filter the pivot table so that it only shows 2018 to 2019.
4. Now show only 2013 to 2014. Is there a difference in the trend of the current ratio in the two time periods?

Using the same workbook, copy both datasets into one worksheet so that they can be used together in a pivot table. Transpose them using Paste Special Transpose.

1. Create a pivot table that shows the quarter over quarter change in both Sales and Total Assets.
2. Create a pivot line chart that shows the data from part a. Does there appear to be a strong correlation between the two data series? Now put Sales on a secondary y-axis so that the scales are comparable. Does that change your original answer?
3. Create a calculated field that shows Alphabet's return on equity for each quarter.
4. Create a pivot line chart that shows the ROE over time. Does it appear that there is any trend in the ROE? If so, is it up or down?
5. Add a linear trend line to the pivot chart from part d, and show the equation on the chart. Does this change or confirm your previous answer?

Using the ETF Data.xls workbook from the chapter:

1. Using Get & Transform, clean up the data as described in the chapter, and keep only the Fund Name, Symbol, Asset Class, Total Return 2019, Total Return 2018, and Total Return 2017 fields.
2. Create calculated columns in the original data that calculate the 2017 to 2019 total return for each ETF and for each Asset Class. Be sure to properly account for compounding of returns.
3. Create a pivot table tp show the average three-year total return for each asset class, and sort the pivot table from the highest return category to the lowest. Which asset class did the best? Worst?
4. Create a pivot line chart that shows the ROE over time. Does it appear that there is any trend in the ROE? If so, is it up or down?
5. Add a linear trend line to the pivot chart from part d, and show the equation on the chart. Does this change or confirm your previous answer?

Using the ETF Data.xls workbook from the chapter:

1. Using Get & Transform, clean up the data as described in the chapter, and keep only the Fund Name, Symbol, Asset Class, Total Return 2019, Total Return 2018, and Total Return 2017 fields.
2. Create calculated columns in the original data that calculate the 2017 to 2019 total return for each ETF and for each Asset Class. Be sure to properly account for compounding of returns.
3. Create a pivot table tp show the average three-year total return for each asset class, and sort the pivot table from the highest return category to the lowest. Which asset class did the best? Worst?
4. Create a pivot bar chart that shows the returns of the top 3 best performing asset classes over the last three years.
5. Create another pivot table from this dataset, and then a calculated field that shows each ETF's three-year return minus the asset class three-year return. Which fund did best compared to its asset class over the three-year period? Which did the worst?

Financial researchers have found various violations of market efficiency that are referred to as "calendar effects." Using the S&P 500 1950 to 2018 Daily xIsx file from the official Web site (www.cengage.com/finance/mayes/analysis/e);

1. Create a pivot table and pivot chart that shows the average percentage change by day of the week for the entire period. Are the average returns different for each day? If so, which are the best and worst days of the week?
2. Now add the Decade field to the Row area of your pivot table. Are there any decades in which Mondays had a positive average return?
3. Change your pivot table to show the average daily return by month for the entire period. What is the best month on average? Which is the worst? Does this vary by decade?
4. What was the best decade in which to invest based on average daily return? What was the sum of the daily returns in each decade (ignore compounding of returns)? Add a Pivot Chart that shows the results.

Dr. Eve Carter PhD, Applied Mathematics
Senior lecturer at University. 12+ years tutoring algebra and calculus..

Aug 27, 2025