For the Years 2016 to 2019 30-Jun-19 30-Jun-18 30-Jun-17 30-Jun-16 Revenue 11667.8 10938.5 10226.4 9442 Cost of Revenue, Total 6628.7 6220.7 5842 5380.2 Gross Profit 5039.1 4717.8 4384.4 4061.8 Selling/General/Admin. Expenses, Total 2637 2496.9 2370.3 2200.4 Depreciation/Amortization 211.6 206.9 193.9 Unusual Expense (Income) -29.1 -1.4 0 42.7 Operating Income 2219.6 2015.4 1815.1 1624.8 Gain (Loss) on Sale of Assets 13.9 0 0 2.9 Income Before Tax 2233.5 2015.4 1815.1 1627.7 Income After Tax 1493.4 1376.5 1242.6 1122.2 Task What I'm looking for Copy the Income Statement and paste it into a new worksheet. Use of Excel formatting, cell Reformat the data so that it is more readable, add any titles required, referencing anywhere and insert formulas in place of the numbers where possible (ignoring there'd be a calculation any Income tax formulas---leave any tax amounts as numbers). within the statement. 199 Copy the Balance Sheet and paste it into an area below the income Statement. Reformat the data so that it is more readable, add any titles required, and insert formulas in place of the numbers where possible (leave any Retained Earnings (or Deficit) amounts in the Equity section as numbers). Use of Excel formatting, cell referencing anywhere there'd be a calculation within the statement. On a new worksheet, using the years of information you have, create a common-size income statement. Link all the formulas to the income statement worksheet, and ensure formatting and titles are appropriate. Using "time-effective" cell referencing to have created all the formulas. Appropriate titles/formatting #REF! Balance Sheet For the Years Ending June 30 2016 to 2019 30-Jun-19 30-Jun-18 30-Jun-17 30-Jun-16 Cash & Equivalents 3191.1 1639.3 1584 1699.1 Short Term Investments 23.5 26.6 2032.2 28 Total Receivables, Net 1742.8 1546.9 1498.8 1595.3 Other Current Assets, Total 34543 25596.4 22378 22892.1 Total Current Assets 39500.4 28809.2 27493 262 14.5 Property/Plant/Equipment, Total - Gross 1840.8 1776.4 1718.7 2040.7 Accumulated Depreciation, Total -1155.8 -1103.7 -1051.6 -1312.1 Long-Term Note Receivable 27.1 32.2 155.4 138.7 Goodwill, Net 1682 1793.5 1883.5 3039.2 Intangibles, Net 534.2 503.2 491 643.2 Long Term Investments 7.8 28.9 54.1 314 Other Long Term Assets, Total 1233.5 1270.8 1315.7 1189.9 Total Assets 43670 33110.5 32059.8 32268.1 Am I going to know what ratio is calculated? Am ! going to know for what years? Was it calculated correctly? On the original income statement and balance sheet worksheet, choose any three financial ratios from our textbook's chapter 3. Using cell referencing, calculate these three ratios for the three most recent years of information you have. Place these ratio's names and calculations in a space to the right of the Income Statement, with applicable titles, formatting and column headers. Again to the right of these ratio calculations, for each ratio, create a nested IF statement or a combination of IF/AND/OR statements whose result is: "Investigate further" if the ratio gets worse across all 3 years, "Keep an eye out" if the ratio for the most recent year is worse than either of the two previous years, "Carry on" if the ratio get better across all 3 years. I will manually change a ratio value...your "result" should change accordingly... Accounts Payable Accrued Expenses Notes Payable/Short Term Debt Other Current liabilities, Total Total Current Liabilities Total Debt Deferred Income Tax Other Liabilities, Total Total Liabilities Total Equity Total Liabilities & Shareholders' Equity 152.3 1863.5 0 33831.6 35847.4 2007.7 251.1 1082.2 39188.4 4481.6 43670 194.5 1786.5 0 25132.7 27113.7 9.2 172.1 1007 28302 4808.5 33110.5 152.1 1793.9 2173 20048.6 24167.6 2184.5 215.9 994.6 25389.6 6670.2 32059.8 156.5 1809.6 0 22766.7 24732.8 14.7 234.4 1096.3 26078.2 6189.9 32268.1 Total Common Shares Outstanding 455.7 466.4 480.2 482.6 For the Years 2016 to 2019 30-Jun-19 30-Jun-18 30-Jun-17 30-Jun-16 Revenue 11667.8 10938.5 10226.4 9442 Cost of Revenue, Total 6628.7 6220.7 5842 5380.2 Gross Profit 5039.1 4717.8 4384.4 4061.8 Selling/General/Admin. Expenses, Total 2637 2496.9 2370.3 2200.4 Depreciation/Amortization 211.6 206.9 193.9 Unusual Expense (Income) -29.1 -1.4 0 42.7 Operating Income 2219.6 2015.4 1815.1 1624.8 Gain (Loss) on Sale of Assets 13.9 0 0 2.9 Income Before Tax 2233.5 2015.4 1815.1 1627.7 Income After Tax 1493.4 1376.5 1242.6 1122.2 Task What I'm looking for Copy the Income Statement and paste it into a new worksheet. Use of Excel formatting, cell Reformat the data so that it is more readable, add any titles required, referencing anywhere and insert formulas in place of the numbers where possible (ignoring there'd be a calculation any Income tax formulas---leave any tax amounts as numbers). within the statement. 199 Copy the Balance Sheet and paste it into an area below the income Statement. Reformat the data so that it is more readable, add any titles required, and insert formulas in place of the numbers where possible (leave any Retained Earnings (or Deficit) amounts in the Equity section as numbers). Use of Excel formatting, cell referencing anywhere there'd be a calculation within the statement. On a new worksheet, using the years of information you have, create a common-size income statement. Link all the formulas to the income statement worksheet, and ensure formatting and titles are appropriate. Using "time-effective" cell referencing to have created all the formulas. Appropriate titles/formatting #REF! Balance Sheet For the Years Ending June 30 2016 to 2019 30-Jun-19 30-Jun-18 30-Jun-17 30-Jun-16 Cash & Equivalents 3191.1 1639.3 1584 1699.1 Short Term Investments 23.5 26.6 2032.2 28 Total Receivables, Net 1742.8 1546.9 1498.8 1595.3 Other Current Assets, Total 34543 25596.4 22378 22892.1 Total Current Assets 39500.4 28809.2 27493 262 14.5 Property/Plant/Equipment, Total - Gross 1840.8 1776.4 1718.7 2040.7 Accumulated Depreciation, Total -1155.8 -1103.7 -1051.6 -1312.1 Long-Term Note Receivable 27.1 32.2 155.4 138.7 Goodwill, Net 1682 1793.5 1883.5 3039.2 Intangibles, Net 534.2 503.2 491 643.2 Long Term Investments 7.8 28.9 54.1 314 Other Long Term Assets, Total 1233.5 1270.8 1315.7 1189.9 Total Assets 43670 33110.5 32059.8 32268.1 Am I going to know what ratio is calculated? Am ! going to know for what years? Was it calculated correctly? On the original income statement and balance sheet worksheet, choose any three financial ratios from our textbook's chapter 3. Using cell referencing, calculate these three ratios for the three most recent years of information you have. Place these ratio's names and calculations in a space to the right of the Income Statement, with applicable titles, formatting and column headers. Again to the right of these ratio calculations, for each ratio, create a nested IF statement or a combination of IF/AND/OR statements whose result is: "Investigate further" if the ratio gets worse across all 3 years, "Keep an eye out" if the ratio for the most recent year is worse than either of the two previous years, "Carry on" if the ratio get better across all 3 years. I will manually change a ratio value...your "result" should change accordingly... Accounts Payable Accrued Expenses Notes Payable/Short Term Debt Other Current liabilities, Total Total Current Liabilities Total Debt Deferred Income Tax Other Liabilities, Total Total Liabilities Total Equity Total Liabilities & Shareholders' Equity 152.3 1863.5 0 33831.6 35847.4 2007.7 251.1 1082.2 39188.4 4481.6 43670 194.5 1786.5 0 25132.7 27113.7 9.2 172.1 1007 28302 4808.5 33110.5 152.1 1793.9 2173 20048.6 24167.6 2184.5 215.9 994.6 25389.6 6670.2 32059.8 156.5 1809.6 0 22766.7 24732.8 14.7 234.4 1096.3 26078.2 6189.9 32268.1 Total Common Shares Outstanding 455.7 466.4 480.2 482.6