Accounting and finance spreadsheet AC1103-W2021 30-Jun-16 9442 5380.2 4061.8 2200.4 Assignment 2 Covering chapters (1), 2, and 3 Due: Submitted to Assignment 2 page in Brightspace no later than October 10th, 2021, at 6:00 pm. Using the provided excel file: The Assignment 2 Company Income Statement For the Years 2016 to 2019 30-Jun-19 30-Jun-18 11667.8 10938.5 6628.7 6220.7 5039.1 4717.8 2637 2496.9 211.6 206.9 -29.1 -1.4 2219.6 2015.4 13.9 0 2233.5 2015.4 1493.4 1376.5 30-Jun-17 10226.4 5842 4384.4 2370.3 199 0 1815.1 0 1815.1 1242.6 Revenue Cost of Revenue, Total Gross Profit Selling/General/Admin. Expenses, Total Depreciation/Amortization Unusual Expense (Income) Operating Income Gain (Loss) on Sale of Assets Income Before Tax Income After Tax 193.9 Mark: 5 42.7 1624.8 2.9 1627.7 1122.2 Task Copy the Income Statement and paste it into a new worksheet. Reformat the data so that it is more readable, add any titles required, and insert formulas in place of the numbers where possible (ignoring any Income tax formulas---leave any tax amounts as numbers). What I'm looking for Use of Excel formatting, cell referencing anywhere there'd be a calculation within the statement. 5 Copy the Balance Sheet and paste it into an area below the Use of Excel formatting, Income Statement. Reformat the data so that it is more cell referencing anywhere readable, add any titles required, and insert formulas in place there'd be a calculation of the numbers where possible (leave any Retained Earnings (or within the statement. Deficit) amounts in the Equity section as numbers). The Assignment 2 Company Balance Sheet For the Years Ending June 30 2016 to 2019 30-Jun-19 30-Jun-18 Cash & Equivalents 3191.1 1639.3 Short Term Investments 23.5 26.6 Total Receivables, Net 1742.8 1546.9 Other Current Assets, Total 34543 25596.4 Total Current Assets 39500.4 28809.2 Property/Plant/Equipment, Total - Gross 1840.8 1776.4 Accumulated Depreciation, Total -1155.8 -1103.7 Long-Term Note Receivable 27.1 32.2 Goodwill, Net 1682 1793.5 Intangibles, Net 534.2 503.2 Long Term Investments 7.8 28.9 Other Long Term Assets, Total 1233.5 1270.8 Total Assets 43670 33110.5 30-Jun-17 1584 2032.2 1498.8 22378 27493 1718.7 -1051.6 155.4 1883.5 491 54.1 1315.7 32059.8 30-Jun-16 1699.1 28 1595.3 22892.1 26214.5 2040.7 -1312.1 138.7 3039.2 643.2 314 1189.9 32268.1 On a new worksheet, using the years of information you have, Using "time-effective" cell create a common-size income statement. Link all the formulas referencing to have to the income statement worksheet, and ensure formatting and created all the formulas. titles are appropriate. Appropriate titles/formatting On the original income statement and balance sheet Am I going to know what worksheet, choose any three financial ratios from our ratio is calculated? Am I textbook's chapter 3. Using cell referencing, calculate these going to know for what three ratios for the three most recent years of information you years? Was it calculated have. Place these ratio's names and calculations in a space to correctly? the right of the Income Statement, with applicable titles, formatting and column headers. Again to the right of these ratio calculations, for each ratio, I will manually change a create a nested IF statement or a combination of IF/AND/OR ratio value...your "result" statements whose result is: should change "Investigate further" if the ratio gets worse across all 3 years, accordingly... "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. 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 455.7 Total Common Shares Outstanding 466.4 480.2 482.6 Accounting and finance spreadsheet AC1103-W2021 30-Jun-16 9442 5380.2 4061.8 2200.4 Assignment 2 Covering chapters (1), 2, and 3 Due: Submitted to Assignment 2 page in Brightspace no later than October 10th, 2021, at 6:00 pm. Using the provided excel file: The Assignment 2 Company Income Statement For the Years 2016 to 2019 30-Jun-19 30-Jun-18 11667.8 10938.5 6628.7 6220.7 5039.1 4717.8 2637 2496.9 211.6 206.9 -29.1 -1.4 2219.6 2015.4 13.9 0 2233.5 2015.4 1493.4 1376.5 30-Jun-17 10226.4 5842 4384.4 2370.3 199 0 1815.1 0 1815.1 1242.6 Revenue Cost of Revenue, Total Gross Profit Selling/General/Admin. Expenses, Total Depreciation/Amortization Unusual Expense (Income) Operating Income Gain (Loss) on Sale of Assets Income Before Tax Income After Tax 193.9 Mark: 5 42.7 1624.8 2.9 1627.7 1122.2 Task Copy the Income Statement and paste it into a new worksheet. Reformat the data so that it is more readable, add any titles required, and insert formulas in place of the numbers where possible (ignoring any Income tax formulas---leave any tax amounts as numbers). What I'm looking for Use of Excel formatting, cell referencing anywhere there'd be a calculation within the statement. 5 Copy the Balance Sheet and paste it into an area below the Use of Excel formatting, Income Statement. Reformat the data so that it is more cell referencing anywhere readable, add any titles required, and insert formulas in place there'd be a calculation of the numbers where possible (leave any Retained Earnings (or within the statement. Deficit) amounts in the Equity section as numbers). The Assignment 2 Company Balance Sheet For the Years Ending June 30 2016 to 2019 30-Jun-19 30-Jun-18 Cash & Equivalents 3191.1 1639.3 Short Term Investments 23.5 26.6 Total Receivables, Net 1742.8 1546.9 Other Current Assets, Total 34543 25596.4 Total Current Assets 39500.4 28809.2 Property/Plant/Equipment, Total - Gross 1840.8 1776.4 Accumulated Depreciation, Total -1155.8 -1103.7 Long-Term Note Receivable 27.1 32.2 Goodwill, Net 1682 1793.5 Intangibles, Net 534.2 503.2 Long Term Investments 7.8 28.9 Other Long Term Assets, Total 1233.5 1270.8 Total Assets 43670 33110.5 30-Jun-17 1584 2032.2 1498.8 22378 27493 1718.7 -1051.6 155.4 1883.5 491 54.1 1315.7 32059.8 30-Jun-16 1699.1 28 1595.3 22892.1 26214.5 2040.7 -1312.1 138.7 3039.2 643.2 314 1189.9 32268.1 On a new worksheet, using the years of information you have, Using "time-effective" cell create a common-size income statement. Link all the formulas referencing to have to the income statement worksheet, and ensure formatting and created all the formulas. titles are appropriate. Appropriate titles/formatting On the original income statement and balance sheet Am I going to know what worksheet, choose any three financial ratios from our ratio is calculated? Am I textbook's chapter 3. Using cell referencing, calculate these going to know for what three ratios for the three most recent years of information you years? Was it calculated have. Place these ratio's names and calculations in a space to correctly? the right of the Income Statement, with applicable titles, formatting and column headers. Again to the right of these ratio calculations, for each ratio, I will manually change a create a nested IF statement or a combination of IF/AND/OR ratio value...your "result" statements whose result is: should change "Investigate further" if the ratio gets worse across all 3 years, accordingly... "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. 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 455.7 Total Common Shares Outstanding 466.4 480.2 482.6