Chapter 16 Data Analytics Case Deferred Taxes and the Tax Cuts and Jobs Act of 2017 LO164

Data analytics is the process of examining data sets in order to draw conclusions about the information they contain. If you havent completed any of the prior data analytics cases, follow the instructions listed in the Chapter 1 Data Analytics case to get set up. You will need to watch the videos referred to in the Chapters 1 - 3 Data Analytics cases. No additional videos are required for this case. All short training videos can be found here.

Data Analytics Case Deferred Taxes and the Tax Cuts and Jobs Act of 2017

In the Data Analytics Cases in the previous chapter, we applied Tableau to examine a data set and create charts to examine two (hypothetical) publicly traded companies: GPS Corporation and Tru, Inc. as to their pattern of leasing facilities, their transition to the new lease accounting standard in 2019, and the effect of that transition on debt covenants. In this case, we examine the effect of the Tax Cuts and Jobs Act of 2017 on these companies operations and financial positions.

Resources:

Download the "GPS_Tru_Financials.xlsx" Excel file and save it to the computer on which you will be using Tableau.

For this case, you will create a calculations to produce the effective tax rate to allow you to compare and contrast the effect of the 2017 Tax Act on the two companies.

After you view the training videos, follow these steps to create the charts you'll use for this case:

• Open Tableau and connect to the Excel spreadsheet you downloaded.
• Starting on the Sheet 1 tab, drag Company and Year under Dimensions to the Columns shelf. Change Year to discrete by right-clicking and selecting Discrete.
• Drag Provision for income tax and Deferred income taxes under Measures 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 of the years on the Color marks card if desired by dragging Year on to the color card.
• Change the title of the sheet to be Provision for Income Taxes and Deferred Tax Liability Trend 2012-2021 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.
• On the Sheet 2 tab, follow the procedure above for the company and year.
• Drag Provision for income tax and Income from continuing operations under Measures into the Rows shelf. Change each to Discrete data and uncheck Show Header.
• Create a calculated field by clicking the Analysis tab at the top of the screen and selecting Create Calculated field. Name the calculation Effective Tax Rate. In the calculation box, drag the following from the Rows shelf: Provision for income taxes," type a division sign and open parenthesis, "Income from continuing operations, type an addition sign, Provision for income taxes, and type a closed parenthesis. Make sure the window says that the calculation is valid and click OK.
• Drag the newly created Effective Tax Rate to 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.
• Change the title of the sheet to be Effect of the Tax Cuts and Jobs Act of 2017 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.
• Format all other labels to be Times New Roman, bold, black and 12-point font.
• Once complete, save the file as DA16_Your initials.twbx.

Required:

For each of the two companies in the ten-year period, 2012-2021, use Tableau to calculate and display the trends for:

• (a) the provision for income taxes
• (b) the deferred tax liability, and
• (c) effective tax rate.

Based upon what you find, answer the following questions:

1. Is Tru, Inc.s provision for income tax (a) higher, (b) lower, or (c) roughly the same over the period 2018-2021 than in previous years?
2. Did Tru, Inc.s deferred tax liability appear to benefit from the Tax Act?
3. What is the effective tax rate for GPS (provision for income tax divided by [net income plus provision for income tax]) in 2017? (Enter your answer as a whole percentage.)
4. What is the effective tax rate for GPS (provision for income tax divided by [net income plus provision for income tax]) in 2018? (Enter your answer as a whole percentage.)

		Answer
1.	Change in Tru, Inc. provision for income tax over years 2018-2021
2.	Did Tru, Inc.'s deferred tax liability benefit from the Tax Act?
3.	Effective tax rate for GPS in 2017	%
4.	Effective tax rate for GPS in 2018	%

