Part A) I need to calculate the following ratios concerning (1) Common-size analysis, (2) Liquidity ratios, (3) Solvency Ratios and the (4) DuPont Model
- Quick ratio
- Current ratio
- Solvency ratio
- Debt-to-equity (liabilities to equity ratio)
- Interest coverage (times interest earned)
- Profit Margin (PM)
- Asset Turnover (AT)
- Return on Assets (ROA)
- Financial Leverage (FL)
- Return on Equity (ROE)
Help me to understand the concept (1) Common-Size Analysis to fill out a table on Excel that looks like this:
Page 8 98198005 Page 9 JB198005 EXHIBIT 1: BON-TON-STOCK PRICE CHANGES EXHIBIT 2: BON-TON-CONSOLIDATED ANNUAL INCOME STATEMENT Date Close (US$) Volume Date Close (US$) Volume (IN US$ MILLIONS) 01/31/2014 10.7500 12,021,369 01/31/2017 1.1800 2,245,508 2013 2016 2017 02/28/2014 10 8600 11,554,096 02/28/2017 1.2000 3.238,586 Period End Date 02-Feb- 01-Feb- 1-Jan- 30-Jan- 28-Jan- 03/31/2014 10.9800 13,321,443 03/31/2017 0.8401 4,125,950 2013 2014 2015 2016 2017 04/30/2014 11.0000 8,062,696 04/28/2017 0.5800 1,578,068 Net Sales 2.919.4 2.770.1 2.756.2 2,717.7 2.600.6 05/30/2014 10.2400 9,823,094 05/31/2017 0.4900 2.718,422 Other Income 59.4 54.0 66.7 71.8 73.8 06/30/2014 10.3100 4,341,273 06/30/2017 0.4375 2,399,762 Total Revenue 2,978.8 2,834.1 2,822.9 2,789.5 2,674.4 07/31/2014 9.3000 6,161,942 07/31/2017 0.5611 30,098,772 08/29/2014 10.5600 7,245,606 08/31/2017 0.6600 8,866,622 Costs of Merchandise Sold 1.873.9 1.768.7 1,773.0 1.775.7 1.677.7 09/30/2014 8.3600 5,129,172 09/29/2017 6,578,453 Gross Profit 1,104.9 1,065.4 1,049.9 1,013.8 996. Selling, General, and 10/3 1/2014 8.8100 5,088,194 10/31/2017 0.7498 125,880,307 Administrative 806.8 771.1 771.4 764.2 737.3 11/28/2014 8.4200 3,039,167 11/30/2017 0.3900 19,184,022 Advertising 129.3 128.3 135.6 137.3 141.5 12/31/2014 7.4100 5,036,654 12/29/2017 0.3421 2,354.666 Depreciation and Amortization 38.3 85.9 90.1 91.8 92.2 01/30/2015 5.4800 5,522,130 01/31/2018 0. 1600 4,705,119 Amortization of Lease-Related 02/27/2015 5.5500 2,749,656 02/28/2018 0.0579 2,904,991 Interests 4.5 4.5 4.2 4.2 Termination Benefits and 03/31/2015 5.9600 8,936,071 Other Closing Cost 0.0 0.0 1.9 04/30/2015 7.1700 3,230,145 Gain on Insurance Recovery 0.0 0.0 -1.4 0.0 05/29/2015 5.8500 1,424,906 Impairment Charges 5.8 6.2 2.5 3.6 17.0 06/30/2015 4.6200 5,711,156 Total Operating Expense 1.034.9 996.0 993.3 1.003.8 994.1 07/31/2015 4.3700 5,233,506 Operating Incom 70.0 69.4 56.6 10.0 2.6 08/31/2015 3.9900 4,323,936 09/30/2015 3.1400 4,250,240 Interest Expense, Net (83.0) (68.8) (62.0) (63.0) (67.1) 10/30/2015 3.1500 2,591,712 Loss on Extinguishment of 1 1/30/2015 2.6800 16,068,764 Debt B.4 0.2 6.2 0.8 12/31/2015 2.1000 17,549,682 Capitalized Interest 0.2 0.2 0.3 0.5 0.2 01/29/2016 6,538,218 Net Income before Taxes -21.3 -3.6 -5.4 -58.9 -65.0 1.7000 02/29/2016 .9000 2,853,779 Provision for Income Taxes 0.2 -0.1 1.6 03/31/2016 2.2700 5,857,231 Net Income after Taxes/Loss -21.6 -3.6 -7.0 -57.1 -63.4 04/29/2016 2.3100 1,651,298 05/31/2016 1.5100 2,390,247 Basic EPS -1.16 -0.19 0.36 -3.18 -2.90 06/30/2016 1.4100 1,769,216 Diluted EPS -1.16 -0.19 -0.36 -3.18 07/29/2016 1.4900 1,446,724 Note: EPS = eamings per share. 08/31/2016 1.7000 2,453,819 Source: Created by the case authors with information from "The Bon Ton Stores Inc. BONTQ: Income Statement," Morningstar, accessed February 23, https /financials.momingstar.com/income- 09/30/2016 1.7100 1,153,691 statement/is.html?1=0PQ090goUQ&culture=en-USSplatform=sal; and Thomson Reuters Elkton 10/3 1/2016 .3601 1,026,520 11/30/2016 1.5800 1,843,072 1.4700 1,988,047Page 10 98198005 Page 11 98198005 EXHIBIT 3: BON-TON-CONSOLIDATED ANNUAL BALANCE SHEET (IN US$ MILLIONS) EXHIBIT 3 (CONTINUED) 2013 2014 2015 2016 2017 2013 2014 2015 2016 2017 Period End Date 02-Feb- 01-Feb- 31-Jan- 30-Jan- 28-Jan- Total Current Liabilities 192.7 413.3 406.5 344.1 368.8 2013 2014 2015 2016 2017 Assets Long-Term Debt, Less Current Maturities 768.9 804.4 851.0 856.0 848.8 Current Assets Cash and Cash Equivalents 7.9 7.1 8.8 6.9 Long-Term Finance Lease and Hire 6.7 Purchase Agreement 52.5 19.0 45.0 126.7 133.9 Merchandise Inventories '58.4 709.7 735.0 711.7 724.5 Other Receivable 6.0 19.6 59.7 60.5 68.3 Total Long-Term Debt 821.3 853.3 396.0 382.7 982 6 Prepaid Expenses 34.6 36.7 33.7 36.7 30.3 Employee Defined Benefit Plans 83.6 51.5 0.0 0.0 0.0 Total Current Assets 836.9 793.1 837.1 815.8 829.7 Other Long-Term Liabilities, Other 126.0 131.1 193.9 179.1 166.0 Fixed Assets Land and Improvements 118.4 118.4 120.7 17.5 77.5 Deferred Income Taxes 0.0 0.0 3.7 9.8 10.4 Buildings 690.3 714.4 720.3 677.0 673.8 Total Liabilities 1,523.6 1,449.3 1,505.1 1,515.7 1,527.8 Furniture and Equipment 579.4 610.2 650.9 683. 693.8 Shareholders' Equity Capital Lease 69.3 62.1 60.5 148.8 164.1 Common Stock 0.19 0.19 0.18 0.19 0.19 Accumulated Depreciation and -804.6 -865.1 -910.5 -951.8 -1,024.4 Treasury Stock -1.4 -1. -1.4 -1.4 -1.4 Amortization Additional Paid-In Capital 158.7 160.8 161.4 164.4 166.9 Deferred Taxes 15.0 15.8 0.0 0_0 Accumulated Other Comprehensive Lease-Related Interests 94.6 91.3 81.2 73.0 70.5 Loss -73.2 -50.4 -80.4 -76.1 -72.9 Customer Lists and Relationships 22.9 22.9 22.9 2 6 22 6 Accumulated Deficit 26.3 18.8 7.9 -52.2 -115.6 Accumulated Amortization and Other Total Equity 110.6 128.0 87.6 34.9 -22.8 Intangibles -57.6 -62.1 -64.4 -62 2 -65.7 Total Liabilities and Shareholders' Other Long-Term Assets 18.9 25.6 23.5 17.4 17.4 Equity 1,634.2 1,577.2 1,592.7 1,550.6 1,505.1 Trade Name, Net 40.3 10.3 10.2 38.4 36.0 Source: Created by the case authors with information from "The Bon-Ton Stores Inc. BONTO: Balance Sheet," Momingstar. Brands/Patents, Net 10.4 10.3 10.3 10.3 9.7 accessed February 23, 2018, http:/financials.morningstar.com/balance-sheet/bs.html?=BONTO®ion=usa&culturemen- US&platform=sal; and Thomson Reuters Eikon. Total Assets 1,634.2 1,577.2 1,592.7 1,550.6 1,505.1 Liabilities Current Liabilities Accounts Payable 193.9 200.5 208.9 162.8 185.9 Other Accrued Expenses 130.5 119.7 77.0 69.2 69.7 Accrued Payroll and Benefits 32.4 28.3 28.8 28.5 25.8 Current Maturities of Long-Term Debt 75.9 7.4 6.8 0.0 0.0 Current Maturities of Capital Lease 3.9 3.8 4.0 5.4 6.7 Deferred Taxes 20.3 22.7 0.0 0.0 0.0 Income Taxes Payable 35.7 30.9 35.1 32.8 34.5 Customer Liabilities 0.0 0.0 46.0 45.4 46.3EXHIBIT 4: BON-TON-CONSOLIDATED ANNUAL CASH FLOW STATEMENT (IN US$ MILLIONS) EXHIBIT 5: BON-TON-CONSOLIDATED QUARTERLY INCOME STATEMENT (IN US$ MILLIONS, EXCEPT PER-SHARE DATA] Fiscal year ends in January, 2015-01 2016-01 2017-01 Fiscal year ends in January. 2017-04 2017-07 2017-10 Cash Flows from Operating Activities Revenue 553 525 Depreciation and Amortization 90 92 Cost of Revenue 354 325 365 Amortization of Debt Discount/Premium and Issuance Costs 7 7 9 Gross Profit 189 200 197 InvestmentAsset Impairment Charges 2 4 17 Operating Expenses Deferred Income Taxes 2 -2 -2 Sales, General, and Administrative 205 191 203 Stock Based Compensation 3 3 Other Operating Expenses 23 21 Inventory -25 -13 Total Operating Expenses 228 215 123 Prepaid Expenses -17 A -1 Operating Income -39 -15 -76 Accounts Payable -16 21 Other Income (Expense) -19 -18 -19 Accrued Liabilities -7 1 Income before Income Taxes -57 -33 -45 Income Taxes Payable Provision for Income Taxes Other Working Capital -20 -15 -12 Net Income from Continuing Operations -57 -33 -45 Other Non-Cash Items -B -47 -57 Net Income -57 -33 -45 Net Cash Provided by Operating Activities 47 18 59 Net Income Available to Common Shareholders -57 -33 -45 Cash Flows from Investing Activities Investments in Property, Plant, and Equipment -55 Source: Excerpted from "The Bon-Ton Stores Inc. DONTQ: Income Statement," Morningstar, accessed February 20, 2018, httpsonfinancials. momingstar.comincome-statements.him/7=0POD00DOUD4 culture=an-LIS&platform=sal Property, Plant, and Equipment Reductions 0 Other Investing Charges 2 Net Cash Used for Investing Activities 2 -58 Cash Flows from Financing Activities Short-Term Borrowing 4 -10 2 Long-Term Debt Issued 770 309 77 Long-Term Debt Repayment -728 -915 -778 Cash Dividends Paid -4 -4 Other Financing Activities -2 -1 -6 Net Cash Provided by (Used for) Financing Activities 40 -72 -5 Net Change in Cash 2 -2 Cash at Beginning of Period 7 9 7 Cash at End of Period 7 7 Free Cash Flow Operating Cash Flow 47 Capital Expenditure -91 -55 Free Cash Flow -44 -67Page 14 98198005 Page 15 98198005 EXHIBIT 6: BON-TON-CONSOLIDATED QUARTERLY BALANCE SHEET (IN US$ MILLIONS) EXHIBIT 7: BON-TON-CONSOLIDATED QUARTERLY CASH FLOW STATEMENT (IN US$ MILLIONS) Fiscal year ends in January. 2017-04 2017-07 2017-10 Assets Fiscal year ends in January. 2017-04 2017-07 2017-10 Current Assets Cash Cash Flows from Operating Activities Cash and Cash Equivalents 7 6 Depreciation and Amortization 22 23 20 Total Cash 6 Amortization Of Debt Discount/Premium and Issuance Costs 3 2 2 Receivables 59 Inventories 852 Investment/Asset Impairment Charges 714 658 Prepaid Expenses 83 83 43 Deferred Income Taxes 0 0 Total Current Assets 804 747 961 Stock Based Compensation -1 Non-Current Assets Inventory 11 56 -193 Property, Plant, and Equipment Land Prepaid Expenses 16 0 -19 Fixtures and Equipment Accounts Payable -29 22 54 Other Properties 1.616 1,596 .585 Accrued Liabilities 4 -16 Property and Equipment, at Cost 1.616 1,596 1,585 -1,049 -1,056 Other Working Capital -3 -7 Accumulated Depreciation 1.046 Property, Plant, and Equipment, Net 571 547 529 Other Non-Cash Items -55 -37 -44 Intangible Assets 72 70 69 Net Cash Provided by Operating Activities -31 42 -172 Other Long-Term Assets 22 28 Total Non-Current Assets 665 22 639 626 Cash Flows from Investing Activities Total Assets 1.468 1,387 1.587 Investments in Property, Plant, and Equipment -10 -11 -14 Liabilities and Stockholders' Equity Property, Plant, and Equipment Reductions 0 21 19 Liabilities Net Cash Used for Investing Activities -10 10 4 Current Liabilities Short-Term Debt 665 Cash Flows from Financing Activities Capital Leases 7 7 Long-Term Debt Issued 170 106 281 Accounts Payable 161 178 248 Long-Term Debt Repayment -122 -157 -108 Taxes Payable Other Financing Activities -7 -2 -5 Accrued Liabilities 179 137 149 Other Current Liabiliti 26 22 Net Cash Provided by (Used for) Financing Activities 41 -53 168 Total Current Liabilities 346 348 1,091 Net Change in Cash 0 Non-Current Liabilities Cash at Beginning of Period 7 -1 7 6 Long-Term Debt 895 849 345 Cash at End of Period 7 6 7 Capital Leases 132 130 129 Other Long-Term Liabilities 174 170 177 Free Cash Flow Total Non-Current Liabilities 1,201 1,150 651 Operating Cash Flow -31 42 -172 Total Liabilities 1.547 1,498 1,743 Capital Expenditure -10 -11 -14 Stockholders Equity Common Stock 0 0 Free Cash Flow -41 31 -186 Additional Paid-In Capital 168 168 167 Retained Earnings -173 -207 -252 Source: Excerpted from "The Bon-Ton Stores Inc. Cash Flow Statement," Morningstar, accessed February 23, 2018. http://financials.morningstar.com/cash-flowof.html?t=BONTO®ion=usa&culturemen-US&platform=sal. Treasury Stock -1 -1 -1 Accumulated Other Comprehensive Income -72 -71 -71 Total Stockholders' Equity -79 -111 -156 Total Liabilities and Stockholders' Equity 1.468 1,387 1,587 Source: Excerpted from "The Bon-Ton Stores Inc. BONTQ: Balance Sheet," Morningstar, accessed February 23, 2018, http:/financials.momingstar.combalance-sheet/bs.html71=BONTOSregion=usa&culture=en-US$platform=sal.EXHIBIT 8: COMPETITOR COMPANY DATA (IN US$ MILLIONS, FOR FISCAL YEAR ENDING DECEMBER 2016 / JANUARY 2017) Company Dillard's KOHL'S Macy's Nordstrom Inc. Corp. Inc. Inc. Amazon Inventory 1,375 4.038 5,506 1,945 11,461 Current Assets 1,669 5,076 7,652 3,014 45,781 Total Assets 3.866 13,606 20.576 7,698 83,420 Current Liability 751 2,714 5,728 2,911 43,816 Total Liability 2.070 8,115 16,326 6 827 64,117 Revenue 6,755 19.204 27.079 14.437 135,987 Gross Profit 2.404 6,939 10,583 5,269 65,932 Operating Profit 457 1,553 2,039 1,101 4,186 Net Profit 269 673 1,072 600 2,371 Source: Created by the case authors with information from "Dillard's Inc. DOS: Income Statement," Momingstar, accessed March 20. 2018, https:/financials.morningstar.com/income statementis.html71=0P000001085culture=en-US&platform=sal; "Kohl's Corp KKS: Income Statement," Momingstar, accessed March 20, 2018, https //financials.momingstar.comincome statementis.html?t-OP0000035Z&culture=en-US&platform=sal; "Macy's Inc. M: Income Statement," Momingstar, accessed March 20, 2018, https:/financials.morningstar.comincome-statementis.html?t=OP00000246& culture=en-US&platform=sal; "Nordstrom Inc. JWN: Income Statement," Momingstar, accessed March 20, 2018, https //financials.momingstar.com/income- statement/is.html71=0P000003XP&culturemen-US&platform=sal; "Amazon.com Inc. AMZN: Income Statement," Momingstar, US&platform=sal. accessed March 20, 2010, https:/financials.morningstar.com/income-statementis.him/71=0P00000087&culture=en- EXHIBIT 9: Z-SCORE FORMULA FOR NON-MANUFACTURING FIRMS X1 = (Current Assets - Current Liabilities) + Total Assets X = Retained Earnings + Total Assets X, = Earnings before Interest and Taxes + Total Assets X4 = Book Value of Equity + Total Liabilities Z-Score Bankruptcy Model: Z = 6.56(X1) + 3.26(X2) + 6.72(X3) + 1.05(Xq) Zones of Discrimination: 2 > 2.9 = Safe Zone; 1.23
