Question
Common-Size Financial Statement Analysis Using Excel Case scenario: You are working at a small business that has been mostly stable. You want to analyze the
Common-Size Financial Statement Analysis Using Excel
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.
Line Items | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 |
Net Sales | $125,000.00 | $115,000.00 | $105,000.00 | $135,000.00 | $145,000.00 |
Less: Cost of Goods Sold | ($60,000.00) | ($65,000.00) | ($60,000.00) | ($70,000.00) | ($65,000.00) |
Gross Margin | $65,000.00 | $50,000.00 | $45,000.00 | $65,000.00 | $80,000.00 |
Less: Operating Expenses | ($35,000.00) | ($27,500.00) | ($25,000.00) | ($40,000.00) | ($47,500.00) |
Less: Taxes | ($11,000.00) | ($8,200.00) | ($7,500.00) | ($10,500.00) | ($12,500.00) |
Net Income | $19,000.00 | $14,300.00 | $12,500.00 | $14,500.00 | $20,000.00 |
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. 1. 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
![image text in transcribed](https://s3.amazonaws.com/si.experts.images/answers/2024/07/669bfe0e8efe3_150669bfe0e14768.jpg)
2. 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)
![image text in transcribed](https://s3.amazonaws.com/si.experts.images/answers/2024/07/669bfe0f365c9_150669bfe0ebe58c.jpg)
new sheet but it will have no data. 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 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 )
Step by Step Solution
There are 3 Steps involved in it
Step: 1
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-text-image.webp)
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Step: 3
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started