…

Table of Contents:

1. Bank Statements
2. Bank Statement Spreadsheet Ledger
3. Bank Statement Spreadsheet Ledger Summary
4. Journalized Summary
5. Modified Trial Balance
6. Adjusted Trial Balance
7. Statement of Income
8. Balance Sheet
9. Worksheets and Other Record Keeping Concerns
10. Appendix

An example dataset is provided in .xls format to follow along with the examples below.

…

Introduction

The purpose of this document is to illustrate a spreadsheet-based method to assist in compiling financial statements from corporate bank account data. Use of this method may be appropriate in cases where a corporation has fallen behind on its accounting responsibilities; is undergoing a corporate tax or trust exam audit with lost, incomplete or erroneous records; or when an inactive corporation has few transactions in a reporting period and requires financial statements in short order.

The spreadsheet-based method may be preferred as an alternative to double-entry accounting software in cases where it is faster to work in spreadsheets as compared to setting up a new software instance and meticulously journalizing each transaction. However, the reader is cautioned that the method described below is essentially a “cash” method of accounting for corporate financial transactions with hints of “accrual” accounting. To reiterate, the method does not use pure accrual accounting as required under generally accepted accounting principles and the Income Tax Act. Finally, the reader is cautioned that the spreadsheet-based method is not an adequate substitute for regular accrual bookkeeping: it is strictly a solution of last resort. The consequences of, and potential remedies for, its shortcomings are examined in the Appendix below.

This document is provided for informational purposes only and does not constitute legal or accounting advice. It is recommended that the reader consult legal counsel or professional advisors as the reader is responsible for being in compliance with all applicable laws, statutes, and regulations. Information contained in this document is subject to change.

1. Bank Statements

Whether using double-entry accounting software or the spreadsheet-based method, retrieving a complete set of corporate bank statements for each fiscal compilation period is a necessary first step. Bank account statements are often formatted with layouts distinct to the originating bank; however, they almost always contain the minimum key elements of date, transaction description or code, transaction amount, and account balance (see Example 1, below).

Example 1 – January 20XX Corporate Bank Statement

2. Bank Statement Spreadsheet Ledger

Once a complete set of corporate bank statements has been assembled, data extraction may begin. Bank statement data may be compiled into the bank statement spreadsheet ledger manually or with the assistance of optical character recognition (“OCR”) technology. In the double-entry accounting software method, each transaction in the corporation’s bank statement is manually recorded as a journal entry in the software. Using the spreadsheet-based method, each transaction is represented by a single row in the bank statement spreadsheet ledger (see Example 2, below).

Example 2 – Excerpt of Corporate Bank Statement Spreadsheet Ledger

Concerning the transposition of bank statement data into the bank statement spreadsheet ledger, three important points deserve mention as illustrated in Example 2:

1. a “Category” column is added to the ledger in order to record a primary classification for each bank statement transaction,
2. transaction amount columns are consolidated in one column, “Gross”, then broken into two distinct columns named “Net” and “GST/HST” to assist with the reconciliation of corporate excise tax, and
3. due to data consolidation, each transaction’s debit/credit identity is retained in its own column, “DR/CR”, so the transaction quality (+/-) is not lost.

In Example 2, above, the bank statement’s original “Date” column is separated into “Day”, “Month”, and “Year” columns enabling the filtering of relevant fiscal periods, and for easy reference to the original data source. Depending on the detail required for each specific use case, the original date format may be retained, simplified, or discarded entirely.

Given the potential for human error while using spreadsheets, the “Balance” column is retained to verify the completeness of data compiled. To confirm data compilation accuracy, the bank statement spreadsheet ledger balance must equal the bank statement balance at all times.

3. Bank Statement Spreadsheet Ledger Summary

Once all bank statement data has been compiled into the bank statement spreadsheet ledger, the ledger data is ready to be summarized and reconciled with the opening and closing corporate bank statement balances. By applying an auto-filter function to the Category column, the data contained in the bank statement spreadsheet ledger is easily summarized by each category type and transcribed to the summary ledger. As an alternative solution, pivot tables may be used to generate the required Category summary information and amounts. In either approach, Gross, GST/HST, and Net amounts are summed from the filtered rows and transcribed to the summary ledger.

