Expert answer:Analyst report assignment
Solved by verified expert:Please follow the instructions in the power point and the vocabulary used in the report is simple and easy to understand. I have completed most of the financial statements, but I hope you start over from the beginning and I have attached it for you for illustration purposes only. The report consists of 4 to 5 pages and it is the main requirement in the assignment.
presentation1.pptx
macys_10_k.xlsx
jcp_10_k.xlsx
Unformatted Attachment Preview
Analyst report assignment
.
Overview
• Separate into 12 groups of 3 students
• Each group is working as financial analysts preparing reports for
clients interested in potential investments
• Analyze the business and financial statements of 2 publicly-traded
companies within the same industry
• Provide an opinion as to whether or not each company is a good
investment based on your analysis of the company’s liquidity,
solvency and profitability analyses as well as your overall
interpretation of the horizontal and vertical analyses
Overview
• Prepare a written analyst report of 4-5 pages based on your analysis.
• Written reports will be due on November 4.
• With your group, present your findings to the class. Presentations
should be between 8-10 minute
Overview
• Use proper business writing
• Professional
• avoid clichés and colloquialisms
• Concise
• Free of errors
• All writing should be in your own words not those of your sources
• Format: follow the template on Blackboard
• Submission: Upload your Word and Excel documents in Blackboard
• Present your findings to the class (8-10 minutes per group)
Outline
• Brief discussion of each company and its business
• Brief discussion of significant recent developments that have affected any of your specific
companies or the entire industry
• Analysis of Excel-based financial statements including:
• 3-year horizontal analysis
• Vertical analysis for each of the 3 years
• Ratio calculations for each of the 3 years divided into 3 components: liquidity, solvency and
profitability
• Interpretation of each item above (main focus of the assignment)
• Overall conclusion as to whether the company is a good investment
Discussion of the companies and their
businesses and industry
• Sources:
• Companies’ websites
• “Business” section of annual report (10-k)
• What should your client know about the businesses and
their environment?
Significant recent developments
• How/why have the companies been in the news recently?
• Use reputable sources (WSJ instead of Wikipedia)
• Summarize what your client should know about the
company’s recent developments
Source:
http://writing.wisc.edu/Handbook/QPA_paraphrase2.html
• Use proper business writing
• Professional
• avoid clichés and colloquialisms
• Concise
• Free of errors
Analysis of Excel-based financial statements
• 3-year horizontal analysis
• Vertical analysis for each of the 3 years
• Ratio calculations
• Find the most recent fiscal year-end that all companies have filed and
use that year as the most recent year
• Download Excel-based financial statements
Accessing and downloading Excel-based financial
statements
Access the most recent 10-K (and the 10-k from two years earlier) using
the following link and entering the company name or ticker
https://www.sec.gov/edgar/searchedgar/companysearch.html
Search filing type 10-K
Click on the link for
Interactive Data
Accessing and downloading Excel-based financial
statements
Choose: View Excel Document
Downloading Excel-based financial
statements
Once opened, you will need the following worksheets:
• Income Statements (also called Statement of Operations)
• Do not use the Statements of Comprehensive Income, which is
different from the Income Statement
• Balance Sheets (also called Statements of Financial Position)
• Statements of Cash Flows
Downloading Excel-based financial
statements
• Copy the 3 financial statement worksheets from Edgar into the ratio
analysis template
• The income statements and statements of cash flows report 3 years
of data.
• Rename the worksheets and present in the following order:
• Balance sheets
• Income statements
• Statements of cash flows
Downloading Excel-based financial
statements
• You will need to add two years of balance sheet data from an older10k in order to report 4 years of balance sheet data necessary to
calculate ratios for the most recent 3 years.
• May need to add accounts (lines) to the balance sheet if an
account was used three and/or four years ago but not in the two
most recent balance sheets
Analyzing financial statements
• Horizontal Analysis
• Copy the balance sheets into a new worksheet, title the worksheet
Horizontal Analysis and keep the most recent 3 years of data
• Calculate (using formulas in Excel) and report dollar and
percentage increases or decreases over the 3-year period so that
the most recent balance sheet accounts are reported as a % of the
base year balances
Horizontal Analysis – Balance Sheet
Horizontal Analysis – Income Statement
Analyzing financial statements
• Vertical Analysis
• Copy the balance sheets into a new worksheet, title the worksheet
Vertical Analysis and keep the most recent 3 years of data
• Calculate (using formulas in Excel) and report each balance sheet
account as a percentage of total assets
Vertical Analysis – Balance Sheets
Vertical Analysis – Income Statements
Ratio Analysis
• Although analysts may use different variations of some of
the ratios we are analyzing, use the ratio definitions given on
the template
• Use average balances by year where indicated (not ending
balances)
Analyzing financial statements
• Link the underlying data needed as inputs for calculating each ratio
• Do not hard-code the numbers on the spreadsheet
• For example, when determining the current ratio:
• Link the value of current assets in cell E10 to the current assets
field in the company’s Excel balance sheet
• Link the value of current liabilities in cell E11 to the current
liabilities field in the company’s Excel balance sheet
Analyzing financial statements
• Use Excel for all calculations
• Do not hard-code the numbers on the spreadsheet
• For example, when determining the current ratio:
Analyzing financial statements
• If a required value is not directly reported in the balance sheet, show
your calculation using Excel
• For example, suppose the balance sheet does not show a subtotal for
current liabilities. In the ratio template field requesting current
liabilities, use Excel to calculate it by adding (and linking to) each
current liability field on the balance sheet
Analyzing financial statements
• If a value is not required and not reported, enter 0.
• For example, Walmart does not separately report “Short-term
investments”. For simplicity, instead of looking in the footnotes for
this data, report a value of zero.
• Be sure to delete the formula when a field is missing or has a value
of zero so your spreadsheet does not report #DIV/0!, #VALUE!, or an
inappropriate change of 100%
Analyzing financial statements
Non-controlling Interests
• The company has an investment in one or more subsidiaries
• Accounting rules require consolidation of financial information if the parent
company owns more than 50% of the subsidiary.
• Consolidation of 100% of the assets, liabilities, equity and income
• Ownership of 51-99% of the assets, liabilities, equity and income
• “Non-controlling interest” represents the portion that is consolidated by the
(parent) company but owned by others
Non-controlling Interests
Source: http://macabacus.com/accounting/noncontrolling-interest
Non-controlling Interests
Source: http://macabacus.com/accounting/noncontrolling-interest
Analyzing financial statements
Non-controlling Interests
• In ratio analysis, use stockholders’ equity and net income amounts that are attributable to
the (parent) company only
• Ratios that use equity and net income:
•
•
•
•
•
•
ROE
ROA
Profit margin
Times interest earned
EPS
Payout ratio
Earnings per share
• Use basic earnings per share
• Diluted EPS accounts for the conversion of options
(employee stock options, convertible debt, etc) to redeem
common stock
Earnings per share
• Although EPS is reported on the face of the income
statement, show the calculation by linking to earnings
attributable to the company and basic weighted
average common shares outstanding
Market Price per Share
Find the closing price on the dates of all three fiscal year-ends being
analyzed.
• Use any source to find stock prices. Two common sources are Yahoo
Finance and Google Finance. Use the “historical data” section.
• Stocks do not trade on weekends or certain holidays. If the company’s
fiscal year end is on a non-trading day, use the last closing price prior
to the fiscal year end.
Source:
https://www.google.com/finance/historical?cid=38230&startdate=Jan+20%2C+2016&enddate=Feb+4%2C+2016&num=30&ei=338FWMGaDMfEeqqUnMgG
Use (and defend) your judgment
• If a ratio calculation requires your judgement, explain your
decision.
• For example, Wal-Mart reports revenues as “net sales”
and “membership and other income”. Either can be used
as the “net sales” number in ratio calculations, I
should/would explain why I chose to include both types of
revenue in my calculation of net sales.
Analysis is an art, not a science
• You may find different ratios telling different stories (for example,
ROA is increasing but ROE is decreasing)
• Instead of telling a conflicting story (profitability is both increasing
and decreasing), describe the big picture (income was relatively
unchanged over the 3-year period but the company sold PP&E,
reducing its assets, while at the same time the company issued new
shares of common stock, increasing equity).
The analysis
• You don’t need to write about each number you calculated
• Tell an interesting story
• Discuss only the most significant changes and why they matter
• Focus on overall liquidity, solvency and profitability but be able to
support your rationale with specific ratios where necessary
• Understand what the changes mean for the company and whether
the changes are good or bad for the company
• Discuss the “story” told by the data for each company, one company
at a time.
The report
• General guideline: Written portion should be 4-5 pages doublespaced
• Upload the written portion, without tables, in Blackboard using the
link called Analyst Report Word Document
• Check your Safe Assign score; scores above 25% indicate that too
much text matches underlying sources. Make sure your writing is in
your own words
• Upload each Excel file (one for each company analyzed) using the link
called Analyst Report Excel Files
Consolidated Balance Sheets – USD ($) $ in Millions
Current Assets:
Cash and cash equivalents
Receivables
Merchandise inventories
Prepaid expenses and other current assets
Total Current Assets
Property and Equipment – net
Goodwill
Other Intangible Assets – net
Other Assets
Total Assets
Current Liabilities:
Short-term debt
Merchandise accounts payable
Accounts payable and accrued liabilities
Income taxes
Total Current Liabilities
Long-Term Debt
Deferred Income Taxes
Other Liabilities
Shareholders’ Equity:
Common stock (304.1 and 310.3 shares outstanding)
Additional paid-in capital
Accumulated equity
Treasury stock
Accumulated other comprehensive loss
Total Macy’s, Inc. Shareholders’ Equity
Noncontrolling interest
Total Shareholders’ Equity
Total Liabilities and Shareholders’ Equity
Jan. 28, 2017
Jan. 30, 2016
$ 1.297
522
5.399
408
7.626
7.017
3.897
498
813
19.851
$ 1.109
558
5.506
479
7.652
7.616
3.897
514
897
20.576
309
1.423
3.563
352
5.647
6.562
1.443
1.877
642
1.526
3.333
227
5.728
6.995
1.477
2.123
3
617
6.088
(1.489)
(896)
4.323
(1)
4.322
$ 19.851
3
621
6.334
(1.665)
(1.043)
4.250
3
4.253
$ 20.576
Jan. 31, 2015
Feb. 01, 2014
$ 2.246
424
5.417
493
8.580
7.800
3.743
496
711
21.330
$2.273
438
5.557
420
8.688
7.930
3.743
527
732
21.620
76
1.594
3.109
296
5.075
7.233
1.443
2.201
463
1.691
2.810
362
400
5.726
6.714
1.273
4
1.048
7.340
(1.942)
(1.072)
5.378
0
5.378
$ 21.330
4
2.522
6.235
-1.847
-665
6.249
$21.620
Consolidated Statements of Income – USD ($) $ in Millions
Income Statement [Abstract]
Net sales
Cost of sales
Gross margin
Selling, general and administrative expenses
Impairments, store closing and other costs
Settlement charges
Operating income (loss)
Interest expense
Premium on early retirement of debt
Interest income
Income (loss) before income taxes
Federal, state and local income tax expense
Net income
Net loss attributable to noncontrolling interest
Net income attributable to Macy’s, Inc. shareholders
Basic earnings per share attributable to Macy’s, Inc. shareholders
Diluted earnings per share attributable to Macy’s, Inc. shareholders
Jan. 28, 2017
12 Months Ended
Jan. 30, 2016
$ 25.778
(15.621)
10.157
(8.265)
(479)
(98)
1.315
(367)
0
4
952
(341)
611
8
$ 619
$ 2,01
$ 1,99
$ 27.079
(16.496)
10.583
(8.256)
(288)
0
2.039
(363)
0
2
1.678
(608)
1.070
2
$ 1.072
$ 3,26
$ 3,22
nths Ended
Jan. 31, 2015
$ 28.105
(16.863)
11.242
(8.355)
(87)
0
2.800
(395)
(17)
2
2.390
(864)
1.526
0
$ 1.526
$ 4,30
$ 4,22
Consolidated Statements of Cash Flows – USD ($) $ in Millions
Cash flows from operating activities:
Net income
Adjustments to reconcile net income to net cash provided by operating activities:
Impairments, store closing and other costs
Settlement charges
Depreciation and amortization
Stock-based compensation expense
Gains on sale of real estate
Amortization of financing costs and premium on acquired debt
Changes in assets and liabilities:
(Increase) decrease in receivables
(Increase) decrease in merchandise inventories
Increase in prepaid expenses and other current assets
Increase in other assets not separately identified
Increase (decrease) in merchandise accounts payable
Increase (decrease) in accounts payable, accrued liabilities and other items not separately identified
Increase (decrease) in current income taxes
Increase (decrease) in deferred income taxes
Increase (decrease) in other liabilities not separately identified
Net cash provided by operating activities
Cash flows from investing activities:
Purchase of property and equipment
Capitalized software
Acquisition of Bluemercury, Inc., net of cash acquired
Disposition of property and equipment
Other, net
Net cash used by investing activities
Cash flows from financing activities:
Proceeds from Issuance of Commercial Paper
Financing costs
Debt repaid
Dividends paid
Increase (decrease) in outstanding checks
Acquisition of treasury stock
Issuance of common stock
Proceeds from noncontrolling interest
Net cash used by financing activities
Net increase (decrease) in cash and cash equivalents
Cash and cash equivalents beginning of period
Cash and cash equivalents end of period
Supplemental cash flow information:
Interest paid
Interest received
Income taxes paid (net of refunds received)
Jan. 28, 2017
12 Months Ended
Jan. 30, 2016
$ 611
$ 1.070
Jan. 31, 2015
$ 1.526
479
98
1.058
61
(209)
(14)
288
0
1.061
65
(212)
(14)
87
0
1.036
73
(92)
(5)
(1)
107
(8)
0
(132)
(162)
(45)
(60)
0
(1)
(78)
68
22
44
(3)
(61)
(21)
129
125
(139)
(73)
1.801
(69)
(1)
(88)
1.984
(65)
29
10
2.709
(596)
(316)
0
673
52
(187)
(777)
(336)
(212)
204
29
(1.092)
(770)
(298)
0
172
(74)
(970)
2
(3)
(751)
(459)
61
(316)
36
4
(1.426)
188
1.109
1.297
499
(4)
(152)
(456)
(83)
(2.001)
163
5
(2.029)
(1.137)
2.246
1.109
1.044
(9)
(870)
(421)
133
(1.901)
258
0
(1.766)
(27)
2.273
2.246
396
4
$ 352
383
2
$ 635
413
2
$ 834
Consolidated Balance Sheets – USD ($) $ in Millions
Current Assets:
Cash and cash equivalents
Receivables
Merchandise inventories
Prepaid expenses and other current assets
Total Current Assets
Property and Equipment – net
Goodwill
Other Intangible Assets – net
Other Assets
Total Assets
Current Liabilities:
Short-term debt
Merchandise accounts payable
Accounts payable and accrued liabilities
Income taxes
Total Current Liabilities
Long-Term Debt
Deferred Income Taxes
Other Liabilities
Shareholders’ Equity:
Common stock (304.1 and 310.3 shares outstanding)
Additional paid-in capital
Accumulated equity
Treasury stock
Accumulated other comprehensive loss
Total Macy’s, Inc. Shareholders’ Equity
Noncontrolling interest
Total Shareholders’ Equity
Total Liabilities and Shareholders’ Equity
Jan. 28, 2017
Jan. 30, 2016
$ 1.297
522
5.399
408
7.626
7.017
3.897
498
813
19.851
$ 1.109
558
5.506
479
7.652
7.616
3.897
514
897
20.576
309
1.423
3.563
352
5.647
6.562
1.443
1.877
642
1.526
3.333
227
5.728
6.995
1.477
2.123
3
617
6.088
(1.489)
(896)
4.323
(1)
4.322
$ 19.851
3
621
6.334
(1.665)
(1.043)
4.250
3
4.253
$ 20.576
Jan. 31,
2015
change over 3 year period
Amount
Percent
$ 2.246
424
5.417
493
8.580
7.800
3.743
496
711
21.330
$ (949)
$ 98
$ (18)
$ (85)
$ (954)
$ (783)
$ 154
$2
$ 102
$ (1.479)
-42%
23%
0%
-17%
-11%
-10%
4%
0%
14%
-7%
76
1.594
3.109
296
5.075
7.233
1.443
2.201
$ 233
$ (171)
$ 454
$ 56
$ 572
$ (671)
$0
$ (324)
307%
-11%
15%
19%
11%
-9%
0%
-15%
$ (1)
$ (431)
$ (1.252)
$ 453
$ 176
$ (1.055)
$ (1)
$ (1.056)
$ (1.479)
-25%
-41%
-17%
-23%
-16%
-20%
#DIV/0!
-20%
-7%
4
1.048
7.340
(1.942)
(1.072)
5.378
0
5.378
$ 21.330
Consolidated Statements of Income – USD ($) $ in Millions
Income Statement [Abstract]
Net sales
Cost of sales
Gross margin
Selling, general and administrative expenses
Impairments, store closing and other costs
Settlement charges
Operating income (loss)
Interest expense
Premium on early retirement of debt
Interest income
Income (loss) before income taxes
Federal, state and local income tax expense
Net income
Net loss attributable to noncontrolling interest
Net income attributable to Macy’s, Inc. shareholders
Basic earnings per share attributable to Macy’s, Inc. shareholders
Diluted earnings per share attributable to Macy’s, Inc. shareholders
Jan. 28, 2017
12 Months Ended
Jan. 30, 2016
$ 25.778
(15.621)
10.157
(8.265)
(479)
(98)
1.315
(367)
0
4
952
(341)
611
8
$ 619
$ 2,01
$ 1,99
$ 27.079
(16.496)
10.583
(8.256)
(288)
0
2.039
(363)
0
2
1.678
(608)
1.070
2
$ 1.072
$ 3,26
$ 3,22
nths Ended
Jan. 31, 2015
$ 28.105
(16.863)
11.242
(8.355)
(87)
0
2.800
(395)
(17)
2
2.390
(864)
1.526
0
$ 1.526
$ 4,30
Change over 3-year period
Amount Percent
$ (2.327)
$ 1.242
$ (1.085)
$ 90
$ (392)
$ (98)
$ (1.485)
$ 28
$ 17
$2
$ (1.438)
$ 523
$ (915)
$8
$ (907)
-8%
-7%
-10%
-1%
451%
#DIV/0!
-53%
-7%
-100%
100%
-60%
-61%
-60%
#DIV/0!
-59%
$ (2)
-53%
$ (2)
-53%
$ 4,22
Consolidated Balance Sheets – USD ($) $ in Millions
Current Assets:
Cash and cash equivalents
Receivables
Merchandise inventories
Prepaid expenses and other current assets
Total Current Assets
Property and Equipment – net
Goodwill
Other Intangible Assets – net
Other Assets
Total Assets
Current Liabilities:
Short-term debt
Merchandise accounts payable
Accounts payable and accrued liabilities
Income taxes
Total Current Liabilities
Long-Term Debt
Deferred Income Taxes
Other Liabilities
Shareholders’ Equity:
Common stock (304.1 and 310.3 shares outstanding)
Additional paid-in capital
Accumulated equity
Treasury stock
Accumulated other comprehensive loss
Total Macy’s, Inc. Shareholders’ Equity
Noncontrolling interest
Total Shareholders’ Equity
Total Liabilities and Shareholders’ Equity
Jan. 28, 2017
Percent
$ 1.297
522
5.399
408
7.626
7.017
3.897
498
813
19.851
309
1.423
3.563
352
5.647
6.562
1.443
1.877
3
617
6.088
(1.489)
(896)
4.323
(1)
4.322
$ 19.851
7%
3%
27%
2%
38%
35%
20%
3%
4%
100%
Jan. 30, 2016
Percent
$ 1.109
558
5.506
479
7.652
7.616
3.897
514
897
20.576
5%
3%
27%
2%
37%
37%
19%
2%
4%
100%
Jan. 31,
2015
$ 2.246
424
5.417
493
8.580
7.800
3.743
496
711
21.330
642
1.526
3.333
227
5.728
6.995
1.477
2.123
76
1.594
3.109
296
5.075
7.233
1.443
2.201
3
4
621
6.334
(1.665)
(1.043)
4.250
3
4.253
$ 20.576
1.048
7.340
(1.942)
(1.072)
5.378
0
5.378
$ 21.330
Percent
11%
2%
25%
2%
40%
37%
18%
2%
3%
100%
Consolidated Statements of Income – USD ($) $ in Millions
Income Statement [Abstract]
Net sales
Cost of sales
Gross margin
Selling, general and administrative expenses
Impairments, store closing and other costs
Settlement charges
Operating income (loss)
Intere …
Purchase answer to see full
attachment
How it works
- Paste your instructions in the instructions box. You can also attach an instructions file
- Select the writer category, deadline, education level and review the instructions
- Make a payment for the order to be assignment to a writer
- Download the paper after the writer uploads it
Will the writer plagiarize my essay?
You will get a plagiarism-free paper and you can get an originality report upon request.
Is this service safe?
All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades