Spreadsheet Analysis Preparing a statement of cash flows using the direct method Refer to the information in Problem 14-18A. Solve for the statement of cash flows using the direct method. Instead of using the T-account method, set up the following spreadsheet to work through the analysis. The Debit/Credit entries are very similar to the T-account method except that they are entered onto a spreadsheet. Two distinct differences are as follows:

1. Instead of making entries on row 2 for Cash, cash entries are made beginning on row 24 under the heading Cash Transactions.

2. Entries for Retained Earnings are made on rows 15 through 20 since there are numerous revenue and expense entries to that account.

Required

a. Enter information in Column A.

b. Enter the beginning balance sheet amounts in Column B and ending balances in Column G. Total the debits and credits for each column.

c. To prevent erroneous entries to Cash in row 2, darken the area in Columns C through F.

d. In Columns C through F, record entries for the revenue and expenses and then the related conversions to cash flow. The first entry (a1) and (a2) converting Sales to Cash Received from Sales has been provided for you. So has the labeling for the expense entries (b1 through f1).

e. Record the four entries from the Other Information provided in Problem 14-18A. These are investing and financing activities.

f. In Column H, set up the IF function to determine whether the balance sheet accounts are in balance or not (“off ”). Cell H3 for Accounts Receivable is provided for you. Cell H3 can be copied to all the balance sheet debit accounts. The balance sheet credit account formulas will differ given the different debit/credit rules for those accounts. The formula for Retained Earnings will need to include rows 14 through 20. When the word “Balance” is reflected in every balance sheet cell in column H, the spreadsheet analysis is complete.
g. Total the Debit and Credit columns to ensure that the two columns are equal.
h. As a final check, beginning in cell J2, compute the change in the Cash account by subtracting the beginning balance from the ending balance. The difference will equal $28,200. Also beginning in cell J24, compute the difference in the debit and credit cash entries in rows 24 through 34. The difference should also equal $28,200.
Spreadsheet Tip (1) Darken cells by highlighting the cells to be darkened. Select Format and then Cells. Click on the tab titled Patterns and choose a color.