PROBLEMS

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

a. 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.

Answer a:

This data was already in consistence with a pivot table so there was no need to go through the

process of Get & Transform through Power- Query format. Also, the Transposing could not have

been done because the data was already in a transposed form. Only, the pivot- table could have

been drawn by using the variables of date, cash only, total short term investment, short term

receivables and total current assets.

The data spans from the oldest date, September 30, 2013, to the newest date, June 30, 2019,

showcasing changes in financial variables over time. Here is an analysis of trends:

All variables in these cash- statements exhibited some level of fluctuation. "Total Short Term
Investments" and "Total Current Assets" demonstrated the most pronounced upward trends,
reflecting considerable financial growth. Conversely, "Cash Only" showed a relatively modest
increase with occasional declines, and "Short-Term Receivables" exhibited steady growth. These
changes indicate overall strengthening in short-term financial positions during the given
timeframe.
 Sum of Sum of Total Short Term Sum of Short-Term Sum of Total
Dates Cash Only Investments Receivables Current Assets
9/30/201
3 15242 41281 8592 68858
12/31/20
13 18898 39819 9390 72886
3/31/201
4 16639 42740 8421 75314
6/30/201
4 19620 41584 9539 77905
9/30/201
4 15605 46552 10019 80115
12/31/20
14 18347 46048 11556 80685
3/31/201
5 16976 48460 10310 80313
6/30/201
5 18453 51327 10019 84164
9/30/201
5 18068 54699 10436 88103
12/31/20
15 16549 56517 13909 90114
3/31/201
6 15111 60153 12972 90955
6/30/201
6 13627 64833 12762 94238
9/30/201
6 9406 73650 12395 98546
12/31/20
16 12918 73415 14232 105408
3/31/201
7 18132 74307 12969 108794
6/30/201
7 15711 79002 14877 112386
9/30/201
7 10581 89562 15577 119345
12/31/20
17 10715 91156 18705 124308
3/31/201
8 12658 90227 16814 123761
6/30/201
8 14148 88106 17244 124157
9/30/201
8 13443 92973 18067 129702
12/31/20
18 16701 92439 21193 135676
3/31/201
9 19148 94340 19260 138207
6/30/201
9 16587 104469 21317 147437
Grand 373283 1637659 330575 2451377
Total

b. Build a pivot table from the data showing Total Assets by Quarter. Add a pivot chart

showing how total assets have grown over time

Answer b:

Row Labels Sum of Total Current Assets

Q1 2014 75314
Q1 2015 80313
Q1 2016 90955
Q1 2017 108794
Q1 2018 123761
Q1 2019 138207
Q2 2014 77905
Q2 2015 84164
Q2 2016 94238
Q2 2017 112386
Q2 2018 124157
Q2 2019 147437
Q3 2013 68858
Q3 2014 80115
Q3 2015 88103
Q3 2016 98546
Q3 2017 119345
Q3 2018 129702
Q4 2013 72886
Q4 2014 80685
Q4 2015 90114
Q4 2016 105408
Q4 2017 124308
Q4 2018 135676
Grand Total 2451377
 Growth of Total Assets by Quarter
160000
140000
120000
100000
Total
80000
60000
40000
20000
0
14 16 18 14 16 18 13 15 17 13 15 17
20 20 20 20 20 20 20 20 20 20 20 20
Q1 Q1 Q1 Q2 Q2 Q2 Q3 Q3 Q3 Q4 Q4 Q4

Description b:

According to the table and the chart, the growth of Total Assets by quarter, as

shown in the data, reflects a steady upward trend over time. The minimum Total

Assets were recorded in Q3 2013 at 68,858, while the maximum Total Assets

reached 147,437 in Q2 2019.

Across the quarters, Q1 2014 recorded a relatively balanced Total Assets figure of

75,314, demonstrating a stable progression from earlier periods. This trend of

growth is consistent across all quarters, with notable increases in later years,

reflecting substantial financial strengthening over time.

c. Create a calculated field that shows the current ratio in the pivot table, and format the

numbers with two decimal places.

Row Sum of Total Sum of Total Current Sum of Current Sum of Current
Labels Current Assets Liabilities Ratio Ratio2
Q1 2014 75314 16270 4.629010449 4.63
Q1 2015 80313 14336 5.60219029 5.60
Q1 2016 90955 17684 5.143349921 5.14
Q1 2017 108794 15256 7.131227058 7.13
Q1 2018 123761 25394 4.873631567 4.87
Q1 2019 138207 34910 3.95895159 3.96
Q2 2014 77905 17097 4.556647365 4.56
Q2 2015 84164 17362 4.847598203 4.85
Q2 2016 94238 17341 5.434404014 5.43
Q2 2017 112386 18685 6.014771207 6.01
Q2 2018 124157 29903 4.151991439 4.15
Q2 2019 147437 37000 3.984783784 3.98
Q3 2013 68858 14475 4.757029361 4.76
Q3 2014 80115 17920 4.470703125 4.47
Q3 2015 88103 18457 4.773419299 4.77
Q3 2016 98546 14323 6.880262515 6.88
Q3 2017 119345 20693 5.767409269 5.77
Q3 2018 129702 31301 4.143701479 4.14
Q4 2013 72886 15908 4.581719889 4.58
Q4 2014 80685 16805 4.801249628 4.80
Q4 2015 90114 19310 4.666701191 4.67
Q4 2016 105408 16756 6.290761518 6.29
Q4 2017 124308 24183 5.140305173 5.14
Q4 2018 135676 34620 3.919006355 3.92
Grand
Total 2451377 505989 4.844723897 4.84
d. 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.

Current Ratio by Time

8
6.88 7.13
7 6.29 6.01 5.76
6 5.6
4.84 4.77 4.66 5.14 5.43 5.14 4.87
5 4.75 4.58 4.62 4.55 4.47 4.8 3.9847837837837
4.15 4.14 3.91 3.95 8
4
3
2
1
0
e. Now, use a Timeline to filter the pivot table so that it only shows 2018 to 2019. Now show

only 2013 to 2014. Is there a difference in the trend of the current ratio in the two time

periods?

Answer e:

Sum of Current
Date Ratio
30-Sep-13 4.75
31-Dec-13 4.58
31-Mar-14 4.62
30-Jun-14 4.55
30-Sep-14 4.47
31-Dec-14 4.8
30-Sep-15 4.77
31-Mar-18 4.87
30-Jun-18 4.15
30-Sep-18 4.14
31-Dec-18 3.91
31-Mar-19 3.95
30-Jun-19 3.984783784

Grand Total 57.54478378

Description e:

The trend analysis shows that liquidity deteriorated significantly between the two
periods. While the company maintained a robust liquidity position in 2013–2014
(with current ratio of over 4), the downward trend in 2018–2019 highlights potential
financial risks that may need to be addressed (current ration touching 3 or below 4).