Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Please help me figure out these ratios, I made mistake in the first Excel assignment, and cannot figure out the second one. I attached the
Please help me figure out these ratios, I made mistake in the first Excel assignment, and cannot figure out the second one. I attached the my excel assignment along with the question pdf. For my assignment I am using financial information from yahoo finance for Apple Inc. The assignment was due yesterday so I need to hurry with it. Thank you
Apple Inc 2016 Balance Sheet Period Ending 26-Sep-15 27-Sep-14 28-Sep-13 Assets Current Assets Cash And Cash Equivalents Short Term Investments Net Receivables Inventory Other Current Assets Total Current Assets Long Term Investments Property Plant and Equipment 21,120,000 13,844,000 14,259,000 20,481,000 35,889,000 2,349,000 11,233,000 31,537,000 2,111,000 26,287,000 24,094,000 1,764,000 9,539,000 9,806,000 6,882,000 89,378,000 68,531,000 73,286,000 164,065,000 130,162,000 106,215,000 22,471,000 20,624,000 16,597,000 Goodwill 5,116,000 4,616,000 1,577,000 Intangible Assets 3,893,000 4,142,000 4,179,000 Accumulated Amortization Other Assets Deferred Long Term Asset Charges Total Assets - - - 5,556,000 3,764,000 5,146,000 - - - 290,479,000 231,839,000 207,000,000 Liabilities Current Liabilities Accounts Payable Short/Current Long Term Debt Other Current Liabilities 60,671,000 48,649,000 36,223,000 10,999,000 6,308,000 - 8,940,000 8,491,000 7,435,000 Total Current Liabilities 80,610,000 63,448,000 43,658,000 Long Term Debt 53,463,000 28,987,000 16,960,000 Other Liabilities 33,427,000 24,826,000 20,208,000 3,624,000 3,031,000 2,625,000 Minority Interest - - - Negative Goodwill - - - 171,124,000 120,292,000 83,451,000 Deferred Long Term Liability Charges Total Liabilities Stockholders' Equity Misc Stocks Options Warrants - - - Redeemable Preferred Stock - - - Apple Inc 2016 Balance Sheet Preferred Stock Common Stock Retained Earnings 27,416,000 92,284,000 - - 23,313,000 19,764,000 87,152,000 104,256,000 Treasury Stock - - - Capital Surplus - - - Other Stockholder Equity -345,000 1,082,000 -471,000 Total Stockholder Equity 119,355,000 111,547,000 123,549,000 Net Tangible Assets 110,346,000 102,789,000 117,793,000 2016 Apple Inc Income Statement 26-Sep-15 27-Sep-14 28-Sep-13 Period Ending Total Revenue 233,715,000 182,795,000 170,910,000 Cost of Revenue 140,089,000 112,258,000 106,606,000 Gross Profit 93,626,000 70,537,000 64,304,000 6,041,000 4,475,000 11,993,000 - 10,830,000 - - - - 71,230,000 52,503,000 48,999,000 Operating Expenses Research Development 8,067,000 Selling General and Administrative 14,329,000 Non Recurring Others Total Operating Expenses Operating Income or Loss Income from Continuing Operations Total Other Income/Expens es Net 1,285,000 980,000 Earnings Before Interest And Taxes 72,515,000 53,483,000 Interest Expense Income Before Tax 72,515,000 53,483,000 Income Tax Expense 19,121,000 13,973,000 Minority Interest Net Income From Continuing Ops 53,394,000 Net Income Applicable To Common Shares 50,155,000 50,155,000 13,118,000 - 39,510,000 37,037,000 - - - - - - - - - 53,394,000 39,510,000 37,037,000 - - - 53,394,000 39,510,000 37,037,000 Non-recurring Events Discontinued Operations Extraordinary Items Effect Of Accounting Changes Other Items Net Income Preferred Stock And Other Adjustments 1,156,000 2016 Apple Inc Statement of Cash Flows Period Ending Net Income 26-Sep-15 27-Sep-14 28-Sep-13 53,394,000 39,510,000 37,037,000 Operating Activities, Cash Flows Provided By or Used In Depreciation 11,257,000 7,946,000 6,757,000 Adjustments To Net Income 4,968,000 5,210,000 3,394,000 Changes In Accounts Receivables -3,124,000 -6,452,000 -1,949,000 Changes In Liabilities 15,188,000 13,408,000 Changes In Inventories -238,000 Changes In Other Operating Activities -179,000 Total Cash Flow From Operating Activities -76,000 167,000 8,320,000 -973,000 1,080,000 81,266,000 59,713,000 53,666,000 Investing Activities, Cash Flows Provided By or Used In Capital Expenditures -11,247,000 -9,571,000 Investments -44,417,000 -9,017,000 -24,042,000 Other Cash flows from Investing Activities Total Cash Flows From Investing Activities -610,000 -3,991,000 -8,165,000 -1,567,000 -56,274,000 -22,579,000 -33,774,000 Financing Activities, Cash Flows Provided By or Used In Dividends Paid -11,561,000 -11,126,000 -10,564,000 Sale Purchase of Stock -34,710,000 -44,270,000 -22,330,000 Net Borrowings 29,305,000 18,266,000 16,896,000 Other Cash Flows from Financing Activities Total Cash Flows From Financing Activities Effect Of Exchange Rate Changes Change In Cash and Cash Equivalents -1,499,000 -1,158,000 -1,082,000 -17,716,000 -37,549,000 -16,379,000 - 7,276,000 - - -415,000 3,513,000 Stock Prices Historical Prices Date 29-Apr-16 28-Apr-16 27-Apr-16 26-Apr-16 25-Apr-16 22-Apr-16 21-Apr-16 Rate of Retur Open 93.99 97.61 96 103.91 105 105.01 106.93 High 94.72 97.88 98.71 105.3 105.65 106.48 106.93 Low 92.51 94.25 95.68 103.91 104.51 104.62 105.52 Close Volume Adj Close* 93.74 68,334,400 93.74 94.83 81,990,700 94.83 97.82 113,538,400 97.82 104.35 40,287,500 104.35 105.08 27,951,000 105.08 105.68 33,477,100 105.68 105.97 31,356,400 105.97 First Price Last price First$- Last$ / First $ *100 $93.74 $105.97 ($12.23) -0.13046725 -13.04672 Period Ending Cash And Cash Equivalents Short Term Investment s Net Receivable s Inventory Other Current Assets ### 21,120,000 20,481,000 35,889,000 2,349,000 9,539,000 Total Current Assets Long Term Investments Property Plant and Equipment Goodwill Intangible Assets Accumulated Amortization Other Assets Deferred Long Term Asset Charges 89,378,000 164,065,000 22,471,000 5,116,000 3,893,000 5,556,000 - Total Assets 290,479,000 Accounts Payable 60,671,000 Short/Curre nt Long Term Debt 10,999,000 Other Current Liabilities Total Current Liabilities Long Term Debt Other Liabilities Deferred Long Term Liability Charges Minority Interest Negative Goodwill 8,940,000 80,610,000 53,463,000 33,427,000 3,624,000 - Total Liabilities Misc Stocks Options Warrants Redeemable Preferred Stock Preferred Stock Common Stock Retained Earnings Treasury Stock Capital Surplus Other Stockholder Equity 171,124,000 27,416,000 92,284,000 -345,000 Total Stockholder Equity 119,355,000 Net Tangible Assets 110,346,000 Current Ratio: 1.11 Debt/equity ratio: 1.43 Free Cash Flow: 171,124,000 Earnings Per Shar 2.43 All numbers in thousands Period Ending ### Assets Current Assets Cash And Cash Equivalents Short Term Investment s Net Receivable s Inventory Other Current Assets Total Current Assets 13,844,000 11,233,000 31,537,000 2,111,000 9,806,000 68,531,000 Long Term Investments Property Plant and Equipment Goodwill Intangible Assets Accumulated Amortization Other Assets Deferred Long Term Asset Charges 130,162,000 Total Assets 231,839,000 Liabilities Current Liabilities Accounts Payable Short/Curre nt Long Term Debt Other Current Liabilities Total Current Liabilities Long Term Debt 20,624,000 4,616,000 4,142,000 3,764,000 - 48,649,000 6,308,000 8,491,000 63,448,000 28,987,000 Other Liabilities Deferred Long Term Liability Charges Minority Interest Negative Goodwill Total Liabilities 24,826,000 3,031,000 120,292,000 Stockholders' Equity Misc Stocks Options Warrants Redeemable Preferred Stock Preferred Stock Common Stock 23,313,000 Retained Earnings Treasury Stock Capital Surplus Other Stockholder Equity 87,152,000 1,082,000 Total Stockholder Equity 111,547,000 Net Tangible Assets 102,789,000 - All numbers in thousands View: Annual Data | Quarterly Data Period Ending ### Assets Current Assets Cash And Cash Equivalents Short Term Investments Net Receivables Inventory Other Current Assets Total Current Assets 14,259,000 26,287,000 24,094,000 1,764,000 6,882,000 73,286,000 Long Term Investments Property Plant and Equipment Goodwill Intangible Assets Accumulated Amortization Other Assets Deferred Long Term Asset Charges 106,215,000 Total Assets 207,000,000 Liabilities Current Liabilities Accounts Payable Short/Curre nt Long Term Debt Other Current Liabilities Total Current Liabilities Long Term Debt Other Liabilities 16,597,000 1,577,000 4,179,000 5,146,000 - 36,223,000 - 7,435,000 43,658,000 16,960,000 20,208,000 Deferred Long Term Liability Charges Minority Interest Negative Goodwill Total Liabilities 2,625,000 83,451,000 Stockholders' Equity Misc Stocks Options Warrants Redeemable Preferred Stock Preferred Stock Common Stock 19,764,000 Retained Earnings 104,256,000 Treasury Stock Capital Surplus Other Stockholder Equity -471,000 Total Stockholder Equity 123,549,000 Net Tangible Assets 117,793,000 Income Statement View: Annual Data | Quarterly Data Period Ending Total Revenue Cost of Revenue 26-Sep-15 233,715,00 0 140,089,00 0 93,626,000 Gross Profit Operating Expenses Research Developme nt 8,067,000 Selling General and Administrati 14,329,000 ve Non Recurring Others Total Operating Expenses - Operating Income or 71,230,000 Loss Income from Continuing Operations Total Other Income/Exp enses Net 1,285,000 Earnings Before Interest And 72,515,000 Taxes Interest Expense Income 72,515,000 Before Tax Income Tax 19,121,000 Expense Minority Interest - Net Income From Continuing 53,394,000 Ops Non-recurring Events Discontinue d Operations Extraordinar y Items Effect Of Accounting Changes Other Items - 53,394,000 Net Income Preferred Stock And Other Adjustments - Net Income Applicable 53,394,000 To Common Shares Income Statement View: Annual Data | Quarterly Data Period Ending Total Revenue Cost of Revenue 27-Sep-14 182,795,00 0 112,258,00 0 70,537,000 Gross Profit Operating Expenses Research Developme nt 6,041,000 Selling General and Administrati 11,993,000 ve Non Recurring Others Total Operating Expenses - Operating Income or 52,503,000 Loss Income from Continuing Operations Total Other Income/Exp enses Net 980,000 Earnings Before Interest And 53,483,000 Taxes Interest Expense Income 53,483,000 Before Tax Income Tax 13,973,000 Expense Minority Interest - Net Income From Continuing 39,510,000 Ops Non-recurring Events Discontinue d Operations Extraordina ry Items Effect Of Accounting Changes Other Items - 39,510,000 Net Income Preferred Stock And Other Adjustments - Net Income Applicable 39,510,000 To Common Shares Income Statement View: Annual Data | Quarterly Data Period Ending Total Revenue Cost of Revenue 28-Sep-13 170,910,00 0 106,606,00 0 64,304,000 Gross Profit Operating Expenses Research Developme nt 4,475,000 Selling General and Administrati 10,830,000 ve Non Recurring Others Total Operating Expenses - Operating Income or 48,999,000 Loss Income from Continuing Operations Total Other Income/Exp enses Net 1,156,000 Earnings Before Interest And 50,155,000 Taxes Interest Expense Income 50,155,000 Before Tax Income Tax 13,118,000 Expense Minority Interest - Net Income From Continuing 37,037,000 Ops Non-recurring Events Discontinue d Operations Extraordina ry Items Effect Of Accounting Changes Other Items - 37,037,000 Net Income Preferred Stock And Other Adjustments - Net Income Applicable 37,037,000 To Common Shares Cash Flow View: Annual Data | Quarterly Data Period Ending ### Net Income 53,394,000 Operating Activities, Cash Flows Provided By or Used In Depreciation 11,257,000 Adjustments To Net Income 4,968,000 Changes In Accounts Receivables -3,124,000 Changes In Liabilities 15,188,000 Changes In Inventories -238,000 Changes In Other Operating Activities -179,000 Total Cash Flow From Operating Activities 81,266,000 Investing Activities, Cash Flows Provided By or Used In Capital Expenditures -11,247,000 Investments -44,417,000 Other Cash flows from Investing Activities -610,000 Total Cash Flows From Investing Activities -56,274,000 Financing Activities, Cash Flows Provided By or Used In Dividends Paid -11,561,000 Sale Purchase of Stock -34,710,000 Net Borrowings 29,305,000 Other Cash Flows from Financing Activities -1,499,000 Total Cash Flows From Financing Activities Effect Of Exchange Rate Changes Change In Cash and Cash Equivalents -17,716,000 - 7,276,000 Cash Flow View: Annual Data | Quarterly Data Period Ending ### Net Income 39,510,000 Operating Activities, Cash Flows Provided By or Used In Depreciation 7,946,000 Adjustments To Net Income 5,210,000 Changes In Accounts Receivables -6,452,000 Changes In Liabilities 13,408,000 Changes In Inventories -76,000 Changes In Other Operating Activities 167,000 Total Cash Flow From Operating Activities 59,713,000 Investing Activities, Cash Flows Provided By or Used In Capital Expenditures -9,571,000 Investments -9,017,000 Other Cash flows from Investing Activities -3,991,000 Total Cash Flows From Investing Activities -22,579,000 Financing Activities, Cash Flows Provided By or Used In Dividends Paid -11,126,000 Sale Purchase of Stock -44,270,000 Net Borrowings 18,266,000 Other Cash Flows from Financing Activities -1,158,000 Total Cash Flows From Financing Activities Effect Of Exchange Rate Changes Change In Cash and Cash Equivalents -37,549,000 - -415,000 Cash Flow View: Annual Data | Quarterly Data Period Ending ### Net Income 37,037,000 Operating Activities, Cash Flows Provided By or Used In Depreciation 6,757,000 Adjustments To Net Income 3,394,000 Changes In Accounts Receivables -1,949,000 Changes In Liabilities 8,320,000 Changes In Inventories -973,000 Changes In Other Operating Activities 1,080,000 Total Cash Flow From Operating Activities 53,666,000 Investing Activities, Cash Flows Provided By or Used In Capital Expenditures -8,165,000 Investments -24,042,000 Other Cash flows from Investing Activities -1,567,000 Total Cash Flows From Investing Activities -33,774,000 Financing Activities, Cash Flows Provided By or Used In Dividends Paid -10,564,000 Sale Purchase of Stock -22,330,000 Net Borrowings 16,896,000 Other Cash Flows from Financing Activities -1,082,000 Total Cash Flows From Financing Activities Effect Of Exchange Rate Changes Change In Cash and Cash Equivalents -16,379,000 - 3,513,000 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%Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started