ACG2021-Comprehensive Financial Statement Analysis Project

ACG2021
Comprehensive Financial Statement Analysis Project
Fall 2016
Group Members
Last 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 t
relates to the year which ended August 31, 2014. Requirement 1: Basic Questions
Answer 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 only
annual 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 rati
boxes. 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 th
to calculate them. When calculating the ratios, only use the amounts included in the audited financial statements (Item 8. Finan
may be shown in other sections of the annual report. All other sections of the annual report except for the fin
any 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 201
well. However, some ratios for the 2014 year will require you go to back an additional year because some of the 201
For example, the inventory turnover for 2014 uses average inventory calculated by using the 2014 ending inve
balance 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 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! 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 fis
14. 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 p
tents 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 shade
llustration. 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 th
report. All other sections of the annual report except for the financial statements are not audited and as such the preparer can
y 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 + 20
uses average inventory calculated by using the 2014 ending inventory balance + the 2014 beginning inventory balance (which is
2013 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 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 221 points which is equivalent to 100%. Requirement 1
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 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 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 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 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 fixed
asset 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, 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 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 else
then 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 are
the 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 the
Balance 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 acquisition
add 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.com or www.finance.yahoo.com, research the discount variety store industry. List three
competitors 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 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 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 2 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 Augus
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 decreased. This is negative because each dollar of sales produced less income. b. Asset turnover #14
Ratio: 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 #10
Ratio: 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 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 Turnover
Days Ratio: Did the ratio increase or decrease? Is that positive or negative? Why? b. Receivable turnover & days sales outstanding #4 - 5 Turnover
Days Ratio: Did the ratio increase or decrease? Is that positive or negative? Why? c. Accounts payable turnover & days payable outstanding #6 Turnover
Days Ratio: Did the ratio increase or decrease? Is that positive or negative? Why? d. Current ratio #1
Ratio: Did the ratio increase or decrease? Is that positive or negative? Why? e. Quick (acid-test) ratio #2
Ratio: Did the ratio increase or decrease? Is that positive or negative? Why? f. Debt ratio #8
Ratio: Did the ratio increase or decrease? Is that positive or negative? Why? f. Times interest earned #9
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 4
Evaluate Walgreens' CASH FLOW. a. Does Wagreens use a Direct or Indirect method to calculate Operating Cash Flow
b. 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 investing
2014 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 financing
2014 and 2013. 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. 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?
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 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. 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 completed
for you as an example e. 4 4 4 4 4 4 he following ratios, and then Turnover
Days 6 Turnover 6 Days Turnover 6 Days 4 4 4 4 4 e difference? 1
1 2 e(s) of cash from investing activities in
2 e(s) of cash from financing activities in
2 d revenues from 2014 to 2015 to be explain this difference? rs? 4 1 3 3 1
1
2 6 91 Walgreens 2015 Fiscal Year as of August 31, 2015
Ratio
(Refer to pgs 775 - 777 for ratio formula) Copy Walgreens from "Requirement
2 - 6" Sheet CVS 2015 Fiscal Year as of January
Calculation 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 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). | ----- From "Requirement 2 - 6" Sheet -----|
(Walgreens)
a. Rate of return on sales #13
Ratio:
Net income / Net sales b. Asset turnover #14
Ratio: c. Return of return on assets (#15 non-DuPont model)
Ratio: 4.1%
$
$ 4,279
103,444 $
$ 5,239
153,290 d. Return of return on equity (#17 non-DuPont model)
Ratio: e. Gross margin percentage #10
Ratio: 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 comm
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 Turnover
Days Ratio: b. Receivable turnover & days sales outstanding #4 - 5 Turnover
Days Ratio: c. Accounts payable turnover & days' payable outstanding #6 Turnover
Days Ratio: d. Current ratio #1
Ratio: e. Quick (acid-test) ratio #2
Ratio: f. Debt ratio #8
Ratio: #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 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? 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 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
explanation. 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 the
balance 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 purchase
specific 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 completed
for you as an example 2 2 2 2 2 4 ratios, and then comment on what Turnover
Days 3 Turnover 3 Days Turnover 3 Days 2 2 2 he difference? 2 4 1
1
2 2 2 5 to outpace inflation? Provide ders? 4 2
2 1
1
2 ock would you purchase? Provide wo companies. 4 2 61 Requirement 5d / 10d
Other 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 $$
Sales
Gross Profit
Operating Income
Net Income CVS %
100% $$ %
100% $$ 2015 %
100% 2014 $$
Sales
Gross Profit %
100% Operating Income
Net Income Which company has the strongest profitability indicators based on this analysis? Requirement 5e / 10e
Other 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 $$
Sales
Gross Profit
Operating Income
Net Income $$ $
$
$
$ - CVS $
$
$
$ 2015 2014 $$
Sales
Gross Profit
Operating Income
Net 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