Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Common-Size Financial Statement Analysis Using Excel Completing this activity will help you learn to: 1. create Excel formulas using cell references. 2. create absolute cell

image text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribed Common-Size Financial Statement Analysis Using Excel Completing this activity will help you learn to: 1. create Excel formulas using cell references. 2. create absolute cell references to perform calculations efficiently and correctly. 3. apply conditional formatting to highlight cells based on criteria that you specify. Case scenario: You are working at a small business that has been mostly stable. You want to analyze the financial statements of the past five years to examine the trends over time and compare the percentages of income statement items relative to overall sales. You also want to highlight cells according to whether the calculated ratio increases or decreases more than a specified amount. Required: You will build a spreadsheet that performs common-size analysis using five years of a company's income statements. Specifically, you will create two sheets within the workbook, one for the horizontal analysis and one for the vertical analysis. Further, you will use conditional formatting to visually differentiate between cells that reflect increases and cells that reflect decreases over a specified percentage. HINT: STEP-BY-STEP WALKTHROUGH 1. Horizontal analysis: Create a new sheet within your Excel workbook that you will use to perform the horizontal analysis, comparing each year's income statement item to its corresponding item in year 1. Copy column A from the Financial Statement Data sheet to Column A in your new sheet. Also copy the information in row 1 to row 1 in your new sheet so that the basic structure of the table will be the same in the new sheet but it will have no data. a. Now create formulas in your new sheet using the data in the Financial Statement Data sheet that will reflect the correct percentages as described in the previous question. Instead of creating every formula one by one, how could you create just one formula in cell B2 that can be copied and pasted to all cells? Note that there are negative values in the Financial Statement Data sheet. Choose the correct formula. I. ='Financial Statement Data'!\$B2/'Financial Statement Data'!B2 II. ='Financial Statement Data'!B2/'Financial Statement Data'!\$B2 III. ='Financial Statement Data'!B2/'Financial Statement Data'!B \$2 IV. ='Financial Statement Data'!B2/'Financial Statement Data'!\$B \$2 2. Vertical analysis: Create another new sheet to perform the vertical analyss, comparing each year's item to that year's net sales: a. Instead of creating every formula one by one, how can you create just one formula in cell B2 that can be copied and pasted to all cells? Note that there are negative values in the Financial Statement Data sheet. Choose the correct formula. I. =ABS('Financial Statement Data'!\$B2/'Financial Statement Data'!B2) II. =ABS('Financial Statement Data'!B2/'Financial Statement Data'!\$B2) III. =ABS('Financial Statement Data'!B2/'Financial Statement Data'!B \$2) IV. =ABS('Financial Statement Data'!B2/'Financial Statement Data'!\$B2) 3. Conditional formatting: You want to show the increases and decreases with visually different colors. Apply the following conditional formatting to your horizontal analysis sheet: For the that have an increase of more than 105 percent, use a yellow fill with dark yellow text. For cells that have a decrease of less than 95 percent, use a light red fill with dark red text. Choose table with the correct conditional formatting. I. III. II. \begin{tabular}{l|l|l|l|l|l|l|} \hline \multicolumn{1}{|c|}{ A } & \multicolumn{1}{|c|}{ B } & \multicolumn{1}{c|}{ C } & D & E & F \\ \hline 1 & Line Items & Year 1 & Year 2 & Year 3 & Year 4 & Year 5 \\ \hline 2 & Net Sales & & & & \\ \hline 3 & Less: Cost of Goods Sold & & & & \\ \hline 4 & Gross Margin & & & & \\ \hline 5 & Less: Operating Expenses & & & & \\ \hline 6 & Less: Taxes & & & & \\ \hline 7 & Net Income & & & & \\ \hline \end{tabular} IV. \begin{tabular}{l|l|l|l|l|l|l} \multicolumn{1}{c|}{ A } & \multicolumn{1}{|c|}{ B } & \multicolumn{1}{c|}{ C } & D & E & F \\ \hline 1 & Line Items & Year 1 & Year 2 & Year 3 & Year 4 & Year 5 \\ \hline 2 & Net Sales & & & & \\ \hline 3 & Less: Cost of Goods Sold & & & & \\ \hline 4 & Gross Margin & & & & \\ \hline 5 & Less: Operating Expenses & & & & \\ \hline 6 & Less: Taxes & & & & \\ \hline 7 & Net Income & & & & \\ \end{tabular} he correct sheet is Explain the use of the common size financial statements. If you were a manager, which area would require further analysis? he input in the box below will not be graded, but may be reviewed and considered by your instructor

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Learning From Collaborative Audit

Authors: Higher Education Quality Council

1st Edition

1858242312, 978-1858242316

More Books

Students also viewed these Accounting questions