Excel question

profilehenry0427
Untitledspreadsheet.xlsx
Home>Business & Finance homework help>Financial markets homework help>Excel question

Sheet4

Lowe's Corporation Valuations Project
Data taken from Sec 10-K's
Team 10
May 13 2022
Lowe's Companies, Inc.
Consolidated Statements of Earnings
(In millions, except per share and percentage data)
Y 2021 Y 2020 Y 2019 Y 2018 Y 2017
January 29, 2021 January 31, 2020 February 1, 2019 February 2, 2018 February 3, 2017
Amount Common size Amount Common size Amount Common size Amount Common size Amount Common size
Net sales $89,597 100.00% $72,148 100.00% $ 71,309 100.00% $ 68,619 100.00% $ 65,017 100.00%
Cost of sales 60,025 66.99% 49,205 68.20% 48,401 67.88% 46,185 67.31% 43,343 66.66%
Gross margin 29,572 33.01% 22,943 31.80% 22,908 32.12% 22,434 32.69% 21,674 33.34%
Expenses:
Selling, general and administrative 18,526 20.68% 15,367 21.30% 17,413 24.42% 14,444 21.05% 14,375 22.11%
Depreciation and amortization 1,399 1.56% 1,262 1.75% 1,477 2.07% 1,404 2.05% 1,453 2.23%
Operating income 9,647 10.77% 6,314 8.75% 4,018 5.63% 6,586 9.60% 5,846 8.99%
Interest - net 848 0.95% 691 0.96% 624 0.88% 633 0.92% 645 0.99%
Loss on extinguishment of debt 1,060 1.18% - - 0 464 0.68% - 0
Pre-tax earnings 7,739 8.64% 5,623 7.79% 3,394 4.76% 5,489 8.00% 5,201 8.00%
Income tax provision 1,904 2.13% 1,342 1.86% 1,080 1.51% 2,042 2.98% 2,108 3.24%
Net earnings $5,835 6.51% $4,281 5.93% $ 2,314 3.25% $ 3,447 5.02% $ 3,093 4.76%
Basic earnings per common share $ 7.77 $ 5.49 $ 2.84 $ 4.09 $ 3.48
Diluted earnings per common share $ 7.75 $ 5.49 $ 2.84 $ 4.09 $ 3.47
Cash dividends per share - $ 2.13 $ 1.85 $ 1.58 $ 1.33
Consolidated Balance Sheets
(In millions, except par value)
Y 2021 Y 2020 Y 2019 Y 2018 Y 2017
January 29, 2021 January 31, 2020 February 1, 2019 February 2, 2018 February 3, 2017
Common Size Common Size Common Size Common Size Common Size
Assets
Current assets:
Cash and cash equivalents $ 4,690 10.04% 716 1.81% 511 1.48% $ 588 1.67% 558 1.62%
Short-term investments 506 1.08% 160 0.41% 218 0.63% 102 0.29% 100 0.29%
Merchandise inventory - net 16,193 34.65% 13,179 33.39% 12,561 36.40% 11,393 32.28% 10,458 30.39%
Other current assets 937 2.00% 1,263 3.20% 938 2.72% 689 1.95% 884 2.57%
Total current assets 22,326 47.77% 15,318 38.81% 14,228 41.23% 12,772 36.19% 12,000 34.88%
Property, less accumulated depreciation 19,155 40.99% 18,769 47.55% 18,432.00 53.41% 19,721 55.88% 19,949 57.98%
Operating lease right-of-use assets 3,832 8.20% 3,891 9.86%
Long-term investments 200 0.43% 372 0.94% 256 0.74% 408 1.16% 366 1.06%
Deferred income taxes - net 340 0.73% 216 0.55% 294 0.85% 168 0.48% 222 0.65%
Goodwill 303 0.77% 303 0.88% 1,307 3.70% 1,082 3.14%
Other assets 882 1.89% 905 2.29% 995 2.88% 915 2.59% $ 789 2.29%
Total assets $ 46,735 100.00% $39,471 100.00% $ 34,508 100.00% $ 35,291 100.00% $34,408 100.00%
Liabilities and shareholders' (deficit)/equity
Current liabilities:
Short-term borrowings - 1,941 5.18% $ 722 2.34% $ 1,137 3.86% $ 510 1.82%
Current maturities of long-term debt $ 1,112 2.45% 597 1.59% 1,110 3.60% 294 1.00% 795 2.84%
Current operating lease liabilities 541 1.19% 501.00 1.34% - - - - - -
Accounts payable 10,884 24.03% 7,659 20.42% 8,279 26.82% 6,590 22.40% 6,651 23.78%
Accrued compensation and employee benefits 1,350 2.98% 684 1.82% 662 2.14% 747 2.54% 790 2.82%
Deferred revenue 1,608 3.55% 1,219 3.25% 1,299 4.21% 1,378 4.68% 1,253 4.48%
Other current liabilities 3,235 7.14% 2,581 6.88% 2,425 7.86% 1,950 6.63% 1,975 7.06%
Total current liabilities 18,730 41.35% 15,182 40.49% 14,497 46.97% 12,096 41.12% 11,974 42.80%
Long-term debt, excluding current maturities 20,668 45.63% 16,768 44.72% 14,391 46.63% 15,564 52.91% 14,394 51.45%
Noncurrent operating lease liabilities 3,890 8.59% 3,943 10.51% - - - - - -
Deferred revenue - extended protection plans 1,019 2.25% 894 2.38% 827 2.68% 803 2.73% 763 2.73%
Other liabilities 991 2.19% 712 1.90% 1,149 3.72% 955 3.25% 843 3.01%
Total liabilities 45,298 100.00% 37,499 100.00% 30,864 100.00% 29,418 100.00% 27,974 100.00%
Commitments and contingencies
-
Shareholders' equity:
Preferred stock - $5 par value, none issued 415
Common stock - $.50 par value; 22
Shares issued and outstanding 381 401 5,425
February 1, 2019
February 2, 2018 90 - - 11
February 3, 2017 1,117 1,727 3,452 $ 5,873
Capital in excess of par value (136) (136) (209) $35,291
Retained earnings 1,437 1,972 3,644
Accumulated other comprehensive income/(loss) $39,471 $ 34,508
Total shareholders' equity $ 46,735
Total liabilities and shareholders' equity

