Monthly Accounting and Excel Spreadsheets

Share it with your friends Like

Thanks! Share it with your friends!


In the past year I have been engaged to do monthly accounting and bookkeeping for several clients. Although my rates are higher than a normal part-time bookkeeper that a company may hire, sometimes it makes sense to hire your accountant do also perform interim financial statements. Because I am familiar with the work and detail needed when comes time to perform the Year End Financial Statements and Income Tax Returns, it is “BETTER” and “EASIER” getting information while it is fresh in everybody’s head.

Overall, professional fees for the year might be a little highe, but some clients can appreciate the benefit of having their accountant “in the loop”. I make sure (or at least try to the best that I can) that the client is “COMPLIANT” with all reporting requirements. This would include reminders for:

* Payroll remittances, generally due on the 15th of the following month (but not in all cases)
* Provincial Sales Tax remittances, generally due on the 20th of the month, or the weekday BEFORE if falls on a weekend
* Federal Goods and Services Tax remittances, generally due on the last day of the following month of the reporting period.

It also helps making proper remittance calculations, opposed to estimating amounts .. When or if you pay too much one month, then get a big refund next month, and your estimates are all over the place, chances are some government official might want to wonder why.

I have no desire to replace an internal bookkeeper or become a controller for a client’s corporation. My preference, is that if I can train your staff and set-up a system – that’s what they probably need. I go in once, set it up – then only do the Year Ends and work once a year. If I’m going to do a monthly account … *sure.. money’s nice… but I just don’t have the time to make a big project out of monthly… now. Before I agree to engage in monthly accounting, I inform my clients that I will do whatever I can .. to work less .. and work more efficiently. That sometimes includes the owners getting involved and coding checkstubs, deposit books, providing copies of certain invoice and statements, and the like. Sometimes, if a system is already on another accounting system, say Quickbooks, I will discontinue that program and do it on the program of MY choice, that I decide to get the job fast most efficiently for me – at the lowest amount of time.

I will not lowball monthly accounting estimates … existing and potential clients are always asking me how much I will “DO” their monthly accounting for, and it doesn’t matter what client they are – the answer is always the same.

>> I will charge you by the hour for as many hours as it takes. I will try to reduce the hours over time, but you can expect larger bills at the beginning of the job during development of better systems and tweaking of certain processes.

However, sometimes I am quite lenient with my intial billings. For instance, it may turn out that I have set up a system that seemed suitable at one point, but later seemed inefficient – and I create an entirely new system to save time. I would not charge my client the time to recreate a new system. If the initial setup time is a lot of hours, I sometimes will just amortize the ‘set-up’ time and hours spent, over 9 completed monthlies that I would provide in the future. This way, it’s not such a burden to my client’s cash flow.

I will not discuss specifics about client engagements and processes

… but in the recent months, I think I can generally describe a situation that has helped me become more efficient with my monthly preparation processes – and that’s using Excel Spreadsheets.

It’s much easier to recap numbers on an excel spreadsheet, and record the TOTAL summary than record every transaction. This is ideal for such cases as recapping deposits, daily cash reports, cash disbursement journals, etc etc.

I use TABS.

* Cash Receipts and Cash Disbursements .. I get my client’s to prepare, on excel themselves – or on a posting sheet and just recap the figures into a Tab Worksheet in excel as a journal entry. The template keeps track of growing accounts, divisional chart of accounts, etc etc.

* In another Tab, I will recap all bank statement items, and extend them under different columns. I create formulas to automatically calculate the GST ITC included in the total payment, or in a neighboring column create a formula to automatically extend a figure under a specific column. For instance

>> I could calculate the gst included:
Purchase 5000.00 >> I might create a formula for GST ITC ..[@round( (+5000/1.14*0.07),2)]

>> I could calculate the expense portion:
based on the calculation, or if I know (coded in the cheque stub) the difference will be in another formula [+5000-GST ITC = expense]

>> Or if there is a majority of one or two types of coding and extension, I could combine the GST calculation and extend under a calculated column heading.. e.g. under the Purchase column
[@iif(+Acell=”purchase”, +5000, 0) I might do this for every column, and copy the formula’s down the page.

* Excel and New tab worksheets are ideal for accounts receivable and accounts payable list >> I maintain my own list. I request that my client just keep the unpaid vendor or customer invoices separately – until paid. At the end of the month, make a list of the unpaid bills. What I do .. is that I create a little spreadsheet and it grows every month. My columns would be:
dept- account – vendor – G/L Acct – Balance Opening – Payments – Adjustments – Invoices – Balance Ending

>> I would create formulas for all adjustments and invoices, that the GST is broken down with expense beside the Balance ending, in a journal entry form. When I go through the checkstubs, they should already be coded by the client and marked “A/P” if they are paying a bill from previous month, already on the list. Entering half the cheques to 1 liner accounts payable saves time, than having to extend and break it down between different accounts.
>> It’s also great for keeping a list of coded cheque NAMES and G/L accounts selected by the client .. so, in future – the entries are consistently coded to the same accounts in the general ledger.

And, All of this is “SAVED AS” a file called .. 1800HART-Client-Monthly-YYYY-MM.xls

In the following month, I make a COPY … and leave the above file untouch and only work with the new month’s spreadsheet.

In each month, I modify or rename the TAB names .. in UPPER caps, or LOWER caps as I go along to remind me which ones are complete, and which ones are not. I also use and name a blank tab called “DONE TO THE LEFT OF HERE” and drag it over tabs, to keep the ones left to do on the right side.

If you use Excel a lot .. you probably know what I’m talking about. One day, I’ll put up a good working template in my “Stuff for the taking” section.

Take care.


Write a comment