need help with this spreadsheet asap
etenalcopy_of_mproject_template_comparing_kohls_and_jcpenneys_1-1.xlsx
Title Page
Hussain, Manal | ||||||
ACCT 504-60733 | ||||||
21-Aug-14 | ||||||
Professor Norton | ||||||
Financial Statement Analysis Project - Week 7 | ||||||
Profiles
History: | |
In 1962, Max Kohl opened the first Kohl's Department store in Brookfield, Wisconsin. He started his career with a small grocery business that developed into the largest supermarket chain in the Milwaukee area. | |
By 1972 Kohl's expanded into 5 department stores. In 1978, BATUS acquired purchased 80% of the Koh'ls food and department stores along with Gimbels, Saks Fifth Avenue and Marshalls. | |
BATUS then sold Kohl's to Great Atlantic and Pacific Tea Company (A&P) who closed the Kohl's food store in 2003. | |
Expansion: | |
By 2000 Kohl's operated 298 stores in 25 states with 43,000 Associates. | |
In 2006, 85 new stores, including expansion into the Northwest with stores in Oregon and Washington were opened. To support the continuous growth in the Southweest, Kohls then opened up in Patterson, California with a capacity to support 110 stores. | |
By the end of the year, 817 stores in 47 states with 114,000 associates were operating as a fullly functioning department store retailing clothing, accessories and house furnishings. |
Ratios
Use this Excel spreadsheet to compute ratios; show your computations for all ratios on this tab and also include your commentary. | ||||||||
The financial statements used to calculate these ratios are available in Appendix A and Appendix B of your textbook. | ||||||||
Kohl's | JC Penney | Interpretation and comparison between the two companies' ratios (reading the Appendix of Chapter 13 will help you prepare the commentary). | ||||||
The comparison of the ratios is an important part of the project. A good approach is to briefly explain what the ratio tells us. Indicate whether a higher or lower ratio is better. Then compare the two companies on this basis. Remember—each ratio below requires a comparison. | ||||||||
Earnings per Share of Common Stock (basic - common) | As given in the income statement | $3.67 | $ 0.89 | $ 3.01 | ||||
Current Ratio | Current assets | $5,645,000,000,000 | = | 2.08 | $2,113,485 | = | 1.44 | |
Current liabilities | $2,710,000,000,000 | $1,471,110 | ||||||
Gross Profit Margin | Gross profit | $7,032,000,000 | = | 38.2% | $2,859,882 | = | 43.0% | |
Net Sales | $18,391,000,000 | $6,644,252 | ||||||
Rate of Return (Net Profit Margin) on Sales | Net Income | $1,114,000,000 | = | 6.1% | $660,931 | = | 9.9% | |
Net Sales | $18,391,000,000 | $6,644,252 | ||||||
Inventory Turnover | Cost of Goods Sold | $11,359,000,000 | 100.5 | $3,784,370 | 5.9 | |||
Average Inventory | $113,000,000 | times | $641,108 | times | ||||
Days' inventory outstanding (DIO) | 365 days | 365 | = | 4 | 365 | = | 62 | |
Inventory turnover | 100.5 | days | 5.9 | days | ||||
Accounts Receivable Turnover | Net credit sales | $70,000,000 | = | 1,666.6 | $6,644,252 | = | 15.4 | |
Average Net Accounts Receivable | $42,002 | $430,441 | ||||||
Days' sales outstanding (DSO) | 365 | 365 | = | 0.2 | 365 | = | 23.6 | |
Receivable Turnover Ratio | 1,666.6 | days | 15.4 | days | ||||
Asset turnover | Net Sales | $18,391,000,000 | = | 1.34 | $6,644,252 | = | 1.45 | |
Average Total Assets | $13,726,000,000 | $4,580,967 | ||||||
Rate of Return on Total Assets (ROA) | Rate of return on sales times Asset Turnover | $52,004 | = | 6.1% | $660,931 | = | 14.4% | |
$852,297 | $4,580,967 | |||||||
Debt Ratio | Total Liabilities | $2,710,000,000 | = | 20.0% | $3,706,466 | = | 84.0% | |
Total Assets | $13,564,000,000 | $4,412,199 | ||||||
Times-Interest-Earned Ratio | Net Income + Int Expense + Tax Expense | $74,301 | = | 542.3 | 1,111,148 | = | 11.6 | |
Interest Expense | $137 | 95,569 | ||||||
Dividend Yield | Dividend per share of common stock (Yahoo Finance 11/1/2013) | $0.32 | = | 1.0% | $1.94 | = | 2.0% | |
Market price per share of common stock (Yahoo Finance 11/1/2013) | $31.72 | $98.85 | ||||||
Rate of Return on Common Stockholders' Equity (ROE) | Net income - Preferred dividends | $52,431 | = | 8.0% | $660,931 | = | 68.5% | |
Average common stockholders' equity | $657,875 | $964,658 | ||||||
Free cash flow | Net cash provided by operating activities minus cash payments earmarked for investments in plant assets | $93,033 | = | $93,033 | $836,100 | $ 836,100 | ||
= | ||||||||
Price/Earnings Ratio (Multiple) | 12/31/12 | $25.92 | = | 29 | $72.22 | = | 24 | |
(please see the instructions for the dates to use for this ratio) | EPS as of 12/31/2012 | $0.89 | $3.01 | |||||
Summary
You all get the chance to play the role of financial analyst below. The summary should be a comparison of each company's performance for each major category of ratios (liquidity, solvency, and profitability) listed below. Focus on major differences as you compare each company's performance. A nice way to conclude is to state which company you feel is the better investment and why. | ||
Measuring Ability to Pay Current Liabilities: Tootsie Roll has the advantage for the current ratio. Tootsie Roll has $3.25 in current assets for every dollar in current liabilities while Hershey has only $1.44 in current assets for every dollar in current liabilities. | The higher the current ratio the easier the capacity to pay off the debt. | |
Measuring Turnover: Hershey has the advantage for the inventory turnover and accounts receivable turnover ratios. Hershey turns over their inventory 5.9 times to Tootsie Roll's 5.5 times and Hershey turns over their accounts receivable 15.4 times to Tootsie Roll's 13.1 times. | ||
Measuring Leverage- Overall Ability to Pay Debts: Tootsie Roll has significantly less debt than Hershey as evidenced by Tootsie Roll's 23% debt to asset ratio as compared to Hershey's 84% debt to asset ratio. Tootsie Roll can cover their interest expense 504 times with income before interest and taxes while Hershey can only cover their interest expense 11 times with their income before interest and taxes. Tootsie Roll has the advantage for each of these ratios. | ||
Measuring Profitability: Hershey has the advantage for each of the profitability ratios. Hershey has a significant edge in return on common stockholders' equity with a 68.5% return on common stockholders' equity as compared to Tootsie Roll's 8.0% return on common stockholders' equity. Hershey also has a higher gross profit rate (43.0% to 33.3%) and higher profit margin ratio (9.9% to 9.5%). | ||
Analyzing Stock as an Investment: Hershey returns a 2% dividend yield to their investors while Tootsie Roll's yield is 1%. Hershey has positive free cash flow of $836.1 million while Tootsie Roll has positive free cash flow of $93 million. Free cash flow can be used to undertake acquisitions, pay additional dividends, pay down debt, or buy back stock. | ||
Conclusion: Tootsie Roll is the safer investment when you examine their ability to pay current liabilities and overall liabilities; however, Hershey has the edge for all of the profitability ratios. For the conservative investor, Tootsie Roll looks like the way to go because of their strong current and times-interest-earned ratios. For the growth-oriented investor, Hershey is the way to go because of their stronger profitability ratios and large amount of free cash flow. | ||
Bibliography
References: | |
1) | Early Beginnings. (n.d.). Retrieved from http://tucson.jobing.com/company_profile.asp?i=59924&p=7432 |