For example, during the year 20XX in our sample data set the corporation had twenty-four “ATM Deposit Main&Broadway 83924” bank statement transactions: twenty-three with the amount $4,708 and one with the amount $4,716 – all including HST at 13% (see Example 3, below).

Example 3 – Bank Statement Spreadsheet Ledger Auto-Filtered for “Revenue” Category

All twenty-four transactions, which are identified and categorized in the ledger as “Revenue”, sum to a total gross revenue of $113,000 (including $13,000 HST). Once each Category type in the bank statement spreadsheet ledger^[1] – and its corresponding data – has been summarized and transcribed to the ledger summary, the end result will resemble Example 4, below.

Example 4 – Bank Statement Spreadsheet Ledger Summary

At this point in the spreadsheet-based method, the bank statement spreadsheet ledger summary begins to resemble the trial balance structure of traditional financial accounting. Credit and debit transactions are segregated for clarity, and each member of the Category column is now explicitly attached to a financial accounting account classification (see Example 4, above, “Accounting Category”).

Each step of the spreadsheet-based method requires transposition verification to ensure data accuracy and completeness. In the ledger summary step, there are several spot-checks that should be done:

1. check that the CR, Gross column, TOTAL row ($115,440) of the bank statement spreadsheet ledger summary matches the sum of all CR, Gross rows of the bank statement spreadsheet ledger (the same process may also be used to verify GST/HST and Net amounts),
2. check that the DR, Gross column, TOTAL row ($80,750) of the bank statement spreadsheet ledger summary matches the sum of all DR, Gross rows of the bank statement spreadsheet ledger (the same process may also be used to verify GST/HST and Net amounts), and
3. check that the opening balance of the corporate bank account ($0.00 in our data set) plus total CRs ($115,440) minus total DRs ($80,750) is equal to the bank account closing balance at the end of the fiscal period ($34,690). Note that this reconciliation is included at the bottom of the bank statement spreadsheet ledger summary presented in Example 4, above.

4. Journalized Summary

In this step of the spreadsheet-based method, the goal is to move from the “single-entry” system used in the bank statements, ledger, and ledger summary, and transition into the “double-entry” system of financial accounting. This is a necessary step as our next course of action is to complete the corporation’s trial balance. In many ways, the journalized summary is analogous to recording the corporation’s transactions in a general journal.

To formulate the journalized summary, two compound journal entries are extracted from the bank statement spreadsheet ledger summary created in Section 3, above. The first journal entry encapsulates cash inflows into the corporation’s bank account, and the second entry represents cash outflows exiting the same account (see Example 5, below).

Example 5 – Journalized Summary

Journal Entry 1 begins by recording the cash inflows of $115,440 DR, which are allocated to their respective CR financial accounting classifications: Revenue (+), Due to Shareholder (+), Supplies Expense (-), and GST/HST Payable (+). Each item in Journal Entry 1 corresponds to a single row in the bank statement spreadsheet ledger summary (see Example 4 and Example 5, above, at indicators “m”, “a”, “b”, “c”).

Journal Entry 2 follows by recording the corporation’s cash outflows of $80,750 CR, and corresponding debits for the expenses Accounting Fees (+), Bank Fees (+), Insurance Expense (+), Supplies Expense (+), Payroll Expense (+), and Vehicle Expense (+). Journal Entry 2 also records adjustments against the liability accounts Due to Shareholder (-), and GST/HST Payable (-).

Six of the nine items in Journal Entry 2 correspond to a single row in the bank statement spreadsheet ledger summary (see Example 4 and Example 5, above, at indicators “d”, “e”, “f”, “j”, “l”, and “n”), whereas three journal entry items – including Payroll Expense, Vehicle Expense, and GST/HST Payroll – are composed of two or more rows from the bank statement spreadsheet ledger summary. From this observation, it is apparent that the journalized summary is not just a transition from the single-entry to the double-entry accounting system, it is also a process of consolidating bank statements transactions into financial accounting account classifications.

As the corporation must also account for excise tax collected and expended, both journal entries in Example 5, above, utilize the Net column amounts – not Gross – from the ledger summary illustrated in Example 4. Similarly, both journal entries record the GST/HST columns of the ledger summary through “GST/HST Payable” liability adjustments (see Journal Entry 1, indicator “a”, and Journal Entry 2, indicator “d, g, k”). By recording both Net and GST/HST columns, the journal entries reconcile to the summarized ledger’s Gross column, and account for all cash inflows and outflows from the corporation’s bank account.

