EA9-A1

Perform a Financial Statement Analysis for Legal Services Co.

In this exercise, you will perform a financial statement analysis for Legal Services Co. 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

Quick Ratio Debt Ratio

Profit Margin

2016 3.72

0.072 .342

2015 4.51

0.061 .294

2014 3.90

0.052 .206

1. Open EA9-A1-FSA from your Chapter 09 folder and save it as: EA9-A1-FSA-[YourName]

2. On the Horizontal Analysis tab, create formulas to calculate the dollar change in column K for all lines in the balance sheet.

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 asset account.

5. Position the chart to the right of and top-aligned with the balance sheet. 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 income statement for 2018 and 2017.

8. Create a Column Chart on its own tab that is based on the 2018 expense account percentages. Include the Total Expenses and the Net Income rows in the chart.

9. Ensure that an appropriate chart title is centered and positioned on the column chart itself.

10. Rename the new worksheet tab to: Expense Chart - 2018

Create Different Chart Types 11. Change the chart type to 3-D Pie. 12. Filter the chart to not display Total Expenses.

13. Display the legend at the bottom of the chart, display Best Fit data labels, and rotate the Y-axis of the chart to 40 degrees.

14. Add the Yellow fill to the background of the chart title. 15. Explode the largest pie slice. 16. Change the chart type associated with the balance sheet to Clustered Bar and remove gridlines.

Apply Chart Layouts, Chart Styles, and Sparklines 17. Apply Layout 2 and Style 3 to the 3-D Pie chart. 18. Adjust the data labels to display as Percentages with 2 decimal places.

19. Display Column Sparklines to the right of each revenue and expense account in the income statement.

20. Adjust the borders around the income statement to include the sparklines.

21. Insert a Sparkline Trends header in the appropriate location, making sure its formatting is consistent with that of the adjacent cell entries. Calculate Ratios and Insert Cell Comments

22. Create a new worksheet named Financial Ratios. 23. Enter headers for the five-year period from 2014-2018 in the range B1:F1, starting with the most recent year.

24. Enter the Quick Ratio, Debt Ratio, and Profit Margin headers in the range A2:A4, in that order.

25. Format all headers with bold formatting, add a bottom border and center alignment to all year headers, and expand the width of column A to 14.

26. Create formulas to calculate the ratios for 2018 and 2017. 27. Enter the ratios for 2016, 2015, and 2014 based on the table in the instructions.

28. Apply Comma Style to all quick and debt ratios, add one decimal place to the debt ratios, and apply Percent Style with one decimal place to all profit margins.

29. Insert Line Sparklines to the right of each set of ratios, displaying all five years of ratio data.

30. Insert comments in the cells for the largest debt ratio and the smallest profit margin to indicate that they require further investigation.

31. Display all comments in the worksheet.

32. Enter a response in the comment for the smallest profit margin, indicating client attri-tion led to the reduced earnings for the year. Make sure the response is distinct from the original comment.

33. If necessary, resize the profit margin cell comment so it is entirely visible.

34. Embed a Line chart below the ratio data that displays the profit margin trend over the five-year period.

35. Modify the chart to include a chart title. 36. Save and close the file

Legal Services Co.Common-Size Income StatementFor the Years Ended December 31, 2017 and December 31, 20182018 Dollar Amount2017 Dollar Amount2018 Percentage2017 PercentageRevenues:Service Revenue$351,800$394,200Expenses:Wages Expense114,500107,600Insurance Expense31,30030,300Advertising Expense102,80098,500Maintenance Expense32,30018,600Computer Expense4,36012,400Supplies Expense3,2503,600Total Expenses288,510271,000Net Income63,290123,200