write a two page report based on the MACY’s excel data that I posted.

2 PAGE REPORT – FINANCE

I want you to write a two page report based on the MACY’s excel data that I posted.

I have posted 2 sample reports so you know what is expected in the report.

SAMPLE1.docx

PepsiCo Analyst Report

Overview:

Overall, after our research and studying the fortune 500 company PepsiCo. we have concluded that there will not be any staggering fluctuations in the business. We issue a HOLD recommendation on PepsiCo. In our opinion the ideal position in this should be to hold as, the growth is negligible right now, the future is unpredictable seeing the current situation, it is better to hold the current value. (PEP) with a target price of $156.00, with a current stock price of $134.66 and a low of $130.00. Pepsico. has had a steady foothold in the industry for years and this remains today. We do not see this changing and in light of the economy today, we do not see anything but steadiness from Pepsi. Key points in factoring the result of the continued steady success of PepsiCo are it is a mature company with brand recognition and a quality business model to manage income and expenses, steady sales and operating income over the years, and a full line of branded products that offer competitive pricing while cutting costs.

 

Investment Summary:

PepsiCo’s portfolio contains 23 product brands and sells in more than 200 countries. Doritos, Mountain Dew, Tropicana, Lays, and Gatorade are only a few of PepsiCo’s popular Trademarks. Pepsico continues to be a growing company despite the current economic state. Shelf stable foods and beverages are in high demand due to COVID-19 as people stock up on groceries to self isolate, putting high pressure on the supply chain and manufacturing. Furthermore, their recent purchase of Rockstar gives them a wider range of beverages and a direct connection into the energy drink market. One of the biggest key driving factors of steady success for Pepsi is their brand. Their brand recognition is second to none and they have a very loyal consumer base. When it comes to brand quality resulting in sales Pepsi is at the top of the list. PepsiCo has a large operating income year by year and we expect this to remain the same. Another huge factor is their borrowing rate as of recent years has been relatively low which is a good indication of satisfying cash flow. This is a result of their large portfolio of products that can compete at a relative price point. The new acquisition of Rockstar and the increased popularity of energy drinks overall should result in an optimistic outcome for the company and we expect should pay dividends in the near future.

 

Financial Analysis:

With our five year forecast we expect revenues to steadily increase slowly. Revenue from 2014 through 2019 has always stayed above $62 billion but we predict revenues reaching $70 billion and above. Total Assets numbers have only increased over the past 3 years and short and long term debt has decreased. This is a wonderful indication of the current state of PepsiCo. Pepsi is also facing pressure from their significant competitor of Coca Cola brand but this is nothing unusual for PepsiCo. Increases in sales/gross profit/and net income are slightly up but for such a mature company as PepsiCo this is at no surprise. PepsiCo is slightly better than industry average when it comes to debt-to-equity ratio meaning that cash still drives the business and they rely heavily on sales which have been steady.

 

 

 

Valuation:

We used three different valuation methods in which to support our position, P/E, P/S and EV/EBITIDA multiples. Using the average value for each method allowed us to calculate a good estimate for Pepsi in relation to comparable firms.  The marginally small increase within the past year for PepsiCo signals small but constant growth. PepsiCo can grow and expand as it acquires more brands and products and keeps up with customer demand. Incorporating each of the models allowed us to get a general sense of where the company is at currently and where it may be headed. Just as most mature long standing companies fluctuations to these models at times do not differentiate much. That is why it is best to get an average which can compare to the overall industry it presides in. The P/E multiple method was determined to be the best model that represents our forecast assuming that the market is pricing the stocks of other firms efficiently. P/E multiple method should provide a reasonable valuation while other methods are dependent on a larger set of assumptions which we tried to avoid. The Prices with methods used are looking static, and are not showing much growth, in situations like this we cannot predict profit for the future more accurately. We are assuming the profit will prevail in  future as well with very negligible growth rate as we can see from Common Size Statements the growth in earning is either negative or negligible , if we take average of last years it also come to negative  hence based on this, we can say it is better to hold it.

 

Investment Risks:

 

The carbonated beverage industry has in fact however taken a few hits in recent years due to the increased awareness of health implications. This is a factor to be cautious of when financing any beverage company of this nature. Society has a tendency to go with trends and when a company offers a product that is detrimental to health it always has risk of being unpopular. However, PepsiCo. has such a large consumer base and ones who are loyal to the brand to allow them to be successful today and for years to come. In a highly competitive industry, sales are mostly dependent on the purchasing power of consumers when the economy is impacted by an epidemic. Consumers will likely shift toward a firm that offers their needs at the cheapest prices. Failure to respond to changes in the economy exposes PepsiCo to risks like, losing its loyal customers and declining sales. This is particularly important to keep close watch on during these current times in our economy. Increased regulatory scrutiny and or legislation is another risk facing PepsiCo. A proposed soda tax aimed at curbing obesity could put increased pressure on the company. There are certain drinks that could be eligible for this additional tax depending on their sugar content. And mostly all PepsiCo beverages fall under this category.

