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.

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	fill in the blank	fill in the blank	fill in the blank	fill in the blank
Less: Cost of Goods Sold	fill in the blank	fill in the blank	fill in the blank	fill in the blank	fill in the blank
Gross Margin	fill in the blank	fill in the blank	fill in the blank	fill in the blank	fill in the blank
Less: Operating Expenses	fill in the blank	fill in the blank	fill in the blank	fill in the blank	fill in the blank
Less: Taxes	fill in the blank	fill in the blank	fill in the blank	fill in the blank	fill in the blank
Net Income	fill in the blank	fill in the blank	fill in the blank	fill in the blank	fill in the blank

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	fill in the blank	fill in the blank	fill in the blank	fill in the blank
Less: Cost of Goods Sold	fill in the blank	fill in the blank	fill in the blank	fill in the blank	fill in the blank
Gross Margin	fill in the blank	fill in the blank	fill in the blank	fill in the blank	fill in the blank
Less: Operating Expenses	fill in the blank	fill in the blank	fill in the blank	fill in the blank	fill in the blank
Less: Taxes	fill in the blank	fill in the blank	fill in the blank	fill in the blank	fill in the blank
Net Income	fill in the blank	fill in the blank	fill in the blank	fill in the blank	fill in the blank

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.

Financial statement Data:

\begin{tabular}{|l|c|c|c|c|c|} \hline Line Items & Year 1 & Year 2 & Year 3 & \multicolumn{1}{|c|}{ Year 4 } & Year 5 \\ \hline Net Sales & $200,000.00 & $180,000.00 & $200,000.00 & $220,000.00 & $250,000.00 \\ \hline Less: Cost of Goods Sold & ($100,000.00) & ($90,500.00) & ($100,500.00) & ($100,000.00) & ($120,000.00) \\ \hline Gross Margin & $100,000.00 & $89,500.00 & $99,500.00 & $120,000.00 & $130,000.00 \\ \hline Less: Operating Expenses & ($50,000.00) & ($55,000.00) & ($60,000.00) & ($65,000.00) & ($70,000.00) \\ \hline Less: Taxes & ($16,000.00) & ($14,400.00) & ($16,000.00) & ($17,000.00) & ($20,000.00) \\ \hline Net Income & $34,000.00 & $20,100.00 & $23,500.00 & $38,000.00 & $40,000.00 \\ \hline \end{tabular} \begin{tabular}{|l|c|c|c|c|c|} \hline Line Items & Year 1 & Year 2 & Year 3 & \multicolumn{1}{|c|}{ Year 4 } & Year 5 \\ \hline Net Sales & $200,000.00 & $180,000.00 & $200,000.00 & $220,000.00 & $250,000.00 \\ \hline Less: Cost of Goods Sold & ($100,000.00) & ($90,500.00) & ($100,500.00) & ($100,000.00) & ($120,000.00) \\ \hline Gross Margin & $100,000.00 & $89,500.00 & $99,500.00 & $120,000.00 & $130,000.00 \\ \hline Less: Operating Expenses & ($50,000.00) & ($55,000.00) & ($60,000.00) & ($65,000.00) & ($70,000.00) \\ \hline Less: Taxes & ($16,000.00) & ($14,400.00) & ($16,000.00) & ($17,000.00) & ($20,000.00) \\ \hline Net Income & $34,000.00 & $20,100.00 & $23,500.00 & $38,000.00 & $40,000.00 \\ \hline \end{tabular}