Bank deposit without any record to track

Hi,

I am a new bookkeeper for an Academy. 

This school has no bookkeeper from last year end til now. When I took over this job, I really had a hard time to solve some problems such as deposit on bank statements.

Here is the situation. My employer collect the tuition revenue, supplies revenue, after school revenue and registration fee...etc. from the parents, but he only gave the parents a hand writing receipt on a receipt book (including duplicate paper, one is for parent and he keeps another one). Then he will deposit all the cheques into the bank randomly without any record that shows how many cheque he deposit and any detail about the cheques. He deposited the cheques once he thinks the amount is large enough to be deposited, so the bank statement will show a lump sum amount for all the cheques that he deposit on the day he deposited. However, he did not have any record to show what cheques he deposited to the bank. The only resources I have are the receipt book and a excel spreadsheet that shows how many students we have now and what their fees are. Therefore, I know how much revenue do we earn from every students (from the excel spreadsheet), and I know when they made payments (from the receipt book, but it is going to be very time consuming to track for every students.)

here comes a question. Every parents paid on the different time and the amount is different for every parents because we have 2% discount if they paid in full and 3% discount if they have brothers or sisters. When I am doing bookkeeping for the school, I cant tell who made a payments to us based on the lump sum deposit amount from the bank statement. In our bank statement, we have too many deposits, so it is hard for me to track the payments based on receipt book. Especially, my employer asks me to separate the payments for every students. I can record all the payments separately from every student based on the receipt books, but the amount is not going to match the deposit amount on the bank statements because I dont have the detail of the deposits (there is no deposit book).

0) How can I reconcile the deposit amount on the bank statement to the accounting software?

1) What is the proper procedure to do the bookkeeping in this situation? I dont have lots of bookkeeping experience before.

2) Should I record revenue based on the spreadsheet and then enter payments based on the receipt book? in this way, it is going to be hard for me to reconcile the amount with the bank statement.

3) or Can I just record all the entries based on the deposit amounts on the bank statements? then make journal entry to separate the revenues. in this way, I am not able to separate the revenue to different students.

4) One more question, My employer's children do not pay the tuition. How can I do the bookkeeping for his children?? Should I set up a discount account (100%) for his children?? 

please help me with these questions. Thank you

Today is Apr. 20th, 2015. I have to do the bookkeeping from last year end which is Jun 30th, 2014 to Apr. 20th, 2015. 

Yen

Parents
  • 0

    My suggestion - enter all the revenue per student as per the Excel spreadsheet as a pay later sales invoice.

    Create an account in the 1000 acct nbr called 'Cash/Chq Clearing' (you will need to class this as 'bank'). Then enter all the payments made for each student as per receipts into this new account using the date shown on the receipt - I'm guessing each receipt will have a student name. When entering the payment see if the receipt matches any of the revenue - if pmt is less than revenue see if the difference equals 2% or 3% discount. If not then enter the payments as a 'Deposit' for now (on the receipt journal you will see an icon that looks like a safe, when you hold your mouse over it will say 'enter deposit'.)

    When you have done this then going by your bank stmt enter all the deposits made by a general journal entry and debit the bank account and credit the new acct 'Cash/Chq Clearing' - use the same date as the bank stmt shows for each deposit. Hopefully when you have done this you will be able to pull up your 'Cash/Chq Clearing' general ledger you can see a correlation between the receipts which are entered as debits and the bank deposit entries which are entered as credits.

    This will at least get your revenues recorded per student and the payments recorded per student. You can figure out later what the balance outstanding is for each student.

    Request that deposit slips be made up for bank deposits and to show each chqs even if to just run a calculator tape and write down the names beside each amount on the tape.

    I have some client that will also record the date and bank deposit amount on the bottom of the last receipt of the batch of receipts that was deposited.

  • 0 in reply to Smith and Co

    Hi Smithco,

    Thank you for your suggestion. I have another issue that need to be solve before I entering all these transactions.

    Currently, our school's year end is Jun30th. Most of students paid their tuition and other fees before Jun 25th (which is before year end). When I start to work for this company, we are in the process of transitioning  to simply accounting (the previous bookkeeper use QuickBooks). Therefore, I create a new company file in Sage 50 after I took over this job. Now I dont have the financial statements for 2014 year end (Jun 30th, 2014) because the accounting firm has not provided this information to us (he is too busy for filing personal tax). Therefore, I am not able to enter the opening balance now.

    If I follow your suggestion, I have to enter all the Revenue as historical transactions which is ok. However, most of the students already made the tuition payments for the school year 2014-2015 that will start in Sep, 2014 before Jun 25th, 2014 (this is the due date of tuition, but I can see there are still many tuition payments later than this date.....). Therefore, I should assume most of student's tuition payments already have been recorded before the 2014 year end. Now Should I just enter the rest of the payments in simply accounting first? when I get the financial statement, I can then breakdown the deposits that the previous bookkeeper entered to different Revenue account??? (the previous bookkeeper has no experience in accounting. He is my employer's friend, and he used to work part-time for doing bookkeeping for the school.)

    It is a little messy here. Your suggestions are pretty good, but I am not sure how to record transactions or payments because of the 2014 year end and historical transactions. I am a little bit confuse how to do it in the right way. Thank you for your help.

    Yen

  • 0 in reply to Yen

    So it is my impression you will start at July1/14 as the current year end transactions to process. Leave the History as open so that you can enter the historical transactions later when you get the June30/14 year-end trial balance. Continue with posting any transactions dated July1/14 or later. If it is a payment for the tuition that is dated as July1/14 or later then enter those as 'deposits'.

    unfortunately if I remember right you cannot enter 'deposits' (prepayments) using the receipt module in history mode - you will need to enter those as invoices with negative amounts.

    You should have a list of students that are accepted into the 2014/2015 year so you can go ahead and create these as students in your Acct Receivable module. What is the date of the tuition invoices that was sent out for the 204/2015 year? If last year's date then it is part of historical transaction. If these invoices are dated July1/15 or later then you can go ahead and enter these invoices.

    I'm guessing that you still have the QuickBook so you can print off the detailed receivable listing to have a list of invoices and payments.

  • 0 in reply to Smith and Co

    Sorry this line sounds confusing --> "unfortunately if I remember right you cannot enter 'deposits' (prepayments) using the receipt module in history mode - you will need to enter those as invoices with negative amounts." I meant when you are entering invoices/payments as historical transactions for the previous year. You can use Chq Nbr as invoice nbr - it is best to also use the wording ChqNbr prior to the number.

