Welcome to Luca!globe
 The CPA Journal Online Current Issue!    Navigation Tips!
Main Menu
CPA Journal
Professional Libary
Professional Forums
Member Services
Oct 1993

Comprehensive gross cash flow preparation: a spreadsheet illustration. (The CPA & The Computer)

by Howard, Barbara J.

    Abstract- A dual approach needs to be taken when preparing gross cash flow statements. This method comprehensively and systematically transforms accrual system information to gross operating, financing and investing cash flows while avoiding the confusion and high costs related to system modifications. The first essential procedure in this approach are reversing the start-up balances in non-cash balance sheet accounts, not including retained earnings. The second step involves the closure of these accounts on the worksheet, and the conversion to gross operating, investing and financing cash flows. The resulting cash flows indicate the debits to cash with interchangeable credits for activities that provide cash. The dual method is shown with Lotus 1-2-3 spreadsheet statements and a worksheet illustration.

Prior to finalizing No. 95, the Board determined that providers and users of financial statements had limited exposure TABULAR DATA OMITTED to the direct method. Moreover, some providers contended that gearing information systems to direct reporting would require excessive implementation costs. The FASB opted for voluntary efforts to accomplish changing to the more comprehensive operating section approach, and permitted the indirect method, which begins with accrual net income and adjusts to net cash flows from operations. This method however, does not disclose some of the details involving operating cash receipts and payments.

The dual approach is a comprehensive method that systematically converts accrual system information to gross operating, financing, and investing cash flows without complicated or costly system modifications. The method is presented with Lotus 1-2-3 spreadsheet statements and a worksheet illustration.

Dual Approach to Convert to Cash Flows

The basic dual procedures are --

Step 1: Reverse the beginning balances in non-cash balance sheet accounts, except retained earnings;

Step 2: Close those accounts on the worksheet, converting to gross operating, investing, and financing cash flows.

The cash flows that result explain debits to cash with reciprocal credits for activities providing cash, e.g., for receipts from customers, or proceeds from sale of plant assets, or bond sales; and cash credits with reciprocal debits for uses, e.g., payments for operating expenses, or equipment purchases, or dividends.

Step 1 Explanation

Step 1 reversals make obvious the amounts and directions of net account changes by juxtaposing the ending and reversed beginning balances in the Adjusted (Adj) Trial Balance and Step 1 Adjustments columns, respectively, on the worksheet. For example, in the worksheet illustration, Step a1 reverses the beginning debit (dr) balance of 46 for accounts receivable in the Step 1 Adjustments credit column. This facilitates comparison to the ending balance in the 12-31-92 Adjusted Trial Balance column, 60 dr, and calculation of the net increase, 14 dr, in the Step 1 Balance (Bal) column.

Step 1 reversals for beginning balances (BBs) of real accounts associated annually (AAN) with the accrual earnings process TABULAR DATA OMITTED or accrued dividends are balanced with entries to retained earnings. Step 1 ANN entries are a1 through c1, g1 through i1, and k1, in the Step 1 Adjustments columns. Step 1 reversals for real accounts not associated annually (NAAN) with income statement accounts are balanced with entries that accumulate prior years' investing and financing activities in a worksheet account, final realization/settlement open (real/set account). They are d1 through f1, j1, and l1.

Step 1 Reversal Statements for the Spreadsheet

Using accounts receivable (A/R) to illustrate, the " @IF" Lotus statements for the Step 1

Adjustments columns, are @IF(BBA/R|is less than0,+BBA/R,0) in the debit column, and @IF(BBA/R|is greater than0,+BBA/R,0) in the credit column. The format @IF(condition,x,y) means if the condition is met, then x is the result, but if the condition is not met, then y is the result. The " @IF" statements assume standard debit/credit conventions using a positive sign (0) for a normal balance.

Step 1 reversals for asset beginning balances would tend to be credits; nevertheless, non-normal and contra asset BBs would be reversed in the Step 1 debit worksheet column. The Step 1 "



by stating that if the condition, "beginning balance of accounts receivable (BBA/R) less than 0" exists, then the BBA/R is entered in the debit column. If this condition does not exist, then 0 is entered in the debit column. The Step 1 credit column statement enters the BBA/R in the credit column if the condition, "BBA/R more than 0," exists, or 0, if the condition does not exist. For a normal asset beginning balance, there should be a "0" in the Step 1 debit column, and the amount of the BB in the credit column.

The Lotus Step 1 " @IF" statements for liabilities and equity accounts are basically the opposite of those used in Step 1 for assets. For example, for a liability, the " @IF" statement for the Step 1

Adjustments debit column tests for normality with "if the condition is greater than zero" and enters a normal BB in the debit column. The Lotus Step 1 statements for accounts payable are @IF(BBA/P|is greater than0,+BBA/P,0) in the debit column, and @IF(BBA/P|is less than0,+BBA/P,0) in the credit column.

Step 2 Explanation

The second procedure focuses on the net account changes calculated on the worksheet in the Step 1 Balance (Bal) column, the differences between EBs and Step 1 reversed BBs. The second step reverses the account changes in the Step 2 Adjustments columns, closing those accounts on the worksheet, and translates accrual information to cash flows in the activities sections.

