Comprehensive Financial Statement Analysis Project
Instructions:
You will need the annual reports for Walgreens 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 format.
Note that neither Walgreens nor CVS have calendar 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 year which ended 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.a. As of August 31, 2015, how many stores did Walgreens operate in total and in how many countries?
b. What are the three reportable Segments that Walgreens has? Based on "Sales to external customers", which is the largest segment?
c. Does Walgreens report a multi-step or single-step income statement? How do you know?
d. What is Walgreens' largest asset? Largest liability? What is the amount of each?
e. How many shares of common stock are Issued?
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?
g. What was the amount of the dividend declared and paid (per share) in fiscal 2015?
h. When does Walgreens record revenue sold at the stores?
i. What methods do Walgreens use to value its inventory (FIFO, LIFO, average, etc.) ?
j. What method does Walgreens use to depreciate its property and equipment? What are the useful lives assigned to different fixed asset categories?
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?
l. What items are recorded as cash equivalents?
m. What is the net Accounts Receivable balance at August 31, 2015?
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?
o. What type of financial instruments does Walgreens invest in? How do they classify these investments?
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?
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?
r. What activities are planned under the new restructuring "Cost Transformation" program?
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?
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.
u. How many employees does Walgreens have at August 31, 2015?
v. Describe IN YOUR OWN WORDS the major business risk factor that Walgreens faces?
w. In what countries in Central and South America does Walgreens operate?
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?
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?
The location (source) where you can find the answer is provided for you above the shaded input box. Use only that source to answer the question. The Table of Contents of the annual report 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 ratio and what the ratio measures. Enter your answers in the shaded input boxes. 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 the ratio listed in pages 775-777 to each ratio so that you know exactly how to calculate them.
When calculating the ratios, only use the amounts included in the audited financial statements (Item 8. Financial Statements and Supplementary Data); do not just enter those that may be shown in other sections of the annual report. All other sections of the annual report except for the financial statements are not audited and as such the preparer can calculate any other ratios or financial indicators are they wish, not necessarily as the textbook prescribes. If you pick up amounts in other sections you risk getting the question wrong.
"Since the fiscal 2015 financial statements are comparative, they include info for both fiscal years 2015 and 2014. Use those financial statements to answer the questions about 2014 as well.
However, some ratios for the 2014 year will require you go to back an additional year because some of the 2014 ratios use averages which are calculated using the 2014 + 2013 balances. For example, the inventory turnover for 2014 uses average inventory calculated by using the 2014 ending inventory balance + the 2014 beginning inventory balance (which is the ending balance in 2013). You need to obtain the fiscal 2013 ending inventory balance included in the 2014 financial statements which have been provided to you."
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 spreadsheet: Ratio formula and actual ratio answer for Walgreens 2015
You only need to calculate the 2015 ratio for CVS on the columns on the right-hand side of the spreadsheet.
Attachment:- Group Project.xlsx