Hi,
I need the below 2 questions answered.
I have attached the spreadsheet with the data pertaining to the questions.
ACG2021 Comprehensive Financial Statement Analysis Project Spring 2017 Group Members Last Name First Name Instructions: You will need the annual reports for Walgreens for fiscal years 2015 and 2014, along with the annual report fo Note that neither Walgreens nor CVS have calendar year ends, both use fiscal years that end on a date that is on August 31, 2015 and the 2014 fiscal year relates to the year which ended August 31, 2014). Requirement 1: Basic Questions Answer the questions for Walgreens for the fiscal 2015 year in the shaded input box. The location (source) where you can find the answer is provided for you above the shaded input box. Use only PDF file allows you to quickly find the pages where the different Items commence. Requirement 2 - 6: Ratio and Financial Analysis For the ratios in requirements 2 and 3, provide the formula of the ratio, the amounts used to calculate the rati The first one (a.) was completed as an illustration. The ratio formulas are listed in pages 775-777 in chapter 13 of your textbook. I have added the number of th calculate them. When calculating the ratios, only use the amounts included in the audited financial statements (Item 8. Finan shown in other sections of the annual report. All other sections of the annual report except for the financial st ratios or financial indicators are they wish, not necessarily as the textbook prescribes. If you pick up amounts i Since the fiscal 2015 financial statements are comparative, they include info for both fiscal years 2015 and 201 However, some ratios for the 2014 year will require you go to back an additional year because some of the 201 example, the inventory turnover for 2014 uses average inventory calculated by using the 2014 ending inventor in 2013). You need to obtain the fiscal 2013 ending inventory balance included in the 2014 financial statement For ratios that include averages, use a simple average calculation (beginning + ending balance / 2). Requirement 7 - 11: Ratio and Financial Analysis (Walgreen's versus competitor CVS) Copy the following from the "Requirement 2 - 6" sheet to the columns on the left-hand side of the spreadshee You only need to calculate the 2015 ratio for CVS on the columns on the right-hand side of the spreadsheet. Other: Note the denomination of the amounts reported since they are in millions. So, the Cash and Cash Equivalents Certain terminology may be a bit different than what you learned in the textbook. For example, instead of Net amount reported on the Income Statement (which they call the "Statement of Earnings"). If you have any ques Several questions have multiple parts. Make sure you answer every part in order to receive full credit. Please do not add columns or rows to this file. I will be using this file to grade and my grading template is in th The points assigned to each question are included in the boxes highlighted in pink on the right-hand side of th t Analysis Project #REF! #REF! #REF! #REF! s for fiscal years 2015 and 2014, along with the annual report for CVS for fiscal year 2015. They will be provided to you in pdf fo endar year ends, both use fiscal years that end on a date that is not December 31 (e.g. the 2015 fiscal year relates to the fiscal y elates to the year which ended August 31, 2014). scal 2015 year in the shaded input box. answer is provided for you above the shaded input box. Use only that source to answer the question. The Table of Contents of th here the different Items commence. e the formula of the ratio, the amounts used to calculate the ratio and what the ratio measures. Enter your answers in the shade tion. 7 in chapter 13 of your textbook. I have added the number of the ratio listed in pages 775-777 to each ratio so that you know e unts included in the audited financial statements (Item 8. Financial Statements and Supplementary Data); do not just enter th All other sections of the annual report except for the financial statements are not audited and as such the preparer can calculat t necessarily as the textbook prescribes. If you pick up amounts in other sections you risk getting the question wrong. comparative, they include info for both fiscal years 2015 and 2014. Use those financial statements to answer the questions abou quire you go to back an additional year because some of the 2014 ratios use averages which are calculated using the 2014 + 20 s average inventory calculated by using the 2014 ending inventory balance + the 2014 beginning inventory balance (which is the nding inventory balance included in the 2014 financial statements which have been provided to you. average calculation (beginning + ending balance / 2). Walgreen's versus competitor CVS) 6" sheet to the columns on the left-hand side of the spreadsheet: Ratio formula and actual ratio answer for Walgreens 2015 CVS on the columns on the right-hand side of the spreadsheet. ted since they are in millions. So, the Cash and Cash Equivalents balance of "$3,000" means 3,000 million, or $3 billion. n what you learned in the textbook. For example, instead of Net Income, Walgreens reports "Net Earnings" as the final which they call the "Statement of Earnings"). If you have any questions on the terminology, please contact me. ure you answer every part in order to receive full credit. . I will be using this file to grade and my grading template is in this same format. uded in the boxes highlighted in pink on the right-hand side of the question. There are 207 points which is equivalent to 100%. Walgreens 2015 Fiscal Year as of August 31, 2015 Ratio (Refer to pgs 775 - 777 for ratio formula) Copy Walgreens ratio from "Requirement 2 - 6" Sheet Ratio Requirement 2 Evaluate PROFITABILITY. Using information you have learned in the text and elsewhere, evaluate Walgreens' profitability for 2015 compared with 2014. In you following ratios and then comment on what those ratios indicate. NOTE: Use amounts in the audited financial statements only to calculate these ratios (Item 8. of annual report). | ----- From "Requirement 2 - 6" Sheet -----| (Walgreens) a. Rate of return on sales #13 Ratio: Net income / Net sales 4.1% $ $ 4,279 103,444 b. Asset turnover #14 Ratio: Net Sales/Average Assets 2.0 $ $ 103,444 53,016 $ $ 4,279 53,016 c. Return of return on assets (#15 non-DuPont model) Ratio: (Net income - Preferred Dividends)/Average Assets 8.1% Walgreens has no preferred shares. d. Return of return on equity (#17 non-DuPont model) Ratio: (Net Income - Preferred Dividends)/Average common equity 16.5% $ $ 4,279 25,959 Walgreens has no preferred shares. e. Gross margin percentage #10 Ratio: Gross Margin/Net Sales 26.0% $ $ 26,924 103,444 $ $ 4,279,000,000 1,172,513,618 f. Earnings per share #18 (do NOT use one shown on the income statement, calculate yourself using simple average of outstanding shares) Ratio: (Net Income - Preferred Shares)/Shares outstanding $ 3.65 g. Generally, who is more profitable? Walgreens or CVS Companies? Explain your answer. Walgreens seems more profitable than CVS. All their ratios are better except for EPS. In your response, you can discuss all the ratios one by one to show that Walgreens Requirement 8 Evaluate the company's ABILITY TO SELL INVENTORY AND PAY DEBTS during 2015. In your analysis, you should compute the following ratios, and then comm NOTE: Use amounts in the audited financial statements only to calculate these ratios (Item 8. of annual report). a. Inventory turnover & days' inventory outstanding #3 Ratio: $ 76,520 Turnover 10.4 Days 35.2 Inventory turnover = COGS/Average Inventory $ 7,377 Days in Inventory = 365/Average Inventory $ 365 10.37 b. Receivable turnover & days sales outstanding #4 - 5 Ratio: Receivable turnover = Net Sales/Average accounts receivable $ $ 103,444 5,034 Days sales outstanding = 365/Accounts receivable turnover $ 365 Turnover 20.6 Days 17.8 Turnover 10.6 Days 34.4 20.55 c. Accounts payable turnover & days' payable outstanding #6 Ratio: Accounts payable turnover = COGS/Average payable $ $ 76,520 7,202 Days payable outstanding = 365/Average payable $ 365 10.63 d. Current ratio #1 Ratio: Current Assets/Current Liabilities 1.2 $ $ 19,657 16,557 $ $ 9,849 16,557 $ $ 37,482 68,782 e. Quick (acid-test) ratio #2 Ratio: (Cash and cash equivalents + short-term investments + Receivales)/Current Liabilities 0.6 f. Debt ratio #8 Ratio: Total Liabilities/Total Assets 0.5 f. Times interest earned #9 Ratio: Income from operations/Interest expense 9.8 $ $ 5,916 605 g. Generally, who has a stronger financial position? Walgreens or CVS Companies? Explain your answer. CVS seems in a better position. All of their ratios are better than those of Walgreens except for Debt Ratio. For your answer again, you can discuss every ratio one by on Requirement 9 Evaluate CVS Companies' CASH FLOW. a. Does CVS use a Direct or Indirect method to calculate Operating Cash Flow b. For 2015, was cash provided by, or used in, CVS Companies' operations? c. For 2015, is CVS Companies' net Cash Flow from Operations greater than or less than Income from Operations? What is the primary cause of the difference? Net Cash flow from operations = 8,412. Net Income from Operations = 5,239. The statement of cash flow uses the cash basis while to compute net income, the accrual basis revenue that is earned even though it is not received. It also considers expenses incurred, but not paid. The net cash from operations only considers cash received and disbu d. For 2015, what is the primary sources and uses of cash from Investing activities? The biggest uses under the investing activities is acquisitions and other investments. The biggest source is proceeds from sale-leaseback transactions. e. For 2015, what is the primary sources and uses of cash from Financing activities?e The biggest uses under the financing acitivities is repurchase of common stock. The biggest source is proceeds from the proceeds from issuance of long-term debt. Requirement 10 OTHER FINANCIAL ANALYSIS. a. Total revenues was $139,367 in 2014 and $153,290 in 2015. Has CVS Companies been able to generate increased revenues from 2014 to 2015 to outpace inflation? P of your inflation data. Inflation is usally 2% in the economy. 139,367 * (1+0.02) = 142,154.34. The revenue in 2015 surpasses this amount. The revenue did outpace the inflation. b (1). What was the closing market price of CVS stock on January 5, 2016, the next trading day after the balance sheet date of December 31, 2015? b (2). What is the book value per share on December 31, 2015? (ratio #21) Ratio: Total Stock holder's Equity/Number of Preferred Shares This numbers were found in the earlier problems. We do not need to consider preferred shares as there is none. c (1). How much was CVS dividend declared per share (found in Consolidated Statements of Income) c (2). How much was CVS earnings per share (calculated above in Requirement 2, g. ) c (3). What percent of the earnings per share was distributed as a dividend ? Is CVS Companies distributing significant earnings to their shareholders? 1.4/4.76=0.29. Whether this represents a significant amount depends on your interpretation of what significant represents. CVS 2015 Fiscal Year as of January 30, 2016 Calculation of Ratio Show work Points Ratio mpared with 2014. In your analysis, you should compute the 3.4% $ $ 5,239 153,290 $ $ 153,290 83,922 $ $ 5,239 83,922 CVS has no preferred shares.
