Financial Analysis Project

1. Case Common-Size Financial Statement Analysis.ALGO
2. Case Job-Order Sheets Cost Analysis.ALGO
3. Case Break-Even Analysis.ALGO

Progress:1/3 items

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-06b542.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

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.

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.

='Financial Statement Data'!$B2/'Financial Statement Data'!B2

='Financial Statement Data'!B2/'Financial Statement Data'!$B2

='Financial Statement Data'!B2/'Financial Statement Data'!B$2

='Financial Statement Data'!B2/'Financial Statement Data'!$B$2

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%

Vertical analysis: Create another new sheet to perform the vertical analysis, comparing each year's item to that year's net sales:

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.

=ABS('Financial Statement Data'!$B2/'Financial Statement Data'!B2)

=ABS('Financial Statement Data'!B2/'Financial Statement Data'!$B2)

=ABS('Financial Statement Data'!B2/'Financial Statement Data'!B$2)

=ABS('Financial Statement Data'!B2/'Financial Statement Data'!$B$2)

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 58%
Net Income	fill in the blank 59%	fill in the blank 60%	fill in the blank 61%	fill in the blank 62%	fill in the blank 63%

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 cells 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 the table with the correct conditional formatting.

I.		II.
III.		IV.

IIIIIIIV

Explain the use of the common size financial statements. If you were a manager, which area would require further analysis?

The input in the box below will not be graded, but may be reviewed and considered by your instructor.

Check My Work3 more Check My Work uses remaining.

• Next

Financial Analysis Project

1. Case Common-Size Financial Statement Analysis.ALGO
2. Case Job-Order Sheets Cost Analysis.ALGO
3. Case Break-Even Analysis.ALGO

Progress:1/3 items

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-06b542.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

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.

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.

='Financial Statement Data'!$B2/'Financial Statement Data'!B2

='Financial Statement Data'!B2/'Financial Statement Data'!$B2

='Financial Statement Data'!B2/'Financial Statement Data'!B$2

='Financial Statement Data'!B2/'Financial Statement Data'!$B$2

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%

Vertical analysis: Create another new sheet to perform the vertical analysis, comparing each year's item to that year's net sales:

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.

=ABS('Financial Statement Data'!$B2/'Financial Statement Data'!B2)

=ABS('Financial Statement Data'!B2/'Financial Statement Data'!$B2)

=ABS('Financial Statement Data'!B2/'Financial Statement Data'!B$2)

=ABS('Financial Statement Data'!B2/'Financial Statement Data'!$B$2)

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 58%
Net Income	fill in the blank 59%	fill in the blank 60%	fill in the blank 61%	fill in the blank 62%	fill in the blank 63%

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 cells 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 the table with the correct conditional formatting.

I.		II.
III.		IV.

IIIIIIIV

Explain the use of the common size financial statements. If you were a manager, which area would require further analysis?

The input in the box below will not be graded, but may be reviewed and considered by your instructor.

Check My Work3 more Check My Work uses remaining.

• NextLine Items Year 1 Year 2 Year 3 Year 4 Year 5 Net Sales $200,000.00 $180,000.00 $200,000.00 $220,000.00 $250,000.00 Less: Cost of Goods Sold ($100,000.00) ($90,500.00) ($100,500.00) ($100,000.00) ($120,000.00) Gross Margin $100,000.00 $89,500.00 $99,500.00 $120,000.00 $130,000.00 Less: Operating Expenses ($50,000.00) ($55,000.00) ($60,000.00) ($65,000.00) ($70,000.00) Less: Taxes ($16,000.00) ($14,400.00) ($16,000.00) ($17,000.00) ($20,000.00) Net Income $34,000.00 $20,100.00 $23,500.00 $38,000.00 $40,000.00