ACG2021-Comprehensive Financial Statement Analysis Project

ACG2021Comprehensive Financial Statement Analysis ProjectFall 2016Group MembersLast Name First Name Instructions: You will need the annual report for Walgreens Corporation for fiscal years 2015 and 2014. They will be provide Note that Wahlgreen’s fiscal year is referenced one year back, so, for example, the 2015 fiscal year relates to trelates to the year which ended August 31, 2014. Requirement 1: Basic QuestionsAnswer the questions 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 onlyannual report PDF file gives you a table of contents which allows you to quickly find the sources of the informa 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 ratiboxes. The first one (a.) was completed as an illustration. The ratio formulas are listed in pages 786-787 in chapter 13 of your textbook. I have added the number of thto calculate them. When calculating the ratios, only use the amounts included in the audited financial statements (Item 8. Finanmay be shown in other sections of the annual report. All other sections of the annual report except for the finany other ratios or financial indicators are they wish, not necessarily as the textbook prescribes. If you pick up Since the fiscal 2015 financial statements are comparative, they include info for both fiscal years 2015 and 201well. However, some ratios for the 2014 year will require you go to back an additional year because some of the 201For example, the inventory turnover for 2014 uses average inventory calculated by using the 2014 ending invebalance in 2013). You need to obtain the fiscal 2013 ending inventory balance included in the 2014 financial st 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 spreadsheeYou 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 Netamount reported on the Income Statement (which they call the “Statement of Earnings”). If you have any quesSeveral 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! Corporation for fiscal years 2015 and 2014. They will be provided to you in pdf format. d one year back, so, for example, the 2015 fiscal year relates to the fiscal year which ended on August 31, 2015 and the 2014 fis14. in the shaded input box. answer is provided for you above the shaded input box. Use only that source to answer the question. The Index on the second ptents which allows you to quickly find the sources of the information. e the formula of the ratio, the amounts used to calculate the ratio and what the ratio measures. Enter your answers in the shadellustration. 7 in chapter 13 of your textbook. I have added the number of the ratio listed in pages 786-787 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 threport. All other sections of the annual report except for the financial statements are not audited and as such the preparer cany wish, not 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 + 20uses average inventory calculated by using the 2014 ending inventory balance + the 2014 beginning inventory balance (which is2013 ending 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 Walgreen’s 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 finalwhich 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 221 points which is equivalent to 100%. Requirement 1Answer BASIC INFORMATION about company operations and financial results for the Walgreens 2015 fiscal year.NOTE: Use amounts found in the “Source” only to answer these questions. Points a. As of August 31, 2015, how many stores did Walgreens operate in total and in how many countries?Source: Item 1. Busines – Overview 1 b. What are the three reportable Segments that Walgreens has? Based on “Sales to external customers”, which is the largestsegment?Source: Item 8. Financial Statements- Note 19. Segment Reporting 2 c. Does Walgreens report a multi-step or single-step income statement? How do you know?Source: Item 8. Financial Statement – Consolidated Statements of Earnings 3 d. What is Walgreens’ largest asset? Largest liability? What is the amount of each?Source: Item 8. Financial Statement – Consolidated Balance Sheets 2 e. How many shares of common stock are Issued?Source: Item 8. Financial Statements – Consolidated Balance Sheets 1 f. The Balance Sheets do not explicitely report “outstanding” common stock shares at August 31, 2015. However, this amount can becalculated by other information provided in the Balance Sheet. How many common stock shares were outstanding at August 31, 2015and how did you calculate it?Source: Item 8. Financial Statements – Consolidated Balance Sheets 3 g. What was the amount of the dividend declared and paid (per share) in fiscal 2015?Source: Item 8. Financial Statements – Consolidated Statements of Equity 1 h. When does Walgreens record revenue sold at the stores?Source: Item 8. Financial Statements – Note 2. Summary of Major Accounting Policies: Revenue Recognition 1 i. What methods do Walgreens use to value its inventory (FIFO, LIFO, average, etc.) ?Source: Item 8. Financial Statements – Note 2. Summary of Major Accounting Policies: Inventory 1 j. What method does Walgreens use to depreciate its property and equipment? What are the useful lives assigned to different fixedasset categories?Source: Item 8. Financial Statements – Note 2. Summary of Major Accounting Policies- Property, Plant & Equipment 1 k. Calculate the percentage of Walgreens total property and equipment that has been depreciated at August 31, 2015. On average, isthe property, plant and equipment that Walgreens owns fairly new or fairly old? Why?Source: Item 8. Financial Statements – Note 2. Summary of Major Accounting Policies- Property, Plant & Equipment 2 l. What items are recorded as cash equivalents?Source: Item 8. Financial Statements – Note 2. Summary of Major Accounting Policies- Cash & Cash Equipments 1 m. What is the net Accounts Receivable balance at August 31, 2015?Source: Item 8. Financial Statements – Consolidated Balance Sheets 1 n. Walgreens has significant Accounts Receivable balances for what you may believe is primarily a cash/credit card business. Who elsethen owes Walgreens these receivables?Source: Item 8. Financial Statements – Note 2. Summary of Major Accounting Policies- Accounts Receivable 1 o. What type of financial instruments does Walgreens invest in? How do they classify these investments?Source: Item 8. Financial Statements – Note 2. Summary of Major Accounting Policies- Investments 1 p. On November 2014 Walgreens issued $8 billion in long-term debt. How much did Walgreens actually receive in proceeds? What arethe ranges of fixed rates on this debt?Source: Item 8. Financial Statements- Note 10. Short-Term Borrowings and Long-Term Debt 2 q. Do the gift cards Walgreens sell expire? How would the outstanding (not redeemed yet) gift card balance be classified on theBalance Sheet and why?Source: Item 8. Financial Statements – Note 2. Summary of Major Accounting Policies- Gift Cards 3 r. What activities are planned under the new restructuring “Cost Transformation” program?Source: Item 8. Financial Statements- Note 4. Restructuring 1 s. What company is Walgreens planning to acquire as agreed upon in October 2015? How many more stores would this acquisitionadd to the Walgreens portfolio? Is this a significant acquisition?Source: Item 8. Financial Statements- Note 21. Subsequent Event 1 t. Using a site such as www.hoovers…. or www.finance.yahoo…., research the discount variety store industry. List threecompetitors of Walgreens Corporation.Source: Type in source you used here 2 u. How many employees does Walgreens have at August 31, 2015?Source: Item1. Business – Employees 1 v. Describe IN YOUR OWN WORDS the major business risk factor that Walgreens faces?Source: Item 1A. Risk Factors 2 w. In what countries in Central and South America does Walgreens operate?Source: Item 2. Properties 1 x. What was the cause of the increase in total sales in the Retail Pharmacy USA division? Was it because Walgreens operated morestores or that the average sales per stores increased? And by how much?Source: Item 7. Management’s Discussion and Analysis: Results of Operations by Segment, Retail Pharmacy USA 2 y. Did total capital expenditures (additions to property plan and equipment) for the Retail Pharmacy USA segment increase ordecrease from prior year? What is the explanation for the change?Source: Item 7. Management’s Discussion and Analysis: Liquidity and Capital Resources 2 39 Walgreens 2015 Fiscal Year as of August 31, 2015Ratio(Refer to pgs 775 – 777 for ratio formula) Calculation of Ratio Show work Walgreens 2014 Fiscal Year as of AugusCalculation of Ratio Ratio Show work Requirement 2 Evaluate PROFITABILITY. Using information you have learned in the text and elsewhere, evaluate Walgreenls’ profitability for 2015 compared with 2014. In yoyou should compute the 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). a. Rate of return on sales #13Ratio:Net income / Net sales 4.1%$$ 4,279103,444 $$ 2,03176,392 Did the ratio increase or decrease? Is that positive or negative? Why? The ratio decreased. This is negative because each dollar of sales produced less income. b. Asset turnover #14Ratio: Did the ratio increase or decrease? Is that positive or negative? Why? c. Rate of return on assets (#15 non-DuPont model) Ratio: Did the ratio increase or decrease? Is that positive or negative? Why? d. Rate of return on equity (#17 non-DuPont model)Ratio: Did the ratio increase or decrease? Is that positive or negative? Why? e. Gross margin percentage #10Ratio: Did the ratio increase or decrease? Is that positive or negative? Why? f. Earnings per share #18 (do NOT use one shown on the income statement,calculate yourself using simple average of outstanding shares)Ratio: Did the ratio increase or decrease? Is that positive or negative? Why? g. Generally, are these indicators positive or negative? What trend(s) do you deduce from this analysis? Requirement 3 Evaluate the company’s ABILITY TO SELL INVENTORY AND PAY DEBTS during 2015 and 2014. In your analysis, you should compute the following ratios, and tcomment on what those ratios indicate.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 TurnoverDays Ratio: Did the ratio increase or decrease? Is that positive or negative? Why? b. Receivable turnover & days sales outstanding #4 – 5 TurnoverDays Ratio: Did the ratio increase or decrease? Is that positive or negative? Why? c. Accounts payable turnover & days payable outstanding #6 TurnoverDays Ratio: Did the ratio increase or decrease? Is that positive or negative? Why? d. Current ratio #1Ratio: Did the ratio increase or decrease? Is that positive or negative? Why? e. Quick (acid-test) ratio #2Ratio: Did the ratio increase or decrease? Is that positive or negative? Why? f. Debt ratio #8Ratio: Did the ratio increase or decrease? Is that positive or negative? Why? f. Times interest earned #9Ratio: Did the ratio increase or decrease? Is that positive or negative? Why? g. Generally, are these indicators positive or negative? What trend(s) do you deduce from this analysis? Requirement 4Evaluate Walgreens’ CASH FLOW. a. Does Wagreens use a Direct or Indirect method to calculate Operating Cash Flowb. For 2015, was cash provided by, or used in, operations? c. For 2015, is Walgreenls’ net Cash Flow from Operations greater than or less than Income from Operations? What is the primary cause of the difference? d. For 2015, what is the primary source of cash from Investing Activities? Is this the same as in 2014 and 2013? If not, state the primary source(s) of cash from investing2014 and 2013. e. For 2015, what is the primary source of cash from Financing Activities? Is this the same as in 2014 and 2013? If not, state the primary source(s) of cash from financing2014 and 2013. Requirement 5OTHER FINANCIAL ANALYSIS. a. Total sales for the Retail Pharmacy USA segment was $76,392 in 2014 and $80,974 in 2015. Has Walgreens been able to generate increased revenues from 2014 to 20outpace inflation? Provide explanation. Reference the source of your inflation data. b (1). What was the closing market price of Walgreens’ Corporation stock on September 1, 2015, thenext trading day after the balance sheet date of August 31, 2015?b (2). What is the book value per share on August 31, 2015? (ratio #21)Ratio: b (3). What is the difference between the Walgreens market price from b(1). above and the book value from b(2). above? What factors could be explain this difference? c (1). How much was the dividend declared per share (from Requirement 1, g. )c (2). How much was the earnings per share (calculated above in Requirement 2, f. )c (3). What percent of the earnings per share was distributed as a dividend ? Is Walgreens distributing significant earnings to their shareholders? d. Prepare vertical analysis forWalgreens Companiese. Prepare horizontal analysis for Walgreens Companies Refer to separate sheetRefer to separate sheet Requirement 6Overall, determine the financial health of Walgreens based on the information gathered from requirements 2 – 5 above.Provide specific reasons as to why. 4 Fiscal Year as of August 31, 2014 ation of Ratio ow work Ratio Points mpared with 2014. In your analysis, 2.7% <— This item was completedfor you as an example e. 4 4 4 4 4 4 he following ratios, and then TurnoverDays 6 Turnover 6 Days Turnover 6 Days 4 4 4 4 4 e difference? 11 2 e(s) of cash from investing activities in2 e(s) of cash from financing activities in2 d revenues from 2014 to 2015 to be explain this difference? rs? 4 1 3 3 112 6 91 Walgreens 2015 Fiscal Year as of August 31, 2015Ratio(Refer to pgs 775 – 777 for ratio formula) Copy Walgreens from “Requirement2 – 6” Sheet CVS 2015 Fiscal Year as of JanuaryCalculation of Ratio Ratio Show work Requirement 2 Evaluate PROFITABILITY. Using information you have learned in the text and elsewhere, evaluate Walgreens’ profitability for 2015 compared with 2014. In youshould compute the 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 #13Ratio:Net income / Net sales b. Asset turnover #14Ratio: c. Return of return on assets (#15 non-DuPont model)Ratio: 4.1%$$ 4,279103,444 $$ 5,239153,290 d. Return of return on equity (#17 non-DuPont model)Ratio: e. Gross margin percentage #10Ratio: f. Earnings per share #18 (do NOT use one shown on the income statement,calculate yourself using simple average of outstanding shares)Ratio: g. Generally, who is more profitable? Walgreens or CVS Companies? Explain your answer. 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 commthose ratios indicate.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 TurnoverDays Ratio: b. Receivable turnover & days sales outstanding #4 – 5 TurnoverDays Ratio: c. Accounts payable turnover & days’ payable outstanding #6 TurnoverDays Ratio: d. Current ratio #1Ratio: e. Quick (acid-test) ratio #2Ratio: f. Debt ratio #8Ratio: #DIV/0! f. Times interest earned #9 #DIV/0! Ratio: g. Generally, who has a stronger financial position? Walgreens or CVS Companies? Explain your answer. Requirement 9Evaluate CVS Companies’ CASH FLOW. a. Does CVS use a Direct or Indirect method to calculate Operating Cash Flowb. 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? d. For 2015, what is the primary sources and uses of cash from Investing activities? e. For 2015, what is the primary sources and uses of cash from Financing activities?e Requirement 10OTHER 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? Pexplanation. Reference the source of your inflation data. b (1). What was the closing market price of CVS stock on January 5, 2016, the next trading day after thebalance sheet date of December 31, 2015?b (2). What is the book value per share on December 31, 2015? (ratio #21)Ratio: 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? d. Prepare vertical analysis for CVS Companies Refer to separate sheet e. Prepare horizontal analysis for CVS Companies Refer to separate sheet Requirement 11 Overall, based on the results of this analysis, assuming hypothetically that the stock price was the same for both companies, which company’s stock would you purchasespecific reasons as to why. There is a significant difference in the size of these two companies. How has ratio analysis helped compare the financial performance of these two companies. cal Year as of January 30, 2016 ation of Ratio ow work Ratio Points pared with 2014. In your analysis, you 3.4%<— This item was completedfor you as an example 2 2 2 2 2 4 ratios, and then comment on what TurnoverDays 3 Turnover 3 Days Turnover 3 Days 2 2 2 he difference? 2 4 112 2 2 5 to outpace inflation? Provide ders? 4 22 112 ock would you purchase? Provide wo companies. 4 2 61 Requirement 5d / 10dOther financial analysis. Refer to pgs 749 – 752 in chapter 13 for explanation.d. Perform Vertical Analysis for sales, gross profit, operating income, and net income for 2014 – 2015.Walgreens 2015 2014 $$SalesGross ProfitOperating IncomeNet Income CVS %100% $$ %100% $$ 2015 %100% 2014 $$SalesGross Profit %100% Operating IncomeNet Income Which company has the strongest profitability indicators based on this analysis? Requirement 5e / 10eOther financial analysis. Refer to pgs 743 – 747 in chapter 13 for explanation.e. Perform Horizontal Analysis for sales, gross profit, operating income, and net income for 2014 – 2015.Walgreens 2015 2014 $$SalesGross ProfitOperating IncomeNet Income $$ $$$$ – CVS $$$$ 2015 2014 $$SalesGross ProfitOperating IncomeNet Income – $$$$ $$- $$$$ – For each company, which was the item with the greatest percentage increase year over year? 0d or 2014 – 2015.3 3 2 0e e for 2014 – 2015.Increase (decrease)$$ 3% Increase (decrease)$$ 3% r year? 2 16