Hello

Please see attached assignments

2 separate assignments but they are one assignment as whole.

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. 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 described in the prompt. Citations should be formatted according to APA style. Instructor Feedback: This activity uses an integrated rubric in Blackboard. Students can view instructor feedback in the Grade Center. For more information, review these instructions. Critical Elements Financial Reports Stock Prices Professionalism References Proficient (100%) Includes annual balance sheet, annual income statement, and annual statement of cash flows for the company selected Accurately calculates the stock's rate of return over the seven-day period Formats data with a neat and professional appearance Includes properly formatted APA-style citations and links with minor errors or no errors Not Evident (0%) Does not include annual balance sheet, annual income statement, and annual statement of cash flows for the company selected Does not accurately calculate the stock's rate of return over the seven-day period Does not format data with a neat and professional appearance Does not include properly formatted APA-style citations or includes citations and links with noticeable errors Earned Total Value 25 45 15 15 100% 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 Using the Write Submission area of Blackboard for this part of the assignment, respond to the following: o 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. If you are not familiar with the Write Submission feature, see the screen shot below. 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 the Write Submission area of Blackboard. Citations should be formatted according to APA style. Instructor Feedback: This activity uses an integrated rubric in Blackboard. Students can view instructor feedback in the Grade Center. For more information, review these instructions. 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 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 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 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 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 Describes how and why each of the ratios has changed over the three-year period in a clear and logical manner 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 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 APAstyle citations and links; there are major errors related to organization, grammar, and style 10 Earned Total 100% Period Ending 12/31/2015 Current Assets Cash And Cash Equivalents Short Term Investments Net Receivables 10,923,000 1,988,000 13,324,000 Inventory - Other Current Assets - Total Current Assets - Long Term Investments 18,792,000 Property Plant and Equipment Goodwill Intangible Assets Accumulated Amortization Other Assets Deferred Long Term Asset Charges Total Assets 4,861,000 44,453,000 8,391,000 7,791,000 860,000 ### Current Liabilities Accounts Payable Short/Current Long Term Debt 26,324,000 6,634,000 Other Current Liabilities - Total Current Liabilities - Long Term Debt 32,094,000 Other Liabilities 11,775,000 Deferred Long Term Liability Charges Minority Interest Negative Goodwill Total Liabilities 5,729,000 -105,000 75,817,000 Stockholders' Equity Misc. Stocks Options Warrants 1,736,000 Redeemable Preferred Stock - Preferred Stock - Common Stock 10,000 Retained Earnings 37,125,000 Treasury Stock - Capital Surplus 29,000 Other Stockholder Equity -3,334,000 Total Stockholder Equity 33,830,000 Net Tangible Assets ### Period Ending 12/31/2014 Current Assets Cash And Cash Equivalents 7,495,000 Short Term Investments 1,741,000 Net Receivables 9,750,000 Inventory - Other Current Assets - Total Current Assets - Long Term Investments 18,827,000 Property Plant and Equipment Goodwill Intangible Assets Accumulated Amortization Other Assets Deferred Long Term Asset Charges Total Assets 4,418,000 32,940,000 3,669,000 6,986,000 556,000 ### Current Liabilities Accounts Payable Short/Current Long Term Debt 21,287,000 1,399,000 Other Current Liabilities - Total Current Liabilities - Long Term Debt 17,406,000 Other Liabilities 9,810,000 Deferred Long Term Liability Charges 4,037,000 Minority Interest Negative Goodwill Total Liabilities -105,000 ### Stockholders' Equity Misc. Stocks Options Warrants 1,388,000 Redeemable Preferred Stock - Preferred Stock - Common Stock 10,000 Retained Earnings 33,836,000 Treasury Stock - Capital Surplus 29,000 Other Stockholder Equity -1,392,000 Total Stockholder Equity 32,454,000 Net Tangible Assets ### Period Ending 12/31/2013 Current Assets Cash And Cash Equivalents 7,276,000 Short Term Investments 1,937,000 Net Receivables 7,050,000 Inventory - Other Current Assets - Total Current Assets - Long Term Investments 19,605,000 Property Plant and Equipment Goodwill Intangible Assets Accumulated Amortization Other Assets Deferred Long Term Asset Charges Total Assets 4,010,000 31,604,000 3,844,000 6,126,000 430,000 ### Current Liabilities Accounts Payable Short/Current Long Term Debt 19,033,000 1,969,000 Other Current Liabilities - Total Current Liabilities - Long Term Debt 16,860,000 Other Liabilities 9,269,000 Deferred Long Term Liability Charges 3,396,000 Minority Interest Negative Goodwill Total Liabilities -105,000 ### Stockholders' Equity Misc. Stocks Options Warrants 1,175,000 Redeemable Preferred Stock - Preferred Stock - Common Stock 10,000 Retained Earnings 33,047,000 Treasury Stock - Capital Surplus 29,000 Other Stockholder Equity Total Stockholder Equity -908,000 32,149,000 Net Tangible Assets ### Income Statement All numbers in thousands 12/31/201 5 Revenue Total Revenue ### Cost of Revenue ### Gross Profit ### Operating Expenses Research Development - Selling General and Administrative - Non Recurring - Others 42,211,000 Total Operating Expenses - Operating Income or Loss ### Income from Continuing Operations Total Other Income/Expenses Net - Earnings Before Interest and Taxes 11,021,000 Interest Expense 790,000 Income Before Tax 10,231,000 Income Tax Expense 4,363,000 Minority Interest -105,000 Net Income From Continuing Ops 5,868,000 Non-recurring Events Discontinued Operations - Extraordinary Items - Effect Of Accounting Changes - Other Items - Net Income Net Income Preferred Stock And Other Adjustments Net Income Applicable To Common Shares 5,813,000 - 5,813,000 Income Statement All numbers in thousands 12/31/201 4 Revenue Total Revenue ### Cost of Revenue 93,633,000 Gross Profit ### Operating Expenses Research Development - Selling General and Administrative - Non Recurring - Others 26,567,000 Total Operating Expenses - Operating Income or Loss ### Income from Continuing Operations Total Other Income/Expenses Net - Earnings Before Interest and Taxes 10,274,000 Interest Expense 618,000 Income Before Tax 9,656,000 Income Tax Expense 4,037,000 Minority Interest -105,000 Net Income From Continuing Ops 5,619,000 Non-recurring Events Discontinued Operations - Extraordinary Items - Effect Of Accounting Changes - Other Items - Net Income Net Income Preferred Stock And Other Adjustments Net Income Applicable To Common Shares 5,619,000 - 5,619,000 Income Statement All numbers in thousands 12/31/201 3 Revenue Total Revenue ### Cost of Revenue 89,659,000 Gross Profit ### Operating Expenses Research Development - Selling General and Administrative - Non Recurring - Others 23,207,000 Total Operating Expenses - Operating Income or Loss 9,623,000 Income from Continuing Operations Total Other Income/Expenses Net - Earnings Before Interest and Taxes 9,623,000 Interest Expense 708,000 Income Before Tax 8,915,000 Income Tax Expense 3,242,000 Minority Interest -105,000 Net Income From Continuing Ops 5,673,000 Non-recurring Events Discontinued Operations - Extraordinary Items - Effect Of Accounting Changes - Other Items - Net Income Net Income Preferred Stock And Other Adjustments Net Income Applicable To Common Shares 5,625,000 - 5,625,000 Cash Flow All numbers in thousands Period Ending 12/31/2015 Net Income 5,813,000 Depreciation 1,693,000 Operating Activities, Cash Flows Provided By or Used In Adjustments To Net Income Changes In Accounts Receivables Changes In Liabilities 98,000 -591,000 4,102,000 Changes In Inventories - Changes In Other Operating Activities -1,430,000 Total Cash Flow From Operating Activities 9,740,000 Capital Expenditures -1,556,000 Investing Activities, Cash Flows Provided By or Used In Investments Other Cash flows from Investing Activities Total Cash Flows From Investing Activities -531,000 -16,308,000 ### Financing Activities, Cash Flows Provided By or Used In Dividends Paid Sale Purchase of Stock Net Borrowings Other Cash Flows from Financing Activities -1,786,000 -916,000 14,607,000 334,000 Total Cash Flows From Financing Activities 12,239,000 Effect Of Exchange Rate Changes Change In Cash and Cash Equivalents -156,000 3,428,000 Cash Flow All numbers in thousands Period Ending 12/31/201 4 Net Income 5,619,000 Depreciation 1,478,000 Operating Activities, Cash Flows Provided By or Used In Adjustments To Net Income Changes In Accounts Receivables Changes In Liabilities -51,000 -911,000 2,506,000 Changes In Inventories Changes In Other Operating Activities -590,000 Total Cash Flow From Operating Activities 8,051,000 Capital Expenditures -1,525,000 Investing Activities, Cash Flows Provided By or Used In Investments Other Cash flows from Investing Activities Total Cash Flows From Investing Activities 799,000 -1,808,000 -2,534,000 Financing Activities, Cash Flows Provided By or Used In Dividends Paid -1,362,000 Sale Purchase of Stock -3,546,000 Net Borrowings Other Cash Flows from Financing Activities 391,000 -776,000 Total Cash Flows From Financing Activities -5,293,000 Effect Of Exchange Rate Changes Change In Cash and Cash Equivalents -5,000 219,000 Cash Flow All numbers in thousands Period Ending Net Income 12/31/2015 12/31/201 12/31/201 4 3 5,813,000 5,619,000 5,625,000 Operating Activities, Cash Flows Provided By or Used In Depreciation 1,693,000 1,478,000 1,375,000 98,000 -51,000 249,000 -591,000 -911,000 -317,000 4,102,000 2,506,000 849,000 - - - Changes In Other Operating Activities -1,430,000 -590,000 -838,000 Total Cash Flow From Operating Activities 9,740,000 8,051,000 6,991,000 Adjustments To Net Income Changes In Accounts Receivables Changes In Liabilities Changes In Inventories Investing Activities, Cash Flows Provided By or Used In Capital Expenditures Investments Other Cash flows from Investing Activities Total Cash Flows From Investing Activities -1,556,000 -1,525,000 -1,307,000 -531,000 799,000 -1,611,000 -16,308,000 -1,808,000 -171,000 ### -2,534,000 -3,089,000 Financing Activities, Cash Flows Provided By or Used In Dividends Paid Sale Purchase of Stock Net Borrowings Other Cash Flows from Financing Activities -1,786,000 -1,362,000 -1,056,000 -916,000 -3,546,000 -4,046,000 14,607,000 391,000 152,000 334,000 -776,000 4,000 Total Cash Flows From Financing Activities 12,239,000 -5,293,000 -4,946,000 Effect Of Exchange Rate Changes Change In Cash and Cash Equivalents -156,000 3,428,000 -5,000 -86,000 219,000 -1,130,000 Date Sep 22, 2016 Sep 21, 2016 Sep 20, 2016 Sep 19, 2016 Sep 16, 2016 Sep 15, 2016 Sep 14, 2016 Stock Prices 140.83 139.91 137.47 137.68 138.47 135.61 133.69 Rate of return over 7 days = 5.34%