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 Statement
		For the Years Ended December 31, 2017 and December 31, 2018
						2018 Dollar Amount		2017 Dollar Amount		2018 Percentage	2017 Percentage
		Revenues:
		Service Revenue				$ 351,800		$ 394,200
		Expenses:
		Wages Expense				114,500		107,600
		Insurance Expense				31,300		30,300
		Advertising Expense				102,800		98,500
		Maintenance Expense				32,300		18,600
		Computer Expense				4,360		12,400
		Supplies Expense				3,250		3,600
		Total Expenses				288,510		271,000
		Net Income				63,290		123,200

Answer rating: 100% (QA)

I can certainly help you perform the financial statement analysis and create the requested charts bu... View the full answer