MACYS449.xlsx

MACY’S Financials

Assumptions 0 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024 Beyond
Sales growth 1% 1% -4% -4% -4% 0% 1% 2% 2.5% 2.5% 2.5%
Terminal Growth Rate 2.5%
Interest Expense – Based on Debt * Cost of Debt % 3.38% 3.38% 3.38% 3.38% 3.38%
All Others Grow at the same rate as Sales
Highlighted yellow cells are numbers that stood out
P/E Multiples Valuation
Benchmark P/E 26.44
Earnings available to common shareholders 1,738,000
Equity intrinsic value 45944030.00
Shares 309605426 remember to adjust for millions!
Implied price per share 0.15
P/S Multiples Valuation
Benchmark P/S 0.83
Sales Revenue $ 24,971,000
Equity intrinsic value 20,788,357.50
Shares 309605426
Implied price per share 0.07
EV/EBITDA Multiples Valuation
Benchmark EV/EBITDA 14.24
EBITDA 2,700,000
Enterprise value 38,444,625.00
Market value of non-operating assets 1,162
Market value of debt 6851716.33039058
Equity intrinsic value 31,594,070.67
Shares 309605426
Implied price per share 0.10
Equity Cash Flow Model: Discounting Equity Cash Flows at Cost of Equity
Ke (Cost of Equity) 9.12%
1 2 3 4 5
Equity cash flow (FCFE) 1,584 1,570 1,539 1,539 1,540
Terminal value 23846
PV 1452 1319 1185 1086 995 15415
Equity value of operations 21450678
Market value of non-operating assets 1,162
Equity intrinsic value 21,451,840
Shares 309605
Implied price per share 69.29
Corporate Valuation Model: Discounting Free Cash Flows at WACC
WACC (Weighted Average Cost of Capital) 6.76%
1 2 3 4 5
Free cash flow (FCFF) 1,325 1,287 1,208 1,206 1,204
Terminal value 28928
PV 1241 1129 992 928 868 20854
Value of operations 26012279
Market value of debt 6851716.3
Market value of non-operating assets 1,162
Equity intrinsic value 19161725
Shares 309605
Implied price per share 61.89
Adjusted Present Value Model: Discounting Unlevered Cash Flows at Unlevered Cost of Capital
Ku (Unlevered Cost of Capital) 6.88%
1 2 3 4 5
FCFF 1,325 1,287 1,208 1,206 1,204
Terminal value 28174
PV 1240 1127 989 924 863 20202
Unlevered value of operations 25344648
1 2 3 4 5
Interest tax savings = IntExp * tax Rate – 47.99 – 47.62 – 47.25 – 46.89 – 46.52
Terminal value – 1,089.01
PV – 44.90 – 41.69 – 38.71 – 35.93 – 33.36 – 780.86
Value of tax shield – 975,444
Market value of debt 6851716.33039058
Market value of non-operating assets 1,162
Equity intrinsic value 31,219,758.30
Shares 309605
Implied price per share 100.84
Cash Flow Computations 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024
Year Ended… 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024
Shares Outstanding
Figures in… Millions Millions Millions Millions Millions Millions Millions Millions Millions Millions Millions
OpInc 2,678 2,800 2,039 1,371 1,864 1,738 1,739 1,740 1,741 1,742 1,743
Tax Rate (t) 30% 31% 30% 25% -2% 19% 18% 18% 18% 18% 18%
NOPAT = OpInc x (1-t) 1874 1936 1431 1025 1903 1416 1418.02 1420.05 1422.1 1424.15 1426.2
Dep & Amort Exp 1,012 1,031 1,047 1,044 946 947 1,008 1,006 998 990 993
OCF = NOPAT + Dep & Amort 2,886 2,967 2,478 2,069 2,849 2,363 2,426 2,426 2,420 2,414 2,419
ΔNOWC 63 -283 53 -341 -13 -68 13 19 32 33 34
ΔGFA 754 906 877 459 646 927 1,088 1,119 1,180 1,175 1,181
FCFF = OCF – ΔNOWC – ΔGFA 2,069 2,344 1,548 1,951 2,216 1,504 1,325 1,287 1,208 1,206 1,204
Interest Expense, net (390) (395) (363) (367) (321) (261) (260) (259) (258) (257) (256)
FCFF 2,069 2,344 1,548 1,951 2,216 1,504 1,325 1,287 1,208 1,206 1,204
ΔDebt -92 519 -238 -433 -701 -1,153 47 71 121 124 127
Int(1-t) -273 -273 -255 -274 -328 -213 -212 -211 -211 -210 -209
PfdCF
FCFE = FCFF + ΔDebt – Int(1-t) – PfdCF 2,250 3,136 1,565 1,792 1,843 564 1,584 1,570 1,539 1,539 1,540
FCFF 2,069 2,344 1,548 1,951 2,216 1,504 1,325 1,287 1,208 1,206 1,204
Int(t) -117 -122 -108 -93 7 -48 -48 -48 -47 -47 -47
UCF = FCFF + Int(t) 1,952 2,222 1,440 1,858 2,223 1,456 1,277 1,240 1,160 1,159 1,157
Consolidated Statements of Income – USD ($) $ in Thousands 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024
Income Statement [Abstract]
Net sales $27,686 $ 27,931 $ 28,105 $ 27,079 $ 25,908 $ 24,939 $ 24,971 24972 24973 24974 24975 24976
Credit Card Revenues, Net 656 702 768 769 770 771 772 773
Cost of sales -16,538 (16,725) (16,863) (16,496) (15,666) (15,181) (15,215) -15214 -15213 -15212 -15211 -15210
Gross margin 11,148 11,206 11,242 10,583 1 2 3 4 5
Selling, general and administrative expenses -8,482 (8,440) (8,355) (8,468) (9,257) (8,954) (9,039) -9038 -9037 -9036 -9035 -9034
Gains on sale of real estate 212 209 544 389 390 391 392 393 394
Restructuring, impairment, store closing and other costs -5 (88) (87) (288) (479) (186) (136) -135 -134 -133 -132 -131
Settlement charges 0 0 1 2 3 4 5
Operating income (loss) 2,661 2,678 2,800 2,039 1,371 1,864 1,738 1739 1740 1741 1742 1743
Benefit plan income, net 55 57 39 40 41 42 43 44
Settlement charges (98) (105) (88) -87 -86 -85 -84 -83
Interest expense -425 (390) (395) (363) (367) (321) (261) -260 -259 -258 -257 -256
Gains (losses) on early retirement of debt -137 0 (17) 0 0 10 (33) -32 -31 -30 -29 -28
Interest income 3 2 2 2 4 11 25 26 27 28 29 30
Income (loss) before income taxes 2,102 2,290 2,390 1,678 965 1,516 1,420 1421 1422 1423 1424 1425
Federal, state and local income tax benefit (expense) -767 (804) (864) (608) (346) 39 (322) -321 -320 -319 -318 -317
Net income (loss) $1,335 1,486 1,526 1,070 619 1,555 1,098 1099 1100 1101 1102 1103
Net loss attributable to noncontrolling interest 0 0 2 8 11 10
Net income attributable to Macy’s, Inc. shareholders $ 1,486 $ 1,526 $ 1,072 $ 627 $ 1,566 $ 1,108
Basic earnings per share attributable to Macy’s, Inc. shareholders $3.29 $ 3.93 $ 4.30 $ 3.26 $ 2.03 $ 5.13 $ 3.60
Diluted earnings per share attributable to Macy’s, Inc. shareholders $3.24 $ 3.86 $ 4.22 $ 3.22 $ 2.02 $ 5.10 $ 3.56
Consolidated Balance Sheets – USD ($) $ in Thousands 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024
Current Assets:
Cash and cash equivalents $1,836 $2,273 $ 2,246 $ 1,109 $ 1,297 $ 1,455 $ 1,162 1174 1191 1221 1252 1283
Receivables 371 438 424 558 522 363 400 404 410 420 431 442
Merchandise Inventories 5,308 5,557 5,417 5,506 5,399 5,178 5,263 5316 5395 5530 5669 5810
Prepaid expenses and other current assets 361 420 493 479 408 650 620 626 636 651 668 684
Total Current Assets 7,876 8,688 8,580 7,652 7,626 7,646 7,445 7519 7632 7823 8019 8219
Property and Equipment – net 8,196 7,930 7,800 7,616 7,017 6,672 6,637 6703 6804 6974 7148 7327
Goodwill 3,743 3,743 3,743 3,897 3,897 3,897 3,908 3947 4006 4106 4209 4314
Other Intangible Assets – net 561 527 496 514 498 488 478 483 490 502 515 528
Other Assets 615 732 711 897 813 880 726 733 744 763 782 801
Total Assets 20,991 21,620 21,330 20,576 19,851 19,583 19,194 19386 19677 20169 20673 21190
Current Liabilities:
Short-term debt 124 463 76 642 309 22 43 43 44 45 46 47
Merchandise accounts payable 1,579 1,691 1,594 1,526 1,423 1,590 1,655 1672 1697 1739 1783 1827
Accounts payable and accrued liabilities 2,610 2,810 3,109 3,333 3,563 3,271 3,366 3400 3451 3537 3625 3716
Income taxes 355 362 296 227 352 296 168 170 172 177 181 185
Deferred income taxes 407 400
Total Current Liabilities 5,075 5,726 5,075 5,728 5,647 5,179 5,232 5284 5364 5498 5635 5776
Long-Term Debt 6,806 6,714 7,233 6,995 6,562 5,861 4,708 4755 4826 4947 5071 5198
Deferred Income Taxes 1,238 1,273 1,443 1,477 1,443 1,148 1,238 1250 1269 1301 1333 1367
Other Liabilities 1,821 1,658 2,201 2,123 1,877 1,662 1,580 1596 1620 1660 1702 1744
Shareholders’ Equity:
Common stock (307.5 and 304.8 shares outstanding) 4 4 4 3 3 3 3 3 3 3 3 3
Additional paid-in capital 3,872 2,522 1,048 621 617 676 652 659 668 685 702 720
Accumulated equity 5,108 6,235 7,340 6,334 6,088 7,246 8,050 8131 8252 8459 8670 8887
Treasury stock -2,002 -1,847 (1,942) (1,665) (1,489) (1,456) (1,318) -1331 -1351 -1385 -1420 -1455
Accumulated other comprehensive loss -931 -665 (1,072) (1,043) (896) (724) (951) -961 -975 -999 -1024 -1050
Total Macy’s, Inc. Shareholders’ Equity 5,378 4,250 4,323 5,745 6,436 6500 6598 6763 6932 7105
Noncontrolling interest 0 3 (1) (12) 0 0 0 0 0 0
Total Shareholders’ Equity 6,051 6,249 5,378 4,253 4,322 5,733 6,436 6500 6598 6763 6932 7105
Total Liabilities and Shareholders’ Equity $20,991 $21,620 $ 21,330 $ 20,576 $ 19,851 $ 19,583 $ 19,194 19386 19677 20169 20673 21190
Consolidated Statements of Cash Flows $ in Thousands 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024
Cash flows from operating activities:
Net income $1,335 $ 1,486 $ 1,526 $ 1,070 $ 619 $ 1,555 $ 1,098 1099 1100 1101 1102 1103
Adjustments to reconcile net income to net cash provided by operating activities:
Restructuring, impairment, store closing and other costs 5 88 87 288 479 186 136 137 138 139 140 141
Settlement charges 0 0 98 105 88 89 90 91 92 93
Depreciation and amortization 1,049 1,020 1,036 1,061 1,058 991 962 1,022 1,019 1,010 1,001 1,003
Stock-based compensation expense 61 62 73 65 61 58 63 64.0 65.0 66.1 67.1 68.1
Gains on sale of real estate (92) (212) (209) (544) (389) -388.0 -387.0 -386.0 -384.9 -383.9
Amortization of financing costs and premium on acquired debt -16 (8) (5) (14) (14) (45) (15) -14.0 -13.0 -12.0 -10.9 -9.9
Changes in assets and liabilities: 1.0 2.0 3.1 4.1 5.1
(Increase) decrease in receivables 7 (58) 22 (45) (1) 120 (61) -60.0 -59.0 -58.0 -56.9 -55.9
(Increase) decrease in merchandise inventories -191 (249) 44 (60) 107 221 (87) -86.0 -85.0 -84.0 -82.9 -81.9
Decrease in prepaid expenses and other current assets -7 (2) (3) 0 37 17 21 22.0 23.0 24.1 25.1 26.1
Increase in other assets not separately identified 23 (1) (61) 1.0 2.0 3.1 4.1 5.1
Increase (decrease) in merchandise accounts payable 23 101 (21) (78) (132) 162 55 56.0 57.0 58.1 59.1 60.1 Used a rolling average for depreciation
Increase (decrease) in accounts payable, accrued liabilities and other items not separately identified -33 48 129 116 (185) (186) 44 45.0 46.0 47.1 48.1 49.1
Increase (decrease) in current income taxes -16 7 (65) (69) 125 (114) (136) -135.0 -134.0 -133.0 -131.9 -130.9
Increase (decrease) in deferred income taxes 14 (142) 29 (1) (134) (421) 112 113.0 114.0 115.1 116.1 117.1
Increase (decrease) in other liabilities not separately identified -75 197 10 1.0 2.0 3.1 4.1 5.1
Increase (Decrease) in Other Noncurrent Assets and Liabilities, Net (137) (108) (129) (156) -155.0 -154.0 -153.0 -151.9 -150.9
Net cash provided by operating activities 2,179 2,549 2,709 1,984 1,801 1,976 1,735 1736.0 1737.0 1738.1 1739.1 1740.1
Cash flows from investing activities: 1.0 2.0 3.1 4.1 5.1
Purchase of property and equipment -698 (607) (770) (777) (596) (487) (657) -656.0 -655.0 -654.0 -652.9 -651.9
Capitalized software -244 (256) (298) (336) (316) (273) (275) -274.0 -273.0 -272.0 -270.9 -269.9
Acquisition of Bluemercury, Inc., net of cash acquired 0 0 (212) 1.0 2.0 3.1 4.1 5.1
Disposition of property and equipment 66 132 172 204 673 411 474 475.0 476.0 477.1 478.1 479.1
Other, net 95 (57) (74) 29 (4) (2) 2 3.0 4.0 5.1 6.1 7.1
Net cash used by investing activities -781 (788) (970) (1,092) (243) (351) (456) -455.0 -454.0 -453.0 -451.9 -450.9
Cash flows from financing activities: 1.0 2.0 3.1 4.1 5.1
Repayments of Debt (754) (988) (1,149) -1148.0 -1147.0 -1146.0 -1144.9 -1143.9
Proceeds from Issuance of Commercial Paper 1,000 400 1,044 499 1.0 2.0 3.1 4.1 5.1
Financing costs -11 (9) (9) (4) 1.0 2.0 3.1 4.1 5.1
Debt repaid -1,803 (124) (870) (152) 1.0 2.0 3.1 4.1 5.1
Dividends paid -324 (359) (421) (456) (459) (461) (463) -462.0 -461.0 -460.0 -458.9 -457.9
Increase (decrease) in outstanding checks -88 24 133 (83) 61 (15) 16 17.0 18.0 19.1 20.1 21.1
Acquisition of treasury stock -1,397 (1,571) (1,901) (2,001) (316) (1) 0 1.0 2.0 3.1 4.1 5.1
Issuance of common stock 234 315 258 163 36 6 45 46.0 47.0 48.1 49.1 50.1
Proceeds from noncontrolling interest 0 0 5 6 13 7 8.0 9.0 10.1 11.1 12.1
Net cash used by financing activities -2,389 (1,324) (1,766) (2,029) (1,426) (1,446) (1,544) -1543.0 -1542.0 -1541.0 -1539.9 -1538.9
Net increase (decrease) in cash, cash equivalents and restricted cash -991 437 (27) (1,137) 132 179 (265) -264.0 -263.0 -262.0 -260.9 -259.9
Cash, Cash Equivalents, Restricted Cash and Restricted Cash Equivalents, beginning of period 2,827 1,836 2,273 2,246 1,202 1,334 1,513 1514.0 1515.0 1516.1 1517.1 1518.1
Cash, Cash Equivalents, Restricted Cash and Restricted Cash Equivalents, end of period 1,836 2,273 2,246 1,109 1,334 1,513 1,248 1249.0 1250.0 1251.1 1252.1 1253.1
Supplemental cash flow information: 1.0 2.0 3.1 4.1 5.1
Interest paid 585 388 413 383 396 361 328 329.0 330.0 331.1 332.1 333.1
Interest received 2 2 2 2 4 12 25 26.0 27.0 28.1 29.1 30.1
Income taxes paid (net of refunds received) $738 $ 835 $ 834 $ 635 $ 352 $ 496 $ 345 346.0 347.0 348.1 349.1 350.1
Depreciation Expense Consolidated Statements of Cash Flows 1,049 1,020 1,036 1,061 1,058 991 962 1,022 1,019 1,010 1,001 1,003
COMMON SIZE INCOME STATEMENT 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2024 2025
Net sales 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100%
Credit Card Revenues, Net 0% 0% 0% 0% 3% 3% 3% 3% 3% 3% 3% 3%
Cost of sales -60% -60% -60% -61% -60% -61% -61% -61% -61% -61% -61% -61%
Gross margin 40% 40% 40% 39% 0% 0% 0% 0% 0% 0% 0% 0%
Selling, general and administrative expenses -31% -30% -30% -31% -36% -36% -36% -36% -36% -36% -36% -36%
Gains on sale of real estate 0% 0% 0% 1% 1% 2% 2% 2% 2% 2% 2% 2%
Restructuring, impairment, store closing and other costs -0% -0% -0% -1% -2% -1% -1% -1% -1% -1% -1% -1%
Settlement charges 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0%
Operating income (loss) 10% 10% 10% 8% 5% 7% 7% 7% 7% 7% 7% 7%
Benefit plan income, net 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0%
Settlement charges 0% 0% 0% 0% -0% -0% -0% -0% -0% -0% -0% -0%
Interest expense -2% -1% -1% -1% -1% -1% -1% -1% -1% -1% -1% -1%
Gains (losses) on early retirement of debt -0% 0% -0% 0% 0% 0% -0% -0% -0% -0% -0% -0%
Interest income 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0%
Income (loss) before income taxes 8% 8% 9% 6% 4% 6% 6% 6% 6% 6% 6% 6%
Federal, state and local income tax benefit (expense) -3% -3% -3% -2% -1% 0% -1% -1% -1% -1% -1% -1%
Net income (loss) 5% 5% 5% 4% 2% 6% 4% 4% 4% 4% 4% 4%
COMMON SIZE BALANCE SHEET 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2024 2025
Current Assets:
Cash and cash equivalents 11% 11% 5% 7% 7% 6% 6% 6% 6% 6% 6%
Receivables 2% 2% 3% 3% 2% 2% 2% 2% 2% 2% 2%
Merchandise Inventories 26% 25% 27% 27% 26% 27% 27% 27% 27% 27% 27%
Prepaid expenses and other current assets 2% 2% 2% 2% 3% 3% 3% 3% 3% 3% 3%
Total Current Assets 40% 40% 37% 38% 39% 39% 39% 39% 39% 39% 39%
Property and Equipment – net 37% 37% 37% 35% 34% 35% 35% 35% 35% 35% 35%
Goodwill 17% 18% 19% 20% 20% 20% 20% 20% 20% 20% 20%
Other Intangible Assets – net 2% 2% 2% 3% 2% 2% 2% 2% 2% 2% 2%
Other Assets 3% 3% 4% 4% 4% 4% 4% 4% 4% 4% 4%
Total Assets 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100%
Current Liabilities:
Short-term debt 2% 0% 3% 2% 0% 0% 0% 0% 0% 0% 0%
Merchandise accounts payable 8% 7% 7% 7% 8% 9% 9% 9% 9% 9% 9%
Accounts payable and accrued liabilities 13% 15% 16% 18% 17% 18% 18% 18% 18% 18% 18%
Income taxes 2% 1% 1% 2% 2% 1% 1% 1% 1% 1% 1%
Deferred income taxes 2% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0%
Total Current Liabilities 26% 24% 28% 28% 26% 27% 27% 27% 27% 27% 27%
Long-Term Debt 31% 34% 34% 33% 30% 25% 25% 25% 25% 25% 25%
Deferred Income Taxes 6% 7% 7% 7% 6% 6% 6% 6% 6% 6% 6%
Other Liabilities 8% 10% 10% 9% 8% 8% 8% 8% 8% 8% 8%
Shareholders’ Equity:
Common stock (307.5 and 304.8 shares outstanding) 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0%
Additional paid-in capital 12% 5% 3% 3% 3% 3% 3% 3% 3% 3% 3%
Accumulated equity 29% 34% 31% 31% 37% 42% 42% 42% 42% 42% 42%
Treasury stock -9% -9% -8% -8% -7% -7% -7% -7% -7% -7% -7%
Accumulated other comprehensive loss -3% -5% -5% -5% -4% -5% -5% -5% -5% -5% -5%
Total Macy’s, Inc. Shareholders’ Equity 0% 25% 21% 22% 29% 34% 34% 34% 34% 34% 34%
Noncontrolling interest 0% 0% 0% -0% -0% 0% 0% 0% 0% 0% 0%
Total Shareholders’ Equity 29% 25% 21% 22% 29% 34% 34% 34% 34% 34% 34%
Total Liabilities and Shareholders’ Equity 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100%
GROWTH OF INCOME STATEMENT 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2024 2025
Net sales 1% 1% -4% -4% -4% 0% 0% 0% 0% 0% 0%
Credit Card Revenues, Net ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! 7% 9% 0% 0% 0% 0% 0%
Cost of sales 1% 1% -2% -5% -3% 0% -0% -0% -0% -0% -0%
Gross margin 1% 0% -6% -100% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! 100% 51% 34% 25%
Selling, general and administrative expenses -0% -1% 1% 9% -3% 1% -0% -0% -0% -0% -0%
Gains on sale of real estate ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! -1% 160% -28% 0% 0% 0% 0% 0%
Restructuring, impairment, store closing and other costs 1660% -1% 231% 66% -61% -27% -1% -1% -1% -1% -1%
Settlement charges ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! 100% 51% 34% 25%
Operating income (loss) 1% 5% -27% -33% 36% -7% 0% 0% 0% 0% 0%
Benefit plan income, net ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! 4% -32% 3% 3% 2% 2% 2%
Settlement charges ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! 7% -16% -1% -1% -1% -1% -1%
Interest expense -8% 1% -8% 1% -13% -19% -0% -0% -0% -0% -0%
Gains (losses) on early retirement of debt -100% ERROR:#DIV/0! -100% ERROR:#DIV/0! ERROR:#DIV/0! -430% -3% -3% -3% -3% -4%
Interest income -33% 0% 0% 100% 175% 127% 4% 4% 4% 4% 4%
Income (loss) before income taxes 9% 4% -30% -42% 57% -6% 0% 0% 0% 0% 0%
Federal, state and local income tax benefit (expense) 5% 7% -30% -43% -111% -926% -0% -0% -0% -0% -0%
Net income (loss) 11% 3% -30% -42% 151% -29% 0% 0% 0% 0% 0%
GROWTH OF BALANCE SHEET 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2024 2025
Current Assets:
Cash and cash equivalents 24% -1% -51% 17% 12% -20% 1% 1% 2% 2% 2%
Receivables 18% -3% 32% -6% -30% 10% 1% 1% 2% 2% 2%
Merchandise Inventories 5% -3% 2% -2% -4% 2% 1% 1% 2% 2% 2%
Prepaid expenses and other current assets 16% 17% -3% -15% 59% -5% 1% 1% 2% 2% 2%
Total Current Assets 10% -1% -11% -0% 0% -3% 1% 1% 2% 2% 2%
Property and Equipment – net -3% -2% -2% -8% -5% -1% 1% 1% 2% 2% 2%
Goodwill 0% 0% 4% 0% 0% 0% 1% 1% 2% 2% 2%
Other Intangible Assets – net -6% -6% 4% -3% -2% -2% 1% 1% 2% 2% 2%
Other Assets 19% -3% 26% -9% 8% -18% 1% 1% 2% 2% 2%
Total Assets 3% -1% -4% -4% -1% -2% 1% 1% 2% 2% 2%
Current Liabilities:
Short-term debt 273% -84% 745% -52% -93% 95% 1% 1% 2% 2% 2%
Merchandise accounts payable 7% -6% -4% -7% 12% 4% 1% 1% 2% 2% 2%
Accounts payable and accrued liabilities 8% 11% 7% 7% -8% 3% 1% 1% 2% 2% 2%
Income taxes 2% -18% -23% 55% -16% -43% 1% 1% 2% 2% 2%
Deferred income taxes -2% -100% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Total Current Liabilities 13% -11% 13% -1% -8% 1% 1% 1% 2% 2% 2%
Long-Term Debt -1% 8% -3% -6% -11% -20% 1% 1% 2% 2% 2%
Deferred Income Taxes 3% 13% 2% -2% -20% 8% 1% 1% 2% 2% 2%
Other Liabilities -9% 33% -4% -12% -11% -5% 1% 1% 2% 2% 2%
Shareholders’ Equity:
Common stock (307.5 and 304.8 shares outstanding) 0% 0% -25% 0% 0% 0% 1% 1% 2% 2% 2%
Additional paid-in capital -35% -58% -41% -1% 10% -4% 1% 1% 2% 2% 2%
Accumulated equity 22% 18% -14% -4% 19% 11% 1% 1% 2% 2% 2%
Treasury stock -8% 5% -14% -11% -2% -9% 1% 1% 2% 2% 2%
Accumulated other comprehensive loss -29% 61% -3% -14% -19% 31% 1% 1% 2% 2% 2%
Total Macy’s, Inc. Shareholders’ Equity ERROR:#DIV/0! ERROR:#DIV/0! -21% 2% 33% 12% 1% 1% 2% 2% 2%
Noncontrolling interest ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! -133% 1100% -100% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Total Shareholders’ Equity 3% -14% -21% 2% 33% 12% 1% 1% 2% 2% 2%
Total Liabilities and Shareholders’ Equity 3% -1% -4% -4% -1% -2% 1% 1% 2% 2% 2%
Useful Ratios & Other Figures 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024
PM (Net Profit Margin = NI/Revenue) 5% 5% 4% 2% 6% 4% 4% 4% 4% 4% 4%
TAT (Total Asset Turnover = Revenue/Total Assets) 129% 132% 132% 131% 127% 130% 129% 127% 124% 121% 118%
EM (Equity Multiplier = Total Assets/Total Equity) 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100%
ROE (Return on Equity = NI/Total Equity = PM*TAT*EM) 7% 7% 5% 3% 8% 6% 6% 6% 5% 5% 5%
ROA (Return on Assets = NI/Total Assets = PM*TAT) 7% 7% 5% 3% 8% 6% 6% 6% 5% 5% 5%
d (Dividend Payout Ratio = Dividends/NI … note: result is POSTIVE) 24% 28% 43% 74% 30% 42% 42% 42% 42% 42% 42%
b (Retention Ratio = 1-d) 76% 72% 57% 26% 70% 58% 58% 58% 58% 58% 58%
SGR (Sustainable Growth Rate = b*ROE) 5% 5% 3% 1% 6% 3% 3% 3% 3% 3% 3%
IGR (Internal Growth Rate = b*ROA) 5% 5% 3% 1% 6% 3% 3% 3% 3% 3% 3%
TIE (Times Interest Earned Ratio = EBIT/Interest)
DOL (Degree of Operating Leverage [point estimate] = Gross Profit/OpInc) 4.18 4.02 5.19 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Days of sales in cash ( = Cash/[Revenue/365]) 29.70 29.17 14.95 18.27 21.29 16.98 17.15 17.41 17.85 18.29 18.75
Inventory Turnover ( = COGS/Avg Inventory … note: Avg Inventory = [BOY+EOY]/2 ) -3.08 -3.07 -3.02 -2.87 -2.87 -2.91 -2.88 -2.84 -2.78 -2.72 -2.65
Receivables Turnover (= Sales/Avg Receivables … note: Avg Receivables = [BOY+EOY]/2 ) 69.05 65.21 55.15 47.98 56.36 65.45 62.12 61.35 60.15 58.69 57.26
Purchases ( = COGS + Δ inventory) (16,476) (17,003) (16,407) (15,773) (15,402) (15,130) (15,161) (15,133) (15,077) (15,073) (15,068)
Payables Turnover ( = Purchases/Avg Payables … note: Avg Payables = [BOY+EOY]/2 ) -11.09 -10.95 -9.84 -8.56 -7.98 -8.80 -8.58 -8.47 -8.31 -8.11 -7.91
Days in inventory ( = 365/Inventory Turnover) -118.56 -118.77 -120.84 -127.04 -127.15 -125.24 -126.90 -128.49 -131.08 -134.36 -137.73
Days in receivables ( = 365/Receivables Turnover) 5.29 5.60 6.62 7.61 6.48 5.58 5.88 5.95 6.07 6.22 6.37
Operating cycle ( = Days in Inventory + Days in Receivables) -113.27 -113.17 -114.23 -119.43 -120.68 -119.66 -121.02 -122.54 -125.01 -128.14 -131.36
Days in payables ( = 365/Paybales Turnover) -32.92 -33.34 -37.11 -42.64 -45.73 -41.48 -42.56 -43.08 -43.91 -45.02 -46.16
Cash cycle ( = Operating Cycle – Days in Payables) -80.35 -79.83 -77.12 -76.79 -74.95 -78.18 -78.46 -79.47 -81.10 -83.13 -85.20

