Question

COMPLETE THE FOLLOWING USING THE ATTACHED DOCUMENTS

In this exercise, you will perform a financial statement analysis for Water Feature Designers Inc. You will perform horizontal/vertical analyses and create charts to highlight key information from these analyses. You will also calculate financial ratios and insert cell comments. Use this information to complete the ratio analysis. Ratio Current Ratio Debt-to-Equity Ratio Profit Margin 2016 7.62 0.17 .186 2015 3.45 0.28 292 2014 8.21 0.18 255 1. Open EA9-A2-FSA from your Chapter 09 folder and save it as: EA9-A2-FSA-[YourName] 2. On the Horizontal Analysis tab, create formulas to calculate the dollar change in column K for all lines in the income statement. 3. In column M. create formulas to calculate the percent change for all lines. 4. Create an embedded Clustered Column chart based on the percent changes in each expense account. 5. Position the chart to the right of and top-aligned with the income statement 6. Add an appropriate chart title and, if necessary, resize the chart so all elements are logi cally displayed. 7. Switch to the Vertical Analysis tab and create formulas to complete a vertical analysis on the balance sheet for 2018 and 2017. 8. Create a Column Chart on its own tab that is based on the 2018 asset account percent- ages. Include the Total Assets row in the chart. 9. Ensure that an appropriate chart title is displayed above the column chart and that it's positioned on the column chart itself. 10. Rename the new worksheet tab to: Asset Chart - 2018 Create Different Chart Types 11. Change the chart type to 3-D Pie. 12. Filter the chart to not display Total Assets. 13. Display the legend at the bottom of the chart, display Outside End data labels, and rotate the Y-axis of the chart to 30 degrees. 14. Add the Blue fill to the background of the chart title. 15. Explode the smallest pie slice. 16. Change the chart type associated with the income statement to Clustered Bar and remove gridlines.

Answer 1

Water Feature Designers Inc. Comparative Income Statement For the Years ended December, 31 Dollar Change Percent Change 2018 2017 -38700 -13.52% 247600 1520 249120 286300 1600 287900 -38780 -13.47% -27200 0 Revenues: Service Revenue Interest Revenue Total Revenue Expenses: Supplies Expense Rent Expense Salaries Expense Utilities Expense Insurance Expense Auto Expense Computer Expense Repair & Maintenance Expense Total Expenses Net Income 74600 60000 45300 3800 3250 2900 1140 800 191790 57330 101800 60000 51200 7500 3670 2080 1800 410 228460 59440 -5900 -3700 -420 820 -660 390 -36670 -2110 -26.72% 0.00% -11.52% -49.33% -11.44% 39.42% -36.67% 95.12% -16.05% -3.55% Common size Balancesheet As at December 31, Amount 2018 2017 Percent 2018 2017 Assets: Cash Notes Receivable Supplies Land Total Assets Liabilities: Accounts Payable Owner's Equity Arnold McBride, Capital Total Liabilities & Owner's Equity 428000 18700 4670 51400 502770 443200 23200 400 51400 518200 85.13% 3.72% 0.93% 10.22% 100.00% 85.53% 4.48% 0.08% 9.92% 100.00% 31000 57300 6.17% 11.06% 471770 502770 460900 518200 93.83% 100.00% 88.94% 100.00%

1 Water Feature Designers Inc. Dollar Change Percent Change =B8-C8 =B9-C9 =B10-C10 =D8/08 =D9/09 =D10/010 3 Comparative Income Statement 4 For the Years ended December , 31 5 2018 2017 7 Revenues: 8 Service Revenue 247600 286300 9 Interest Revenue 1520 1600 10 Total Revenue =SUM(B8:39) =SUM(C8:09) 11 Expenses: 12 Supplies Expense 74600 101800 13 Rent Expense 60000 60000 14 Salaries Expense 45300 51200 15 Utilities Expense 3800 7500 16 Insurance Expense 3250 3670 17 Auto Expense 2900 2080 18 Computer Expense 1140 1800 19 Repair & Maintenance Expense 800 410 20 Total Expenses =SUM(B12:B19) ESUM(C12:C19) 21 Net Income =B10-B20 =C10-C20 22 23 Common size Balancesheet 24 As at December 31, Amount 26 2018 2017 27 Assets: 28 Cash 428000 443200 29 Notes Receivable 18700 23200 30 Supplies 4670 400 31 Land 51400 51400 32 Total Assets =SUM(B28:B31) SUM(C28:C31) 33 Liabilities: 34 Accounts Payable 31000 57300 35 Owner's Equity 36 Arnold McBride, Capital 471770 460900 37 Total Liabilities & Owner's Equity -SUM(B34:B36) -SUM(C34:C36) =B12-C12 =B13-C13 =B14-C14 =B15-C15 =B16-C16 =B17-C17 =B18-C18 =B19-C19 =B20-C20 =B21-C21 =D12/C12 =D13/C13 =D14/C14 =D15/015 =D16/016 =D17/C17 =D18/C18 =D19/019 =D 20/020 =D21/C21 25 Percent 2017 2018 =B28/$B$32 =B29/$B$32 =B30/$B$32 =B31/$B$32 =B32/$B$32 =C28/$C$32 =C29/$C$32 =C30/$C$32 =C31/$C$32 =C32/$C$32 =B34/$B$37 =C34/$C$37 =B36/$B$37 =B37/$B$37 =C36/$C$37 =C37/$C$37