0 ($ in 000s) Company GPS GPS GPS GPS GPS GPS GPS GPS GPS GPS Tru Tru Tru Tru Tru Tru Cash and cash Year equivalents 2012 6,867 2013 8,705 2014 9,135 2015 7,281 2016 7,781 2017 6,550 2018 7,395 2019 7,907 2020 8,569 2021 6,851 2012 7,257 2013 4,871 2014 7,727 2015 10,122 2016 8,859 2017 9,929 2018 8,501 2019 7,906 2020 8,145 2021 5,922 Assets of Prepaid Receivables, discontinued expenses and Total current net Inventory operations other assets 5,835 43,046 1,941 57,689 5,624 44,469 0 1,441 60,239 6,778 45,141 0 2,224 63,278 6,677 44,858 460 1,909 61,185 6,768 43,803 0 1,588 59,940 5,937 40,714 0 1,774 54,975 5,089 36,318 131 2,960 51,893 4,144 32,713 140 1,473 46,377 5,986 26,523 -211 3,625 44,491 3,613 32,724 - 150 2,958 45,996 3,908 36,929 307 4,889 53,289 6,294 45,044 1,431 5,160 62,800 3,438 37,724 5,924 7,796 62,609 1,042 28,604 3,524 8,143 51,436 2,306 35,124 18,280 8,267 72,836 1,235 26,343 27,555 8,044 73,107 2,663 27,347 30,204 7,787 76,502 3,258 31,907 29,573 9,240 81,884 2,746 32,517 46,730 7,158 97,295 4,964 42,335 18,540 6,464 78,225 Tru Tru Tru Tru O O O O O O O O O O O O O O Buildings and Fixtures and Land improvements equipment 26,026 117,567 23,334 25,659 115,907 23,005 25,722 116,194 23,061 25,098 113,373 22,502 24,045 108,615 21,557 22,475 101,526 20,150 21,545 97,323 19,316 19,988 100,290 17,920 26,931 121,653 24,145 18,748 84,688 16,808 27,138 117,116 24,458 27,222 113,701 23,764 27,231 115,719 23,760 27,302 135,179 23,107 27,582 122,624 23,831 27,209 141,383 22,849 26,347 102,810 21,951 25,747 95,400 21,080 31,267 84,228 34,105 22,949 87,447 24,829 Computer hardware Less and Construction- accumulated software in-progress depreciation Goodwill 10,770 1,795 -71,782 17,037 10,617 1,770 -66,787 16,695 10,644 1,774 -63,115 18,102 10,385 1,731 -57,725 19,510 9,950 1,658 -51,896 20,497 9,300 1,550 -45,399 20,651 8,915 1,486 -43,486 16,763 8,271 2,478 -36,649 25,026 11,144 3,457 -58,733 21,028 7,758 1,993 -24,288 13,524 11,782 889 -55,391 3,480 11,631 1,400 -56,613 3,760 11,347 1,884 -58,813 5,391 10,644 364 -69,516 4,921 11,409 5,227 -60,040 4,987 10,969 4,280 -55,031 4,587 11,258 2,520 -51,356 4,440 11,444 2,331 -46,600 3,684 12,406 3,083 -65,861 3,883 9,504 1,544 -45,057 2,210 Property Right of use asset - Right of use asset - under capital Pension Plan operating net, finance, net lease Assets 6,468 20,078 6,345 19,386 0 2,375 19,582 0 2,543 20,804 2,752 22,742 2,721 19,728 0 2,780 19,470 4,015 1,503 0 22,514 4,513 1,671 0 25,456 3,417 1,777 0 26,011 5,498 16,944 5,330 16,829 2,256 15,648 2,340 17,036 2,504 18,708 2,612 19,896 o 2,502 20,823 4,030 2,812 21,642 4,022 3,011 0 22,617 3,875 3,034 0 23,621 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 1,231 309 2,236 2,219 334 2,270 1,556 1,322 578 O O O Total Current liabilities of discontinued operations 0 0 0 89 0 0 Total noncurrent assets Total assets 161,214 218,903 158,728 218,967 160,010 223,288 164,370 225,555 165,907 225,847 158,159 213,134 148,240 200,133 169,299 215,676 186,051 230,542 153,860 199,856 152,015 205,304 148,256 211,056 144,733 207,342 153,614 205,050 159,051 231,886 179,087 252,194 143,565 220,067 143,127 225,011 134,083 231,378 134,533 212,758 47 92 99 Accounts payable 23,102 20,330 25,690 19,492 9,765 20,147 18,005 10,329 15,197 14,061 24,651 17,929 22,319 15,835 19,813 19,158 20,019 20,724 22,172 20,521 Accrued Accrued income liabilities taxes 20,654 921 19,607 521 19,152 1,021 18,793 966 18,808 2,211 18,180 1,164 18,701 157 18,734 1,347 23,894 243 18,200 984 16,609 810 22,189 458 14,544 898 19,822 850 22,913 1,946 28,116 1,024 6,997 1,341 12,977 1,185 14,130 214 5,522 866 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 o 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 1,931 384 0 2,253 172 0 3,435 184 0 2,970 200 0 4,001 199 0 1,529 208 0 2,977 0 301 602 2,966 0 392 583 4,113 0 400 533 2,357 Short-term Debt to borrowings Equity 1,099 1.718115 2,708 1.618874 1,592 1.598271 7,670 1.773018 6,805 1.776789 4,047 1.819013 1,031 1.862641 523 2.074984 914 2.269199 210 2.108377 988 1.904694 1,136 1.884151 2,098 1.939625 1,598 1.842474 1,009 1.98395 2,044 1.821943 1,198 1.980673 1,671 2.204869 929 2.138373 1,439 2.077653 current liabilities 48,690 46,424 51,671 50,720 42,499 45,636 42,150 35,421 46,116 37,347 45,385 44,349 43,466 41,259 49,947 52,171 32,928 40,507 42,620 31,728 102 0 0 0 0 65 102 188 81 87 90 Lease liability - finance lease 0 0 0 0 0 0 0 0 Projected Benefit Obligation 22,309 21,540 21,757 23,115 23,939 20,767 20,495 22,514 24,243 24,309 18,827 18,699 17,387 18,929 20,787 21,166 21,919 22,781 23,317 24,103 Long-term debt 52,032 54,255 52,512 58,085 66,516 59,696 58,195 74,563 76,696 61,958 56,906 63,269 65,914 61,904 73,270 79,429 83,134 80,406 80,438 77,192 Capital lease Lease liability - liability operating lease 6,003 0 5,816 0 2,606 0 2,788 0 3,023 0 3,009 0 3,150 0 0 6,215 0 5,313 0 4,017 5,283 0 5,118 0 2,293 0 2,453 0 2,660 0 2,648 0 2,772 0 4,034 3,971 3,807 3,108 3,110 3,305 0 Deferred income taxes 9,334 7,321 8,805 9,508 8,536 8,421 6,231 3,716 4,545 4,624 8,223 6,443 7,748 8,367 7,511 7,410 5,483 4,308 4,379 3,806 Total Total liabilities noncurrent (Cur + Common liabilities noncur) stock 89,678 138,368 305 88,932 135,356 317 85,680 137,351 323 93,496 144,216 323 102,015 144,513 332 91,892 137,528 342 88,071 130,221 352 110,116 145,537 378 113,907 160,023 365 98,214 135,560 373 89,238 134,624 204 93,529 137,878 222 93,342 136,808 236 91,653 132,912 236 104,228 154,175 240 110,654 162,825 249 113,308 146,236 262 114,295 154,802 276 115,033 157,653 274 111,900 143,628 278 Accumulated other Total Additional Retained comprehensive shareholders' paid-in capital earnings income(loss) equity 5,108 89,354 -14,232 80,535 5,436 90,021 -12,163 83,611 6,914 85,777 -7,077 85,937 7,455 76,566 -3,005 81,339 7,766 72,978 258 81,334 6,817 68,691 -244 75,606 5,076 63,967 517 69,912 4,551 66,357 -1,147 70,139 5,377 64,645 133 70,520 2,138 62,433 -648 64,296 25,160 48,151 -2,836 70,680 23,769 41,391 7,796 73,178 21,773 42,862 5,662 70,533 19,867 55,996 -3,960 72,138 17,444 58,467 1,560 77,711 15,498 66,596 7,027 89,369 14,716 61,436 -2,582 73,831 12,973 60,542 -3,582 70,209 11,728 65,878 -4,154 73,726 10,942 59,103 -1,193 69,130 0 0 O O O 0 0 0 0 2,767 2,929 2,993 Difference Change in accrued liabilities (CF) 137 -1,047 O O O O O O -455 -359 15 -628 521 O O O O O o 33 Total liabilities Check Total and equity Assets 218,903 218,903 218,967 218,967 223,288 223,288 225,555 225,555 225,847 225,847 213,134 213,134 200,133 200,133 215,676 215,676 230,542 230,542 199,856 199,856 205,304 205,304 211,056 211,056 207,342 207,342 205,050 205,050 231,886 231,886 252,194 252,194 220,067 220,067 225,011 225,011 231,378 231,378 212,758 212,758 Change in Change in Change in accrued accounts accounts income taxes receivable (CF) payable (CF) (CF) 3,444 -3,942 -492 -211 1,423 -400 1,154 672 500 -101 -283 -55 91 -1,055 1,245 -831 -3,089 -1,047 -848 -4,396 -1,007 -945 -3,605 1,190 1,842 -6,191 -1,104 -2,373 6,202 741 294 4,205 -174 2,386 8,115 -352 -2,856 -7,320 440 -2,396 -9,120 -48 1,263 6,520 1,096 -1,070 -8,781 -921 1,428 1,004 317 595 4,560 -156 -513 610 -972 2,219 9,818 652 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 5,160 -5,694 -1,591 5,580 -7,645 5,278 3,091 5,203 -21,119 5,980 1,153 -8,608 Change in Other Net cash flows Inventories operating from operating (CF) activities activities 7,122 1,455 1,423 14,829 672 16,856 -283 -2,234 16,959 -1,055 20,428 -3,089 13,718 -4,396 20,563 -3,605 497 23,639 -6,191 -1,591 25,637 6,202 -2,332 15,725 4,205 294 13,851 8,115 -938 24,056 -7,320 -454 11,361 -9,120 4,610 26,504 6,520 21,074 -8,781 1,569 23,349 1,004 0 -2,221 4,560 -39 22,910 610 5,630 24,959 9,818 -1,288 3,187 850 Other financing activities Stock option exercises 479 719 600 0 0 0 0 0 426 282 0 0 0 0 0 Proceeds from Investment disposal of and business Other property and acquisitions, investing equipment net of cash activities 1,118 0 -122 881 -5,000 -79 1,241 -10,000 -192 727 -5,800 -138 532 0 -271 580 0 -131 489 -5,558 219 1,002 -15,431 -438 236 -11,043 -101 2,378 0 -694 0 124 -6,547 333 191 2,173 0 -7,133 -1,427 880 -5,689 -6,893 0 -8,551 -4,138 151 -480 0 13 -209 147 13 -1,283 -1,928 -1,115 -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 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 Payments of long- term debt -2,055 -4,432 -3,904 -4,968 -1,478 -4,584 -4,080 -6,033 -5,475 -9,069 -11,738 -378 -9,217 -1,391 -6,796 -13,889 -10,040 -8,946 -14,504 -9,699 0 0 O O O Net change in Proceeds from Purchase of short-term issuance of long- Treasury borrowings term debt stock 676 20,672 -8,298 1,609 10,495 -4,112 -1,116 0 6,078 3,866 -1,758 211 3,925 5,050 -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 Dividends paid -3,306 -3,433 -3,488 -4,035 -5,048 -5,048 -5,237 -5,317 -5,421 -5,446 -1,591 -1,600 -1,600 -1,600 -1,600 -1,710 -1,710 -1,820 -1,820 -1,820 Net cash flows from financing activities 8,168 846 -7,908 1,367 -7,791 -657 -15,713 3,412 1,520 -17,409 -9,253 -20,976 -10,362 8,042 10,375 8,183 -17,694 -11,964 -28,405 -14,030 0 0 -5,000 -11,023 5,222 0 o 15,009 0 0 982 1,333 1,658 2,027 1,600 396 1,307 209 Woo 0 0 o 0 0 0 Sales Net change revenue 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 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 6 572,852 567,419 578,167 560,083 544,518 514,401 485,992 479,084 627,955 446,296 341,836 358,908 355,163 352,340 356,546 344,654 344,188 327,881 415,012 320,982 Selling, general Credit Less Depreciation and card Less Sales Discount Cost of Gross and administrative Net pension revenue returns on Sales Net sales sales margin amortization expenses cost 4,556 -36,099 -55,436 485,873 361,256 124,617 4,211 101,593 535 3,516 -43,883 -44,922 482,130 360,984 121,146 4,411 97,279 517 3,422 -43,883 -52,055 485,651 365,086 120,565 4,292 93,056 522 3,218 -42,104 -44,903 476,294 358,069 118,225 4,160 90,665 555 3,047 -38,095 -40,819 468,651 352,297 116,354 4,007 87,954 575 3,096 -37,728 -32,819 446,950 -32,819 446,950 335,127 111,823 4,104 84,516 498 2,897 -41,057 -25,983 421,849 314,946 106,903 3,948 81,762 492 2,953 -40,108 -33,844 408,085 304,444 103,641 3,828 76,157 540 3,246 -52,332 -51,933 526,936 393,308 133,629 4,074 99,079 582 2,494 -41,860 -23,612 383,318 285,895 97,423 3,433 70,815 583 0 -30,981 -1,988 308,867 225,530 83,337 6,355 55,355 452 0 -28,776 -2,199 327,933 228,284 99,649 9,836 60,787 449 0 -32,047 -370 322,747 231,822 90,924 9,462 60,473 417 0 0 -32,052 -3,493 316,796 287,872 28,924 8,871 313 454 5,960 -34,221 -2,503 325,782 291,436 34,347 9,084 646 499 6,218 -36,966 -3,394 310,511 279,240 31,271 9,471 -277 508 7,129 -43,507 -8,299 299,511 266,435 33,076 9,262 163 526 8,542 -39,182 -6,766 290,476 257,502 32,973 8,991 1,557 547 7,244 -43,879 -7,321 371,056 336,381 34,674 9,911 685 560 6,776 -47,930 -9,288 270,540 244,558 25,982 7,198 336 578 : -2,382 5,652 -7,983 22,533 18,467 31,038 -20,418 10,916 -6,944 -13,413 Pension non- service cost Pension service cost Basic EPS Market Diluted price per EPS share components Gain on sale 323 521 0.7% 1.8% 1.14 1.18 1.15 1.20 300 1.37 17.7% 19.6% 20.8% 263 1.38 1.37 1.47 1.36 1.46 272 288 1.34 1.53 1.35 1.54 409 550 564 83 -10 18 277 278 Provision for Interest Interest income income expense taxes 2,267 2,102 6,204 2,469 2,239 6,558 2,529 2,446 7,985 2,351 2,456 8,105 2,268 2,338 7,968 2,165 2,493 7,944 3,255 2,425 4,480 1,884 1,841 4,801 1,246 2,849 5,871 1,489 1,706 4,600 0 3,101 5,760 0 3,493 7,120 0 3,919 5,351 0 3,808 5,342 4,472 6,838 13 4,036 6,287 44 4,567 3,866 109 4,341 3,617 55 5,632 3,733 188 3,998 2,833 1.63 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 14,899 16,053 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 18.4% 15.7% 17.6% 18.3% 19.7% 22.4% 27.3% 27.2% 31.3% 30.3% 17.8% 18.3% 1.66 2.03 260 25.1% 18.1% 29.4% 33.7% 36.4% 24.5% 19.6% 533 50 220 44.50 44.56 46.23 44.50 47.10 41.54 48.32 48.41 52.21 47.45 8.30 9.89 7.88 8.08 8.23 8.05 10.24 11.12 11.54 6.8% 6.0% 6.8% 6.6% 7.1% 8.0% 9.5% 8.8% 9.6% 9.7% 6.1% 6.3% 4.9% 4.9% 4.6% 4.5% 5.4% 6.0% 5.9% 5.9% 6.8% 7.5% 7.5% 9.0% 6.4% 10.3% 11.0% 11.1% 7.9% 6.7% 11.4% 5.5% 12.9% 9.1% 9.3% -1.0% 10.2% 10.8% 1.5% 0 2.02 1.59 0.39 0.42 0.36 0.32 0.38 0.34 1.60 0.40 0.43 0.37 0.33 0.39 0.36 32.9% -4,756 0 14.4% 0 -716 o 0 16.1% 36.7% 27.1% 26.1% -3.0% 32.6% 33.9% 4.6% 14.1% 13.8% 12.6% 16.2% 19.4% 38 0 0.45 0.46 0 488 511 488 542 36 72 36 0 0.43 0.43 0.33 0.44 0.44 0.34 18.6% 0 9.93 18.3% 6.1% 5.6% 18.8% 17.0%