statement of Cash Flows

Completing this activity will help you learn to:

$$Determine cash flows for operating, investing, and financing activities.

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

Scenario:

Company A provides accounting services to small business clients.

Each client is assigned one of six of the company's professional staff members.

The staff members prepare their clients' financial statements, including a statement of cash flows using the indirect method.

Assume buying and selling assets, issuing stock, and paying dividends are all cash transactions.

The Data worksheet contains a list of clients that Company A serves.

Each row provides information about one client, including staff member assigned, net income for the year, depreciation, gain or loss on sale of investments, increase or decrease between the previous year's ending balance and the current year's ending balance in each of its current asset and current liability accounts, amount received from sale of investments, amount paid for purchase of equipment, amount received from issuing stock, and amount paid for dividends.

Required:

Open spreadsheet

Complete five columns on the Data worksheet: Cash flows $-$operating activities, Cash flows $-$investing activities, Cash flows $-$financing activities, Increase $($decrease$)$in cash, and Investing cash outflow.

R$1.$Cell P$4$: Enter a formula that results in cash flows from operating activities using the data in row $4.$Enter a net cash outflow as a negative number.

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

R$2.$Cell Q$4$: Enter a formula that results in cash flows from investing activities using the data in row $4.$Enter a net cash outflow as a negative number.

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

R$3.$Cell R$4$: Enter a formula that results in cash flows from financing activities using the data in row $4.$Enter a net cash outflow as a negative number.

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

R$4.$Cell S$4$: Enter a formula that results in the increase or decrease in the cash amount using the data in row $4.$Enter a decrease in cash as a negative number.

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

R$5.$Cell T$4$: Use an IF function to return a $1$for a cash outflow or a $0$for a cash inflow for investing activities.

fill in the blank $1$of $1$

R$6.$Copy down the entries in cells P$4,$Q$4,$R$4,$S$4,$and T$4$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.$Which staff person had the highest amount of stock issuances for their clients combined?

Casey Overman

Connor Bradley

Limo Matsumoto

Mark Rado

Nora Rodriguez

Violeta Scardino

Q$2.$What was Olivia Corporation's beginning cash balance if its ending cash balance was $$306,340?$

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

Q$3.$What was the original cost of the investment that Alexandria, Inc. sold this year?

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

Q$4.$How many of Scardino and Rodriguez's clients combined had a net cash outflow from investing activities during the year?

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

Q$5.$Which of the following statements about current position measures is NOT accurate?

Apple Valley Corporation adds $$18,500$for depreciation to net income and deducts the $$17,700$increase in inventory from net income as part of determining its cash flows from operating activities.

East Grand Forks, Inc. had a net cash inflow from investing activities because it paid $$136,890$for equipment and sold investments for $$234,670.$

Lino Lakes Corporation adds both $$3,410$for the gain on sale of investments and $$10,000$for the increase in wages payable to net income as part of determining its cash flows from operating activities.

Dilworth Company had a net cash outflow from financing activities because it paid $$51,390$in dividends and issued $$30,000$in stock.

Please show formulas needed for R$1-$R$5$ and how to do on Pivot Table $($like what to put in to rows, columns, etc.$)$ for Q$1-$Q$4$