Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Question Content Area Common - Size Financial Statement Analysis Using Excel Completing this activity will help you learn to: 1 . create Excel formulas using

Question Content Area
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:
Download spreadsheet CommonSizeCaseData-540d81.xlsx
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
II
b. Copy and paste that one formula to all the relevant cells in your new sheet. Round your answers to two decimal places.
Line Items Year 1 Year 2 Year 3 Year 4 Year 5
Net Sales fill in the blank 3
% fill in the blank 4
% fill in the blank 5
% fill in the blank 6
% fill in the blank 7
%
Less: Cost of Goods Sold fill in the blank 8
% fill in the blank 9
% fill in the blank 10
% fill in the blank 11
% fill in the blank 12
%
Gross Margin fill in the blank 13
% fill in the blank 14
% fill in the blank 15
% fill in the blank 16
% fill in the blank 17
%
Less: Operating Expenses fill in the blank 18
% fill in the blank 19
% fill in the blank 20
% fill in the blank 21
% fill in the blank 22
%
Less: Taxes fill in the blank 23
% fill in the blank 24
% fill in the blank 25
% fill in the blank 26
% fill in the blank 27
%
Net Income fill in the blank 28
% fill in the blank 29
% fill in the blank 30
% fill in the blank 31
% fill in the blank 32
%
2. Vertical analysis: Create another new sheet to perform the vertical analysis, 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'!$B$2)
III
b. Again, copy and paste that one formula to all the relevant cells in your new sheet. Round your answers to two decimal places. Enter all percentages as positive numbers.
Line Items Year 1 Year 2 Year 3 Year 4 Year 5
Net Sales fill in the blank 34
% fill in the blank 35
% fill in the blank 36
% fill in the blank 37
% fill in the blank 38
%
Less: Cost of Goods Sold fill in the blank 39
% fill in the blank 40
% fill in the blank 41
% fill in the blank 42
% fill in the blank 43
%
Gross Margin fill in the blank 44
% fill in the blank 45
% fill in the blank 46
% fill in the blank 47
% fill in the blank 48
%
Less: Operating Expenses fill in the blank 49
% fill in the blank 50
% fill in the blank 51
% fill in the blank 52
% fill in the blank 53
%
Less: Taxes fill in the blank 54
% fill in the blank 55
% fill in the blank 56
% fill in the blank 57
% fill in the blank
image text in transcribed

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

ACC 120 Wake Tech Financial Accounting W Connect Plus Access

Authors: J. David Spiceland

1st Edition

1308168926, 978-1308168920

More Books

Students also viewed these Accounting questions

Question

Darby dapples income stayement

Answered: 1 week ago