Stock transactions and dividends

Completing this activity will help you learn to:

$$ Calculate dividends per share based on number of shares of stock outstanding.

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

Scenario:

Companies determine their dividend per share amounts for preferred stock and for common stock.

The Data worksheet contains a list of companies and their regions, with information related to shares of stock and dividend payments in a year.

Each row provides information about one company's shares of preferred and common stock, treasury shares, total dividends declared, and preferred and common dividends per share, as follows:

The Common shares beginning balance and Preferred shares beginning balance columns indicate the number of shares issued and outstanding at the beginning of the year.

The Common shares additional issued and Preferred shares additional issued columns indicate the number of additional shares issued during the year.

The Treasury shares sold $1$ and Treasury shares sold $2$ columns indicate two different sales of treasury COMMON stock during the year, after the original purchase shown in the Treasury shares purchased column.

The Total dividends declared column indicates the total dollar amount that the company's board of directors authorized for dividend payments on both classes of stock.

The Preferred dividend per share column and the Common dividend per share column indicate the dividend amounts that will be paid on each share of preferred and common stock, respectively.

Required:

Open spreadsheet

Complete four columns on the Data worksheet: Preferred shares outstanding, Common shares outstanding, Total preferred dividend, and Total common dividend.

R$1.$ Cell K$2$: Enter a formula that results in the number of outstanding shares of preferred stock using the data in row $2.($NOTE: All treasury stock is COMMON stock.$)$

fill in the blank $1$ of $1$

R$2.$ Cell L$2$: Enter a formula that results in the number of outstanding shares of common stock using the data in row $2.($NOTE: All treasury stock is COMMON stock.$)$

fill in the blank $1$ of $1$

R$3.$ Cell P$2$: Enter a formula that results in the total amount of dividend paid to preferred stockholders using the data in row $2.$

fill in the blank $1$ of $1$$

R$4.$ Cell Q$2$: Enter a formula that results in the total amount of dividend paid to common stockholders using the data in row $2.$

fill in the blank $1$ of $1$$

R$5.$ Copy down the entries in cells K$2,$ L$2,$ P$2,$ and Q$2$ to all other rows with data.

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.$ What is the total number of treasury common shares owned by Carle Place, Kings Point, and Northport corporations combined?

fill in the blank $1$ of $1$

Q$2.$ What is the highest average common dividend per share amount when sorted by region? $($Round the amount to the nearest cent, such as $1\mathrm{.}99.)$

fill in the blank $1$ of $1$$

Q$3.$ How much greater is the total common stock dividend than the total preferred stock dividend for Centereach, Riverhead, and Wantagh companies combined?

fill in the blank $1$ of $1$$

Q$4.$ What is the percentage of preferred shares issued to common shares issued in the Northwest region? $($Round the percentage to two decimal places, such as $18\mathrm{.}67\%.)$

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

Q$5.$ Investors in common stock would be most interested in knowing

Holbrook Corporation's number of common stock shares outstanding.

West Babylon Company's ratio of common stock dividend per share to preferred stock dividend per share.

Albertson, Inc.$\text{'}$s total common stock dividend to be paid to shareholders.

Manorville Corporation's common stock dividend per share.

Please show me how I would put in Q$1-$Q$4$ on the Pivot table $($Like what goes into rows, columns, etc., and any additional formulas needed to get said answer$)$