1 Excel Case #1-Analysis of Intel Corporation (INTC) and Taiwan Semiconductor Ma
ID: 2809191 • Letter: 1
Question
1 Excel Case #1-Analysis of Intel Corporation (INTC) and Taiwan Semiconductor Manufacturing Company (TSM) 2 all answers should be denoted on the appropriate Financial Stmts tab of this worksheet below the income statements 4 Calculate the following for each of the five years: Net Working Capital format: currency, O decimal places Capital Structure (debt %) Capital structure (equity %) format: percentage, 2 decimal places format: percentage, 2 decimal places format: currency, 0 decimal places format: number, 2 decimal places format: number, 2 decimal places format: number, 2 decimal places format: number, 2 decimal places format: number, 2 decimal places format: number, 2 decimal places format: number, 2 decimal places format: percentage, 2 decimal places format: percentage, 2 decimal places format: percentage, 2 decimal places format: number, 2 decimal places format: percentage, 2 decimal places 10 Operating Cash Flow 12 13 14 15 16 17 18 19 20 21 Current Ratio Times Interest Earned Inventory Turnover Days' Sales in Inventory Receivables Turnover 23 24 25 26 27 28 29 30 31 32 Days'Sales in Receivables 10 Total Asset Turnover 11Profit Margin 12 Return on Assets 13 Return on Equity 14Price-earnings ratio 34 35 36 15 Gross Margin% Gross Profit / RevenueExplanation / Answer
Ratios Intel Corporation Taiwan Semiconductor Formula 2017 2016 2015 2014 2013 2017 2016 2015 2014 2013 1 Net Working Capital Current Assets - Current Liabilities (USD in millions) 12079 15206 24689 11711 18516 470313 469443 506972 401781 154513 29500-17421 35508-20302 40356-15667 27730-16019 32084-13568 857203-386890 817729-348286 746744-239772 626566-224785 358487-203974 2 Capital Structure (Debt%) 1-Equity % 44.00% 41.56% 40.73% 39.25% 36.92% 25.00% 27.95% 27.96% 31.62% 33.97% Capital Structure (Equity%) 56.00% 58.44% 59.27% 60.75% 63.08% 75.00% 72.05% 72.04% 68.38% 66.03% 69019/123249 66226/113327 61085/103065 55865/91956 58256/92358 1493747/1991732 1359051/1886297 1194008/1657397 1022234/1494853 833846/1262801 3 Operating Cash Flow Net Income + Noncash Expenses+Changes in working capital 31101 34778 45494 32348 36656 1082114 1031597 1038691 862688 499966 9601+12079 10316+15206 11420+24689 11704+11711 9620+18516 344998+470313+266803 331714+230440+469443 302833+506972+228886 254184+401781+206723 183978+154513+161475 9421 9256 9385 8933 8520 266803 230440 228886 206723 161475 Depreciation = EBITDA-EBIT (29127-20352+646) 21459-12936+733 23260-14212+337 24542-15801+192 20887-12611+244 659635-396162+3330 613056-385922+3306 576174-350478+3190 505561-302074+3236 374790-215962+2647 4 Current ratio Current Assets/Current Liabilities 1.69 1.75 2.58 1.73 2.36 2.22 2.35 3.11 2.79 1.76 29500/17421 35508/20302 40356/15667 27730/16019 32084/13568 857203/386890 817729/348286 746744/239772 626566/224785 358487/203974 5 Times Interest Earned EBIT/Interest 32.50 18.65 43.17 83.30 52.68 119.97 117.73 110.87 94.35 82.59 (20352+646)/646 (12936+733)/733 (14212+337)/337 (15801+192)/192 (12611+244)/244 (396162+3330)/3330 (385922+3306)/3306 (350478+3190)/3190 (302074+3236)/3236 (215962+2647)/2647 6 Inventory Turnover Cost of Goods Sold/Inventory 3.39 4.18 4.00 4.74 5.08 6.53 9.72 6.46 5.81 8.42 23692/6983 23196/5553 20676/5167 20261/4273 21187/4172 482616/73881 473077/48682 433118/67052 385113/66338 315642/37495 7 Days Sales in Inventory (In Days) 365/Inventory Turnover 107.58 87.38 91.21 76.98 71.87 55.88 37.56 56.51 62.87 43.36 8 Receivables Turnover Sales/Accounts Receivables 11.19 12.66 11.56 12.62 14.71 7.96 7.30 9.80 6.60 8.24 62761/5607 59387/4690 55355/4787 55870/4427 52708/3582 977447/122789 947938/129785 843497/86054 762806/115534 597024/72428 9 Days Sales in Receivables(In Days) 365/Receivables Turnover 32.61 28.83 31.56 28.92 24.81 45.85 49.97 37.24 55.28 44.28 10 Total Asset turnover Sales/Total assets 0.51 0.52 0.54 0.61 0.57 0.49 0.50 0.51 0.51 0.47 62761/123249 59387/113327 55355/103065 55870/91956 52708/92358 977447/1991732 947938/1886297 843497/1657397 762806/1494853 597024/1262801 11 Profit Margin Net Income/Sales 15.30% 17.37% 20.63% 20.95% 18.25% 35.30% 34.99% 35.90% 33.34% 30.82% 9601/62761 10316/59387 11420/55355 11704/55870 9620/52708 344998/977447 331714/947938 302851/843497 254301/762806 183978/597024 12 Return on Assets Net Income/Total Assets 7.79% 9.10% 11.08% 12.73% 10.42% 17.32% 17.59% 18.27% 17.01% 14.57% 9601/123249 10316/113327 11420/103065 11704/91956 9620/92358 344998/1991732 331714/1886297 302851/1657397 254301/1494853 183978/1262801 13 Return on Equity Net Income/Shareholders Equity 13.91% 15.58% 18.70% 20.95% 16.51% 23.10% 24.41% 25.36% 24.88% 22.06% 9601/69019 10316/66226 11420/61085 11704/55865 9620/58256 344998/1493747 331714/1359051 302851/1194008 254301/1022234 183978/833846 14 Price-Earnings Ratio Price/Earnings 23.20 17.11 14.79 15.71 13.74 18.27 13.76 11.97 13.99 15.03 46.16/1.99 36.27/2.12 34.45/2.33 36.29/2.31 25.96/1.89 39.65/2.17 28.75/2.09 22.75/1.90 22.38/1.60 17.44/1.16 15 Gross Margin % Gross Profit/Revenue 62.25% 60.94% 62.65% 63.74% 59.80% 50.62% 50.09% 48.65% 49.51% 47.13% 39069/62761 36191/59387 34679/55355 35609/55870 31521/52708 494831/977447 474861/947938 410380/843497 377694/762806 281382/597024
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.