finance tech assignment

Anonymous
timer Asked: Nov 12th, 2016

Question description

Assignment from nickkynickky


Tasks 1. See attached financial statement and ratio workbooks for the following companies a. Abbott Laboratories b. Bristol-Myers Squibb c. Glaxosmithklien d. Eli Lilly & Co. e. Merck & Co. f. Novartis g. Pfizer 2. Use Eli Lilly & Co. pharmaceutical company, to complete the following: A. The product and service of Eli Lilly B. The target market of Eli Lilly C. The competition and market structure of Eli Lilly D. Use the major income statement figures and put the figures in the pie chart. E. Use the major balance sheet figures to represent them in pie chart F. Outline and explain the trend G. What is market distribution channels of Eli Lilly? 3. Complete the following elements of an industry analysis: a. Table and chart showing an index of sales for all seven companies using FYE Dec 05 as the base year b. Table and chart showing an index of net income for all seven companies using FYE Dec 05 as the base year. c. Table and chart showing an index of total assets for all seven companies using FYE Dec 05 as the base year. d. Table and chart showing an index of net PPE for all seven companies using FYE Dec 05 as the base year. e. Table and chart showing an index of inventory for all seven companies using FYE Dec 05 as the base year. f. Table and chart showing the gross profit margins for all seven companies g. Table and chart showing the inventory turnover for all seven companies h. Table and chart showing the net PPE turnover for all seven companies 4. Put the tables and charts from step 2 and 3 into a PowerPoint slide presentation. 5. Use Eli Lilly & Co. company, complete the following: a. Download and review the 10-K document for your company to develop an understanding of its business model b. Conduct an IODE financial analysis of the company, including but not limited to: i. Balance sheet analysis of structure and trends ii. Income statement analysis of structure and trends iii. Analysis of the DuPont ratio structure and trends iv. Analysis of key profitability ratio structure and trends v. Analysis of key efficiency ratios and trends vi. Analysis of key leverage ratios and trends c. Prepare a set of exhibit tables and/or charts that support the findings and implication of each step of your analysis. Put these exhibits into a PowerPoint slide presentation. d. Develop some conclusions regarding the sustainability of the business model for Eli Lilly Pharmaceutical company in the context of what you know about the industry (e.g., from your industry analysis in Step 2 and 3, and comparison to other companies in the sample). Include these conclusions in your presentation slides.
Balance Sheets Lilly (Eli) & Co. TICKER: SIC: GICS: ASSETS Cash & Equivalents Net Receivables Inventories Prepaid Expenses Other Current Assets Total Current Assets Gross Plant, Property & Equipment Accumulated Depreciation Net Plant, Property & Equipment Investments at Equity Other Investments Intangibles Deferred Charges Other Assets TOTAL ASSETS LIABILITIES Long Term Debt Due In One Year Notes Payable Accounts Payable Taxes Payable Accrued Expenses Other Current Liabilities Total Current Liabilities Long Term Debt Deferred Taxes Investment Tax Credit Other Liabilities TOTAL LIABILITIES Redeemable Noncontrolling Int. EQUITY Preferred Stock - Redeemable Preferred Stock - Nonredeemable Total Preferred Stock Common Stock Capital Surplus Retained Earnings Less: Treasury Stock Common Equity Stockholder's Equity - Parent Nonredeemable Noncontrolling Int. TOTAL EQUITY TOTAL LIABILITIES & EQUITY COMMON SHARES OUTSTANDING ANNUAL BALANCE SHEET LLY 2834 35202010 Dec-15 Dec-14 Dec-13 Dec-12 Dec-11 Dec-10 Dec-09 Dec-08 Dec-07 Dec-06 Dec-05 $ 4.451,80 $ 4.071,60 $ 3.445,80 @CF $ 604,40 -----------------$ 12.573,60 $ 16.660,90 $ 8.607,40 -----------------$ 8.053,50 @CF $ 3.646,60 $ 9.074,70 $ $ 2.220,50 -----------------$ 35.568,90 $ 4.827,00 $ 3.801,30 $ 2.740,00 @CF $ 811,50 -----------------$ 12.179,80 $ 16.029,30 $ 8.065,40 -----------------$ 7.963,90 @CF $ 4.568,90 $ 4.642,30 $ $ 7.823,30 -----------------$ 37.178,20 $ 5.397,30 $ 4.022,80 $ 2.928,80 @CF $ 755,80 -----------------$ 13.104,70 $ 15.646,70 $ 7.671,20 -----------------$ 7.975,50 @CF $ 7.624,90 $ 4.331,10 $ $ 2.212,50 -----------------$ 35.248,70 $ 5.684,30 $ 3.888,30 $ 2.643,80 @CF $ 822,30 -----------------$ 13.038,70 $ 14.918,00 $ 7.157,80 -----------------$ 7.760,20 @CF $ 6.313,30 $ 4.752,70 $ $ 2.534,00 -----------------$ 34.398,90 $ 6.897,10 $ 4.237,90 $ 2.299,80 @CF $ 813,40 -----------------$ 14.248,20 $ 14.594,10 $ 6.833,80 -----------------$ 7.760,30 @CF $ 4.029,80 $ 5.128,10 $ $ 2.493,40 -----------------$ 33.659,80 $ 6.727,00 $ 4.158,10 $ 2.517,70 @CF $ 1.437,20 -----------------$ 14.840,00 $ 14.486,60 $ 6.545,90 -----------------$ 7.940,70 @CF $ 1.779,50 $ 4.818,80 $ $ 1.622,40 -----------------$ 31.001,40 $ 4.497,60 $ 3.831,80 $ 2.849,90 @CF $ 1.307,20 -----------------$ 12.486,50 $ 15.100,00 $ 6.902,60 -----------------$ 8.197,40 @CF $ 1.155,80 $ 3.699,80 $ $ 1.921,40 -----------------$ 27.460,90 $ 5.926,10 $ 3.277,30 $ 2.493,20 $ 374,60 $ 382,10 -----------------$ 12.453,30 $ 15.315,90 $ 6.689,60 -----------------$ 8.626,30 @CF $ 1.544,60 $ 4.054,10 $ $ 2.534,30 -----------------$ 29.212,60 $ 4.831,20 $ 3.704,80 $ 2.523,70 $ 613,60 $ 642,80 -----------------$ 12.316,10 $ 14.841,30 $ 6.266,20 -----------------$ 8.575,10 @CF $ 577,10 $ 2.455,40 $ 1.670,50 $ 1.280,60 -----------------$ 26.874,80 $ 3.891,00 $ 2.694,40 $ 2.270,30 $ 319,50 $ 519,20 -----------------$ 9.694,40 $ 13.716,70 $ 5.564,40 -----------------$ 8.152,30 @CF $ 1.001,90 $ 130,00 $ 1.091,50 $ 1.885,30 -----------------$ 21.955,40 $ 5.037,70 $ 2.761,70 $ 1.878,00 $ 362,00 $ 756,40 $ 6,10 $ $ 1.338,20 $ 358,90 @CF $ 6.526,40 -----------------$ 8.229,60 $ 7.972,40 @CF $ $ 4.776,60 -----------------$ 20.978,60 $ - $ 8,10 $ 2.680,60 $ 1.128,10 $ 93,50 @CF $ 7.297,20 -----------------$ 11.207,50 $ 5.367,70 @CF $ $ 5.214,90 -----------------$ 21.790,10 $ - $ 1.012,60 $ $ 1.119,30 $ 254,40 @CF $ 6.530,30 -----------------$ 8.916,60 $ 4.200,30 @CF $ $ 4.491,10 -----------------$ 17.608,00 $ - $ 11,90 $ $ 1.188,30 $ 143,50 @CF $ 7.045,80 -----------------$ 8.389,50 $ 5.519,40 @CF $ $ 5.716,10 -----------------$ 19.625,00 $ - $ 1.516,80 $ 5,50 $ 1.125,20 $ 261,60 @CF $ 6.021,80 -----------------$ 8.930,90 $ 5.464,70 @CF $ $ 5.728,60 -----------------$ 20.124,20 $ - $ 18,20 $ 137,80 $ 1.072,20 $ 457,50 @CF $ 5.415,70 -----------------$ 7.101,40 $ 6.770,50 @CF $ $ 4.716,70 -----------------$ 18.588,60 $ - $ 20,30 $ 7,10 $ 968,10 $ 346,70 @CF $ 5.225,90 -----------------$ 6.568,10 $ 6.634,70 $ 84,80 $ $ 4.648,00 -----------------$ 17.935,60 $ - $ 420,40 $ 5.425,90 $ 885,80 $ 229,20 @CF $ 6.148,40 -----------------$ 13.109,70 $ 4.615,70 $ 74,70 $ $ 4.674,80 -----------------$ 22.474,90 $ 2,40 $ 395,10 $ 18,60 $ 924,40 $ 238,40 @CF $ 3.860,30 -----------------$ 5.436,80 $ 4.593,50 $ 287,50 $ $ 3.053,10 -----------------$ 13.370,90 @CF $ 210,80 $ 8,60 $ 789,40 $ 640,60 @CF $ 3.436,10 -----------------$ 5.085,50 $ 3.494,40 $ 62,20 $ $ 2.332,60 -----------------$ 10.974,70 @CF $ 721,30 $ 13,40 $ 781,30 $ 884,90 @CF $ 3.315,40 $ $ -----------------$ $ 691,30 $ 5.552,10 $ 11.431,10 $ 3.103,20 -----------------$ 14.571,30 -----------------$ 14.571,30 $ 19,00 -----------------$ 14.590,30 -----------------$ 35.568,90 1.055,27 $ $ -----------------$ $ 694,60 $ 5.292,30 $ 12.490,90 $ 3.104,60 -----------------$ 15.373,20 -----------------$ 15.373,20 $ 14,90 -----------------$ 15.388,10 -----------------$ 37.178,20 1.060,63 $ $ -----------------$ $ 698,50 $ 5.050,00 $ 14.989,70 $ 3.106,80 -----------------$ 17.631,40 -----------------$ 17.631,40 $ 9,30 -----------------$ 17.640,70 -----------------$ 35.248,70 1.066,80 $ $ -----------------$ $ 716,60 $ 4.963,10 $ 12.291,10 $ 3.205,60 -----------------$ 14.765,20 -----------------$ 14.765,20 $ 8,70 -----------------$ 14.773,90 -----------------$ 34.398,90 1.093,64 $ $ -----------------$ $ 724,10 $ 4.886,80 $ 11.039,20 $ 3.108,40 -----------------$ 13.541,70 -----------------$ 13.541,70 $ (6,10) -----------------$ 13.535,60 -----------------$ 33.659,80 1.107,79 $ $ -----------------$ $ 721,30 $ 4.746,10 $ 10.062,50 $ 3.109,60 -----------------$ 12.420,30 -----------------$ 12.420,30 $ (7,50) -----------------$ 12.412,80 -----------------$ 31.001,40 1.102,29 $ $ -----------------$ $ 718,70 $ 4.558,20 $ 7.358,50 $ 3.111,70 -----------------$ 9.523,70 -----------------$ 9.523,70 $ 1,60 -----------------$ 9.525,30 -----------------$ 27.460,90 1.099,03 $ $ -----------------$ $ 711,10 $ 3.890,30 $ 4.868,10 $ 2.734,20 -----------------$ 6.735,30 -----------------$ 6.735,30 @NA -----------------$ 6.735,30 -----------------$ 29.212,60 1.096,06 $ $ -----------------$ $ 709,50 $ 3.710,00 $ 11.819,90 $ 2.735,50 -----------------$ 13.503,90 -----------------$ 13.503,90 @NA -----------------$ 13.503,90 -----------------$ 26.874,80 1.094,31 $ $ -----------------$ $ 707,90 $ 3.471,20 $ 9.538,00 $ 2.736,40 -----------------$ 10.980,70 -----------------$ 10.980,70 @NA -----------------$ 10.980,70 -----------------$ 21.955,40 1.091,67 $ $ $ 10.795,80 $ 13.136,00 $ 5.223,50 $ 7.912,50 @CF $ 1.296,60 $ 139,60 $ 2.419,60 $ 2.016,70 $ 24.580,80 $ 5.716,30 $ 5.763,50 $ 695,10 $ $ 1.614,00 $ 13.788,90 @CF - $ $ 706,90 $ 3.217,50 $ 9.606,60 $ 2.739,10 $ 10.791,90 $ 10.791,90 @NA $ 10.791,90 $ 24.580,80 1.090,14 Income Statements Lilly (Eli) & Co. TICKER: SIC: GICS: Sales Cost of Goods Sold Gross Profit Selling, General, & Administrative Expense Operating Income Before Deprec. Depreciation, Depletion, & Amortization Operating Profit Interest Expense Non-Operating Income/Expense Special Items Pretax Income Total Income Taxes Minority Interest Noncontrolling Interest - Inc Acc -----------------Income Before Extraordinary Items & Discontinued Operations Preferred Dividends -----------------Available for Common Savings Due to Common Stock Equivalents -----------------Adjusted Available for Common Extraordinary Items Discontinued Operations -----------------Adjusted Net Income Income to Company Incl Extraordinary Items & Disc Ops Earnings Per Share Basic Excluding Extra Items & Disc Op Earnings Per Share Basic Including Extra Items & Disc Op Earnings Per Share DilutedExcluding Extra Items & Disc Op Earning Per Share Diluted Including Extra Items & Disc Op EPS Basic from Operations EPS Diluted from Ops Dividends Per Share Com Shares for Diluted EPS Com Shares for Basic Eps ANNUAL INCOME STATEMENT LLY 2834 35202010 Dec-15 $ 19.958,70 $ 3.534,80 -----------------$ 16.423,90 Dec-14 $ 19.615,60 $ 3.637,50 -----------------$ 15.978,10 Dec-13 $ 23.113,10 $ 3.578,30 -----------------$ 19.534,80 Dec-12 $ 22.603,40 $ 3.479,50 -----------------$ 19.123,90 Dec-11 $ 24.286,50 $ 3.866,50 -----------------$ 20.420,00 Dec-10 $ 23.076,00 $ 3.231,40 -----------------$ 19.844,60 Dec-09 $ 21.836,00 $ 3.156,50 -----------------$ 18.679,50 Dec-08 $ 20.378,00 $ 3.400,60 -----------------$ 16.977,40 Dec-07 $ 18.633,50 $ 3.393,70 -----------------$ 15.239,80 Dec-06 $ 15.691,00 $ 2.919,10 -----------------$ 12.771,90 Dec-05 $ 14.645,30 $ 2.897,00 $ 11.329,40 -----------------$ 5.094,50 $ 11.235,40 -----------------$ 4.742,70 $ 12.656,90 -----------------$ 6.877,90 $ 12.791,60 -----------------$ 6.332,30 $ 12.900,70 -----------------$ 7.519,30 $ 11.937,60 -----------------$ 7.907,00 $ 11.219,00 -----------------$ 7.460,50 $ 10.452,30 -----------------$ 6.525,10 $ 9.581,80 -----------------$ 5.658,00 $ 8.019,10 -----------------$ 4.752,80 $ 7.522,50 $ 1.349,40 -----------------$ 3.745,10 $ 216,00 $ 483,30 $ (1.222,40) -----------------$ 2.790,00 $ 381,60 -----------------$ 2.408,40 @CF ------------------ $ 1.295,00 -----------------$ 3.447,70 $ 187,10 $ 435,60 $ (695,90) -----------------$ 3.000,30 $ 609,80 -----------------$ 2.390,50 @CF ------------------ $ 1.329,80 -----------------$ 5.548,10 $ 184,20 $ 207,70 $ 317,70 -----------------$ 5.889,30 $ 1.204,50 -----------------$ 4.684,80 @CF ------------------ $ 1.317,00 -----------------$ 5.015,30 $ 198,80 $ 85,00 $ 506,70 -----------------$ 5.408,20 $ 1.319,60 -----------------$ 4.088,60 @CF ------------------ $ 1.201,40 -----------------$ 6.317,90 $ 211,70 $ 32,70 $ (789,40) -----------------$ 5.349,50 $ 1.001,80 -----------------$ 4.347,70 @CF ------------------ $ 1.134,80 -----------------$ 6.772,20 $ 211,50 $ 206,50 $ (242,00) -----------------$ 6.525,20 $ 1.455,70 -----------------$ 5.069,50 @CF ------------------ $ 1.090,50 -----------------$ 6.370,00 $ 291,50 $ 62,00 $ (782,70) -----------------$ 5.357,80 $ 1.029,00 -----------------$ 4.328,80 @CF ------------------ $ 925,10 -----------------$ 5.600,00 $ 276,50 $ 250,40 $ (6.881,50) -----------------$ (1.307,60) $ 764,30 -----------------@NA @CF ------------------ $ 855,10 -----------------$ 4.802,90 $ 323,60 $ 445,60 $ (1.048,10) -----------------$ 3.876,80 $ 923,80 -----------------@NA @CF ------------------ $ 627,40 -----------------$ 4.125,40 $ 344,80 $ 582,60 $ (945,20) -----------------$ 3.418,00 $ 755,30 -----------------@NA @CF ------------------ $ $ 2.408,40 $ -----------------$ 2.408,40 $ 2.390,50 $ -----------------$ 2.390,50 $ 4.684,80 $ -----------------$ 4.684,80 $ 4.088,60 $ -----------------$ 4.088,60 $ 4.347,70 $ -----------------$ 4.347,70 $ 5.069,50 $ -----------------$ 5.069,50 $ 4.328,80 $ -----------------$ 4.328,80 $ (2.071,90) $ -----------------$ (2.071,90) $ 2.953,00 $ -----------------$ 2.953,00 $ 2.662,70 $ -----------------$ 2.662,70 $ 2.001,60 $ - $ -----------------$ 2.408,40 $ $ -----------------$ 2.408,40 $ -----------------$ 2.390,50 $ $ -----------------$ 2.390,50 $ -----------------$ 4.684,80 $ $ -----------------$ 4.684,80 $ -----------------$ 4.088,60 $ $ -----------------$ 4.088,60 $ -----------------$ 4.347,70 $ $ -----------------$ 4.347,70 $ -----------------$ 5.069,50 $ $ -----------------$ 5.069,50 $ -----------------$ 4.328,80 $ $ -----------------$ 4.328,80 $ -----------------$ (2.071,90) $ $ -----------------$ (2.071,90) $ -----------------$ 2.953,00 $ $ -----------------$ 2.953,00 $ -----------------$ 2.662,70 $ $ -----------------$ 2.662,70 $ $ 2.408,40 $ 2.390,50 $ 4.684,80 $ 4.088,60 $ 4.347,70 $ 5.069,50 $ 4.328,80 @NA @NA @NA @NA $ 2,27 $ 2,23 $ 4,33 $ 3,67 $ 3,90 $ 4,58 $ 3,94 $ (1,89) $ 2,71 $ 2,45 $ 1,84 $ 2,27 $ 2,23 $ 4,33 $ 3,67 $ 3,90 $ 4,58 $ 3,94 $ (1,89) $ 2,71 $ 2,45 $ 1,82 $ 2,26 $ 2,23 $ 4,32 $ 3,66 $ 3,90 $ 4,58 $ 3,94 $ (1,89) $ 2,71 $ 2,45 $ 1,83 $ $ $ $ 2,26 3,05 3,03 2,00 1.065,72 1.061,91 $ $ $ $ 2,23 2,78 2,78 1,96 1.074,29 1.069,93 $ $ $ $ 4,32 4,15 4,14 1,96 1.084,77 1.080,87 $ $ $ $ 3,66 3,40 3,39 1,96 1.117,29 1.113,18 $ $ $ $ 3,90 4,41 4,41 1,96 1.113,97 1.113,92 $ $ $ $ 4,58 4,82 4,82 1,96 1.105,81 1.105,79 $ $ $ $ 3,94 4,36 4,36 1,96 1.098,37 1.098,34 $ $ $ $ 2,71 3,55 3,55 1,70 1.090,75 1.090,43 $ $ $ $ 2,45 3,19 3,18 1,60 1.087,49 1.086,24 $ $ $ $ 1,81 2,88 2,87 1,52 1.092,15 1.088,75 (1,89) 3,88 3,88 1,88 1.094,50 1.094,50 $ $ $ $ $ 11.748,30 $ 4.225,80 577,20 $ 3.648,60 $ 245,70 $ 559,90 $ (1.245,30) $ 2.717,50 $ 715,90 -----------------@NA @CF $ 2.001,60 - $ 2.001,60 $ (22,00) $ $ 1.979,60 Financial Ratios Lilly (Eli) & Co. DuPont Analysis Financial Analysis Dec-15 Dec-14 Dec-13 Dec-12 Dec-11 Dec-10 Dec-09 Dec-08 Dec-07 Dec-06 Dec-05 Net Profitability Total Asset Turnover Equity Multiplier 12,07% 0,549 2,427 12,19% 0,542 2,193 20,27% 0,664 2,149 18,09% 0,664 2,404 17,90% 0,751 2,492 21,97% 0,789 2,665 19,82% 0,771 3,485 -10,17% 0,727 2,771 15,85% 0,763 1,994 16,97% 0,674 2,137 13,67% Return on Equity 16,07% 14,48% 28,91% 28,89% 33,51% 46,22% 53,24% -20,47% 24,12% 24,46% 82,29% 18,76% 12,07% 6,62% 16,07% 81,46% 17,58% 12,19% 6,60% 14,48% 84,52% 24,00% 20,27% 13,45% 28,91% 84,61% 22,19% 18,09% 12,01% 28,89% 84,08% 26,01% 17,90% 13,45% 33,51% 86,00% 29,35% 21,97% 17,34% 46,22% 85,54% 29,17% 19,82% 15,28% 53,24% 83,31% 27,48% -10,17% -7,39% -20,47% 81,79% 25,78% 15,85% 12,09% 24,12% 81,40% 26,29% 16,97% 11,44% 24,46% 5,070 71,989 1,143 319,370 2,492 0,832 0,549 5,014 72,794 1,283 284,414 2,461 0,832 0,542 5,843 62,466 1,284 284,213 2,938 1,063 0,664 5,563 65,611 1,408 259,292 2,913 1,109 0,664 5,785 63,091 1,605 227,387 3,094 1,365 0,751 5,776 63,189 1,204 303,146 2,860 1,482 0,789 6,143 59,416 1,182 308,923 2,596 1,376 0,771 5,837 62,530 1,356 269,242 2,369 1,301 0,727 5,824 62,675 1,416 257,803 2,228 1,390 0,763 5,752 63,459 1,407 259,349 1,953 1,205 0,674 1,528 1,036 0,541 17,338 3,439 106,141 1,087 0,770 0,431 18,427 3,069 118,929 1,470 1,056 0,605 30,120 3,348 109,010 1,554 1,141 0,678 25,228 3,305 110,426 1,595 1,247 0,772 29,844 3,321 109,912 2,090 1,533 0,947 32,020 2,842 128,434 1,901 1,268 0,685 21,852 3,790 96,304 0,950 0,702 0,452 20,253 3,723 98,027 2,265 1,570 0,889 14,842 4,256 85,758 1,906 1,295 0,765 11,965 4,216 86,565 1,889 1,364 0,881 14,850 0,590 0,358 1,438 0,586 0,285 1,416 0,500 0,247 0,998 0,571 0,327 1,328 0,598 0,333 1,487 0,600 0,371 1,498 0,653 0,414 1,883 0,769 0,321 3,337 0,498 0,295 0,990 0,500 0,268 0,999 0,561 0,328 1,278 Profitability Analysis Gross Profitability Operating Profitability Net Profitability Return on Assets Return on Equity 80,22% 24,91% 13,67% Efficiency Analysis Receivable Turnover Receivable DSO Inventory Turnover Inventory DCO Net PP&E Turnover Net Fixed Asset Turnover Total Asset Turnover Liquidity Analysis Current Ratio Quick Ratio Cash Ratio Times Interest Earned Accounts Payable Turnover Accounts Payable DCO (DPO) Leverage Ratio Total Debt to Total Assets Long Term Term Debt to Total Assets Total Debt to Total Equity

Tutor Answer

(Top Tutor) Studypool Tutor
School: Duke University
Studypool has helped 1,244,100 students
flag Report DMCA
Similar Questions
Hot Questions
Related Tags
Study Guides

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors