Financial information for publicly traded companies is available to the public
and can be found on a domestic company’s 10-K Annual Report on the
company’s investor relations page (e.g., Apple Financial Information
http://investor.apple.com/financials.cfm ) or the U.S. Securities and
Exchange Commission (SEC) site under
Filings & Forms (http://investor.apple.com/financials.cfm) and on many
other financial sites including Yahoo! Finance.
Mergent Online is a financial industry tool available to SNHU students for
free.
https://www.youtube.com/watch?v=kXjumWDZin0&feature=youtu.be&hd=1
.
To Find a Publicly Traded Company
•Log in to Brightspace, select the Online Student Services tab
at the top of the page, and under Academic Support, select the
Shapiro Library link.
•Select the A-Z Database List link, and in the Search for Databases
field, enter Mergent Online, click on GO, and then click on Mergent Online
in theres ults.
This is the direct link:
http://www.mergentonline.com.ezproxy.snhu.edu/basicsearch.php
•Enter a company name in the Company Search -Enter symbol or
Company Name field in the first section (top, left) and wait one moment for
a drop-down menu to appear.
•If it is a publicly traded company (meaning its stock trades on an
exchange where you can buy and sell shares), it will appear in the list
below and you will see the company stock ticker symbol and
name.
•Click on the company name in the list for details, and confirm that this is
the company you intended to search for by reading the Business
Summary.
•Review the information in the blue box at the top of the page and make
note of the company’s assigned Sector and Industry.
To Find Historical Stock Prices
•Click on the Equity Pricing tab.
•The Time Period defaults to 3years.
•Click on Report above the Controls box.
•Once you see the Historical Pricing, choose
Download near the top right of the screen.
•Save the Excel file to your computer.
•Use the Close price to calculate the percent increase or decrease in the
stock price over the seven weekday or three-year period (“rate of return”
inrubric).
•For example, Apple’s stock price on 8/19/16 closed at $109.36. Seven
weekdays earlier, on 8/11/16, it closed at $107.93. If you had bought 100
shares of Apple stock on 8/11/16 At $107.93 and sold them on 8/19/16 at
$109.36, you would have earned $143 before taxes, which is a 1.32% rate
of return. The calculation is (109.36-107.93)/107.93 x 100 which equals
1.32%.
•Apple’s stock price closed on 8/19/2013 at $72.53. The three-year rate of
return is 50.77%.
To Download Financial Statement Data
•Select the Company Financials tab to retrieve financial statement data.
•Balance Sheet data will appear, and it defaults to Annual.
•Five years’ worth of data will appear, and you can
change to 3 years by selecting that option to
the right of the Annuals/Balance Sheet above the Balance Sheet
data.
•Choose Refresh at the end of the next row.
•Choose Download near the top right of the screen.
•Save the Excel file to your computer.
•Change Balance Sheet to Income Statement and then Cash Flow to get
your other financial statements.
To Find a Competitor
•Click on the Competitors tab.
•Select Public Global to the right of Competitor at the top of the list of
companies.
•Click on company name to review the Business Summary
to confirm that this is the company you are interested
in.
•Review Sector and Industry listed at the top and determine if you want to
use this competitor for your competitive analysis.
To Find Industry Data
•Click on the Competitors tab.
•Select Public Global to the right of Competitor at the top of the list of
companies.
•Choose at least five competitors.
•Click on each company name to review the Business Summary
to confirm that this is the company you are interested in.
•Review Sector and Industry listed at the top and determine if you want to
use this competitor for your industry analysis.
•Determine what financial indicators you would like to compare and collect
the needed data
from the company financial statements for your ratio or Free Cash Flow
(FCF)
calculations.
•Average your calculated results to get an industry average (for example,
add the five company
FCF’s together and then divide by five).
Company A Name & Stock Symbol
Exchange rate used is that of the Year End reported date
As Reported Annual Balance Sheet
Report Date
Currency
Audit Status
Consolidated
Scale
Cash & cash equivalents
Restricted cash & marketable securities
Restricted cash
Accounts receivable
Raw materials
Work in process
Finished goods
Service parts
Inventory
Prepaid expenses & other current assets
Total current assets
Operating lease vehicles, net
Machinery, equipment & office furniture
Tooling
Leasehold improvements
Building & building improvements
Land
Computer equipment & software
Construction in progress
Property & equipment, gross
Less: accumulated depreciation & amortization
Property & equipment, net
Restricted cash
Emission credits
Debt issuance costs, net
Other assets
Other assets
Other assets
Total assets
Accounts payable
Accrued warranty, current portion
Build to suit finance obligation, current portion
Accrued interest
Environmental liabilities, current portion
Other accrued expenses
Taxes payable
Accrued purchases
Payroll & related costs
Warranty & other accrued expenses
12/31/2017
USD
Not Qualified
Yes
Thousands
1,196,908
22,628
168,965
528,935
163,830
476,512
108,561
1,277,838
125,229
2,791,568
1,791,403
1,694,910
550,902
338,392
461,303
60,234
175,512
693,207
3,974,460
571,126
3,403,334
31,522
74,633
8,092,460
916,148
101,206
140,540
86,859
94,193
←
Accrued liabilities
Deferred revenue
Resale value guarantee
Capital lease obligations, current portion
Customer deposits
Convertible debt, current portion
Long term debt & capital leases
Total current liabilities
Deferred revenue
Long term debt & capital leases
Resale value guarantee
Capital lease obligations, less current portion
Convertible debt, less current portion
Accrued warranty, less current portion
Deferred rent liability
Deferred tax liabilities
Environmental liabilities, less current portion
Other long-term liabilities
Other long-term liabilities
Other long-term liabilities
Convertible senior notes
Total liabilities
Common stock
Additional paid-in capital
Accumulated other comprehensive income (loss)
Retained earnings (accumulated deficit)
Total stockholders' equity (deficit)
422,798
423,961
136,831
283,370
633,166
2,816,274
446,105
2,040,375
1,293,741
364,976
42,045
7,003,516
131
3,414,692
-3,556
-2,322,323
1,088,944
Reference (example)
Mergent Online. (2018). Company A Retrieved January 21, 2018 from: (link to financial statement here)
Tip: F2 key let's you easily edit cell data.
Students: You will need three years' worth of data for the next Excel assignment if you want to download all three year
You will also need more historical stock prices too if you would like to download all available stock price data now.
Use most recent year-end report date available
ink to financial statement here)
Data quality check:
Assets = Liabilities + Equity
8,092,460
8,092,460
Company A Name & Stock Symbol
Exchange rate used is that of the Year End reported date
As Reported Annual Income Statement
Report Date
Currency
Audit Status
Consolidated
Scale
Development services
Automotive sales
Services & other revenues
Total revenues
Development services
Automotive sales
Services & other cost of revenues
Total cost of revenues
Gross profit (loss)
Research & development
Selling, general & administrative
Total operating expenses
Income (loss) from operations
Interest income
Interest expense
Other income (expense), net
Income (loss) before income taxes - domestic
Income (loss) before income taxes - international
Income (loss) before income taxes
Current state income taxes (benefit)
Current foreign income taxes (benefit)
Current income taxes (benefit)
Deferred foreign income taxes (benefit)
Deferred income taxes (benefit)
Provision for income taxes (benefit)
Net income (loss)
Weighted average shares outstanding-basic
Weighted average shares outstanding-diluted
Year end shares outstanding
Net income (loss) per share - basic
Net income (loss) per share - diluted
Number of full time employees
Number of common stockholders
Foreign currency translation adjustments
12/31/2017
USD
Not Qualified
Yes
Thousands
3,740,973
305,052
4,046,025
2,823,302
299,220
3,122,522
923,503
717,900
922,232
1,640,132
-716,629
1,508
118,851
-41,652
-415,694
-459,930
-875,624
525
10,342
10,867
2,172
2,172
13,039
-888,663
128,202
128,202
131,425
-7
-7
13,058
836
-10,999
Reference (example)
Mergent Online. (2018). Company A Retrieved January 21, 2018 from: (link to financial statement here)
Company A Name & Stock Symbol
Exchange rate used is that of the Year End reported date
As Reported Annual Cash Flow
Report Date
Currency
Audit Status
12/31/2017
USD
Not Qualified
Consolidated
Yes
Scale
Thousands
Net income (loss)
-888,663
Depreciation & amortization
422,590
Stock-based compensation
197,999
Amortization of discount on convertible debt
72,063
Inventory write-downs
44,940
Write-off of Department of Energy (DOE) loan origination costs
Change in fair value of DOE warrant liability
Fixed asset disposal
37,723
Other non-cash operating activities
26,373
Foreign currency transaction loss (gain)
55,765
Account receivables
46,267
Inventories & operating lease vehicles
-1,573,860
Prepaid expenses & other current assets
-29,595
Other assets
-24,362
Accounts payable
Accrued liabilities
Accounts payable & accrued expenses
263,345
Deferred revenue
322,203
Customer deposits
36,721
Resale value guarantee
442,295
Other long-term liabilities
23,697
Net cash flows from operating activities
-524,499
Purchases of property & equipment excluding capital leases
-1,634,850
Withdrawals out of (transfers into) our dedicated Department of Energy account,
- net
Decrease (increase) in restricted cash
-26,441
Purchase of short-term marketable securities
Maturities of short-term marketable securities
Business acquisition
-12,260
Net cash flows from investing activities
-1,673,551
Proceeds from issuance of convertible debt
Proceeds from issuance of convertible debt & other debt
318,972
Proceeds from issuance of common stock in public
730,000
Proceeds from issuance of warrants
Proceeds from exercise of stock options & other stock issuances
106,611
Proceeds from issuance of common stock in private placements
20,000
Principal payments on DOE loans
Purchase of convertible note hedges
Common stock & convertible debt issuance costs
-17,025
Principal payments on capital leases & other debt
-203,780
Collateralized lease borrowings
568,745
Net cash flows from financing activities
1,523,523
Effect of exchange rate changes on cash & cash equivalents
-34,278
Net increase (decrease) in cash & cash equivalents
Cash & cash equivalents at beginning of period
Cash & cash equivalents at end of period
Interest paid
Income taxes paid
-708,805
1,905,713
1,196,908
32,060
9,461
Reference (example)
Mergent Online. (2018). Company A Retrieved January 21, 2018 from: (link to financial statement here)
Company A Name & Stock Symbol
Last 7 Business Day Stock Prices (most recent day listed first)
Date
1/19/2018
1/18/2018
1/17/2018
1/16/2018
1/15/2018
1/12/2018
1/11/2018
Rate Of Return
Close
91.54
93.44
94.88
94.31
93.59
93.96
90.02
1.69 "The stock had a 1.69 percent return over the period January 11th through
Reference (example)
Mergent Online. (2018). Company A Retrieved January 21, 2018 from: (link to financial statement here)
e period January 11th through January 19th 2018."
Southern New Hampshire University
FIN 320 Module Three Excel Assignment Rubric
This is the first of two Excel assignments you will complete in this course. Before you get started, be sure to watch the Mergent Online video (written
instructions can be found here). For this assignment, consider the stock you own in your TDAU thinkorswim portfolio. Choose one of the companies from your
portfolio and complete the following steps:
1. Financial Reports
For the company you have selected, find the most recent annual balance sheet, annual income statement, and annual statement of cash flows.
Copy each of these three reports to its own worksheet within a single Excel file. The first worksheet should be titled 20XX Balance Sheet; the second, 20XX
Income Statement; the third, 20XX Cash Flow. (Replace the XX with the appropriate year.) The image below illustrates the way the file should be set up.
日ち
HOME
NEET
PAGE LAYOUT
2
Gale
sel
FORMULA DATA W VIW
Gmmal
17
5. Cand for all
Towing Styles Para
பழயா
Beve
7
M
Puste
Sort Find
Opboard
Pont
Eing
A1
E
c
F
F
H
M
N
See the three
worksheets below
1
2
3
4
5
6
7
B
9
To create a new
worksheet, click the "+"
character to the right of
the last worksheet
2013 Balance Sheet 2013 Stat 2013 Cave
READ
F EPS + 100
110
2. Stock Prices
Create a fourth worksheet within the same Excel file for historical stock prices. (You can name the worksheet Stock Prices.)
• Reference the To Find Historical Stock Prices section of the Mergent Online written instructions to locate the stock prices for your company for
the most recent seven days. In your Excel file, enter those seven prices.
On the Stock Prices worksheet, use an Excel formula to calculate the stock price's rate of return over that seven-day period. To do this,
1. Subtract the stock price of the first day from the stock price of the last day.
2. Divide this amount by the stock price from the first day.
3. Multiply by 100 to get the rate of return.
Southern New Hampshire University
3. Professionalism
Format the data on all worksheets so that the file has a neat and professional appearance.
4. References
At the bottom of each data tab in the spreadsheet, include a link and a properly formatted citation referencing the location of the data used.
Guidelines for Submission: Submit an Excel file that meets the criteria described in the prompt. Citations should be formatted according to APA style.
Critical Elements
Proficient (100%)
Needs Improvement (75%)
Not Evident (0%)
Value
Financial Reports Includes annual balance sheet, annual income Includes annual balance sheet, annual income Does not include annual balance sheet, annual 25
statement, and annual statement of cash flows statement, and annual statement of cash flows income statement, and annual statement of
for the company selected
for the company selected, but submission is cash flows for the company selected
incomplete or contains inaccuracies
Stock Prices Accurately calculates the stock's rate of return Calculates the stock's rate of return over the Does not calculate the stock's rate of return 45
over the seven-day period
seven-day period, but calculation contains over the seven-day period
inaccuracies
Professionalism Formats data with a neat and professional Formats data with a neat and professional Does not format data with a neat and
15
appearance
appearance, but contains some formatting professional appearance
issues
References Includes properly formatted APA-style citations Includes citations and links, but citations Does not include properly formatted APA-style 15
and links with no errors
contain some errors
citations or include citations and links with
major errors
Earned Total 100%
Purchase answer to see full
attachment