Lowes 10-k

Lowe's Corporation Valuations Project
Data taken from Sec 10-K's
Team 10
May 13 2022
Consolidated Balance Sheets
(In millions, except par value)
Y 2021 Y 2020 Y 2019 Y 2018 Y 2017
January 29, 2021 January 31, 2020 February 1, 2019 February 2, 2018 February 3, 2017
Assets
Current assets: $ 4,690 $716 $ 511 $ 588 $ 558
Cash and cash equivalents 506 160 218 102 100
Short-term investments 16,193 13,179 12,561 11,393 10,458
Merchandise inventory - net 937 1,263 938 689 884
Other current assets 22,326 15,318 14,228 12,772 12,000
Total current assets 19,155 18,769 18,432 19,721 19,949
Property, less accumulated depreciation 3,832 3,891 - 0 408 366
Operating lease right-of-use assets 200 372 256 168 222
Long-term investments 340 216 294 1,307 1,082
Deferred income taxes - net 882 905 303 915 789
Other assets 995 $ 35,291 $ 34,408
$ 46,735 $39,471 $ 34,508
Total assets
Liabilities and shareholders' (deficit)/equity $ 1,137 $ 510
Current liabilities: $1,941 $ 722 294 795
$ 1,112 597 1,110 6,590 6,651
Current maturities of long-term debt 541 501 - 0 747 790
Current operating lease liabilities 10,884 7,659 8,279 1,378 1,253
Accounts payable 1,350 684 662 1,950 1,975
Accrued compensation and employee benefits 1,608 1,219 1,299 12,096 11,974
Deferred revenue 3,235 2,581 2,425 15,564 14,394
Other current liabilities 18,730 15,182 14,497 803 763
Total current liabilities 20,668 16,768 14,391 955 843
Long-term debt, excluding current maturities 3,890 3,943 - 0 29,418 27,974
Noncurrent operating lease liabilities 1,019 894 827
Deferred revenue - Lowe's protection plans 991 712 1,149
Other liabilities 45,298 37,499 30,864
Total liabilities
- 0 - 0
Commitments and contingencies
Shareholders' (deficit)/equity: - 0
Preferred stock - $5 par value: Authorized - 5.0 - 0 - 415 433
million shares; Issued and outstanding - none 22 - 0
Common stock - $0.50 par value: Authorized - 5.6 5,425 6,241
billion shares; Issued and outstanding - 670 366 381 401 11 (240)
million and 731 million, respectively 90 - - 0 5,873 6,434
Capital in excess of par value 1,117 1,727 3,452 $ 35,291 $ 34,408
(Accumulated deficit)/retained earnings (136) -136 (209)
Accumulated other comprehensive loss 1,437 1,972 3,644
Total shareholders' (deficit)/equity $39,471 $ 34,508
Total liabilities and shareholders' $ 46,735
(deficit)/equity

