Excel Project: Part III Instructions
Common Sized Statements (Tabs 9-10) and Horizontal Analysis (Tabs 11-12)
Continue in the same Excel workbook prepared in Week 3, adding the following tabs:
oThe ninth tab in your Financial Statement Analysis Template must be labeled Common Sized Balance Sheets.
Set-up like the Historical Balance Sheets (Tab 3), this worksheet should use formulas to calculate the common sized percentages, linking to the Historical Balance Sheets for the calculations."Total Assets" should be used as the base for all common sized calculations on the Balance Sheets.Common sized percentages should be calculated for all three historical years presented.
See Exhibit A for an example of this tab.
oThe tenth tab in your Financial Statement Analysis Template must be labeled Common Sized Income Statements.
Set-up like the Historical Income Statements (Tab 2), this worksheet should use formulas to calculate the common sized percentages, linking to the Historical Income Statements for the calculations."Revenue" or "Total Revenue" (depending on your company) should be used as the base for all common sized calculations on the Income Statements. Common sized percentages should be calculated for all three historical years presented.
oThe eleventh tab in your Financial Statement Analysis Template must be labeled Horizontal Analysis of Balance Sheets.
Start by copying and pasting the Historical Balance Sheets from Tab 3 to this worksheet.Then, add dollar and percentage change columns between each of the set of historical years.
See Exhibit B for an example of this tab.
oThe twelfth tab in your Financial Statement Analysis Template must be labeled Horizontal Analysis of Income Statements.
Start by copying and pasting the Historical Income Statements from Tab 2 to this worksheet.Then, add dollar and percentage change columns between each of the set of historical years.
oOther instructions:
The Excel workbook should be professionally formatted.Professional formatting includes:
All numbers are formatted similarly.The use of decimals, commas for thousands, and other formatting is consistent.
Dollar signs are included in the first and last numbers of each column.
Subtotals and totals are preceded by a single underline.Totals are followed by a double underline.
Line descriptions are indented/bolded in a manner that makes the spreadsheet easy to read.
All totals and subtotals should be calculated using formulas.For an example of which cells may need formulas, please see Exhibits A & B.
AutoSave O Off) AND - GH excelprojectlacct370 (1) - Excel Search keturahlove18@icloud.com K X File Home Insert Page Layout Formulas Data Review View Help Share Comments Ruler Formula Bar Split DO View Side by Side Normal Page Break Page Custom Zoom 100% Zoom to New Arrange Freeze Hide IDSynchronous Scrolling G Preview Layout Views Gridlines Headings Switch Macros Selection Window All Panes Unhide I En Reset Window Position Windows Workbook Views Show Zoom Window Macros BS X V A B C D Consolidated Statements of Redeemable Noncontrolling Interest and Stockholders' Equity - USD ($) shares in Millions, $ in Millions Total Convertible Senior Notes due in 2022 [Member] Convertible Senior Notes due in 2024 [Member] May 20 Balance at Dec. 31, 2016 $ 5,538 3 Balance, shares at Dec. 31, 2016 Adjustments for prior periods from adopting Accounting Standards Update | Accounting 4 Standards Update No. 2016-09 [Member] Conversion feature of Convertible Senior Notes $ 146 6 Purchases of convertible note hedges (204) 7 Sales of warrants 53 8 Exercises of conversion feature of convertible senior notes 230 9 Exercise of conversion feature of convertible senior notes, Shares Issuance of common stock for equity incentive awards and acquisitions, net of transaction 269 10 costs Issuance of common stock upon acquisition and equity incentive awards, net of 11 transaction costs, Shares 12 Issuance of common stock public offering 13 Issuance of common stock public offering, shares 14 Stock-based compensation 485 15 Contributions from noncontrolling interests 597 16 Distributions to noncontrolling interests (164) 17 Other in ... Historical Statement of Cash FI Competitor Income Statements Competitor Balance Sheet Con ... + : + 100% Type here to search O 9 e W X 3:48 PM 6/1/2020AutoSave O Off) excelproject1acct370 (1) - Excel Search keturahlove18@icloud.com K X File Home Insert Page Layout Formulas Data Review View Help Share Comments Ruler Formula Bar Split DO View Side by Side Hide IDI Synchronous Scrolling G Normal Page Break Page Custom Preview Layout Views Gridlines Headings Zoom 100% Zoom to New Arrange Freeze Switch Macros Selection Window All Panes Unhide In Reset Window Position |Windows Workbook Views Show Zoom Window Macros B18 X V A B C D E F G H I J K N - Consolidated Statements of Comprehensive Loss - USD ($) $ in Millions 12 Months Ended Dec. 31, 2019 Dec. 31, 2018 Dec. 31, 2017 Statement Of Income And Comprehensive Income [Abstract] 4 Net loss $ (775) $ (1,063) $ (2,241) 5 Other comprehensive loss: 6 Reclassification adjustment for net gains on derivatives into net loss (6) Foreign currency translation adjustment (28) (42) 63 8 Comprehensive loss (803) (1,105) (2,184) Less: Comprehensive income (loss) attributable to noncontrolling interests and 87 (87) (279) 9 redeemable noncontrolling interests in subsidiaries 10 Comprehensive loss attributable to common stockholders $ (890) $ (1,018) $ (1,905) 11 12 13 14 15 16 17 18 19 ... Historical Statement of Cash FI Competitor Income Statements Competitor Balance Sheet Col ... + : Edit + 100% Type here to search O 9 e W X 3:49 PM 6/1/2020AutoSave O Off) excelproject1acct370 (1) - Excel Search keturahlove18@icloud.com K X File Home Insert Page Layout Formulas Data Review View Help Share Comments Ruler Formula Bar Split DO View Side by Side Hide IDSynchronous Scrolling G Normal Page Break Page Custom Zoom 100% Zoom to New Arrange Freeze Switch Preview Layout Views Gridlines Headings Macros Selection Window All Panes Unhide In Reset Window Position | Windows Workbook Views Show Zoom Window Macros X V A B D E F G H I J K N - Consolidated Statements of Redeemable Noncontrolling Interest and Stockholders' 12 Months Ended Equity (Parenthetical) - USD ($) $ in Millions Dec. 31, 2019 Dec. 31, 2017 W Statement Of Stockholders Equity [Abstract] 4 Common stock issued, per share $ 243 $ 262 Common stock public offering issuance costs $ 15 $3 9 10 11 12 13 14 15 16 17 18 19 20 21 ... Competitor Income Statements Competitor Balance Sheet Competition Statement of Cash F F ... + : 0 100% Ready + 3:50 PM Type here to search O 9 e O W X 6/1/2020AutoSave O Off) excelproject1acct370 (1) - Excel Search keturahlove18@icloud.com K X File Home Insert Page Layout Formulas Data Review View Help Share Comments Ruler Formula Bar Split DO View Side by Side Hide G Normal Page Break Page Custom Preview Layout Views Gridlines Headings Zoom 100% Zoom to New Arrange Freeze IDSynchronous Scrolling Switch Macros Selection Window All Panes Unhide I En Reset Window Position | Windows Workbook Views Show Zoom Window Macros AS X V Inventory and purchase commitments write-downs A B C D E F G H I J K N - Consolidated Statements of Cash Flows - USD ($) $ in Millions 12 Months Ended Dec. 31, 2019 Dec. 31, 2018 Dec. 31, 2017 Cash Flows from Operating Activities 4 Net loss $ (775) $ (1,063) $ (2,241) Adjustments to reconcile net loss to net cash provided by (used in) operating activities: 6 Depreciation, amortization and impairment 2,154 1,901 1,636 7 Stock-based compensation 898 749 467 8 Amortization of debt discounts and issuance costs 188 159 91 9 Inventory and purchase commitments write-downs 193 85 132 10 Loss on disposals of fixed assets 146 162 106 11 Foreign currency transaction (gains) loss (48) (2) 52 12 Loss related to SolarCity acquisition 58 13 Non-cash interest and other operating activities 186 49 135 14 Operating cash flow related to repayment of discounted convertible notes (188) 15 Changes in operating assets and liabilities, net of effect of business combinations: 16 Accounts receivable (367) (497) 25) 17 Inventory (429) (1,023) 179) 18 Operating lease vehicles (764) (215) (1,523) 19 Prepaid expenses and other current assets 288 ) (82) (72) 20 Other non currant areate 115 ... Competitor Balance Sheet Competition Statement of Cash F Historical and Compeitior ratio O' ... + Ready + 100% Type here to search O 9 e W X 3:50 PM 6/1/2020