COMPARABLE COMPANIES

Comparable Firms
Ticker P/E ratio P/S ratio EV/EBITDA ratio Beta Debt Equity (Market Cap) Tax Rate Unlevered Beta
TJMAXX TJX 38.68 1.72 24.21 0.71 16,510,000 64,135,000 20.78% 0.59
WALMART WMT 24.62 0.69 12.64 0.30 76,590,000 367,615,000 24.49% 0.26
TARGET TGT 22.96 0.79 11.20 0.79 16,700,000 64,687,000 21.07% 0.66
JC PENNY JCPNQ 24.59 0.01 16.48 1.39 6,130,000 99,326,000 8.08% 1.32
KOHL’S KSS 37.53 0.16 7.34 1.76 7,690,000 3,286,000 23.31% 0.63
DILLARD’S DDS 17.82 0.10 14.43 0.91 615,610,000 616,060,000 33.60% 0.55
BEST BUY BBY 18.85 0.61 9.81 1.50 5,300,000 26,324,000 23.71% 1.30
HOME DEPOT HD 26.43 2.58 17.80 1.06 41,750,000 287,680,000 23.58% 0.95
Average of Comparable Firms 26.44 0.83 14.24 0.78
Median of Comparable Firms 24.61 0.65 13.54 0.64

DEBT

