question archive The assignment is to compare the stock information and compute the data in an excel spreadsheet
The assignment is to compare the stock information and compute the data in an excel spreadsheet
Subject:AccountingPrice: Bought3
The assignment is to compare the stock information and compute the data in an excel spreadsheet. The attached info at the bottom is from Word but needs to be converted to Excel with the proper data. The information at the bottom has false numbers in it as placeholders. The companies being compared are Apple Inc. and HP Inc.
The following are the questions that need to be answered.
Compute the change in net working capital (?NWC) for the recent two years using the balance sheet. Make sure to change in NWC in addition to NWC. Check if your calculations are correct with the spreadsheet. See Chapter 2 Lecture Notes for more details. 14. Find the EBIT (Earnings Before Interest and Taxes) for the recent two years in the Income Statement. 15. Find the Income Tax Expense for the recent two years using the Income Statement. 16. Find the Depreciation for the recent two years in the Statement of Cash Flow. 17. Compute the Operating Cash Flow (OCF) for the recent two years. See Chapter 2 Lecture Notes. 18. Find the Net Income (NI) for the recent two years in the Income Statement. 19. Compute the Current Ratio (CR) and Quick Ratio (QR) for the recent two years. Check if your calculations are correct with the spreadsheet. See Chapter 3 Lecture Notes to compute financial ratios. 20. Compute the Debt-to-Equity (D/E) ratio and Interest Coverage Ratio (Time Interest Earned Ratio) for the recent two years. Check if your calculations are correct with the spreadsheet. 21. Compute the Return on Equity (ROE) and the Net Profit Margin (PM) for the recent two years. Check if your calculations are correct with the spreadsheet. 22. Find the Total Market Value. You may use the Market Cap. in the Summary page for the total market value. Remember the unit for the market cap. is in $ billion. However, the unit for the numbers in the income statement, balance sheet, and cash flow statement is generally in $ thousand. Adjust the total market value by multiplying the market cap in Yahoo Finance Summary page by 1,000,000. 23. Compute the Market-to-Book (M/B) ratio. Remember that M/B ratio = total market value/total shareholders' equity. You can find the total shareholders' equity in the Balance Sheet. Make sure that you may need to adjust the dollar unit for the market value and the book value. To adjust the units, you have to multiply the market cap. by 1 million. Check if your calculation is correct with the spreadsheet. 24. Compute the recent Price-to-Earnings (P/E) ratio. In order to find past share prices, go to a stock's Summary page. Click the stock price chart on the right corner. Set the time range as five years by clicking on 5Y on the top of the chart. Move the cursor to the date (e.g., Jan. 1, 18) to find the close price. Use close price and earnings per share (EPS) to compute the P/E ratio. Remember that EPS = NI/number of shares outstanding. The # of shares outstanding = total market value/current stock price. Assume that the number of shares does not change for the recent three years. Check if your calculation is correct with the spreadsheet. 25. Find the stock beta on the first page of the word file. 26. Assuming the risk-free rate (Rf) of 1 percent and the expected market return (E(Rm)) of 10 percent, find the expected return on the stock (E(Rj)) based on the CAPM equation, E(Rj) = Rf + β(E(Rm) - Rf). 27. Find the dividend yield for the most recent year (DY = the recent dividend/stock price) in the Summary page. Again, you must show your works for the questions above that require computing ratios. Show your calculations by attaching the excel file that includes the functions used to compute the ratios and correctly updated figures. 28. Find Analyst Earnings Estimate for next year. Click on 'Analysis' on the top menu in Yahoo Finance. Use Avg. Earnings Estimate (EPS) for next year (e.g., 2021). 29. Compute Earnings Growth Rate and then estimate next year's stock price (P1) using the estimated earnings and average P/E ratio. 30. Estimate next year's stock price (P1) using the required rate of return based on CAPM. 31. Compute A/R Period, Inventory Period, and A/P Period. 32. Compute Operating Cycle and Cash Cycle.
Case Study Project Financial Ratio Calculation Spreadsheet All numbers in thousands (except for share price, EPS, and Divdend per share) Financial Ratios Formula Apple Inc. HP Inc. 2020 2019 2018 2020 2019 2018 ΔNWC Current year NWC - Previous year NWC (2,875,000) (48,000) (3,084,400) 1,686,800 EBIT (Find this on Income Statement) 69,964,000 69,313,000 76,143,000 3,460,100 5,461,400 6,224,700 OCF EBIT + Depreciation - Taxes 72,268,000 71,096,000 77,911,000 4,002,400 5,141,400 5,419,100 NI (Find this on Income Statement) 57,411,000 55,256,000 59,531,000 1,940,000 3,161,700 3,537,300 CR Total Current Assets/Total Current Liabilities 1.31 1.39 1.40 2.05 2.15 2.04 QR (Current Assets - Inventory)/Current Liabilities 0.94 0.96 0.94 1.86 1.96 1.81 D/E ratio Total Liabilities/Total Shareholders Equity 430% 406% 308% 759% 577% 480% ICR EBIT / Interest expenses 138.00 143.21 163.75 5.09 8.23 8.40 ROE Net Income/Total Shareholders' Equity 387.68% 329.97% 286.06% 28.77% 34.89% 34.46% PM Net Income/Total Sales Revenue 122.12% 100.13% 106.96% 6.72% 8.77% 9.36% MV (Total market value) (Find this on Yahoo Finance summary page. Multiply MV in $billion by 1,000,000) 123,080,000 24,620,000 BV (Total book value) Total shareholders' equity 14,809,000 16,746,000 20,811,000 6,743,400 9,062,600 10,265,800 M/B Total Market Value/Total Shareholders' Equity 8.31 3.65 Current share price (in $) (Find this on Yahoo Finance summary page) 123.08 78.07 No. of shares outstanding Total Market Value/Current Share Price 1,000,000 1,000,000 1,000,000 315,358 315,358 315,358 Past share prices (in $) (Find year-end share prices on the chart, by setting 5 years, in Yahoo Finance summary page) 60.35 91.88 89.82 76.27 88.34 90.66 EPS NI/# of shares outstanding 57.41 55.26 59.53 6.15 10.03 11.22 P/E Share price/EPS 1.05 1.66 1.51 12.40 8.81 8.08 Beta (Find this on Yahoo Finance summary page) 1.3500 0.6966 E(Rj) with CAPM Rf + (beta)(E(Rm) - Rf) 13% 7% DY Dividend/Share Price 1.28% 0.79% Analyst earnings estimate (Find average earnings estimate for next year on Yahoo Finance) 5.20 7.46 Earnigns growth rate (EPS1 - EPS0)/EPS0 -90.94% 21.27% P1 with P/E ratio EPS * (1 + earnigns growth rate) * EPS 7.32 72.84 P1 with CAPM Curent share price + (1 + E(Rj)) 68.29 81.81 A/R period 365/Accounts receivable turnover 138.65 123.13 446.10 363.67 Inventory period 365/Inventory turnover 116.45 112.19 72.72 67.36 A/P period 365/Accounts payable turnover 140.08 131.21 148.30 133.01 Operating cycle A/R period + Inventory period 255.10 235.32 518.82 431.03 Cash cycle Oprating cycle - A/P period 115.02 104.11 370.52 298.03 Accounts Receivable (Find this on Balance Sheet) 17,212,000 18,503,000 18,729,000 33,719,200 36,832,600 35,039,200 Inventory (Find this on Balance Sheet) 9,700,000 12,205,000 12,625,000 3,817,000 4,209,700 4,934,700 Total Current Assets (Find this on Balance Sheet) 34,418,000 38,867,000 38,335,000 42,135,800 46,044,400 45,102,700 Total Assets (Find this on Balance Sheet) 78,497,000 84,681,000 84,896,000 57,947,600 61,336,400 59,521,300 Accounts Payable (Find this on Balance Sheet) 11,727,000 14,622,000 14,417,000 7,552,900 8,816,000 9,240,800 Total Current Liabilities (Find this on Balance Sheet) 26,303,000 27,877,000 27,297,000 20,569,500 21,393,700 22,138,800 Total Liabilities (Find this on Balance Sheet) 63,688,000 67,935,000 64,085,000 51,204,200 52,273,800 49,255,500 Total Shareholders' Equity (Find this on Balance Sheet) 14,809,000 16,746,000 20,811,000 6,743,400 9,062,600 10,265,800 Sales Revenue (Find this on Income Statement) 47,011,000 55,184,000 55,656,000 28,862,800 36,066,900 37,795,400 Cost of Goods sold (Cost of revenue) (Find this on Income Statement) 34,329,000 40,391,000 41,454,000 20,143,200 24,775,800 25,667,300 Interest expenses (Find this on Income Statement) 507,000 484,000 465,000 680,000 664,000 741,300 Income taxes (Find this on Income Statement) 742,000 1,380,000 1,319,000 840,100 1,626,500 1,945,900 Depreciation (Find this on Statement of Cash Flows) 3,046,000 3,163,000 3,087,000 1,382,400 1,306,500 1,140,300 Dividend (Find this on Yahoo Finance summary page) 0.77 0.60 Risk-free rate (Use rates as given in the cells right) 1% 1% Expected market return (Use rates as given in the cells right) 10% 10% Net Working Capital Total current assets - Total current liabilities 8,115,000 10,990,000 11,038,000 21,566,300 24,650,700 22,963,900 Average Accounts Receivables (Current A/R + Last A/R)/2 17,857,500 18,616,000 35,275,900 35,935,900 A/R Turnover Sales revenue/Average A/R 2.63 2.96 0.82 1.00 Average Inventory (Current inventory + Last inventory)/2 10,952,500 12,415,000 4,013,350 4,572,200 Inventory Turnover COGs/Average invenotry 3.13 3.25 5.02 5.42 Average Accounts Payable Current A/P + Last A/P)/2 13,174,500 14,519,500 8,184,450 9,028,400 A/P Turnover COGS/Average A/P 2.61 2.78 2.46 2.74
