Finance Excel sheets. for Dr. Clover!

profileferand14
FIN320ModuleThreeExcelAssignmentExample.xlsx
Home>Business & Finance homework help>Management homework help>Finance Excel sheets. for Dr. Clover!

Company A 2017 Balance Sheet

Company A Name & Stock Symbol Tip: F2 key let's you easily edit cell data.
Exchange rate used is that of the Year End reported date
Students: You will need three years' worth of data for the next Excel assignment if you want to download all three years now.
You will also need more historical stock prices too if you would like to download all available stock price data now.
As Reported Annual Balance Sheet
Report Date 12/31/17 ← Use most recent year-end report date available
Currency USD
Audit Status Not Qualified
Consolidated Yes
Scale Thousands
Cash & cash equivalents 1,196,908
Restricted cash & marketable securities 22,628
Restricted cash -
Accounts receivable 168,965
Raw materials 528,935
Work in process 163,830
Finished goods 476,512
Service parts 108,561
Inventory 1,277,838
Prepaid expenses & other current assets 125,229
Total current assets 2,791,568
Operating lease vehicles, net 1,791,403
Machinery, equipment & office furniture 1,694,910
Tooling 550,902
Leasehold improvements 338,392
Building & building improvements 461,303
Land 60,234
Computer equipment & software 175,512
Construction in progress 693,207
Property & equipment, gross 3,974,460
Less: accumulated depreciation & amortization 571,126
Property & equipment, net 3,403,334
Restricted cash 31,522
Emission credits -
Debt issuance costs, net -
Other assets -
Other assets -
Other assets 74,633
Total assets 8,092,460
Accounts payable 916,148
Accrued warranty, current portion -
Build to suit finance obligation, current portion -
Accrued interest -
Environmental liabilities, current portion -
Other accrued expenses -
Taxes payable 101,206
Accrued purchases 140,540
Payroll & related costs 86,859
Warranty & other accrued expenses 94,193
Accrued liabilities 422,798
Deferred revenue 423,961
Resale value guarantee 136,831
Capital lease obligations, current portion -
Customer deposits 283,370
Convertible debt, current portion -
Long term debt & capital leases 633,166
Total current liabilities 2,816,274
Deferred revenue 446,105
Long term debt & capital leases 2,040,375
Resale value guarantee 1,293,741
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 364,976
Convertible senior notes 42,045
Total liabilities 7,003,516
Common stock 131
Additional paid-in capital 3,414,692
Accumulated other comprehensive income (loss) -3,556 Data quality check:
Retained earnings (accumulated deficit) -2,322,323 Assets = Liabilities + Equity
Total stockholders' equity (deficit) 1,088,944 8,092,460 8,092,460
Reference (example)
Mergent Online. (2018). Company A Retrieved January 21, 2018 from: (link to financial statement here)

Company A 2017 Income Statement

Company A Name & Stock Symbol
Exchange rate used is that of the Year End reported date
As Reported Annual Income Statement
Report Date 12/31/17
Regis, Kristin: Regis, Kristin: Use most recent year-end report date available
Currency USD
Audit Status Not Qualified
Consolidated Yes
Scale Thousands
Development services -
Automotive sales 3,740,973
Services & other revenues 305,052
Total revenues 4,046,025
Development services -
Automotive sales 2,823,302
Services & other cost of revenues 299,220
Total cost of revenues 3,122,522
Gross profit (loss) 923,503
Research & development 717,900
Selling, general & administrative 922,232
Total operating expenses 1,640,132
Income (loss) from operations -716,629
Interest income 1,508
Interest expense 118,851
Other income (expense), net -41,652
Income (loss) before income taxes - domestic -415,694
Income (loss) before income taxes - international -459,930
Income (loss) before income taxes -875,624
Current state income taxes (benefit) 525
Current foreign income taxes (benefit) 10,342
Current income taxes (benefit) 10,867
Deferred foreign income taxes (benefit) 2,172
Deferred income taxes (benefit) 2,172
Provision for income taxes (benefit) 13,039
Net income (loss) -888,663
Weighted average shares outstanding-basic 128,202
Weighted average shares outstanding-diluted 128,202
Year end shares outstanding 131,425
Net income (loss) per share - basic -7
Net income (loss) per share - diluted -7
Number of full time employees 13,058
Number of common stockholders 836
Foreign currency translation adjustments -10,999
Reference (example)
Mergent Online. (2018). Company A Retrieved January 21, 2018 from: (link to financial statement here)

Company A 2017 Cash Flow

Company A Name & Stock Symbol
Exchange rate used is that of the Year End reported date
As Reported Annual Cash Flow
Report Date 12/31/17
Regis, Kristin: Regis, Kristin: Use most recent year-end report date available
Currency USD
Audit Status 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 -708,805
Cash & cash equivalents at beginning of period 1,905,713
Cash & cash equivalents at end of period 1,196,908
Interest paid 32,060
Income taxes paid 9,461
Reference (example)
Mergent Online. (2018). Company A Retrieved January 21, 2018 from: (link to financial statement here)

Company A Stock History

Company A Name & Stock Symbol
Last 7 Business Day Stock Prices (most recent day listed first)
Date Close
1/19/2018 91.54
1/18/2018 93.44
1/17/2018 94.88
1/16/2018 94.31
1/15/2018 93.59
1/12/2018 93.96
1/11/2018 90.02
Rate Of Return 1.69
Regis, Kristin: Regis, Kristin: Note that formula includes *100 which converts the result into a percent. You may do this OR use the % Excel formatting key on the toolbar.		 "The stock had a 1.69 percent return over the period January 11th through January 19th 2018."
Reference (example)
Mergent Online. (2018). Company A Retrieved January 21, 2018 from: (link to financial statement here)