Data verification is achieved in the journalized summary step through the following methods:

1. check that each row of the journalize summary has an indicator (a-m) that corresponds to its respective row in the bank statement spreadsheet ledger summary. This will ensure some form of accounting category verification, but is not conclusive in respect to transposed amounts,
2. check that the bank statement spreadsheet ledger summary CR, GST/HST column, Total row ($13,000) is equal to the journal entry one, GST/HST Payable CR of the journalized summary; and check that the bank statement spreadsheet ledger summary DR, GST/HST column, Total row ($1,188.41) is equal to the journal entry two, GST/HST Payable DR of the journalized summary,
3. check that the DRs and CRs of each journal entry in the journalized summary are balanced as required under the double-entry accounting system, and
4. check that the journalized summary Total rows ($196,190 and $196,190) are equal to themselves, and to the “Total CR” ($115,440) plus “Total DR” ($85,750) rows in the bank statement spreadsheet ledger summary.

5. Modified Trial Balance

As described above, the journalized summary symbolically represents a recording of the corporation’s consolidated transactions into the general journal. Therefore, by necessity, the process of preparing the modified trial balance must incorporate an analogical update of the general ledger. Simply put, keeping the general ledger up to date is crucial for the accuracy of balance sheet accounts which carry forward values from prior periods.

The general ledger update is accomplished in two parts: (1) by carrying forward the balance sheet account balances from the beginning of the accounting period, and (2) by adjusting the year-beginning balances against the transactions captured in the journalized summary (see Example 6, below).

Example 6 – Modified Trial Balance

In the modified trial balance, Accounting Category accounts must be re-ordered according to financial accounting conventions: in the descending order of assets (“A”), liabilities (“L”), equity (“EQ”), revenues (“R”), and expenses (“EX”). The “ID” column is added to enable visual verification of proper account ordering and to assist with the filtering of relevant accounts when preparing financial statements.

In Example 6, above, the fictional corporation used for the example data set is in its first year of operation; therefore, all “Year-Beginning” column amounts, or carry forward amounts, are zero. Given this fact, it is easy to observe how journal entries drafted in Example 5 impact the modified trial balance illustrated in Example 6: the “Change” DR and CR columns record the transactions encapsulated in the two journalized summary journal entries.

Once all Example 5 journalized summary items are input into their corresponding modified trial balance Change DR or CR columns, the final result will resemble Example 6. For example, the Cash $115,440 DR from Journal Entry 1 in Example 5, above, is entered into the “Cash” row of Example 6 (see Example 5 and Example 6, indicator “m”). Similarly, the Cash $80,750 CR from Journal Entry 2 in Example 5 is also entered into the Cash row of Example 6 (see Example 5 and Example 6, indicator “n”). This results in a “Year-Ending” Cash balance of $34,690 DR represented in the Cash row, Year-Ending DR, column of Example 6.

Data transposition in the modified trial balance step may be verified through the following procedures:

1. check that each row of the modified trial balance has an indicator (a-m) that corresponds to its respective row in the journalize summary. This will ensure some form of accounting category verification, but is not conclusive in respect to transposed amounts,
2. check that Change column, Total DR and CR rows of the modified trial balance are identical ($196,190 and $196,190) as required under trial balance preparation rules in financial accounting,
3. check that Total DR and CR rows of the journalized summary ($196,190 and $196,190) are identical to the Change column, Total DR and CR rows of the modified trial balance ($196,190 and $196,190), and
4. check that the Year-Ending column, Cash row balance ($34,690) is identical to the year-ending bank statement balance in the corporation’s bank account statements.

6. Adjusted Trial Balance

In the spreadsheet-based method, trial balance preparation is best separated into two procedural steps, modified and adjusted, retaining the traditional two-step process used in financial accounting. As described in Section 5, above, the “modified” trial balance process incorporates both an update of the general ledger and a preliminary balancing of all financial accounts. The “adjusted” trial balance process, on the other hand, incorporates three processes: (1) an update of the general journal via the journalized summary, (2) an update of the general ledger via the adjusted trial balance, and (3) a final balancing of all financial accounts. As such, the adjusted trial balance procedure used in the spreadsheet-based method is essentially identical to the procedure taught in traditional financial accounting. Examples of common adjustments made during the adjusted trial balance process of the spreadsheet-based method include the accrual of payroll remittances payable, corporate taxes payable, and capital asset amortization.

