FIN 320 Southern New Hampshire University Financial Statement Data and Use Formulas to Calculate Worsheet everything needed is below you just need to go of

FIN 320 Southern New Hampshire University Financial Statement Data and Use Formulas to Calculate Worsheet everything needed is below you just need to go off of what ive already done in the exel sheet FIN 320 Module Four Excel Assignment Rubric
This assignment builds on the work you did for the Excel assignment in Module Three. To get started, find and open the file you submitted. From there,
complete the following steps:
1. Financial Data
Using the same company you selected in Module Three, add another two years of financial statement data so that you have three years of annual data
to review for historical analysis. In all, your Excel file must include the following:
o
o
o
Three worksheets of annual balance sheet data
Three worksheets of annual income statement data
Three worksheets of annual statement of cash flow data
Important Note: Be sure to label each worksheet in Excel with the appropriate year, as you did in the Module Three assignment.
2. Ratio Calculation
On each data tab, use formulas to calculate the following financial indicators for each year of data:
o
o
o
o
o
o
o
Current ratio
Debt/equity ratio
Free cash flow
Earnings per share
Price/earnings ratio
Return on equity
Net profit margin
3. Written Responses
In a separate Word document, respond to the following:
o Describe how and why each of the ratios has changed over the three-year period. For example, did the current ratio increase or decrease? Why?
Describe how three of the ratios you calculated for your company compare to the general industry. Find general industry data by entering your specific
company’s ticker symbol here. Be sure to watch the Mergent Online video (written instructions can be found here), as it will help you find the industry data.
4. Professionalism, References, and Mechanics
Format the data on all worksheets so that the file has a neat and professional appearance. Include links and properly formatted citations referencing the
location of the data used. Your written responses should be free of errors in organization, grammar, and style.
Guidelines for Submission: Submit an Excel file that meets the criteria described in the prompt. The written responses should be done in a Word document. Be
sure to include both the Excel file and Word document. Citations should be formatted according to APA style.
Critical Elements
Financial Data
Exemplary (100%)
Meets “Proficient” criteria and
presents information in a wellorganized manner with clearly
labeled tabs and data sections
Ratio Calculation
Meets “Proficient” criteria and
presents information in a clear
and well-organized manner
Written Responses:
Changes in Ratios
Meets “Proficient” criteria and
includes supporting details and
data
Written Responses:
Comparison to Industry
Meets “Proficient” criteria and
includes supporting details and
data
Professionalism,
References, and
Mechanics
Meets “Proficient” criteria, and
the material is presented in an
especially polished and easy to
read format
Proficient (85%)
Includes three years of financial
statement data (three annual
balance sheets, three annual
income statements, and three
annual statements of cash flows)
for the company selected, with
minor errors or no errors
Accurately calculates the
financial indicators (current
ratio, debt/equity ratio, free
cash flow, earnings per share,
price/earnings ratio, return on
equity, and net profit margin),
with minor errors or no errors
Describes how and why each of
the ratios has changed over the
three-year period in a clear and
logical manner
Needs Improvement (55%)
Includes three years of financial
statement data (three annual
balance sheets, three annual
income statements, and three
annual statements of cash flows)
for the company selected, with
noticeable errors
Calculates the financial
indicators (current ratio,
debt/equity ratio, free cash flow,
earnings per share,
price/earnings ratio, return on
equity, and net profit margin),
with noticeable errors
Describes how and why each of
the ratios has changed over the
three-year period but presents
some information in an unclear
or illogical manner
Describes how the selected
Describes how the selected
ratios compare to the general
ratios compare to the general
industry in a clear and logical
industry but presents some
manner
information in an unclear or
illogical manner
Formats data with a neat and
Formats data and provides
professional appearance and
citations and links, but there are
includes properly formatted
deficiencies in the appearance of
APA-style citations and links with the data and/or flaws in the
minor errors or no errors; any
citation formatting; there are
errors related to organization,
some errors related to
grammar, and style are minor
organization, grammar, and style
Not Evident (0%)
Does not include three years of
financial statement data (three
annual balance sheets, three
annual income statements, and
three annual statements of cash
flows) for the company selected
Value
20
Does not calculate the financial
indicators (current ratio,
debt/equity ratio, free cash flow,
earnings per share,
price/earnings ratio, return on
equity, and net profit margin)
40
Does not describe how and why
each of the ratios has changed
over the three-year period
15
Does not describe how the
selected ratios compare to the
general industry
15
Does not format data with a
neat and professional
appearance or include properly
formatted APA-style citations
and links; there are major errors
related to organization,
grammar, and style
Earned Total
10
100%
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
Accrued liabilities
12/31/2015
USD
Not Qualified
Yes
Thousands
845.889
3.012
49.109
184.665
42.500
69.324
43.866
340.355
27.574
1.265.939
382.425
322.394
230.385
94.763
67.707
45.020
42.073
76.294
878.636
140.142
738.494
6.435
13.930
7.315
2.392
23.637
2.416.930
303.969
19.917
2.132
2.578
38.067
19.023
26.535
108.252
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)
91.882
7.722
163.153
182
675.160
181.180
236.299
12.855
586.119
33.265
9.886
6.821
3.364
4.861
1.749.810
123
1.806.617
-1.139.620
667.120
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.
See Ratios on 2017 Balance Sheet
(Students: Let your instructor know where your ratios are located if it’s not on the first ta
Students: It’s also acceptable to put three years’ of data on one tab.
Data quality check:
Assets = Liabilities + Equity
2.416.930
2.416.930
cial statement here)
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
Accrued liabilities
12/31/2016
USD
Not Qualified
Yes
Thousands
1.905.713
17.947
226.604
392.292
56.114
397.318
107.951
953.675
94.718
3.198.657
766.744
720.746
295.906
230.270
154.362
49.478
98.970
572.125
2.121.857
292.590
1.829.267
11.374
43.209
5.849.251
777.946
32.321
21.030
7.222
3.573
10.470
71.229
68.547
54.492
268.884
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)
191.651
9.532
257.587
601.566
2.107.166
292.271
487.879
12.267
1.806.518
173.244
58.196
4.937.541
126
2.345.266
-1.433.682
911.710
Reference (example)
Mergent Online. (2018). Company A Retrieved January 21, 2018 from: (link to financial statement here)
cial statement here)
Data quality check:
Assets = Liabilities + Equity
5.849.251
5.849.251
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
Accrued liabilities
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
422.798
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)
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
References (example)
Mergent Online. (2018). Company A Retrieved January 21, 2018 from: (link to financial statement here)
Industry Averages. (2018). Industry: Example Only – Major. Retrieved February 11, 2018 from: (link to industry averages here)
Ratios
2017
Current Ratio
Debt / Equity Ratio
Free Cash Flow (000)
Earnings per Share
Price / earnings ratio
Return on Equity
Net profit margin
2016
0,99
1,52
6,43
5,42
-$2.159.349
-$1.027.222
$
(6,93) $
(2,36) $
-34,62
-94,20
-81,61%
-32,25%
-21,96%
-9,19%
Stock Data
Number of Shares Outstanding
131.425
125.688
Closing Price Last Business Day in December
240,01
222,41
2015
1,88
2,62
-$6.230
(0,62)
-242,72
-11,09%
-3,68%
123.091
150,43
Data quality check:
Assets = Liabilities + Equity
8.092.460
8.092.460
o financial statement here)
ary 11, 2018 from: (link to industry averages here)
Industry Average (Students: include a reference for this data too)
109,19
1,63
13,00
12,40%
5,00%
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/2015
USD
Not Qualified
Yes
Thousands
15.710
1.997.786
2.013.496
13.356
1.543.878
1.557.234
456.262
231.976
285.569
517.545
-61.283
189
32.934
22.602
-75.279
3.853
-71.426
178
2.349
2.527
61
61
2.588
-74.014
119.421
119.421
123.091
-1
-1
5.859
699
–
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 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/2016
USD
Not Qualified
Yes
Thousands
5.633
3.192.723
3.198.356
6.674
2.310.011
2.316.685
881.671
464.700
603.660
1.068.360
-186.689
1.126
100.886
1.813
-60.451
-224.185
-284.636
257
9.203
9.460
-56
-56
9.404
-294.040
124.539
124.539
125.688
-2
-2
10.161
769
–
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 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/2015
USD
Not Qualified
Consolidated
Yes
Scale
Thousands
Net income (loss)
-74.014
Depreciation & amortization
106.083
Stock-based compensation
80.737
Amortization of discount on convertible debt
9.143
Inventory write-downs
8.918
Write-off of Department of Energy (DOE) loan origination costs
5.558
Change in fair value of DOE warrant liability
-10.692
Fixed asset disposal
Other non-cash operating activities
3.611
Foreign currency transaction loss (gain)
3.655
Account receivables
-21.917
Inventories & operating lease vehicles
-463.270
Prepaid expenses & other current assets
-17.466
Other assets
-342
Accounts payable
-243
Accrued liabilities
66.567
Accounts payable & accrued expenses
Deferred revenue
268.153
Customer deposits
24.243
Resale value guarantee
236.299
Other long-term liabilities
32.971
Net cash flows from operating activities
257.994
Purchases of property & equipment excluding capital leases
-264.224
Withdrawals out of (transfers into) our dedicated Department of Energy account,
14.752 net
Decrease (increase) in restricted cash
55
Purchase of short-term marketable securities
…
Purchase answer to see full
attachment