Data analysis
Description
This is a data analytics exercise modified from the one presented in your textbook. We will be using
Excel to perform an analysis of two companies: GPS Corporation and Tru, Inc.
Please download the Excel spreadsheet ÐS_Tru_Financials.xls®bsp;from the Data Analytics Module on
Canvas.
Part 1: For each of the two companies in the most recent five-year period 2017-2021 calculate and
display (using the chart option) the trends for the:
(a) Debt to equity ratio (Total Debt / Total Equity)
(b) Rate of return on assets (Net Income / Total Assets)
(c) Rate of return on shareholders®bsp;equity (Net Income / Shareholders®bsp;Equity)
Please make sure to label the charts correctly (including chart title, x-axis, and the y-axis). These charts
will be used to answer the next part and will be submitted as supporting material.
Part 2: Based on what you find, answer the following questions:
1. Which of the two companies finances a higher percentage of its assets by borrowed funds
relative to funds invested by shareholders as measure by the debt to equity ratio in 2021?
2. Which of the two companies indicates a higher profitability during the period 2017-2021
without regard to the sources of financing as measured by the return on assets ratio?
3. Which of the two companies indicates a higher effectiveness of employing resources provided
by owners during the period 2017-2021 as measured by the return on shareholders®bsp;equity
ratio?
4. Management is using its borrowed funds to enhance the earnings for shareholders of (a) GPS
Corporation, (b) Tru, Inc., (c) both firms, or (d) neither firm during the period 2017-2021?
Background Information
®bsp;What does the debt to equity (D/E) ratio tell us? The firmænbsp;capital structure refers to the
amount of debt and equity the company uses to finance its operations. D/E ratio is oftentimes
used as a measure of risk. All else equal, the higher the D/E ratio, the higher the risk. The risk
referred to here is %fault risk®bsp;because it indicates the likelihood the company will default on
its obligations (if it borrows excessively).
However, debt can also create ¡vorable financial leverage¦nbsp;If the company can earn a return
on the borrowed funds in excess of the cost of borrowing the funds, then shareholders are
provided with a total return greater than what could have been earned with equity funding
alone. Examining additional ratios will help determine whether the company has a favorable
financial leverage.
®bsp;What does return on assets (ROA) ratio tell us? ROA (net income / assets) is a measure of the
companyænbsp;profitability. It measures profitability without regard to the source of the companyænbsp;
financing (i.e., whether the financing is debt or equity). Remember, Assets = Liabilities + Equity,
so when we divide by assets we are dividing by debt and equity.
You can also think of ROA in terms of how well the company employs its assets. The ratio helps
us answer the following question: for every $1 of asset the company employs, how much net
income does the company generate?
®bsp;What does return on shareholders®bsp;equity (ROE) ratio tell us? ROE (net income / shareholders®bsp;
equity) is also a measure of the companyænbsp;profitability. However, it does measure profitability
with regard to the source of financing ®bsp;it looks specifically at the shareholders®bsp;equity (rather
than debt and equity combined). Doing so, the ROE measures how effective the company is at
employing resources provided by the owners®bsp;of the company.
The ratio helps us answer the following question: for every $1 of financing provided by
shareholders, how much net income does the company generate?
®bsp;Does the company have favorable financial leverage? For our purposes, we will consider a
company to have favorable financial leverage if its ROE is greater than its ROA.
Unformatted Attachment Preview
Company
GPS
GPS
GPS
GPS
GPS
GPS
GPS
GPS
GPS
GPS
Tru
Tru
Tru
Tru
Tru
Tru
Tru
Tru
Tru
Tru
Year
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
Cash and
Assets of
Prepaid
cash
Receivables,
discontinued expenses and
equivalents
net
Inventory
operations
other
6,867
5,835
43,046
0
1,941
8,705
5,624
44,469
0
1,441
9,135
6,778
45,141
0
2,224
7,281
6,677
44,858
460
1,909
7,781
6,768
43,803
0
1,588
6,550
5,937
40,714
0
1,774
7,395
5,089
36,318
131
2,960
7,907
4,144
32,713
140
1,473
8,569
5,986
26,523
-211
3,625
6,851
3,613
32,724
-150
2,958
7,257
3,908
36,929
307
4,889
4,871
6,294
45,044
1,431
5,160
7,727
3,438
37,724
5,924
7,796
10,122
1,042
28,604
3,524
8,143
8,859
2,306
35,124
18,280
8,267
9,929
1,235
26,343
27,555
8,044
8,501
2,663
27,347
30,204
7,787
7,906
3,258
31,907
29,573
9,240
8,145
2,746
32,517
46,730
7,158
5,922
4,964
42,335
18,540
6,464
1
Total current
assets
57,689
60,239
63,278
61,185
59,940
54,975
51,893
46,377
44,491
45,996
53,289
62,800
62,609
51,436
72,836
73,107
76,502
81,884
97,295
78,225
Buildings and
Land
improvements
26,026
117,567
25,659
115,907
25,722
116,194
25,098
113,373
24,045
108,615
22,475
101,526
21,545
97,323
19,988
100,290
26,931
121,653
18,748
84,688
27,138
117,116
27,222
113,701
27,231
115,719
27,302
135,179
27,582
122,624
27,209
141,383
26,347
102,810
25,747
95,400
31,267
84,228
22,949
87,447
Computer
Less
Fixtures and
hardware
Construction- accumulated
equipment and software in-progress depreciation
23,334
10,770
1,795
-71,782
23,005
10,617
1,770
-66,787
23,061
10,644
1,774
-63,115
22,502
10,385
1,731
-57,725
21,557
9,950
1,658
-51,896
20,150
9,300
1,550
-45,399
19,316
8,915
1,486
-43,486
17,920
8,271
2,478
-36,649
24,145
11,144
3,457
-58,733
16,808
7,758
1,993
-24,288
24,458
11,782
889
-55,391
23,764
11,631
1,400
-56,613
23,760
11,347
1,884
-58,813
23,107
10,644
364
-69,516
23,831
11,409
5,227
-60,040
22,849
10,969
4,280
-55,031
21,951
11,258
2,520
-51,356
21,080
11,444
2,331
-46,600
34,105
12,406
3,083
-65,861
24,829
9,504
1,544
-45,057
2
Goodwill
17,037
16,695
18,102
19,510
20,497
20,651
16,763
25,026
21,028
13,524
3,480
3,760
5,391
4,921
4,987
4,587
4,440
3,684
3,883
2,210
Right of use asset – Right of use asset – Property under
operating net,
finance, net
capital lease
0
0
6,468
0
0
6,345
0
0
2,375
0
0
2,543
0
0
2,752
0
0
2,721
0
0
2,780
4,015
1,503
0
4,513
1,671
0
3,417
1,777
0
0
0
5,498
0
0
5,330
0
0
2,256
0
0
2,340
0
0
2,504
0
0
2,612
0
0
2,502
4,030
2,812
0
4,022
3,011
0
3,875
3,034
0
3
Pension Plan
Assets
20,078
19,386
19,582
20,804
22,742
19,728
19,470
22,514
25,456
26,011
16,944
16,829
15,648
17,036
18,708
19,896
20,823
21,642
22,617
23,621
Total
Investments in
noncurrent
equity securities
assets
Total assets
9,921
161,214
218,903
6,131
158,728
218,967
5,671
160,010
223,288
6,149
164,370
225,555
5,987
165,907
225,847
5,456
158,159
213,134
4,129
148,240
200,133
3,942
169,299
215,676
4,786
186,051
230,542
3,423
153,860
199,856
102
152,015
205,304
1,231
148,256
211,056
309
144,733
207,342
2,236
153,614
205,050
2,219
159,051
231,886
334
179,087
252,194
2,270
143,565
220,067
1,556
143,127
225,011
1,322
134,083
231,378
578
134,533
212,758
Accounts
Accrued Accrued income
payable
liabilities
taxes
23,102
20,654
921
20,330
19,607
521
25,690
19,152
1,021
19,492
18,793
966
9,765
18,808
2,211
20,147
18,180
1,164
18,005
18,701
157
10,329
18,734
1,347
15,197
23,894
243
18,200
14,061
984
24,651
16,609
810
17,929
22,189
458
22,319
14,544
898
15,835
19,822
850
19,813
22,913
1,946
19,158
28,116
1,024
20,019
6,997
1,341
20,724
12,977
1,185
22,172
14,130
214
5,522
866
20,521
4
Current
liabilities of
discontinued
operations
0
0
0
89
0
0
47
92
99
102
0
0
0
0
65
102
188
81
87
90
Current portion Current portion Current portion
Current portion of lease liability of lease liability
of long-term
capital lease
– finance lease – operating lease
debt
720
0
0
2,194
698
0
0
2,560
313
0
0
3,903
335
0
0
3,375
363
0
0
4,547
361
0
0
1,737
378
0
0
3,831
0
342
684
3,370
0
445
650
4,674
0
506
606
2,678
396
0
0
1,931
384
0
0
2,253
172
0
0
3,435
184
0
0
2,970
200
0
0
4,001
199
0
0
1,529
208
0
0
2,977
0
301
602
2,966
0
392
583
4,113
0
400
533
2,357
5
Total
Projected
Short-term Debt to
current
Benefit
borrowings Equity
liabilities Obligation
1,099
48,690
22,309
2
2,708
46,424
21,540
2
1,592
51,671
21,757
2
7,670
50,720
23,115
2
6,805
42,499
23,939
2
4,047
45,636
20,767
2
1,031
42,150
20,495
2
523
35,421
22,514
2
914
46,116
24,243
2
210
37,347
24,309
2
988
45,385
18,827
2
1,136
44,349
18,699
2
2,098
43,466
17,387
2
1,598
41,259
18,929
2
1,009
49,947
20,787
2
2,044
52,171
21,166
2
1,198
32,928
21,919
2
1,671
40,507
22,781
2
929
42,620
23,317
2
1,439
31,728
24,103
2
Capital
Long-term
lease
Lease liability debt
liability operating lease
52,032
6,003
0
54,255
5,816
0
52,512
2,606
0
58,085
2,788
0
66,516
3,023
0
59,696
3,009
0
58,195
3,150
0
74,563
0
6,215
76,696
0
5,313
61,958
0
4,017
56,906
5,283
0
63,269
5,118
0
65,914
2,293
0
61,904
2,453
0
73,270
2,660
0
79,429
2,648
0
83,134
2,772
0
80,406
0
4,034
80,438
0
3,971
77,192
0
3,807
6
Lease liability – Deferred income
finance lease
taxes
0
9,334
0
7,321
0
8,805
0
9,508
0
8,536
0
8,421
0
6,231
3,108
3,716
3,110
4,545
3,305
4,624
0
8,223
0
6,443
0
7,748
0
8,367
0
7,511
0
7,410
0
5,483
2,767
4,308
2,929
4,379
2,993
3,806
Total
Total
liabilities
noncurrent
(Cur +
Common
Additional
liabilities
noncur)
stock
paid-in capital
89,678 138,368
305
5,108
88,932 135,356
317
5,436
85,680 137,351
323
6,914
93,496 144,216
323
7,455
102,015 144,513
332
7,766
91,892 137,528
342
6,817
88,071 130,221
352
5,076
110,116 145,537
378
4,551
113,907 160,023
365
5,377
98,214 135,560
373
2,138
89,238 134,624
204
25,160
93,529 137,878
222
23,769
93,342 136,808
236
21,773
91,653 132,912
236
19,867
104,228 154,175
240
17,444
110,654 162,825
249
15,498
113,308 146,236
262
14,716
114,295 154,802
276
12,973
115,033 157,653
274
11,728
111,900 143,628
278
10,942
7
Retained
earnings
89,354
90,021
85,777
76,566
72,978
68,691
63,967
66,357
64,645
62,433
48,151
41,391
42,862
55,996
58,467
66,596
61,436
60,542
65,878
59,103
Accumulated
other
Total
Total
comprehensive shareholders’ liabilities and
income(loss)
equity
equity
-14,232
80,535
218,903
-12,163
83,611
218,967
-7,077
85,937
223,288
-3,005
81,339
225,555
258
81,334
225,847
-244
75,606
213,134
517
69,912
200,133
-1,147
70,139
215,676
133
70,520
230,542
-648
64,296
199,856
-2,836
70,680
205,304
7,796
73,178
211,056
5,662
70,533
207,342
-3,960
72,138
205,050
1,560
77,711
231,886
7,027
89,369
252,194
-2,582
73,831
220,067
-3,582
70,209
225,011
-4,154
73,726
231,378
-1,193
69,130
212,758
Check Total
Assets
218,903
218,967
223,288
225,555
225,847
213,134
200,133
215,676
230,542
199,856
205,304
211,056
207,342
205,050
231,886
252,194
220,067
225,011
231,378
212,758
8
Difference
0
0
0
0
0
0
0
0
-0
0
0
0
-0
0
-0
-0
0
0
0
-0
Change in
accounts
receivable (CF)
3,444
-211
1,154
-101
91
-831
-848
-945
1,842
-2,373
294
2,386
-2,856
-2,396
1,263
-1,070
1,428
595
-513
2,219
Change in
Change in
accrued
Change in
accounts
income taxes
accrued
payable (CF)
(CF)
liabilities (CF)
-3,942
-492
137
1,423
-400
-1,047
672
500
-455
-283
-55
-359
-1,055
1,245
15
-3,089
-1,047
-628
-4,396
-1,007
521
-3,605
1,190
33
-6,191
-1,104
5,160
6,202
741
-5,694
4,205
-174
-1,591
8,115
-352
5,580
-7,320
440
-7,645
-9,120
-48
5,278
6,520
1,096
3,091
-8,781
-921
5,203
1,004
317
-21,119
4,560
-156
5,980
610
-972
1,153
9,818
652
-8,608
Depreciation
and
amortization
(CF)
4,080
3,454
3,173
4,870
3,478
130
3,641
3,157
3,176
3,446
3,302
4,187
4,862
4,133
4,493
5,169
5,799
4,236
4,986
4,001
9
Change in
Inventories
(CF)
7,122
1,423
672
-283
-1,055
-3,089
-4,396
-3,605
-6,191
6,202
4,205
8,115
-7,320
-9,120
6,520
-8,781
1,004
4,560
610
9,818
Other
operating
activities
0
0
0
-2,234
0
0
0
497
-1,591
-2,332
294
-938
-454
4,610
850
1,569
0
-39
5,630
-1,288
Net cash flows
from operating
activities
1,455
14,829
16,856
16,959
20,428
13,718
20,563
23,639
25,637
15,725
13,851
24,056
11,361
26,504
21,074
23,349
-2,221
22,910
24,959
3,187
Investment
Proceeds from and business
disposal of
acquisitions,
property and
net of cash
equipment
acquired
1,118
0
881
-5,000
1,241
-10,000
727
-5,800
532
0
580
0
489
-5,558
1,002
-15,431
236
-11,043
2,378
0
0
124
333
191
0
-7,133
880
-5,689
0
-8,551
151
-480
13
-209
147
0
-1,283
0
-1,115
0
10
Other
investing
activities
-122
-79
-192
-138
-271
-131
219
-438
-101
-694
-6,547
2,173
-1,427
-6,893
-4,138
0
0
13
-1,928
-1,241
Payments for Net cash flows
property and
for investing
equipment
activities
-10,619
-9,623
-11,477
-15,675
3
-8,948
-13,115
-18,326
-12,898
-12,637
-13,510
-13,061
0
-4,850
-12,184
-27,051
-16,249
-27,157
0
1,684
-558
-6,980
-124
2,573
-422
-8,982
-311
-12,014
-293
-12,982
-164
-493
-307
-503
-190
-30
-287
-3,498
-215
-2,571
Other
financing
activities
0
0
0
0
0
0
0
0
0
0
0
-5,000
-11,023
5,222
0
15,009
0
0
0
0
Payments
of longterm debt Dividends paid
-2,055
-3,306
-4,432
-3,433
-3,904
-3,488
-4,968
-4,035
-1,478
-5,048
-4,584
-5,048
-4,080
-5,237
-6,033
-5,317
-5,475
-5,421
-9,069
-5,446
-11,738
-1,591
-378
-1,600
-9,217
-1,600
-1,391
-1,600
-6,796
-1,600
-13,889
-1,710
-10,040
-1,710
-8,946
-1,820
-14,504
-1,820
-9,699
-1,820
Net change in
Proceeds from Purchase of
short-term
issuance of longTreasury
borrowings
term debt
stock
676
20,672
-8,298
1,609
10,495
-4,112
-1,116
0
0
6,078
3,866
0
-1,758
211
0
3,925
5,050
0
-3,016
11,396
-14,776
-508
15,546
-276
391
13,870
-1,845
-704
4,931
-7,121
778
8,787
-6,471
148
0
-15,480
962
8,858
0
-500
10,778
-6,493
-589
26,093
-8,333
1,035
15,529
-8,187
-846
4,493
-10,898
473
0
-1,880
-742
3,950
-15,289
510
0
-3,021
11
Stock
Net cash flows
option
from financing
exercises
activities
479
8,168
719
846
600
-7,908
426
1,367
282
-7,791
0
-657
0
-15,713
0
3,412
0
1,520
0
-17,409
982
-9,253
1,333
-20,976
1,658
-10,362
2,027
8,042
1,600
10,375
396
8,183
1,307
-17,694
209
-11,964
0
-28,405
0
-14,030
Net (decrease) /
increase in cash and
cash equivalents
1,500
1,838
430
-1,854
500
-1,231
845
512
662
-1,718
406
-2,386
2,856
2,396
-1,263
1,070
-1,428
-595
239
-2,223
12
difference
-1,500
-1,838
-430
1,854
-500
1,231
-845
-512
-662
1,718
-2,788
8,038
-10,839
20,137
19,730
29,968
-18,990
11,511
-7,183
-11,191
Net change
0
-0
0
-0
-0
0
-0
0
-0
-0
-2,382
5,652
-7,983
22,533
18,467
31,038
-20,418
10,916
-6,944
-13,413
Credit
Sales
card
revenue revenue
572,852
4,556
567,419
3,516
578,167
3,422
560,083
3,218
544,518
3,047
514,401
3,096
485,992
2,897
479,084
2,953
627,955
3,246
446,296
2,494
341,836
0
358,908
0
355,163
0
352,340
0
356,546
5,960
344,654
6,218
344,188
7,129
327,881
8,542
415,012
7,244
320,982
6,776
Less
Less
Sales
Discount
returns on Sales Net sales
-36,099 -55,436 485,873
-43,883 -44,922 482,130
-43,883 -52,055 485,651
-42,104 -44,903 476,294
-38,095 -40,819 468,651
-37,728 -32,819 446,950
-41,057 -25,983 421,849
-40,108 -33,844 408,085
-52,332 -51,933 526,936
-41,860 -23,612 383,318
-30,981
-1,988 308,867
-28,776
-2,199 327,933
-32,047
-370 322,747
-32,052
-3,493 316,796
-34,221
-2,503 325,782
-36,966
-3,394 310,511
-43,507
-8,299 299,511
-39,182
-6,766 290,476
-43,879
-7,321 371,056
-47,930
-9,288 270,540
13
Cost of
sales
361,256
360,984
365,086
358,069
352,297
335,127
314,946
304,444
393,308
285,895
225,530
228,284
231,822
287,872
291,436
279,240
266,435
257,502
336,381
244,558
Depreciation
Gross
and
margin amortization
124,617
4,211
121,146
4,411
120,565
4,292
118,225
4,160
116,354
4,007
111,823
4,104
106,903
3,948
103,641
3,828
133,629
4,074
97,423
3,433
83,337
6,355
99,649
9,836
90,924
9,462
28,924
8,871
34,347
9,084
31,271
9,471
33,076
9,262
32,973
8,991
34,674
9,911
25,982
7,198
Selling,
general and
administrative
expenses
101,593
97,279
93,056
90,665
87,954
84,516
81,762
76,157
99,079
70,815
55,355
60,787
60,473
313
646
-277
163
1,557
685
336
Net pension Pension service
cost
cost
535
517
522
555
575
498
409
492
550
540
564
582
533
583
452
449
417
454
499
508
526
547
560
578
Pension nonservice cost
components
83
-10
18
50
488
511
488
542
38
36
72
36
14
Gain on Interest Interest
sale
income expense
323
2,267
2,102
521
2,469
2,239
300
2,529
2,446
263
2,351
2,456
272
2,268
2,338
288
2,165
2,493
277
3,255
2,425
278
1,884
1,841
260
1,246
2,849
220
1,489
1,706
0
0
3,101
-4,756
0
3,493
0
0
3,919
0
0
3,808
-716
0
4,472
0
13
4,036
0
44
4,567
0
109
4,341
0
55
5,632
0
188
3,998
Provision
for
income
taxes
6,204
6,558
7,985
8,105
7,968
7,944
4,480
4,801
5,871
4,600
5,760
7,120
5,351
5,342
6,838
6,287
3,866
3,617
3,733
2,833
Income from
Discontinued
Net
continuing
operations, net income /
operations
of tax
(loss)
12,561
2,267
14,828
13,132
0
13,132
15,092
0
15,092
14,899
0
14,899
16,053
0
16,053
14,720
2,232
16,952
16,837
2,221
19,058
18,095
963
19,058
22,098
0
22,098
17,411
2,066
19,477
12,314
302
12,616
13,209
187
13,396
11,302
-1,156
10,146
10,135
0
10,135
12,091
-1,404
10,686
11,259
13
11,272
14,210
-2,244
11,966
13,483
109
13,592
13,649
55
13,703
10,649
1,988
12,637
15
Basic
EPS
1.14
1.18
1.37
1.36
1.46
1.34
1.53
1.63
2.02
1.59
0.39
0.42
0.36
0.32
0.38
0.34
0.45
0.43
0.43
0.33
Market
Diluted price per
EPS
share
1.15
44.50
1.20
44.56
1.38
46.23
1.37
44.50
1.47
47.10
1.35
41.54
1.54
48.32
1.66
48.41
2.03
52.21
1.60
47.45
0.40
8.30
0.43
9.89
0.37
7.88
0.33
8.08
0.39
8.23
0.36
8.05
0.46
10.24
0.44
11.12
0.44
11.54
0.34
9.93
16
17
18
Purchase answer to see full
attachment
Have a similar assignment? "Place an order for your assignment and have exceptional work written by our team of experts, guaranteeing you A results."
Complete the 
Complete the order payment after filling in the order form. We’ll receive your order and assign it to a writer.
The assigned writer will complete your paper and forward it to our Editing Team for review and approval.
Once approved by the editor, the completed paper will be uploaded to your account for you to download, review and approve.