Using a Worksheet (Indirect Method)

Learning Outcomes

  • Demonstrate the use of a worksheet to create the Statement of Cash Flows

In order to ensure accuracy and make the process as efficient as possible, accountants have developed a worksheet process to create the statement of cash flows. We’ll demonstrate that process here using the indirect method because that is its most common use and the indirect method is the most common presentation.

First, we look at the beginning and ending balance sheets:

RUMBLE CORP
Spreadsheet for Statement of Cash Flows
Year Ended December 31, 20X1
Panel A – Balance Sheet 1/1/X1 Transaction Analysis 12/31/X1
Debit Credit
Cash 1,640 5,040
Accounts Receivable 1,750 1,735
Equipment 24,500 24,920
Accumulated Depreciation (1,540) (1,565)
Total Assets 26,350 30,130
Accounts Payable (1,007) (1,039)
Wages Payable (55) (135)
Income Taxes Payable (42) (60)
Note Payable – Long Term 0 (500)
Total Liabilities (1,104) (1,734)
Common Stock (12,500) (13,500)
Retained Earnings (12,746) (14,896)
Total Liabilities and Stockholders’ Equity (26,350) (30,130)
0 0

We’re showing credit balances on this spreadsheet as negative numbers (in parenthesis), but different accountants may do this differently. Also notice the December 31, 20X0 balance is presented as the January 1, 20X1 balance. Again, that is personal preference.

We’ll add a template to the bottom of this balance sheet spreadsheet that we will use to capture the changes in cash:

Panel B – Statement of Cash Flows
Cash Flows from Operating Activities:
Net Income
Adjustments to Reconcile Net Income to Net Cash Provided by Operating Activities
Depreciation Expense – Plant Assets
Decrease in accounts receivable
Gain on sale of equipment
Increase in Accounts Payable
Increase in Wages Payable
Increase in Income Taxes Payable
Net Cash Provided by Operating Activities
Cash Flows from Investing Activities:
Cash Paid for Acquisition of Plant Assets
Proceeds from sale of equipment
Net Cash Used for Investing Activities
Cash Flows from Financing Activities:
Cash Receipt from Issuance of Common Stock
Proceeds from issuance of long-term debt
Cash Payment of Dividends
Net Cash Provided by Financing Activities
Net Increase (Decrease) in Cash:
Total

And now we fill in the worksheet one line at a time, starting with accounts receivable.

Every debit will have to have a credit. Accounts receivable decreased by $15, so we credit that account on the spreadsheet and debit the corresponding line on the statement of cash flows.

RUMBLE CORP
Spreadsheet for Statement of Cash Flows
Year Ended December 31, 20X1
Panel A – Balance Sheet 1/1/X1 Transaction Analysis 12/31/X1
Debit Credit
Cash 1,640 1,640
Accounts Receivable 1,750 15 1,735
Equipment 24,500 24,500
Accumulated Depreciation (1,540) (1,540)
Total Assets 26,350 0 15 26,335
Accounts Payable (1,007) (1,007)
Wages Payable (55) (55)
Income Taxes Payable (42) (42)
Note Payable – Long Term 0 0
Total Liabilities (1,104) 0 0 (1,104)
Common Stock (12,500) (12,500)
Retained Earnings (12,746) (12,746)
Total Liabilities and Stockholders’ Equity (26,350) 0 0 (26,350)
0 0 15 (15)
Panel B – Statement of Cash Flows
Cash Flows from Operating Activities
Net Income
Adjustments to Reconcile Net Income to Net Cash Provided by Operating Activities
Depreciation Expense – Plant Assets
Decrease in Accounts Receivable 15
Gain on sale of equipment
Increase in Accounts Payable
Increase in Wages Payable
Increase in Income Taxes Payable
Net Cash Provided by Operating Activities
Cash Flows from Investing Activities
Cash Paid for Acquisition of Plant Assets
Proceeds from sale of equipment
Net Cash Used for Investing Activities
Cash Flows from Financing Activities
Cash Receipt from Issuance of Common Stock
Proceeds from issuance of long-term debt
Cash Payment of Dividends
Net Cash Provided by Financing Activities
Net Increase (Decrease) in Cash
Total $ 15 $ –

Next, we record the purchase of equipment for cash by debiting the balance sheet line and crediting the corresponding line on the statement of cash flows (cash used for investing):