Prior to updating the adjusted trial balance, all adjusting journal entries must be recorded in the journalized summary. Continuing with our fictional corporate data scenario, three hypothetical year-end adjusting journal entries are found to be needed:

1. a $1,500 Payroll Payable liability must be recorded as only $4,000^[2] of the required $5,500 in payroll remittances were sent to the Receiver General by year-end,
2. a $4,000 Corporate Tax Payable liability must be recorded to account for taxes payable at year-end, and
3. a $100 adjustment must be made to the Common Shares account as, after a review of the corporation’s minute book, it is determined that the shareholder was to pay the corporation $100 for 100 common shares.

The three events and their corresponding adjusting journal entries are recorded in the journalized summary as illustrated in Example 7, below.

Example 7 – Journalized Summary Updated to Include Adjusting Journal Entries

The main points to take away from the presentation of adjusting journal entries in Example 7 are that the new journal entries: have differentiating identifiers, “adj-#” versus “#”; are recorded separately to enable clear journal entry notes; and are visually separate from the preliminary non-adjusting journal entries as each set of journal entries are summed separately for verification purposes (see Example 7, “Total” versus “Total Adjustments” rows).

After the year-end adjusting journal entries are recorded in the journalized summary, the entries are transposed to the “Adjustments” DR and CR columns of the adjusted trial balance (see Example 8, below) with new rows added as necessary.

Example 8 – View 1, Adjusted Trial Balance Showing All Columns

As observed in Example 8, above, the adjusted trial balance is simply an extension of the modified trial balance presented in Section 5. Therefore, for procedural simplicity, the modified and adjusted trial balances reside in the same spreadsheet “sheet” with non-relevant columns hidden when not in use. Given such structure, two “Views” of the adjusted trial balance are possible: (1) the modified and adjusted trial balances with all columns showing (see Example 8, above), and (2) the adjusted trial balance with only “adjustment” columns showing (see Example 9, below).

Example 9 – View 2, Adjusted Trial Balance Showing “Adjustment” Columns Only

There are several benefits to working with the condensed View 2 format including:

1. hiding non-relevant columns reduces the risk of unintentional data modification,
2. using a condensed View reduces time required to visually locate a particular data row or column, and
3. the condensed View better fits the computer screen especially when working with larger data sets.

Data transposition in the adjusted trial balance process may be verified with the following steps:

1. check that the adjusting journal entry identifiers, “adj-#”, from the journalized summary have been added to their corresponding rows in the adjusted trial balance, and that the adjusting amounts were copied correctly to their designated rows,
2. check that Adjustments column, Total DR and CR rows of the adjusted trial balance are identical ($5,600 and $5,600) as required under trial balance preparation rules in financial accounting, and
3. check that the Total Adjustments DR and CR rows ($5,600 and $5,600) of the journalized summary are identical to the Adjustments column, Total DR and CR rows ($5,600 and $5,600) of the adjusted trial balance.

7. Statement of Income

Once all year-end adjusting journal entries have been recorded, the “Final” revenue (“R”) and expense (“EX”) accounts in the adjusted trial balance are transposed into the standardized financial accounting income statement format (see Example 10, below).

Example 10 – Statement of Income

It is important to emphasize that Accounting Category names chosen in the ledger summary – and maintained throughout the journalized summary and trial balance processes – must match the revenue and expense account names used in the statement of income and balance sheet. Consistent account naming is essential for tracing spreadsheet-based procedures from one end to the other; that is, tracing the bank statements to the financial statements, or vice versa. For a general resource of account names used in corporate tax and financial statement preparation, the General Index of Financial Information (“GIFI”) name listing is available on the Canada Revenue Agency’s (“CRA”) website. Choosing standardized GIFI names from the outset will enable easy matching with T2 corporate tax returns prepared from the adjusted trial balance data.^[3]

