ANSWER EA9-A1 IN EXCEL SPREADSHEET

EA9-A1 Perform a Financial Statement Analysis for Legal

Services Co. PG

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.

Access the proper nile.

• Using Project Grader: Download EA9-A1-FSA-eStart
• Not usino Proiect Grader: Oven EA9-A1-FSA

Horizontal Analysis Tab

• Range KO:K17: Create formulas to calculate the dollar change where appropriate. (PG-1a)
• Range M8:M17: Create formulas to calculate the percent change where appropriate. (PG-1b)

Create an embedded Clustered Column chart. (PG-2)

• Based on: Percent change in each asset account
• Position: To the right of and top-aligned with the balance sheet
• Chart Title: Enter an appropriate name. centered above the column chart.
• Resize the chart so all elements are logically displayed
• Change the chart type to Clustered Bar and remove gridlines

Vertical Analysis Tab

Columns K-_: Create formulas to complete a vertical analvsis on the income statement for 2028 and 2027. (PG-3)

Create a Clustered Column Chart. (PG-4)

• Placement: On its own tab named Expense Chart - 2028
• Based on: 2028 expense account percentages and including the Iotal Expenses and Net Income rows
• Chart Title: Enter an appropriate name, centered above the column chart.

Change the chart type to 3-D Pie.

Filter out: Total Expenses (PG-5a)

• Layout and style: Layout 2, Style 3 (PG-5b)
• Legend: Display it to the right of the 3-D Pie chart. (PG-5c)

• Data labels: Displav Percentages, two decimals: Best Fit (PG-5d
• Y-axis: Rotate to 40 degrees (PG-5e)
• Chart title background: Solid, standard yellow fill (PG-5f
• Explode the largest pie slice.

Excel for Accounting

Display Column Sparklines to the right of each revenue and expense account (hint: column N; based on percentages; exclude totals) in the income statement and adjust the border around the income statement to include them. (PG-6a) Use the header Sparkline Trends and

make its formatting consistent with that of the adjacent headers. (PG-6b)

Calculate Ratios and Insert Cell Comments

You will create a new worksheet on which to calculate the ratios, some of the data for which is

shown here:

4.51

DeD RATIO

0 072

0 061

Profit Margin

0.342

0.294

3.90

0.052

0.206

Worksheet tab name: Financial Ratios (PG-7)

Location

Column A

Range B1:F1

what to Enter/Do

Width: 14 (PG-8a)

in order. no cOmmas. 2028. 2027.2026.2025.2024 176-807

Range A2:A4

Range B2:CA

In order, no commas: Quick Ratio, Debt Ratio, Profit Margin (PG-8C)

Formulas to calculate ratios for 2028 and 2027 (PG-8d)

Enter the provided ratios (PG-8e)

Range G2:G4

Line Sparklines that display all five years of ratio data (PG-8f)

Formatting:

• Range B1:F1: Bottom Border, Bold formatting, Center (horizontal) alignment (PG-9a)
• Range A2:A4: Bold formatting (PG-9) All quick and debt ratios: Comma Style (PG-9b) All debt ratios: Show three decimal places (PG-9b)
• All proht margins: Percent Stvle. one decimal (PG-9b)

After inputting and formatting the data:

Insert comments in the cells for the largest debt ratio and the smallest profit margin to indicate that further investigation is required. Respond to the comment for the smallest pront margin, indicating client attrition led to the reduced earnings for the year. Make sure the response is distinct from the original comment. (PG-10a)

Embed a Line chart below the ratio data that displays the profit margin trend over the five-year period. Make sure to include an appropriate chart title! (PG-10b)

Save and close the rile.

• Using Project Grader: Save the file as EA9-Al-FSA-eSubmission and attach it to the assignment you submit for grading.
• Ivot using Project Grader: submit the mle per your instructor s guidelines