RUMBLE CORP
Spreadsheet for Statement of Cash Flows
Year Ended December 31, 20X1
Panel A – Balance Sheet 1/1/X1 Transaction Analysis 12/31/X1
Debit Credit
Cash 1,640 1,640
Accounts Receivable 1,750 15 1,735
Equipment 24,500 580 25,080
Accumulated Depreciation (1,540) (1,540)
Total Assets 26,350 580 15 26,915
Accounts Payable (1,007) (1,007)
Wages Payable (55) (55)
Income Taxes Payable (42) (42)
Note Payable – Long Term 0 0
Total Liabilities (1,104) 0 0 (1,104)
Common Stock (12,500) (12,500)
Retained Earnings (12,746) (12,746)
Total Liabilities and Stockholders’ Equity (26,350) 0 0 (26,350)
0 580 15 565
Panel B – Statement of Cash Flows
Cash Flows from Operating Activities
Net Income
Adjustments to Reconcile Net Income to Net Cash Provided by Operating Activities
Depreciation Expense – Plant Assets
Decrease in Accounts Receivable 15
Gain on sale of equipment
Increase in Accounts Payable
Increase in Wages Payable
Increase in Income Taxes Payable
Net Cash Provided by Operating Activities
Cash Flows from Investing Activities
Cash Paid for Acquisition of Plant Assets 580
Proceeds from sale of equipment
Net Cash Used for Investing Activities
Cash Flows from Financing Activities
Cash Receipt from Issuance of Common Stock
Proceeds from issuance of long-term debt
Cash Payment of Dividends
Net Cash Provided by Financing Activities
Net Increase (Decrease) in Cash
Total $ 15 $ 580
Total debits/credits $ 595 $ 595

Next, we know that we sold equipment and recorded a gain on the sale:

RUMBLE CORP
Spreadsheet for Statement of Cash Flows
Year Ended December 31, 20X1
Panel A – Balance Sheet 1/1/X1 Transaction Analysis 12/31/X1
Debit Credit
Cash 1,640 1,640
Accounts Receivable 1,750 15 1,735
Equipment 24,500 580 160 24,920
Accumulated Depreciation (1,540) 100 (1,440)
Total Assets 26,350 680 175 26,855
Accounts Payable (1,007) (1,007)
Wages Payable (55) (55)
Income Taxes Payable (42) (42)
Note Payable – Long Term 0 0
Total Liabilities (1,104) 0 0 (1,104)
Common Stock (12,500) (12,500)
Retained Earnings (12,746) (12,746)
Total Liabilities and Stockholders’ Equity (26,350) 0 0 (26,350)
0 680 175 505
Panel B – Statement of Cash Flows
Cash Flows from Operating Activities
Net Income
Adjustments to Reconcile Net Income to Net Cash Provided by Operating Activities
Depreciation Expense – Plant Assets
Decrease in Accounts Receivable 15
Gain on sale of equipment 90
Increase in Accounts Payable
Increase in Wages Payable
Increase in Income Taxes Payable
Net Cash Provided by Operating Activities
Cash Flows from Investing Activities
Cash Paid for Acquisition of Plant Assets 580
Proceeds from sale of equipment 150
Net Cash Used for Investing Activities
Cash Flows from Financing Activities
Cash Receipt from Issuance of Common Stock
Proceeds from issuance of long-term debt
Cash Payment of Dividends
Net Cash Provided by Financing Activities
Net Increase (Decrease) in Cash
Total $ 165 $ 670
Total debits/credits $ 845 $ 845

Bit by bit, we are reconstructing the ending balance sheet by recording the changes to each account caused by cash transactions.

Accumulated depreciation is the next line on the balance sheet as we work our way from top to bottom. We record depreciation expense as a credit to accumulated depreciation (an increase) and a debit to depreciation expense on the statement of cash flows (which will be an add-back to net income).

RUMBLE CORP
Spreadsheet for Statement of Cash Flows
Year Ended December 31, 20X1
Panel A – Balance Sheet 1/1/X1 Transaction Analysis 12/31/X1
Debit Credit
Cash 1,640 1,640
Accounts Receivable 1,750 15 1,735
Equipment 24,500 580 60 25,020
Accumulated Depreciation (1,540) 125 (1,665)
Total Assets 26,350 580 200 26,730
Accounts Payable (1,007) (1,007)
Wages Payable (55) (55)
Income Taxes Payable (42) (42)
Note Payable – Long Term 0 0
Total Liabilities (1,104) 0 0 (1,104)
Common Stock (12,500) (12,500)
Retained Earnings (12,746) (12,746)
Total Liabilities and Stockholders’ Equity (26,350) 0 0 (26,350)
0 580 200 380
Panel B – Statement of Cash Flows
Cash Flows from Operating Activities
Net Income
Adjustments to Reconcile Net Income to Net Cash Provided by Operating Activities
Depreciation Expense – Plant Assets 125
Decrease in Accounts Receivable 15
Gain on sale of equipment 90
Increase in Accounts Payable
Increase in Wages Payable
Increase in Income Taxes Payable
Net Cash Provided by Operating Activities
Cash Flows from Investing Activities
Cash Paid for Acquisition of Plant Assets 580
Proceeds from sale of equipment 150
Net Cash Used for Investing Activities
Cash Flows from Financing Activities
Cash Receipt from Issuance of Common Stock
Proceeds from issuance of long-term debt
Cash Payment of Dividends
Net Cash Provided by Financing Activities
Net Increase (Decrease) in Cash
Total $ 290 $ 670
Total debits/credits $ 870 $ 870

