Hello I need help with requirenments 7-11. please and thank you

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 20 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 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. ded in the boxes highlighted in pink on the right-hand side of the question. There are 207 points which is equivalent to 100%. Answer 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 2015 annual report. Page 1 Walgreens operated more than 13,100 stores in 11 countries. 1 b. What are the three reportable Segments that Walgreens has? Based on "Sales to external customers", which is the largest segment? Source: Item 8. Financial Statements- Note 19. Segment Reporting 2015 annual report. Page The three reportable segments that Walgreens has are Retail pharmacy USA, Retail Pharmacy International, and 114-115 Pharmaceutical Wholesale. The largest segment would be Retail Phamacy USA. 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 2015 annual report. Page Walgreens report a multi-step income staement based on the statement being more in depth of the companies 63 performance. 3 d. What is Walgreens' largest asset? Largest liability? What is the amount of each? Source: Item 8. Financial Statement - Consolidated Balance Sheets 2015 annual report. Page Walgreens' largest current asset is inventories with the amount of 8678. Largest noncurrent asset is Goodwill with the amount of 16,372. Walgreens' largest current liability is trade accounts payable with the amount of 10,008. Largest 61 noncurrent liability is long term debt with the amount of 13,315. 2 e. How many shares of common stock are Issued? Source: Item 8. Financial Statements - Consolidated Balance Sheets 2015 annual report. Page 1,172,513,618 shares of common stocks were issued August 2015. 61. 1 f. The Balance Sheets do not explicitely report "outstanding" common stock shares at August 31, 2015. However, this amount can be calculated by other information provided in the Balance Sheet. How many common stock shares were outstanding at August 31, 2015 and how did you calculate it? Source: Item 8. Financial Statements - Consolidated Balance Sheets 2015 annual report. Page 1,089,910,344 common stock shares were outstanding. Outstanding stocks=issued stocks-treasury stocks. 61. 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 2015 annual report. Page The amount of the divendends is 1,458 with $1.37 per share. 62 1 h. When does Walgreens record revenue sold at the stores? Source: 2015 annual report. Page 73 Item 8. Financial Statements - Note 2. Summary of Major Accounting Policies: Revenue Recognition Walgreens record revenue when persuasive evidence of an arrangement exists, delivery has occurred or services have been rendered, the seller's price to the buyer is fixed or determinable, and collectability is reasonably assured. For retail pharmacy, it is recorded at the time the customer takes possession of the merchandise, after making appropriate adjustments for estimated returns.With wholesale, records upon shipment of goods, which is generally also the day of delivery. 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 2015 annual report. Page 68 The method that Wakgreens use in the United States is LIFO. For international and Pharmeceutical, they use FIFO except for retail inventory which uses the retail inventory method. 1 j. What method does Walgreens use to depreciate its property and equipment? What are the useful lives assigned to different fixed asset categories? Source: Item 8. Financial Statements - Note 2. Summary of Major Accounting Policies- Property, Plant & Equipment 2015 annual report. Page The method Walgreens use to depreciate property and equipment is the composite-method of depreciation. There are 20 69 useful lives for land improvement,3 to 20 lives for fixures, plant, and equipment, and 13 to 0 for building and building improvement. 1 k. Calculate the percentage of Walgreens total property and equipment that has been depreciated at August 31, 2015. On average, is the 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 2015 annual report. Page The percentage that has been depreciated is 71%. What Walgreens is fairly old because of the amount of money that 69 would have to go toward fixing anything that gets damaged. 2 l. What items are recorded as cash equivalents? Source: Item 8. Financial Statements - Note 2. Summary of Major Accounting Policies- Cash & Cash Equipments 2015 annual report. Page The items that are recorded includes cash on hand and all highly liquid investments with a maturity of three months or 67 less. 1 m. What is the net Accounts Receivable balance at August 31, 2015? Source: Item 8. Financial Statements - Consolidated Balance Sheets 2015 annual report. Page The net accounts recievable balance is 6,849. 61 1 n. Walgreens has significant Accounts Receivable balances for what you may believe is primarily a cash/credit card business. Who else then owes Walgreens these receivables? Source: Item 8. Financial Statements - Note 2. Summary of Major Accounting Policies- Accounts Receivable 1 2015 annual report. Page Pharmacy benefit managers, insurance companies, governmental agencies, clients and members, vendors and 68 manufactures. 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 2015 annual report. Page Walgreens invests in corporate debt and other debt securities and equity securities of publicly traded companies. They 69 classify these investments when securities at the time of purchase as held-to-maturity or available-for-sale. 1 p. On November 2014 Walgreens issued $8 billion in long-term debt. How much did Walgreens actually receive in proceeds? What are the ranges of fixed rates on this debt? Source: Item 8. Financial Statements- Note 10. Short-Term Borrowings and Long-Term Debt 2015 annual report. Page Walgreens actually receieved 7.9 billion.Varying maturities and interest rates is the majorityof which are fixed rate. 91 2 q. Do the gift cards Walgreens sell expire? How would the outstanding (not redeemed yet) gift card balance be classified on the Balance Sheet and why? Source: Item 8. Financial Statements - Note 2. Summary of Major Accounting Policies- Gift Cards 2015 annual report. Page Walgreens gift cards in the U.S. do not expire, but internationally they do.The Outstanding gift card balance would not go 74 on the balance sheet, unless its giftcard breakage. 3 r. What activities are planned under the new restructuring "Cost Transformation" program? Source: Item 8. Financial Statements- Note 4. Restructuring 2015 annual report. Page The program plans to close approximately 200 stores across the U.S., recognize corporate and field operations, drive 78 operating efficiencies, and streamline information technology and other functions. 1 s. What company is Walgreens planning to acquire as agreed upon in October 2015? How many more stores would this acquisition add to the Walgreens portfolio? Is this a significant acquisition? Source: Item 8. Financial Statements- Note 21. Subsequent Event 2015 annual report. Page Walgreens planned to acquire Rite Aid Corporation and Victoria Merger Sub, Inc. There would be 4,561 more stores. No 118-119 this isn't a significant acquisition. 1 t. Using a site such as www.hoovers.com or www.finance.yahoo.com, research the discount variety store industry. List three competitors of Walgreens Corporation. Source: Hoover.com Hoover.com The three competitors of Walgreens are CVS Health Corporation, Rite Aid Corporation and Wal-Mart Stores, Inc. 2 u. How many employees does Walgreens have at August 31, 2015? Source: Item1. Business - Employees 1 Walgreens employed approximately 360,000 persons. v. Describe IN YOUR OWN WORDS the major business risk factor that Walgreens faces? Source: Item 1A. Risk Factors 2 They owe billions in dollars to companies andbanks they borrowed from. If Walgreens paid their dues, they could go out of business if there isn't enough profit to match. w. In what countries in Central and South America does Walgreens operate? Source: Item 2. Properties 1 The countries Walgreens operate in Central and South America is Chile and Mexico. x. What was the cause of the increase in total sales in the Retail Pharmacy USA division? Was it because Walgreens operated more stores 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 The cause was the increase from pharmacy sales and its comparable.It was because of the average sales per store. From 2013 to 2014 the incease was 4,175 and from 2014 to 2015 was 4,582. y. Did total capital expenditures (additions to property plan and equipment) for the Retail Pharmacy USA segment increase or decrease from prior year? What is the explanation for the change? Source: Item 7. Management's Discussion and Analysis: Liquidity and Capital Resources The capital expenditures decresed from previous year. It was because they reported after only 8 months because f what they didn't own in December. 2 The capital expenditures decresed from previous year. It was because they reported after only 8 months because f what they didn't own in December. 39 Walgreens 2015 Fiscal Year as of August 31, 2015 Ratio (Refer to pgs 775 - 777 for ratio formula) Calculation of Ratio Show work Walgreens 2014 Fiscal Year as of August Calculation 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 yo you 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 #13 Ratio: Net income / Net sales 4.1% $ $ 4,279 103,444 $ $ 2,031 76,392 Did the ratio increase or decrease? Is that positive or negative? Why? The ratio increased. This is positive because each dollar of sales produced more income. b. Asset turnover #14 Ratio: Net sales/ Average total assets 1.50 $ $ 103,444 68,782 $ $ 76,392 37,250 Did the ratio increase or decrease? Is that positive or negative? Why? The ratio decreased. This is negative because assets has produced lesser sales per dollar on assets. c. Rate of return on assets (#15 non-DuPont model) 6.22% Ratio: Net income/ Total assets $ $ 4,279 68,782 $ $ 2,031 37,250 Did the ratio increase or decrease? Is that positive or negative? Why? The ratio increased. This is positive because company had greater profit. d. Rate of return on equity (#17 non-DuPont model) Ratio: Net income/ Shareholder's Equity 13.67% $ $ 4,279 31,300 $ $ 2,031 20,617 Did the ratio increase or decrease? Is that positive or negative? Why? The ratio increased. This is positive because common stockholders' equity generated more profit each dollar. e. Gross margin percentage #10 Ratio: Gross margin/ Net sales 26.03% $ $ 26,924 103,444 $ $ 21,569 76,392 Did the ratio increase or decrease? Is that positive or negative? Why? The ratio decreased. This is negative because each dollar of revenue produced less gross profit. 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 - Dividends on Preferred Stock)/ Average outstanding shares $ $ 4,279 1,100 3.89 $ $ 2,031 1,028 Did the ratio increase or decrease? Is that positive or negative? Why? Ratio increased. This is positive because of higher net income. g. Generally, are these indicators positive or negative? What trend(s) do you deduce from this analysis? Generally these indicators are positive which means that uthe company had a good year according to this analysis.Though asset turnover has reduced which indicate th improvement scope for higher sales in coming years with same level of assets. 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 t 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. Inventory turnover & days inventory outstanding #3 Ratio: Cost of goods sold/ Average inventory Turnover 8.82 Days 41 $76,520 $8,678 $ $54,823 6,076 365/ Inventory turnover Did the ratio increase or decrease? Is that positive or negative? Why? Ratio decrease.This is negative as inventory holding with reference to activity level has increased. b. Receivable turnover & days sales outstanding #4 - 5 Turnover 15 Days Ratio: Sales / Average receivables 24.17 $103,444 $6,849 $76,392 $3,218 365 / Receivable turnover Did the ratio increase or decrease? Is that positive or negative? Why? Ratio decrease. This is negative because receivables has increased. c. Accounts payable turnover & days payable outstanding #6 Ratio: Cost of goods sold / Average payables Turnover Days 7.59 48 $76,520 $10,088 $ $54,823 4,315 365 / Payable turnover Did the ratio increase or decrease? Is that positive or negative? Why? Ratio decrease. This is positive because suppliers are giving higher credit period. d. Current ratio #1 Ratio: Current assets / Current liabilities 1.19 $19,657 $16,557 $ $12,242 8,895 Did the ratio increase or decrease? Is that positive or negative? Why? Ratio decrease. This is negative because accounts payable increase is more than proportionate increase in curren e. Quick (acid-test) ratio #2 0.66 Ratio: Quick assets / Current liabilities $10,979 $16,557 $ $6,166 8,895 Did the ratio increase or decrease? Is that positive or negative? Why? Ratio has decreased. This is negative as proportionate increase in acccounts payable is more than increase in inve f. Debt ratio #8 Ratio: Debt / Total assets 27.12% $18,652 $68,782 $ $16,633 37,250 Did the ratio increase or decrease? Is that positive or negative? Why? Ratio has decreased. This is positive because det level has reduced. f. Times interest earned #9 Ratio: EBIT / Interest 9.78 $5,916 $605 $ $3,713 156 Did the ratio increase or decrease? Is that positive or negative? Why? Ratio has decreased. This is negative because interest expense has increased. g. Generally, are these indicators positive or negative? What trend(s) do you deduce from this analysis? Overall trend is negative. Here inventory hodlinga nd collection period has reduced. Current ratio has come down. Accounts payable has gone up. Though debt ratio has which is positive but part of this debt is replaced by increase in accounts payable. Requirement 4 Evaluate Walgreens' CASH FLOW. a. Does Walgreens use a Direct or Indirect method to calculate Operating Cash Flow b. For 2015, was cash provided by, or used in, operations? Indirect Method Provided c. For 2015, is Walgreens' net Cash Flow from Operations greater than or less than Income from Operations? What is the primary cause of the difference? For 2015, Walgreens' net Cash Flow from Operations isgreater than income from operation. Primary cause of difference is depreication. 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 investing 2014 and 2013. For 2015, primary cause is sale and leaseback transaction. Its not same as in 2014 and 2013. Primary source of cash for 2014 is sale of assets and for 2013 is sale of busines 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 financing 2014 and 2013. For 2015, primary source of cash is issue of long term debt. Its not same as of 2014 but same as of 2013. For 2014, primary source of cash is employee stock plans. Requirement 5 OTHER 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 20 outpace inflation? Provide explanation. Reference the source of your inflation data. Increase in revenue is 5.6% while inflation rate for 2015 is 0.7%. Therefore revenue increase has outpace inflation. Source of inflation data: http://www.usinflationcalculator.com/inflation/current-inflation-rates/ b (1). What was the closing market price of Walgreens' Corporation stock on September 1, 2015, the next trading day after the balance sheet date of August 31, 2015? $87.43 b (2). What is the book value per share on August 31, 2015? (ratio #21) Ratio: Market price / Book value 26.69 $3.28 $ $ 87.43 26.69 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? Difference is $60.74. Factors to explain this are: Market worth of assets, growth opportunities. 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. ) $1.37 $ 3.89 0.3523 c (3). What percent of the earnings per share was distributed as a dividend ? Is Walgreens distributing significant earnings to their shareholders? Percentage of earnings distributed as dividend = 1.37 / 3.89 = 35.23%. Walgreens is distributing moderate level of earnings to their shareholders. d. Prepare vertical analysis forWalgreens Companies e. Prepare horizontal analysis for Walgreens Companies Refer to separate sheet Refer to separate sheet Requirement 6 Overall, determine the financial health of Walgreens based on the information gathered from requirements 2 - 5 above. Provide specific reasons as to why. Company's payout ratio and investment in assets indicate that company is growing company. Market price suggest that compnay is highly valued company looking at g opportunities. Company is overall cash positive due to good net income margins. Debt level ahs come down. Overall assessment is positive and financial health is good. 4 Fiscal Year as of August 31, 2014 ation of Ratio how work DIFFERENCE Inc / (Dec) Points Ratio mpared with 2014. In your analysis, 2.7% 1.5%