Lowes Valuation

Income Statement
Fiscal year is February-January. All values USD Millions. 2022 2021 2020 2019 2018 5-year trend
Sales/Revenue 96,250 89,597 72,148 71,309 68,619
Sales Growth 7.43% 24.19% 1.18% 3.92% -
Cost of Goods Sold (COGS) incl. D&A 65,856 61,424 50,467 49,878 47,589
COGS excluding D&A 63,457 59,351 48,589 48,271 46,049
Depreciation & Amortization Expense 2,399 2,073 1,878 1,607 1,540
Depreciation 2,367 1,979 1,730 1,477 1,404
Amortization of Intangibles 32 94 148 130 136
COGS Growth 7.22% 21.71% 1.18% 4.81% -
Gross Income 30,394 28,173 21,681 21,431 21,030
Gross Income Growth 7.88% 29.94% 1.17% 1.91% -
Gross Profit Margin 31.58% - - - -
SG&A Expense 18,139 17,291 15,102 15,326 14,444
Other SG&A - 17,291 15,102 15,326 14,444
SGA Growth 4.90% 14.49% -1.46% 6.11% -
EBIT 12,255 10,882 6,579 6,105 -
Unusual Expense 162 2,295 265 2,087 464
Non Operating Income/Expense -38 - - -3 3
Non-Operating Interest Income 12 24 27 28 16
Interest Expense 859 872 718 649 652
Interest Expense Growth -1.49% 21.45% 10.63% -0.46% -
Gross Interest Expense 862 872 719 652 657
Interest Capitalized 3 - 1 3 5
Pretax Income 11,208 7,739 5,623 3,394 5,489
Pretax Income Growth 44.82% 37.63% 65.67% -38.17% -
Pretax Margin 11.64% - - - -
Income Tax 2,766 1,904 1,342 1,080 2,042
Income Tax - Current Domestic 2,626 2,003 1,203 1,237 1,986
Income Tax - Deferred Domestic 140 -99 139 -157 56
Other After Tax Income (Expense) -33 -24 -13 -7 -11
Consolidated Net Income 8,409 5,811 4,268 2,307 3,436
Net Income 8,409 5,811 4,268 2,307 3,436
Net Income Growth 44.71% 36.15% 85.00% -32.86% -
Net Margin 8.74% - - - -
Net Income After Extraordinaries 8,409 5,811 4,268 2,307 3,436
Net Income Available to Common 8,409 5,811 4,268 2,307 3,436
EPS (Basic) 12.03 7.75 5.49 2.84 4.09
EPS (Basic) Growth 55.27% 41.23% 93.17% -30.56% -
Basic Shares Outstanding 696 748 777 811 839
EPS (Diluted) - 7.75 5.49 2.84 4.09
EPS (Diluted) Growth 0.00% 41.24% 93.09% -30.54% -
Diluted Shares Outstanding 699 750 778 812 840
EBITDA 14,654 12,955 8,457 7,712 8,126
EBITDA Growth 13.11% 53.19% 9.66% -5.09% -
EBITDA Margin - - - -
EBIT 12,255 10,882 6,579 6,105 -
Balance Sheet
Assets
Fiscal year is February-January. All values USD Millions. 2022 2021 2020 2019 2018 5-year trend
Cash & Short Term Investments 1,404 5,196 876 729 690
Cash Only 1,133 4,690 716 511 588
Short-Term Investments 271 506 160 218 102
Cash & Short Term Investments Growth -72.98% 493.15% 20.16% 5.65% -
Cash & ST Investments / Total Assets 3.03% 10.76% 2.14% 2.10% 1.96%
Inventories 17,605 16,193 13,179 12,561 11,393
Finished Goods 17,605 16,193 13,179 12,561 11,393
Other Current Assets 1,051 937 1,263 938 689
Miscellaneous Current Assets 1,051 937 1,263 938 689
Total Current Assets 20,060 22,326 15,318 14,228 12,772
Net Property, Plant & Equipment 23,179 22,987 22,560 18,432 19,721
Property, Plant & Equipment - Gross 41,067 40,534 39,836 35,863 36,940
Buildings 18,433 18,090 17,862 18,052 18,521
Land & Improvements 7,278 7,315 7,200 7,196 7,414
Machinery & Equipment 10,533 10,466 10,377 10,090 10,475
Construction in Progress 715 831 506 525 530
Accumulated Depreciation 17,888 17,547 17,276 17,431 17,219
Total Investments and Advances 247 200 372 256 408
Other Long-Term Investments 247 200 372 256 408
Intangible Assets 833 311 562 303 1,307
Net Goodwill 311 311 303 303 1,307
Net Other Intangibles - - 259 - -
Other Assets 157 571 443 995 915
Tangible Other Assets 157 571 443 995 915
Total Assets 46,330 48,290 40,990 34,641 35,291
Assets - Total - Growth -4.06% 17.81% 18.33% -1.84% -
Asset Turnover 2.03 - - - -
Return On Average Assets 17.77% - - - -
Liabilities & Shareholders' Equity
All values USD Millions. 2022 2021 2020 2019 2018 5-year trend
ST Debt & Current Portion LT Debt 1,504 1,653 3,039 1,832 1,431
Short Term Debt - 541 2,442 722 1,137
Current Portion of Long Term Debt 868 1,112 597 1,110 294
Accounts Payable 11,354 10,884 7,659 8,279 6,590
Accounts Payable Growth 4.32% 42.11% -7.49% 25.63% -
Income Tax Payable 128 168 - - -
Other Current Liabilities 6,682 6,025 4,484 4,386 4,075
Dividends Payable 537 440 420 385 340
Accrued Payroll 1,561 1,350 684 662 747
Miscellaneous Current Liabilities 4,584 4,235 3,380 3,339 2,988
Total Current Liabilities 19,668 18,730 15,182 14,497 12,096
Current Ratio 1.02 1.19 1.01 0.98 1.06
Quick Ratio 0.12 0.33 0.14 0.11 0.11
Cash Ratio 0.07 0.28 0.06 0.05 0.06
Long-Term Debt 27,880 24,558 20,711 14,391 15,564
Long-Term Debt excl. Capitalized Leases 23,319 20,104 16,156 13,682 14,718
Non-Convertible Debt 23,319 20,104 16,156 13,682 14,718
Capitalized Lease Obligations 540 564 612 709 846
Deferred Taxes -164 -340 -216 -294 -168
Deferred Taxes - Credit 1,690 1,555 1,519 133 -
Deferred Taxes - Debit 1,854 1,895 1,735 427 168
Other Liabilities 1,908 2,010 1,606 1,976 1,758
Other Liabilities (excl. Deferred Income) 781 991 712 1,149 955
Deferred Income 1,127 1,019 894 827 803
Total Liabilities 51,146 46,853 39,018 30,997 29,418
Total Liabilities / Total Assets 110.39% 97.02% 95.19% 89.48% 83.36%
Common Equity (Total) -4,816 1,437 1,972 3,644 5,873
Common Stock Par/Carry Value 335 366 381 401 415
Additional Paid-In Capital/Capital Surplus - 90 - - 22
Retained Earnings -5,115 1,117 1,727 3,452 5,425
Cumulative Translation Adjustment/Unrealized For. Exch. Gain -41 -37 -115 -209 11
Other Appropriated Reserves 5 -99 -21 - -
Common Equity / Total Assets -10.39% 2.98% 4.81% 10.52% 16.64%
Total Shareholders' Equity -4,816 1,437 1,972 3,644 5,873
Total Shareholders' Equity / Total Assets -10.39% 2.98% 4.81% 10.52% 16.64%
Total Equity -4,816 1,437 1,972 3,644 5,873
Liabilities & Shareholders' Equity 46,330 48,290 40,990 34,641 35,291