Then we adjust the current liability accounts:
 

RUMBLE CORP
Spreadsheet for Statement of Cash Flows
Year Ended December 31, 20X1
Panel A – Balance Sheet 1/1/X1 Transaction Analysis 12/31/X1
Debit Credit
Cash 1,640 1,640
Accounts Receivable 1,750 15 1,735
Equipment 24,500 580 60 25,020
Accumulated Depreciation (1,540) 125 (1,665)
Total Assets 26,350 580 200 26,730
Accounts Payable (1,007) 32 (1,039)
Wages Payable (55) 80 (135)
Income Taxes Payable (42) 18 (60)
Note Payable – Long Term 0 0
Total Liabilities (1,104) 0 130 (1,234)
Common Stock (12,500) (12,500)
Retained Earnings (12,746) (12,746)
Total Liabilities and Stockholders’ Equity (26,350) 0 130 (26,480)
0 580 330 250
Panel B – Statement of Cash Flows
Cash Flows from Operating Activities
Net Income
Adjustments to Reconcile Net Income to Net Cash Provided by Operating Activities
Depreciation Expense – Plant Assets 125
Decrease in Accounts Receivable 15
Gain on sale of equipment 90
Increase in Accounts Payable 32
Increase in Wages Payable 80
Increase in Income Taxes Payable 18
Net Cash Provided by Operating Activities
Cash Flows from Investing Activities
Cash Paid for Acquisition of Plant Assets 580
Proceeds from sale of equipment 150
Net Cash Used for Investing Activities
Cash Flows from Financing Activities
Cash Receipt from Issuance of Common Stock
Proceeds from issuance of long-term debt
Cash Payment of Dividends
Net Cash Provided by Financing Activities
Net Increase (Decrease) in Cash
Total $ 420 $ 670
Total debits/credits $ 1,000 $ 1,000

The long-term borrowing increased cash and was a financing activity:
 

RUMBLE CORP
Spreadsheet for Statement of Cash Flows
Year Ended December 31, 20X1
Panel A – Balance Sheet 1/1/X1 Transaction Analysis 12/31/X1
Debit Credit
Cash 1,640 1,640
Accounts Receivable 1,750 15 1,735
Equipment 24,500 580 60 25,020
Accumulated Depreciation (1,540) 125 (1,665)
Total Assets 26,350 580 200 26,730
Accounts Payable (1,007) 32 (1,039)
Wages Payable (55) 80 (135)
Income Taxes Payable (42) 18 (60)
Note Payable – Long Term 0 500 (500)
Total Liabilities (1,104) 0 630 (1,734)
Common Stock (12,500) (12,500)
Retained Earnings (12,746) (12,746)
Total Liabilities and Stockholders’ Equity (26,350) 0 630 (26,980)
0 580 830 (250)
Panel B – Statement of Cash Flows
Cash Flows from Operating Activities
Net Income
Adjustments to Reconcile Net Income to Net Cash Provided by Operating Activities
Depreciation Expense – Plant Assets 125
Decrease in Accounts Receivable 15
Gain on sale of equipment 90
Increase in Accounts Payable 32
Increase in Wages Payable 80
Increase in Income Taxes Payable 18
Net Cash Provided by Operating Activities
Cash Flows from Investing Activities
Cash Paid for Acquisition of Plant Assets 580
Proceeds from sale of equipment 150
Net Cash Used for Investing Activities
Cash Flows from Financing Activities
Cash Receipt from Issuance of Common Stock
Proceeds from issuance of long-term debt 500
Cash Payment of Dividends
Net Cash Provided by Financing Activities
Net Increase (Decrease) in Cash
Total $ 920 $ 670
Total debits/credits $ 1,500 $ 1,500

And issuing stock for cash also represents cash provided by a financing activity:
 

