Horizontal Analysis

Completing this activity will help you learn to:

$$ Evaluate a company's performance using horizontal analysis.

$$ Extract information from a data set using Excel formulas, functions, and$/$or pivot tables.

Scenario:

Company A has three divisions: $1,2,$ and $3.$

Each division prepares its own financial reports using the same financial statement templates.

The Data worksheet contains a list of $2023$ and $2022$ account balances that relate to these divisions.

Each row provides information about one account in one of the two years and in one of three divisions.

Other information includes account type, financial statement where the account is reported, and account balance.

Open spreadsheet

Steps to create a pivot table:

S$1.$ Select a cell in the data on the Data worksheet.

S$2.$ Select Insert $/$ PivotTable. The data range will automatically be inserted. A $$Create PivotTable$$ window should appear.

S$3.$ In the window, under "Choose where you want the PivotTable report to be placed", select $$New Worksheet$.$ A new worksheet should appear with the PivotTable.

S$4.$ A PivotTable Field List should pop up$.$ Drag fields into rows, columns, filters, and values areas as needed. $($If this gray work box does not appear, right click in the pivot table and select "Show Field List".$)$

Questions:

Use the information on the Data worksheet and pivot tables, formulas, and$/$or functions to answer the following questions.

Q$1$a$.$ Listed by division, what is the lowest percentage increase in total current assets from $2022$ to $2023?($Round the percentage to two decimal places, such as $8\mathrm{.}79\%.)$

fill in the blank $1$ of $1\%$

Q$1$b$.$ Which division has the lowest percent increase?

Division $1$

Division $2$

Division $3$

Q$2$a$.$ For all three divisions combined, what is the amount of the greatest percentage change $($increase or decrease$)$ from $2022$ to $2023$ among the following three non$-$current accounts: $16$ Long$-$term investments, $17$ Property, plant, and equipment $($net$),$ or $22$ Long$-$term note payable? $($Round the percentage to two decimal places, such as $8\mathrm{.}79\%.$ If the percentage change is an increase, enter a positive number; if the percentage change is a decrease, enter a negative number.$)$

fill in the blank $1$ of $1\%$

Q$2$b$.$ Which account has the greatest percent change $($increase or decrease$)-16$ Long$-$term investments, $17$ Property, plant, and equipment $($net$),$ or $22$ Long$-$term note payable?

$16$ Long$-$term investments

$17$ Property, plant, and equipment $($net$)$

$22$ Long$-$term note payable

Q$3$a$.$ Listed by division, what is the highest percentage increase in total operating expenses from $2022$ to $2023?($Round the percentage to two decimal places, such as $8\mathrm{.}79\%.)$

fill in the blank $1$ of $1\%$

Q$3$b$.$ Which division has the highest percent increase?

Division $1$

Division $2$

Division $3$

Q$4$a$.$ For all three divisions combined, what is the percent change in gross profit from $2022$ to $2023?($Round the percentage to two decimal places, such as $8\mathrm{.}79\%.)$

fill in the blank $1$ of $1\%$

Q$4$b$.$ For all three divisions combined, what is the percent change in net income from $2022$ to $2023?($Round the percentage to two decimal places, such as $8\mathrm{.}79\%.)$

fill in the blank $1$ of $1\%$

Q$5.$ Which statement explains how the divisions could have the higher percentage in gross profit but a lower percentage of net income from one year to another?

The divisions experienced a significant decline in cost of goods sold.

The divisions experienced a significant increase in sales.

The divisions are taxed at a lower rate than they were in the previous year.

The divisions experienced a significant increase in operating expenses.