For ANN accounts, Step 2 converts related income statement accounts to operating cash flows and enters cash payment of dividends, if any, in the financing section. ANN accounts and their accrual income counterparts include accounts receivable and sales, accounts payable and inventory and cost of goods sold, advance collections and earned income, and, prepaids and related expense accounts.

Step a2 for accounts receivable reverses its net debit change with a credit (cr) of 14 in the Step 2 Adjustments column. The entry balances with a reciprocal debit of 14 in the operating-activities section, which, when cross-footed with the cr of 462 in accrual sales, yields cash receipts of 448 cr.

For NAAN accounts, Step 2 elicits investing and financing cash flows, and, in some cases, converts to operating cash flows. Since multiple cash flows can be reflected in net NAAN account changes, it is useful to organize information in a Sources, Uses, and Exchanges (SUE) schedule. The SUE Schedule, accumulates and summarizes cash provisions and uses from reversals of accrual system entries. Information for Step 2 worksheet entries is read directly from the SUE Schedule's USE and PROVISION columns and the STEP 2 ADJUST row.

Step e2 elicits cash flows from an equipment purchase and a sale, derived from reversals of related accrual system entries. Reading from the USE and PROVISION columns in the SUE schedule, the 100 dr and 68 cr are entered on the worksheet in the Step 2 Adjustments columns in the investing activities section. Using the information in the STEP TABULAR DATA OMITTED 2 ADJUST row, the cr of 40 and dr of 3 are entered in the Step 2 Adjustments columns to equipment and accumulated depreciation, respectively, closing the accounts on the worksheet. The e2 dr of 14, entered in the operating activities Step 2 Adjustments column, reverses the gain on the sale of equipment, and the e2 cr of 9 to depreciation eliminates its non-cash flow effect.

Step 2 Spreadsheet Statements

Step 2 adjustment statements have " @IF" formats. Using accounts receivable, they are @IF(S1BA/R|is less than0,+ABS(S1BA/R),0) in the debit column, and @IF (S1BA/R|is greater t han0,+ABS(S1BA/R),0) in the credit column.

The Step 2 debit column statement tests for a negative sign in the Step 1 Bal column for accounts receivable (S1BA/R), indicating a decrease in accounts receivable (EB|is less thanBB). Lotus enters the absolute value of S1BA/R in the Step 2 debit column if S1BA/R is negative (a credit), otherwise zero is entered in the debit column. The credit column statement tests for a positive S1BA/R balance, resulting from an increase in receivables (EB|is greater thanBB), and enters the absolute value in the credit column, otherwise zero.

Lotus Step 2 statements for long-term assets follow the " @IF" formulations for receivables, while " @IF" formats for liabilities and equity accounts are the opposite of those for assets.

Completing the Worksheet

Remaining spreadsheet operations include the following: summations that- prove the equality of Steps 1 and 2 debits and credits, 828 and 404, respectively, in the Steps 1 and 2 Adjustments columns at Total-Step 1 and Total-Step 2 Adjustments (Adjust); cross footing each Step 2 real account adjustment with its Step 1 Bal, closing the account on the worksheet; and, in the activities sections, summations for each row, resulting in the detail of cash information in the last column.

The final worksheet entry (FNL)3 reverses the cash BB, facilitating comparison with its EB and calculation of the net cash change. The FNL balances by closing remaining non-cash flow statement accounts on the worksheet. In the illustration, the (FNL) reverses the cash BB with a credit of 26, and balances with a debit of 90 and credit of 64 to retained earnings and the real/set account, respectively. In the last worksheet column, the net cash increase, 39, is explained by the detail of operating, investing, and financing cash flow activities.

The dual approach systematically converts accrual-system information to gross cash flows. Step 1 facilitates comparisons of the EBs of all non- cash real accounts with their BBs. Step 1 reversals of BBs are offset by entries to retained earnings for accounts that regularly affect accrual income, or else, to the worksheet real/set account. Step 2 reverses the amounts and directions of account changes from Step 1, translating accrual information to gross cash flows. The final worksheet entry (FNL) results in the net cash change explained by cash activities in the last worksheet column.


1. A/P = accounts payable 2. A/R = accounts receivable 3. ABS = absolute value 4. ACCUM DEP = accumulated depreciation 5. ANN = real accounts associated annually with earnings 6. ADJ = adjusted 7. ADJUST = adjustments 8. BAL = balance 9. CS = common stock 10. DEP/EQUIP = depreciation expense for equipment 11. DIV = dividends 12. EQUIP = equipment 13. LIAB = liabilities 14. NAAN = real accounts not associated annually with earnings 15. PAY = payable 16. FUR = purchase 17. REAL/SET = cash realization or settlement open 18. REC = receivable 19. RET EARNINGS = retained earnings 20. S1B = Step 1 balance

Sharon E. Bossung, CPA, and Barbara J. Howard, CPA, Suny Geneseo

The CPA Journal is broadly recognized as an outstanding, technical-refereed publication aimed at public practitioners, management, educators, and other accounting professionals. It is edited by CPAs for CPAs. Our goal is to provide CPAs and other accounting professionals with the information and news to enable them to be successful accountants, managers, and executives in today's practice environments.

©2009 The New York State Society of CPAs. Legal Notices

Visit the new cpajournal.com.