RUMBLE CORP
Spreadsheet for Statement of Cash Flows
Year Ended December 31, 20X1
Panel A – Balance Sheet 1/1/X1 Transaction Analysis 12/31/X1
Debit Credit
Cash 1,640 1,640
Accounts Receivable 1,750 15 1,735
Equipment 24,500 580 60 25,020
Accumulated Depreciation (1,540) 125 (1,665)
Total Assets 26,350 580 200 26,730
Accounts Payable (1,007) 32 (1,039)
Wages Payable (55) 80 (135)
Income Taxes Payable (42) 18 (60)
Note Payable – Long Term 0 500 (500)
Total Liabilities (1,104) 0 630 (1,734)
Common Stock (12,500) 1,000 (13,500)
Retained Earnings (12,746) (12,746)
Total Liabilities and Stockholders’ Equity (26,350) 0 1,630 (27,980)
0 580 1,830 (1,250)
Panel B – Statement of Cash Flows
Cash Flows from Operating Activities
Net Income
Adjustments to Reconcile Net Income to Net Cash Provided by Operating Activities
Depreciation Expense – Plant Assets 125
Decrease in Accounts Receivable 15
Gain on sale of equipment 90
Increase in Accounts Payable 32
Increase in Wages Payable 80
Increase in Income Taxes Payable 18
Net Cash Provided by Operating Activities
Cash Flows from Investing Activities
Cash Paid for Acquisition of Plant Assets 580
Proceeds from sale of equipment 150
Net Cash Used for Investing Activities
Cash Flows from Financing Activities
Cash Receipt from Issuance of Common Stock 1,000
Proceeds from issuance of long-term debt 500
Cash Payment of Dividends
Net Cash Provided by Financing Activities
Net Increase (Decrease) in Cash
Total $ 1,920 $ 670
Total debits/credits $ 2,500 $ 2,500

We now post net income to retained earnings:
 

RUMBLE CORP
Spreadsheet for Statement of Cash Flows
Year Ended December 31, 20X1
Panel A – Balance Sheet 1/1/X1 Transaction Analysis 12/31/X1
Debit Credit
Cash 1,640 1,640
Accounts Receivable 1,750 15 1,735
Equipment 24,500 580 60 25,020
Accumulated Depreciation (1,540) 125 (1,665)
Total Assets 26,350 580 200 26,730
Accounts Payable (1,007) 32 (1,039)
Wages Payable (55) 80 (135)
Income Taxes Payable (42) 18 (60)
Note Payable – Long Term 0 500 (500)
Total Liabilities (1,104) 0 630 (1,734)
Common Stock (12,500) 1,000 (13,500)
Retained Earnings (12,746) 2,610 (15,356)
Total Liabilities and Stockholders’ Equity (26,350) 0 4,240 (30,590)
0 580 4,440 (3,860)
Panel B – Statement of Cash Flows
Cash Flows from Operating Activities
Net Income 2,610
Adjustments to Reconcile Net Income to Net Cash Provided by Operating Activities
Depreciation Expense – Plant Assets 125
Decrease in Accounts Receivable 15
Gain on sale of equipment 90
Increase in Accounts Payable 32
Increase in Wages Payable 80
Increase in Income Taxes Payable 18
Net Cash Provided by Operating Activities
Cash Flows from Investing Activities
Cash Paid for Acquisition of Plant Assets 580
Proceeds from sale of equipment 150
Net Cash Used for Investing Activities
Cash Flows from Financing Activities
Cash Receipt from Issuance of Common Stock 1,000
Proceeds from issuance of long-term debt 500
Cash Payment of Dividends
Net Cash Provided by Financing Activities
Net Increase (Decrease) in Cash
Total $ 4,530 $ 670
Total debits/credits $ 5,110 $ 5,110

Dividends reduce retained earnings:
 