Reply
  • 0 in reply to Smith and Co

    Sorry this line sounds confusing --> "unfortunately if I remember right you cannot enter 'deposits' (prepayments) using the receipt module in history mode - you will need to enter those as invoices with negative amounts." I meant when you are entering invoices/payments as historical transactions for the previous year. You can use Chq Nbr as invoice nbr - it is best to also use the wording ChqNbr prior to the number.

Children
  • 0 in reply to Smith and Co

    Hi Smothco,

    I really appreciate your quick answer. Could I ask one more question about this topic?

    I am thinking that I will start doing what you thought me start from next year. The reason is my company does not give the parents any invoices. Our deposit include too many things like tuition, after school program, field trip,donation.....etc. It is almost impossible for me to track all these payments, especially there is no receipt for some of the payments. Last year's book was awful because the bookkeeper did not have a lot of accounting experience.

    If I record all the deposit on the bank statement to School Revenue account, then breakdown the deposits based on what they have on the spreadsheet (I believe that we must have too much unknow payments sitting on our bank account). Will our company have any problem if later we are going to be audited? The payments for next school term is already start, so I want next year's book being very clear. That why I think I will have no time to figure out all the payments. I need to finish 2014-2015 bookkeeping as soon as possible now in order to be prepared for next year. Also, there are lots of mistakes that I need to correct now; for example, I believe that we claim too much CPP on 2013-now. The payroll clerk put the RRSP deduction amounts on the CPP box, so our employee and employer CPP is more than we should be paid.

    Thank you.

    Yen

  • 0 in reply to Yen

    """If I record all the deposit on the bank statement to School Revenue account, then breakdown the deposits based on what they have on the spreadsheet"""

    You should do this the other way around - first record all deposits from Excel sheet as 'Paid Sales Invoice' which will credit your School Revenue account and debit your Cash Clearing account. Then look at your bank stmt and post each deposit showing on bank stmt as a credit to Cash Clearing account and a debit to Bank acct. As you have said there are no invoices sent out for Tuition etc so the receipts should be used as an invoice with pmt..

    Any unknown deposit can then be posted thru General Journal entry as 'Unknown Deposit' using the bank stmt deposit date and debit bank acct, credit School Revenue ( or Revenue - Other).

    Your excel spreadsheet will be a backup document for Audit purpose.

    Your 2013 payroll will have already been filed with CRA and 2013 T4s done so you will not be able to change those. Same with the 2014 T4s. If RRSP were lumped into CPP amounts then you would have received a PIER report from Canada Revenue indicating the CPP overpayments and request for explanation.

    Hope this helps.

  • 0 in reply to Smith and Co

    Hi Smothco,

    Your answer helps me a lot. Thank you.

    When I enter the tuition revenue, do I need to enter those payments as pay later in "creating invoice". Then go to "receipt payment" to pay the invoices. This is two steps

    or I can use cheques as my payment method in "creating invoices"?This is only one step

    Both method would Dr. Cash Clearing Cr. Revenue

    Which one is better for accounting purpose?

    Thank you,

    Yen

  • 0 in reply to Yen

    In my opinion it is better to enter the invoice separately from the payment. The invoice date is rarely the same date as the payment is processed. If by chance they are the same date then yes you can do it in one entry. It should be important to keep your invoicing in the right time period (date) and the same for the payments.

  • 0 in reply to Alwyn

    You would not be classified a as cash basis company so yes you should invoice as pay later and use receipt module for payments. Generally you would invoice all students for the tuition as of one date and then accept payments as they come. You have indicated that there is early payment discount so obviously there is a certain date already set as the invoice date with the discount set within a certain number of days from the invoice date.

    In some case what you could do is enter the invoice using the set invoice date WHEN you have received the payment. Example Invoice set date is July1/15 and payment from one student was Aug1/15 so when you received the Aug15/15 payment then go ahead and make up the invoice as a Pay Later for that student using the set invoice date of July1/15 and then post the payment using the receipt module dated Aug15/15. This is useful IF there are a lot of students who indicate they will renew for following year but end up NOT renewing their tuition and helps to eliminate having to post several credit invoices against the original invoices.

    Ideally the invoice date for the following year's tuition should be set in the following fiscal year - not the current year as otherwise they become unearned revenue - meaning the revenue is not for the current year but rather for the following year. So all the payments that were made in the current fiscal year should be recorded as prepaid deposits - to do this first create a 2000 account nbr called 'Prepaid Tuition Deposit'  then go into Setup--> Settings -> Receivables, then select Linked Accounts, and in the last box called 'Deposit and Prepaid Orders' enter the 2000 account nbr you just created.

    Then in the following year when you apply the payment against the invoice this will automatically come out of the prepaid Deposit account.

    For accounting purpose you always enter the sales in the month it was earned in and the expenses are posted in the month the expense incurred in. To always be posting these items using payment date as the invoice  date is incorrect and only reserved those allowed to do so such as farmers and commissioned sale person as per CRA.

  • 0 in reply to Smith and Co

    Hi Smithco,

    Thanks again. Your answer is always so helpful. Also, thanks for Alwyn, too.

    Our school does not give any invoice for the student because we have too many programs to choose, so my employer thinks it is going to be hard to invoices the parents one by one (everyone has different fees and the fee will depended on their choice of courses) Is there any easier way to solve this problem?

    Because I am doing the last year's bookkeeping, can I just enter all the payments as cash basis instead of going through A/R? Then I will start doing the right process for next fiscal year.

    Could you please explain more about the prepaid tuition deposit? Now I already receive some tuition for next school term. What I did is to record those payments as "unearned revenue". I create unearned revenue accounts based on our revenue accounts. For example, i have tuition, supply and lunch for Revenue account. then I create same account call "Unearned revenue - tuition, supply and lunch...etc" under current liability. When I record the payment, I debit cash clearing and credit "unearned revenue - ....." I believe that this prepaid tuition deposit is a little bit different than the sales order's deposit, right? What is the proper way to record this unearned tuition? Our due date for the tuition is before our school's fiscal year end, so all the tuition will be unearned revenue for this fiscal year.

    I am thinking to record the tuition through sales module by using "pay later" - debit AR and credit unearned revenue. Then I can go to receipt module, enter the payments by debit Cash clearing and credit AR? The date will use the cheque date for the invoice and receipt because there is no invoice date. (or should I just use the set date as you recommended? some students paid half year before the due date..........)

    Based on what you mentioned above, is there an easier way to track for those tuition fee in simply accounting?

    I am planning to create a excel spreadsheet to calculate the amortization (10 straight line basis because our school year include 10 months) for every month based on the accounts. For example, if we receive tuition $50000, then every month I will amortize $5000 to move from unearned revenue to revenue account in the future.

    Yen

  • 0 in reply to Yen

    Hi - you say you are a bookkeeper and as such you are responsible to the Academy for looking after their books as per CRA's requirement. I would suggest you look up their website and follow the requirements. It would be so easy to just credit unearned revenue and debit bank for all transactions as one entry but fortunately this is not allowable. Please read all the information that you can in the CRA's link below to educate yourself in what is required.

    www.cra-arc.gc.ca/records

  • 0 in reply to Smith and Co

    Hi Smithco,

    Thank you! This is a very useful document.

    Yen