Accounting Question

https://drive.google.com/file/d/1MIdR23AnPEjdNoqZR…

Company
Big Store
Big Store
Big Store
Big Store
Big Store
Big Store
Big Store
Big Store
Big Store
Big Store
Discount Goods
Discount Goods
Discount Goods
Year
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2012
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
2014
2015
Discount Goods
2016
2017
Discount Goods
2018
Discount Goods
2019
Discount Goods
2020
Discount Goods
4,871
2013
Discount Goods
Discount Goods
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
46,523
-211
3,625
6,851
3,613
32,724
-150
2,958
7,257
3,908
36,929
307
4,889
2021
Total current
assets
57,689
60,239
63,278
61,185
59,940
54,975
51,893
48,032
64,491
45,996
53,289
Average
accounts
receivable
(net)
6,934
5,730
6,201
6,728
6,723
6,353
5,513
4,617
5,065
4,800
5,582
62,800
5,101
27,222
113,701
23,764
11,631
1,400
62,609
4,866
27,231
115,719
23,760
11,347
1,884
51,436
2,240
27,302
135,179
23,107
10,644
3,364
72,836
1,674
27,582
122,624
23,831
11,409
5,227
73,107
1,770
27,209
116,330
22,849
10,969
4,280
76,502
1,949
26,347
97,810
21,951
11,258
2,520
81,884
2,961
25,747
93,400
21,080
11,444
2,331
97,295
3,002
31,267
121,536
34,105
12,406
3,083
78,225
3,855
22,949
87,447
24,829
9,504
1,544
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
90,290
26,931
121,653
18,748
84,688
27,138
117,116
Computer
Fixtures and hardware and Constructionequipment
software
in-progress
23,334
10,770
1,795
23,005
10,617
1,770
23,061
10,644
1,774
22,502
10,385
1,731
21,557
9,950
1,658
20,150
9,300
1,550
19,316
8,915
1,486
17,920
8,271
2,478
24,145
11,144
3,457
16,808
7,758
1,993
24,458
11,782
889
Less
accumulated Property and
Right of use asset – Right of use asset – Property under
depreciation equipment, net Goodwill
operating net,
finance, net
capital lease
-71,782
107,710
17,037
0
0
6,468
-66,787
110,171
16,695
0
0
6,345
-63,115
114,280
18,102
0
0
2,375
-57,725
115,364
19,510
0
0
2,543
-51,896
113,929
20,497
0
0
2,752
-45,399
109,603
20,651
0
0
2,721
-43,486
105,098
16,763
0
0
2,780
-38,304
99,544
15,026
4,015
1,503
0
-54,733
130,998
21,028
4,513
1,671
0
-36,288
93,007
13,524
3,417
1,777
0
-77,391
103,991
3,480
0
0
5,498
-72,204
105,515
3,760
0
0
5,330
-67,138
112,804
5,391
0
0
2,256
-62,516
137,082
4,921
0
0
2,340
-59,160
131,513
4,987
0
0
2,504
-55,031
126,605
4,587
0
0
2,612
-51,356
108,530
4,440
0
0
2,502
-46,600
107,303
3,684
4,030
2,812
0
-65,861
134,936
3,883
4,022
3,011
0
-45,057
100,515
2,210
3,875
3,034
0
Investments in
equity securities
9,921
6,131
5,671
6,149
5,987
5,456
4,129
3,942
4,786
3,423
102
Total
noncurrent
Average
Average
assets
Total assets total assets fixed assets
141,136
198,825
193,640
107,708
139,342
199,581
199,203
108,931
140,428
203,706
201,644
112,237
143,566
204,751
204,229
114,825
143,165
203,105
203,928
114,641
138,431
193,406
198,256
111,765
128,770
180,663
187,035
107,341
122,375
170,407
175,535
102,328
160,963
225,454
197,930
115,278
113,905
159,901
192,677
112,016
113,071
166,360
149,505
98,491
Accounts
Accrued
payable
liabilities
23,102
20,654
20,330
19,607
25,690
19,152
19,492
18,793
9,765
18,808
20,147
18,180
18,005
18,701
10,329
18,734
15,197
23,894
14,061
18,200
24,651
16,609
1,231
116,142
178,942
172,651
104,737
17,929
22,189
309
121,409
184,018
181,480
109,147
22,319
14,544
2,236
146,578
198,013
191,016
124,919
15,835
19,822
2,219
141,222
214,058
206,036
134,288
19,813
22,913
334
134,138
207,244
210,651
129,057
19,158
28,116
2,270
117,742
194,244
200,744
117,548
20,019
6,997
1,556
116,040
197,924
196,084
107,903
20,724
12,977
1,322
143,844
241,139
219,532
121,102
22,172
14,130
578
106,571
184,796
212,967
117,729
5,522
20,521
Current liabilities
Current
Current portion Current portion of Current portion
Accrued income of discontinued portion capital of lease liability lease liability of long-term
taxes
operations
lease
finance lease
operating lease
debt
921
0
720
0
0
2,194
521
0
698
0
0
2,560
1,021
0
313
0
0
3,903
966
89
335
0
0
3,375
2,211
0
363
0
0
4,547
1,164
0
361
0
0
1,737
157
47
378
0
0
3,831
1,347
92
0
342
684
3,370
243
99
0
445
650
4,674
984
102
0
506
606
2,678
810
0
396
0
0
1,931
458
0
384
0
0
2,253
898
0
172
0
0
3,435
850
0
184
0
0
2,970
1,946
65
200
0
0
4,001
1,024
102
199
0
0
1,529
1,341
188
208
0
0
2,977
1,185
81
0
301
602
2,966
214
87
0
392
583
4,113
866
90
0
400
533
2,357
Short-term
borrowings
1,099
2,708
1,592
7,670
6,805
4,047
1,031
523
914
210
988
Refund
liability
18,238
18,195
13,601
18,625
29,319
16,664
16,334
20,122
26,615
14,582
11,095
Total
current
liabilities
66,928
64,619
65,272
69,345
71,818
62,300
58,484
55,543
72,730
51,928
56,480
1,136
11,749
56,098
53,697
5,118
0
0
2,098
8,694
52,160
59,608
2,293
0
0
1,598
15,528
56,787
58,269
2,453
0
0
1,009
12,413
62,360
67,935
2,660
0
0
2,044
11,326
63,498
63,373
2,648
0
0
1,198
11,828
44,756
70,472
2,772
0
0
1,671
9,835
50,342
66,748
0
4,034
2,767
929
9,330
51,950
86,335
0
3,971
2,929
42,308
61,412
0
3,807
2,993
1,439
10,581
Long-term
debt
36,015
38,214
41,086
41,771
38,394
44,070
40,692
30,378
47,637
28,102
47,695
Capital
lease
Lease liability – Lease liability liability operating lease finance lease
6,003
0
0
5,816
0
0
2,606
0
0
2,788
0
0
3,023
0
0
3,009
0
0
3,150
0
0
0
6,215
3,108
0
5,313
3,110
0
4,017
3,305
5,283
0
0
Deferred
income taxes
9,344
7,321
8,805
9,508
8,536
8,421
8,425
5,024
6,145
6,253
8,223
Total
Total
liabilities
noncurrent
(Cur +
Common Additional paidliabilities
noncur)
stock
in capital
51,362 118,290
305
5,108
51,351 115,970
317
5,436
52,497 117,769
323
6,914
54,067 123,412
323
7,455
49,953 121,771
332
7,766
55,500 117,800
342
6,817
52,267 110,751
352
5,076
44,725 100,268
378
4,551
62,205 134,935
365
5,377
41,677
93,605
373
4,138
61,200 117,680
204
25,160
Accumulated
other
Retained comprehensive
earnings
income(loss)
89,354
-14,232
90,021
-12,163
85,777
-7,077
76,566
-3,005
72,978
258
68,691
-244
63,967
517
66,357
-1,147
84,645
133
62,433
-648
26,151
-2,836
6,442
65,258
121,356
222
23,769
36,391
-2,796
7,748
69,649
121,809
236
21,773
42,862
-2,662
8,367
69,089
125,876
236
19,867
55,996
-3,960
7,512
78,107
140,467
240
17,444
58,467
-2,560
7,410
73,431
136,929
249
15,498
57,596
-3,027
7,414
80,658
125,413
262
14,716
56,436
-2,582
5,824
79,373
129,716
276
12,973
57,542
-2,582
5,921
99,156
151,106
274
16,728
68,878
4,154
5,146
73,358
115,666
278
10,942
59,103
-1,193
Total
Accrued
(CF)-Cash & cash
shareholders’ Total liabilities
Accounts
income taxes
Accrued
equivalents, beginning
equity
and equity
payable (CF)
(CF)
liabilities (CF)
of year
80,535
198,825
3,942
492
1,137
8,705
83,611
199,581
2,008
-472
1,303
9,135
85,937
203,706
2,678
166
1,249
7,281
81,339
204,751
531
-1,224
103
7,781
81,334
203,105
1,061
981
271
6,550
75,606
193,406
2,687
994
-935
7,395
69,912
180,663
2,676
-153
-280
7,907
70,139
170,407
1,052
0
1,348
7,275
90,520
225,454
2,970
0
-725
9,509
707
66,296
159,901
-282
985
6,557
48,680
166,360
0
0
1,876
17,982
57,587
178,942
-2,756
0
0
9,822
62,209
184,018
0
0
1,267
3,089
72,138
198,013
-1,738
0
1,978
3,484
73,591
214,058
-716
0
0
3,529
70,316
207,244
0
684
0
7,609
68,831
194,244
0
2,347
0
9,778
68,209
197,924
0
5,267
0
3,840
90,034
241,139
0
1,267
0
10,556
69,130
184,796
3,697
0
2,356
0
(CF)-Cash & cash
equivalents, end of
year
6,867
8,705
9,135
7,281
7,781
6,550
7,395
7,907
8,869
7,151
7,257
Change in
accounts
receivable
0
0
-89
-698
0
0
0
-44
350
-1,295
761
Depreciation
Deferred
and
Income from
income taxes
amortization Dividends continuing
(CF)
(CF)
paid
operations
761
10,080
-6,306
14,293
-672
9,454
-7,620
15,080
-503
9,173
-8,029
16,814
-279
8,870
-6,435
16,551
-133
8,478
-5,493
17,704
1,050
8,130
-5,048
16,454
651
7,641
-4,437
15,959
-504
7,157
-4,217
17,703
439
9,176
-5,921
19,199
-756
6,446
-4,246
13,500
5,362
302
-5,991
12,164
4,870
-672
8,060
187
-6,053
14,947
7,727
-503
10,884
4,862
-5,356
-7,271
10,123
-279
11,973
-2,133
-4,471
8,760
8,859
-133
3,733
-3,493
-3,862
13,329
9,929
1,050
0
-1,169
-3,333
13,018
8,501
651
0
0
-2,707
12,978
7,907
-504
0
0
-2,204
11,058
8,419
439
0
0
-5,050
14,556
6,200
-756
0
0
-3,326
9,141
Investment and
Inventories business acquisitions,
(CF)
net of cash acquired
1,021
-2,463
-703
0
-1,229
0
-1,667
0
-2,759
0
-3,727
-3,548
-3,205
-202
2,213
0
-4,381
0
1,798
0
182
124
Net (decrease) /
Net cash flows Net cash flows
increase in cash and
from financing from investing
cash equivalents
activities
activities
-1,838
-19,381
-13,987
-430
-17,144
-10,675
1,854
-15,585
-11,125
-500
-11,231
-12,526
1,231
-11,723
-12,637
-845
-8,491
-16,609
-512
-11,962
-12,193
632
-16,738
-11,620
-640
-11,370
-16,114
594
-9,491
-11,769
-10,725
-28,667
-6,218
-1,431
191
-4,952
-42,001
10,822
31
-7,133
4,638
-7,158
-7,756
258
5,689
6,638
-34,668
12,867
298
-8,551
5,330
-6,546
-10,813
1,649
-480
2,320
-22,569
836
1,978
-209
-1,276
-27,058
1,711
1,618
0
4,067
-21,551
116
806
0
-2,137
-27,226
-2,861
267
0
3,844
-15,688
-3,651
Net cash flows
from operating
activities
31,530
27,389
28,564
23,257
25,591
24,255
23,643
28,990
26,843
21,854
24,160
Net change in
short-term
Other financing
borrowings
activities
-1,673
-707
1,235
-1,535
-6,288
-923
911
-702
2,754
165
3,019
-630
503
-568
-1,033
-3,725
254
4,748
-1,253
4,151
0
-4,236
Other
Other
investing
operating
activities
activities
-122
206
-79
1,410
-192
785
-138
938
-271
602
-131
398
219
1,087
-438
318
-101
984
-694
17
-6,547
3,771
Payments for
property and
equipment
-10,619
-11,477
-12,174
-13,115
-12,898
-13,510
-12,699
-12,184
-16,249
-11,735
204
26,227
0
-21,423
2,173
6,709
124
19,551
-356
-2,722
-1,427
9,462
422
28,440
-3,956
-6,384
-6,893
8,871
311
22,689
4,311
4,441
-4,138
9,084
293
24,053
0
-13,084
0
9,471
164
24,071
0
-9,214
0
6,284
307
25,502
0
-8,920
13
7,102
190
27,950
0
3,413
-1,928
10,900
287
23,183
0
1,199
-1,241
9,686
215
Payments of
long-term
debt
-2,055
-4,432
-3,904
-4,968
-1,478
-4,584
-4,080
-6,033
-5,475
-5,953
-11,738
Proceeds from
(CF)
disposal of
Proceeds from
Purchase of
Purchase of
Accounts
Stock
property and
issuance of long- available for sale
Treasury
Receivables, option
equipment
term debt
securities
stock
net
exercises
1,118
137
-1,901
-8,298
-402
-479
881
39
0
-4,112
-19
-719
1,241
5,174
0
-1,015
-569
-600
727
7,072
0
-6,683
-566
-426
532
211
0
-7,600
-614
-282
580
5,050
0
-6,298
-796
0
489
11,396
0
-14,776
-733
0
1,002
5,546
0
-7,276
-297
0
236
13,870
0
-18,845
-818
0
660
4,931
0
-7,121
-561
0
0
8,787
0
-16,471
502
982
-378
8,333
0
0
-15,480
511
1,333
-9,240
0
8,858
471
0
316
1,658
-15,391
13,880
0
578
-6,493
471
2,027
-6,796
1,129
2,093
453
-8,333
453
1,600
-13,889
1,151
15,529
0
-8,187
400
396
-10,040
1,613
4,493
0
-10,898
484
1,307
-8,946
147
0
0
-1,880
458
209
-14,504
-1,283
3,950
0
-15,289
422
-33
-9,699
-1,115
0
0
-3,021
391
-1,057
Cost of
Sales
sales
revenue
361,256 572,852
360,984 567,419
365,086 578,167
358,069 560,083
352,297 544,518
335,127 514,401
314,946 485,992
304,444 479,084
393,308 627,955
285,895 446,296
225,530 341,836
Credit
Less
Depreciation Discontinued
card
Less Sales Discount
and
operations, net
revenue
returns
on Sales Net sales amortization
of tax
4,556
-36,099
-55,436 485,873
4,211
0
3,516
-43,883
-44,922 482,130
4,411
0
3,422
-43,883
-52,055 485,651
4,292
285
3,218
-42,104
-44,903 476,294
4,160
144
3,047
-38,095
-40,819 468,651
4,007
52
3,096
-37,728
-32,819 446,950
4,104
-67
2,897
-41,057
-25,983 421,849
3,948
1,034
2,953
-40,108
-33,844 408,085
3,828
-79
3,246
-52,332
-51,933 526,936
4,074
918
2,494
-41,860
-23,612 383,318
3,433
3,555
0
-30,981
-1,988 308,867
6,355
302
EBIT
18,230
19,173
22,555
22,449
23,446
22,166
21,317
22,896
27,969
22,011
17,622
228,284
358,908
0
-28,776
-2,199
327,933
9,836
187
21,880
231,822
355,163
0
-32,047
-370
322,747
9,462
-18,156
16,236
287,872
352,340
0
-32,052
-3,493
316,796
8,871
-3,213
18,315
291,436
356,546
5,960
-34,221
-2,503
325,782
9,084
-1,404
22,471
279,240
344,654
6,218
-36,966
-3,394
310,511
9,471
0
19,818
266,435
344,188
7,129
-43,507
-8,299
299,511
9,262
0
19,991
257,502
327,881
8,542
-39,182
-6,766
290,476
8,991
0
17,222
336,381
415,012
7,244
-43,879
-7,321
371,056
9,911
0
21,652
320,982
6,776
-47,930
-9,288
270,540
7,198
0
13,683
244,558
Provision Selling, general
for
and
income administrative Interest
taxes
expenses
expense
2102
6,204
101,853
2239
6,558
97,041
2446
7,985
93,418
2456
8,105
91,353
2338
7,958
88,629
2493
7,944
85,265
2425
7,579
81,361
1841
7,156
76,639
2849
9,099
101,326
1706
6,445
71,759
5,760
59,360
3101
14,947
7,120
65,178
3493
-7,271
5,351
65,227
3919
8,760
6,342
0
3808
13,329
7,738
2,076
4472
13,018
6,787
1,982
4036
12,978
7,000
3,822
4567
11,058
6,151
6,760
4341
14,556
7,083
3,111
5632
9,141
4,529
5,101
3998
Income from
Net
continuing
Interest income /
operations
income
(loss)
12,026
2,267
14,293
12,615
2,469
15,080
14,570
2,529
16,814
14,344
2,351
16,551
15,488
2,268
17,704
14,222
2,165
16,454
13,738
3,255
15,959
15,740
1,884
17,703
18,870
1,246
19,199
15,566
1,489
13,500
11,862
0
12,164
Gain on
sale
323
521
300
263
272
288
277
278
260
220
0
Gross
margin
124,617
121,146
120,565
118,225
116,354
111,823
106,903
103,641
133,629
97,423
83,337
-2,756
99,649
14,760
0
0
90,924
10,884
0
-1,738
28,924
11,973
0
-716
34,347
14,733
0
0
31,271
13,005
13
0
33,076
12,934
44
0
32,973
10,949
109
0
34,674
14,501
55
0
25,982
8,953
188
Tableau Assignment 6
Tableau Instructions:
You have available to you an extensive data set that includes detailed financial data for
Discount Goods and Big Store for 2012–2021. The data set is in the form of four Excel
files available to download from Connect or under Student Resources within the Library
tab. The one for use in this chapter is named “Discount_Goods_Big_Store_
Financials.xlsxLinks to an external site.”. Download this file and save it to the computer
on which you will be using Tableau. All short training videos can be found hereLinks to
an external site..
For this case, you will create several calculations to produce several long-term solvency
ratios to allow you to compare and contrast the two companies.
After you view the training videos, follow these steps to create the charts you’ll use for
this case:
1. Open Tableau and connect to the Excel spreadsheet you downloaded.
2. Click on the “Sheet 1” tab at the bottom of the canvas, to the right of the Data
Source at the bottom of the screen. Drag “Company” and “Year” under “Dimensions”
to the Rows shelf. Change “Year” to discrete by right-clicking and selecting
“discrete.” Select “Show Filter” and uncheck all the years except 2018–2021. Click
OK.
3. Drag “Total liabilities” under “Measures” to the Rows shelf. Change “discrete” data
following the same instructions.
4. Drag “Total shareholders’ equity” under “Measures” to the Rows shelf and change to
“discrete” data following the same instructions.
5. Create a calculated field by clicking the “Analysis” tab at the top of the screen and
selecting “Create Calculated field.” Name the calculation “Debt to Equity Ratio.” In
the Calculation Editor window drag “Total liabilities” from the Rows shelf, type a
division sign (/), and then drag “Total shareholders’ equity” from the Rows shelf to
the right of the division sign. Make sure the window says that the calculation is valid
and click OK.
6. Drag the newly created “Debt to Equity Ratio” to the Rows shelf. Click on the “Show
Me” tab in the upper right corner and select “side-by-side bars.” You should now see
a bar chart for Big Store and for Discount Goods that shows the Debt to Equity
Ratios for the years 2018 to 2021. Add labels to the bars by clicking on “Label”
under the “Marks” card and clicking the box “Show mark labels.” Format the labels
to Times New Roman, bold, black and 9-point font. Edit the color on the “Marks”
card if desired.
7. Change the title of the sheet to be “Long-Term Solvency: Debt to Equity Ratio” by
right-clicking and selecting “Edit title.” Format the title to Times New Roman, bold,
black and 15-point font. Change the title of “Sheet 1” to match the sheet title by
right-clicking, selecting “Rename” and typing in the new title.
8. Format all other labels to be Times New Roman, bold, black and 12-point font.
9. Click on the New Worksheet tab on the lower right (“Sheet 2” should open). Drag
“Company” and “Year” under “Dimensions” to the Rows shelf. Change “Year”
to discrete by right-clicking and selecting “discrete.” Select “Show Filter” and
uncheck all the years except 2018–2021. Click OK.
10. Drag “Net income / (loss)”, “Interest expense, and “Provision for income taxes”
under “Measures” into the Rows shelf. Change each to “discrete” data. Create a
calculated field “Times Interest Earned Ratio” following the same process above. In
the Calculation Editor window, type an open parenthesis, then drag “Net income /
(loss)” from the Rows shelf, type an addition sign (+), drag “Interest expense” from
the Rows shelf, type an addition sign, and then drag “Provision for income taxes”
from the Rows shelf and close the parenthesis. Next to the closed parenthesis, type
a division sign (/) and drag “Interest expense” from the Rows shelf next to the right
of the division sign. Make sure the window says that the calculation is valid and click
OK.
11. Right-click “Net income / loss,” “Interest expense,” and “Provision for income taxes”
and uncheck “Show in Header” so they no longer show on the canvas.
12. Drag the newly created “Times Interest Earned Ratio” into the Rows shelf. Click on
the “Show Me” and select “side-by-side bars.” Add labels to the bars by clicking on
“Label” under the “Marks” card and clicking the box “Show mark labels.” Format the
labels to Times New Roman, bold, black and 10-point font. Edit the color on the
Color marks card if desired.
13. Change the title of the sheet to be “Long-Term Solvency: Times Interest Earned
Ratio” by right-clicking and selecting “Edit title.” Format the title to Times New
Roman, bold, black and 15-point font. Change the title of “Sheet 2” to match the
sheet title by right-clicking, selecting “Rename” and typing in the new title.
14. Format all other labels to be Times New Roman, bold, black and 12-point font.
15. Once complete, save the file as “DA14_Your initials.twbx.”
Required:
Based upon what you find, answer the following questions:
A. Other things being equal, do both companies appear to have the ability to meet their
obligations as measured by the debt to equity ratio?
B. Based solely on the times interest earned ratios, do you reach the same conclusion
as in Requirement 1?
C. Is the margin of safety provided to creditors by Discount Goods improving or
declining in recent years as measured by the average times interest earned ratio?