Excel Primer: Data Analytics Show Me How Office $365$

Question Content Area

Vertical Analysis

Completing this activity will help you learn to:

$$ Evaluate a company's performance using vertical 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 the 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$.$ What is the percentage of Property, plant, and equipment $($net$)$ to total assets for Division $3$ in $2023?($Round the percentage to two decimal places, such as $8\mathrm{.}79\%.)$

fill in the blank $1$ of $1$

$\%$

Q$1$b$.$ How much did the percentage increase $($decrease$)$ from $2022?($Round the percentage to two decimal places, such as $8\mathrm{.}79\%.)$

fill in the blank $1$ of $1$

$\%$

Q$2$a$.$ Does total assets $=$ total liabilities $+$ total stockholders' equity for all divisions combined in $2023?$

Yes

No

Q$2$b$.$ What is the percentage of total stockholders' equity to total liabilities $+$ total stockholders' equity for all divisions combined in $2023?($Round the percentage to two decimal places, such as $8\mathrm{.}79\%.)$

fill in the blank $1$ of $1$

$\%$

Q$3$a$.$ Among the divisions, what is the highest percentage of gross profit to sales in $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 percentage?

Division $1$

Division $2$

Division $3$

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

fill in the blank $1$ of $1$

$\%$

Q$4$b$.$ How much did the percentage increase $($decrease$)$ from $2022?($Round the percentage to two decimal places, such as $8\mathrm{.}79\%.)$

fill in the blank $1$ of $1$

$\%$

Q$5.$ Which statement explains why the percentage of net income to sales will always be equal to or lower than the percentage of gross profit to sales?

Cost of goods sold is always greater than total operating expenses.

Total operating expenses decrease the gross profit amount.

Cost of goods sold is always less than total operating expenses.

Total operating expenses increase the gross profit amount.

Feedback Area