RUMBLE CORP
Spreadsheet for Statement of Cash Flows
Year Ended December 31, 20X1
Panel A – Balance Sheet 1/1/X1 Transaction Analysis 12/31/X1
Debit Credit
Cash 1,640 1,640
Accounts Receivable 1,750 15 1,735
Equipment 24,500 580 60 25,020
Accumulated Depreciation (1,540) 125 (1,665)
Total Assets 26,350 580 200 26,730
Accounts Payable (1,007) 32 (1,039)
Wages Payable (55) 80 (135)
Income Taxes Payable (42) 18 (60)
Note Payable – Long Term 0 500 (500)
Total Liabilities (1,104) 0 630 (1,734)
Common Stock (12,500) 1,000 (13,500)
Retained Earnings (12,746) 460 2,610 (14,896)
Total Liabilities and Stockholders’ Equity (26,350) 460 4,240 (30,130)
0 1,040 4,440 (3,400)
Panel B – Statement of Cash Flows
Cash Flows from Operating Activities
Net Income 2,610
Adjustments to Reconcile Net Income to Net Cash Provided by Operating Activities
Depreciation Expense – Plant Assets 125
Decrease in Accounts Receivable 15
Gain on sale of equipment 90
Increase in Accounts Payable 32
Increase in Wages Payable 80
Increase in Income Taxes Payable 18
Net Cash Provided by Operating Activities
Cash Flows from Investing Activities
Cash Paid for Acquisition of Plant Assets 580
Proceeds from sale of equipment 150
Net Cash Used for Investing Activities
Cash Flows from Financing Activities
Cash Receipt from Issuance of Common Stock 1,000
Proceeds from issuance of long-term debt 500
Cash Payment of Dividends 460
Net Cash Provided by Financing Activities
Net Increase (Decrease) in Cash
Total $ 4,530 $ 1,130
Total debits/credits $ 5,570 $ 5,570

And the final step is to post the change in cash:
 

RUMBLE CORP
Spreadsheet for Statement of Cash Flows
Year Ended December 31, 20X1
Panel A – Balance Sheet 1/1/X1 Transaction Analysis 12/31/X1
Debit Credit
Cash 1,640 3,400 5,040
Accounts Receivable 1,750 15 1,735
Equipment 24,500 580 60 25,020
Accumulated Depreciation (1,540) 125 (1,665)
Total Assets 26,350 3,980 200 30,130
Accounts Payable (1,007) 32 (1,039)
Wages Payable (55) 80 (135)
Income Taxes Payable (42) 18 (60)
Note Payable – Long Term 0 500 (500)
Total Liabilities (1,104) 0 630 (1,734)
Common Stock (12,500) 1,000 (13,500)
Retained Earnings (12,746) 460 2,610 (14,896)
Total Liabilities and Stockholders’ Equity (26,350) 460 4,240 (30,130)
0 4,440 4,440 0
Panel B – Statement of Cash Flows
Cash Flows from Operating Activities
Net Income 2,610
Adjustments to Reconcile Net Income to Net Cash Provided by Operating Activities
Depreciation Expense – Plant Assets 125
Decrease in Accounts Receivable 15
Gain on sale of equipment 90
Increase in Accounts Payable 32
Increase in Wages Payable 80
Increase in Income Taxes Payable 18
Net Cash Provided by Operating Activities
Cash Flows from Investing Activities
Cash Paid for Acquisition of Plant Assets 580
Proceeds from sale of equipment 150
Net Cash Used for Investing Activities
Cash Flows from Financing Activities
Cash Receipt from Issuance of Common Stock 1,000
Proceeds from issuance of long-term debt 500
Cash Payment of Dividends 460
Net Cash Provided by Financing Activities
Net Increase (Decrease) in Cash 3,400
Total $ 4,530 $ 4,530
Total debits/credits $ 8,970 $ 8,970

And now the numbers are all in place, everything is accounted for, and all we have to do is place the numbers into the proper format along with some supplemental information we’ll have to get from the general ledger:

Subcategory, Cash flows from financing activities

Rumble Corp.
Statement of Cash Flows
for the year ended 12/31/x1
Description Amount Total
In millions
Subcategory, Cash flows from operating activities
Net income $ 2,610
Adjustments to reconcile net income to net cash provided by operating activities:
Depreciation and amortization 125
Decrease in Accounts Receivable 15
Gain on sale of equipment (90)
Increase in Accounts Payable 32
Increase in income taxes payable 80
Increase in other liabilities 18
Total adjustments 180
Net cash from operating activities Single Line Single Line
$2,790
Subcategory, Cash flows from investing activities
Purchase of property, plant, and equipment (580)
Proceeds from sale of equipment 150
Net cash used in investing activities Single Line Single Line
(430)
Proceeds from issuance of common stock 1,000
Proceeds from issuance of long-term debt 500
Dividends paid (460)
Net cash used in financing activities Single Line Single Line
1,040
Net increase in cash and cash equivalents 3,400
Cash and cash equivalents at beginning of period 1,640
Cash and cash equivalents at end of period Single Line
$5,040
Double Line
Supplemental information:
Cash paid for interest $ 310
Cash paid for income taxes $ 1,700

Congratulations! You’ve now worked all the way through the worksheet and created the Statement of Cash Flows.

practice question