Concerning the statement of income layout, Example 10 includes a reconciliation of the corporation’s Retained Earnings account, the balance of which is fed into the equity section of the balance sheet in Section 8, below.

8. Balance Sheet

After the statement of income is complete and retained earnings calculated, the corporation’s balance sheet is compiled from the asset (“A”), liability (“L”), and equity (“EQ”) accounts listed in the Final DR and CR columns of the adjusted trial balance. The retained earnings amount shown on the balance sheet is transposed from the statement of income created in Section 7, above. For our example data set, the result of this process is illustrated in Example 11, below.

Example 11 – Balance Sheet

The spreadsheet-based method described in this documentation assumes the reader has some experience with preparing corporate statements of income and balance sheets; however, if more direction is needed, please refer to a financial accounting textbook for further guidance.

9. Worksheets and Other Record Keeping Concerns

As the spreadsheet-based method is a solution of last resort – implying that the corporation has kept poor records, or the records have been destroyed – it is essential that the method include strict worksheet and record keeping procedures. With this strategy in mind, all procedures described up to this point are designed to facilitate the creation of worksheets, or records for future reference. Furthermore, the worksheets are designed to be traceable through each procedural step and from either direction; that is, from bank statements to financial statements, or vice versa.

The spreadsheet-based method requires the following documents be retained, at minimum:

1. complete set of corporate bank statements for each compilation period,
2. bank statement spreadsheet ledger,
3. bank statement spreadsheet ledger summary,
4. journalized summary (including year-end adjusting journal entries),
5. modified and adjusted trial balances,
6. financial statements (statement of income and balance sheet),
7. T2 corporate tax return (if prepared),
8. GST/HST excise tax return (if prepared), and
9. T4 payroll return (if prepared).

Additional items to retain in the corporation’s records, if applicable, may include:

• complete set of corporate credit card statements or other financial account statements,
• receipts for expense items that do not source to the corporation’s bank account (for instance receipts for cash purchases or items purchased for the corporation using the shareholder’s personal account),
• (re)assessments detailing the breakdown of penalties and/or interest in CRA program accounts,
• T5 investment returns,
• notes to the financial statements, and
• updates to the corporation’s minute book.

As to which format to store the corporation’s records in, there are really only two options: physical paper, or digital. Paper has the benefit of being tangible and may be preferred by certain clients for meeting discussions and review, and for their personal corporate records. The cons of keeping tangible records for the client is the risk of fading – which is especially a concern for certain receipt types – humidity, accidental water damage, or fire, and the fact that physical records occupy space and use more resources to produce.

Digital records can typically avoid the usual environmental forces that damage paper, they are easily copied, occupy little space, and use little resources. That being said, the security of client records is always a concern, and the risk that documents may be unlawfully accessed or stolen must be managed with heightened data security procedures. Backup of digital client records is also essential, with backups stored in different physical locations, such as one copy in the cloud and one copy on-site in a secure medium. Digital records should also be kept in Portable Document Format (“PDF”) in addition to the chosen proprietary spreadsheet format due to potential future software compatibility issues.

Finally, as the main purpose of worksheets and records are to assist in tracing the accounting procedures followed by the preparer, it is essential that naming of any digital files properly identifies the following details: the compilation period or year, the corporation’s name, a brief description of the record, and some indication of the order and quantity of records created for the compilation period (such as “1 of 9”).

10. Appendix

I. Completing a GST/HST Return with Spreadsheet-Based Method Data

---

Notes:

[1] Eleven “Category types” exist in our sample data set: Accounting Fees, Bank Fees, Business Insurance, Lease Payment, Payroll Cheque, Payroll Remittances, Revenue, Supplies, Transfers In, Vehicle Fuel, and Withdrawals.

[2] Note that the Year-Ending $64,000 DR Payroll Expense account balance in Example 8 is made up of $60,000 in Payroll Cheques and $4,000 in Payroll Remittances, as shown in Example 4 (identifiers “h” and “i”), above.

[3] For the purposes of the spreadsheet-based method documentation, in an attempt to universalize the application of the method’s procedures we have decided to use account naming commonly found in accounting textbooks versus the CRA’s GIFI system. However, if the final goal is to prepare corporate T2 returns, for account traceability, the method requires the use of GIFI naming in the Accounting Category classifications.