Debt
3.88%
2.88%
3.38% Average bond YTM
3.38% Median bond YTM
3.38% Yield on Bonds (using median)
We could also use “synthetic ratings” to estimate the cost of debt…
based on TIE (Times Interest Earned ratio)
based on Altman Z-Score
3.38% Cost of Debt %
$6,851,716.33 Market Value of Debt
Balance Sheet Debt
$2,805.42 Damodaran’s method
-261 Interest Expense from Income Statement
4,708 BV Debt from Balance Sheet
5 Assumed Years to maturity
Operating Leases
$4,046.29 PV of Operating Lease Expenses
Expected Op. Lease Payments:
2020 2021 2022 2023 2024 2025 2026
539 553 575 575 622 903.5 903.5
Based on data listed under “Credit Facilities and Long-Term Contractual Commitments” in HanesBrands 10-k

COST OF CAPITAL

Costs of Capital
2.19%
Michael Dimond: from treasury.gov for date of interest
Rf (Risk-free Rate) source: https://www.treasury.gov/resource-center/data-chart-center/interest-rates/Pages/TextView.aspx?data=longtermrateYear&year=2020 TREASURY 20-Yr CMT on filing date
6.00%
Michael Dimond: Estimated. Consider the implied market return (Rf+MRP) to make sure this is reasonable.
Market Risk-Premium (MRP)
18.53%
Michael Dimond: Same rate used in NOPAT computation for subject company.
Tax Rate
6,851,716.3
Michael Dimond: From “Debt” tab
MV Debt
11,687,604.8
Michael Dimond: Market Capitalization as of the date of interest
MV Equity
$ 37.75 Adjusted Close on filing date
309,605,426 Shares outstanding at time of filing
0.78
Michael Dimond: From “Comparable Firms” tab
Unlevered Beta
6.88%
Michael Dimond: Computed using CAPM and Unlevered Beta
Ku (Unlevered Cost of Capital)
1.15
Michael Dimond: Computed using Hamada’s formula: Bu*(1+D/E(1-t))
Levered Beta (“Bottom-up Beta”)
9.12%
Michael Dimond: Computed using CAPM and Levered Beta
Ke (Cost of Equity … Common Equity)
3.38%
Michael Dimond: From “Debt” tab
Kd (Cost of Debt … Before Tax)
6.76%
Michael Dimond: WACC = we*Ke + wd*Kd*(1-t) where: we = MV Equity / (MV Equity + MV Debt) wd = MV Debt / (MV Equity + MV Debt) If preferred stock exists, include factors for weight of preferred stock and required return for preferred stock.
WACC (Weighted Average Cost of Capital)
Remember: Rf < Kd < WACC < Ku < Ke
2.19% 3.38% 6.76% 6.88% 9.12%
7.00%