principals of finance

User Generated

nc2424

Business Finance

Description

This is the first of two Excel assignments you will complete in this course. Before you get started, be sure to watch the

Mergent Onlinevideo(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.

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.

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

describedin the prompt. Citations should be formatted according to APA

Unformatted Attachment Preview

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
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

...


Anonymous
Goes above and beyond expectations!

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags