UNDERSTANDING ACCOUNTING PROCESS & ACCOUTANT’S ROLE IN ACCOUNTING SYSTEMS

XERO – Part II

Accounting Information Systems

Spring 2018

STUDENT INSTRUCTIONS

In this exercise, You are the chief accountant working for a firm in its second year of operation. Your firm operated for only a partial year in 2016. In 2017, you anticipate a loss, although in 2018 you will be able to turn a profit because you raised prices during 2017.

On December 1, 2017 you went live with a new accounting system, Xero. You have a new clerk working for you who is prone to making mistakes and doesn’t know how to fix them. Your job is to do your normal month-end accounting duties, get the data into the new accounting system with the clerk’s assistance, and find and fix the errors made by the clerk. (Depending on the size of the firm, in practice you might prepare the tax reports in addition to the tasks below; but for this exercise, these are provided for you.) The clerk will be assisting you by doing normal bookkeeping and clerical work, leaving you to do the accounting tasks. Keep in mind that you are human and you make mistakes as well. Some of the mistakes you will find and fix may well be “your own” (i.e., the accountant’s!)

It is December 2017, the 12th month of your fiscal year. Last month you did your normal duties and reconciled the General Ledger (G/L). All subsidiary ledgers correctly tied to the general ledger, all the bank accounts reconciled, and all tax reports agreed with your G/L. At November 30, 2017 you had a clean set of books. Any errors you may find are in December. For help with technical terms, you should check with the “Controller”, your instructor.

You will be granted “read” access to the company file in Xero to allow you to research information. All corrections and changes will be made in your Excel file titled Financial Schedules for Students (Xero II)

CHECK OPENING BALANCES IN NEW SYSTEM (November 30th)

  1. The first order of business is to get the balances from your old accounting system into your new accounting system. You have prepared a journal entry (see December Report & Schedules) with these balances and given them to the clerk to record in the new system as of November 30, 2017. The clerk has done this and reports that the conversion journal debits and creditsbalances agree with your journal entry, but the hash total did NOT agree. You need to help her determine why the hash total is off and correct the error.
  2. Compare your account balances in your journal entry with the trial balance of the clerk run after the conversion (dated 12/1/2017). (See Financial Schedules for Students file, the worksheet titled Trail Balance as of 12 1 17). Hint compare account number, name and amount.
  3. Identify any corrections that need to be made.
  4. Record this journal entry in proper form in the General Journal worksheet on the Financial Schedules for Students file. Label the GJ and GJ1; and include a short description.
  5. Post and update the General Ledger worksheet in the Financial Schedules for this journal entry
  6. All posting references should be indicated in both the General Journal and the General Ledger.

END OF YEAR – 12/31/17

2. It is now the end of the month (12/31). You have been provided with the unadjusted trial balance and general ledger detail as of 12/31 and must go through each account in the General Ledger and ascertain whether the balance is correct as of the end of December 31, 2017. For the Balance Sheet Accounts you will use Reconciliation, bank statements, Aging reports,tax reports and so forth to determine whether the balance are correct. For the Income Statement accounts you will rely on reasonableness tests; that is, is it reasonable for that account to have that kind of balance (debit or credit), tax reports, supporting schedules, and size of balance (large or small)?

  1. Start with the two cash accounts (operating and payroll cash accounts) and prepare bank reconciliations for those bank accounts. Bank statements for December are provided (see December Reports and Schedules), as well as the bank reconciliations for November(See November Reports and Schedules) to use as your guide. You will also be able to review check registers and deposits in each bank account in Xero directly. Any necessary adjustments should be made in proper format in the General Journal in you Financial Schedules for Students file and then posted to the General Ledger and the General Ledger updated. All posting references should be indicated.
  2. You have been informed that Customer 10 will not be paying the remaining balance on their account. You should write-off this account as a Bad Debt write-off. Record your entry to the general journal and post to the general ledger. Remember that you have an Allowance for Doubtful Accounts. Once write-off is complete you need to prepare an A/R Aging Report for December like the one in November based on Invoice Date.
  3. Your firm uses 1.5% of the balance in Accounts Receivable as the Allowance for Doubtful Accounts. Once you have established the correct balance in A/R make sure that the Allowance for Doubtful Accounts equals 1.5% of A/R by making an entry in the General Journal. Post the entry to the General Ledger. Be sure to include Journal references.
  4. A/C 9100, A/C 1230, A/C 1240: Interest Income, Interest Receivable, and Note Receivable should tie (this means that the General Ledger amounts should agree with the amounts in the amortization table) to Amortization Table 1which names ‘Your Company’ as the Lender. Be sure to consider when interest is earned vs. when it is received. (See December Reports & Schedules.)
  5. The auditors have observed the physical count of inventory, and together you have worked up a list of inventory. This list indicates that there are
  6. 8 units of item 10,
  7. 0units of item 20,
  8. 1units of item 30, and
  9. 19units of item 40 on hand.

First, verify the inventory balances in the Xero inventory account. To see this go to the Reports area in Xero and select the “published” tab. Open the Inventory Item Detail report to see if the inventory items balance in Xero agrees with this inventory count. If they do not, then next check the Inventory Purchases and Sales to see if all inventory items have been correctly accounted for. After identifying and correcting any posting errors, if discrepancies remain, then adjust inventory to agree with the auditors count, using the Shrinkage and Waste account (A/C 4700) and the General Journal to make your entry. Post to the General Ledger, being sure to include Journal reference. Prepare an inventory reconciliation like the one in November.

  1. Prepaid Insurance should be expensed at the rate indicated on the invoice. To see the invoice, you should locate the appropriate bill in Xero and open the attached invoice. Determine how much of the insurance needs to be expensed and which accounts are affected. Prepare a General Journal entry to expense the proper amount. Post the entry, with proper posting references to the General Ledger.
  2. Look at the Fixed Asset Subsidiary Ledger provided in the Financial Schedules for Students. Compare the total in the “Cost” column to the total in A/C 1500 – Furniture and Fixtures. (Furniture and Fixtures is a type of Fixed Asset). These two numbers should agree. If the two numbers are not the same, then you should review the subsidiary ledger and general ledger for account 1500 for any current month activity in fixed assets. For any purchases, you can see the purchase in Xero and locate the related invoice. Be sure to check the posting on the bill. Once you identify the error, correct the appropriate ledger or ledgers. On the Fixed Asset Subsidiary Ledger, compare the total in the “Accumulated Depreciation” column to the total in the general ledger A/C 1505 Accumulated Depreciation. If these two numbers do not agree, review the General Ledger to see what amounts were posted to the General Ledger from other journals. Prepare a Fixed Asset Report for December like the one prepared in November. Net Book Value should equal Cost – Accumulated Depreciation.
  3. Open the Xero Purchases area. The unpaid amount represents your current a/p balance. However, you are not convinced that your clerk has recorded everything correctly. For all items in the purchases area open each item and review any attached supporting documentation.Check the dollar amount and the use of the proper General Ledger Account. If you find an error, identify the correcting entry. Record the entry in the General Journal and post the entry to the General Ledger. Prepare an A/P aging report like the one in November, taking into account any information in Xero and any manual adjustments you have made in your Financial Schedules file.
  4. A/C 2150 – A/C 2180; A/C 8300 - 8500 and A/C 6500 Payroll Tax Liabilities, Payroll Tax Expenses, Salaries and Wages: Tie these accounts to the payroll tax reports (see Payroll Taxes Report.)

Here is a bit more background to help you to better understand question 2i:

The net amount paid to an employee consists of gross salary less required tax and voluntary withholdings. Certain taxes are paid partially by the employer (hits an expense account) and partially by the employee (is removed from the employee's paycheck and remitted to the government, but first booked to a liability account). The related Xero items are outlined below:

Item / Employer Paid Portion - Acct # / Employee Paid Portion - Acct #
Federal Income tax Withheld / N/A / 2150 Federal Withholding Payable
FICA (Social Security) / Employer pays 1/2: 8400 Taxes - FICA Expense / Employee pays 1/2: 2160 FICA Payable
Medicare / Employer pays 1/2: 8450 Taxes - Medicare Expense / Employee pays 1/2: 2165 Medicare Payable
FUTA (Federal Unemployment) / Employer pays 100%: 8300 Taxes - FUTA Expense / N/A
SUTA (State Unemployment) / Employer pays 100%: 8500 Taxes - SUTA Expense / N/A
Health Insurance / Employer pays 25%: 5800 Employee Benefits / Employee pays 75%: 5800 Employee Benefits (reduces employee expense)

These are the primary accounts you need to reconcile in the problem, along with cash and salaries. Study the Payroll Tax Reports .pdf file to understand what the FUTA and SUTA amounts should be. Note also that employers, right before cutting payroll checks to employees, move the amount of the payroll run from their operating cash account to a payroll cash account. It's the payroll cash account against which the checks are written.

  1. A/C 9000, A/C 2190, A/C 2220 and A/C 2700: Interest Expense, Interest payable, Current and Notes Payable should be tied to the amortization table for First Bank: Amortization Table 2 (December Reports and Schedules). Be sure to verify that amounts are properly classified between current and long-term liabilities.
  2. A/C 2200 FIT Payable. The tax rate is 35% but only if you manage to have a net income for the year. There will be no taxes if you have a loss. Leave this for last; you will need to figure your net income before you can calculate income tax. If you do have income, then you should record the appropriate federal tax entry.
  3. A/C 2710 is a line of credit from First National Bank. Your company is authorized to draw up to $1,000,000 on this line of credit. Any amount drawn down must pay interest to the bank at the rate of 2.0% per month (see the terms on the line of credit document in December Reports & Schedules); the balance is drawn down and remains outstanding and unpaid to the bank. The $60,000 has been outstanding since July1, 2017. (Use Simple Interest)
  4. If you did everything correct in steps 1a – 2k, then you should have nothing to do for this step; however you might want to review these items. You should review the income statement accounts and note that there were a few unusual items.
  5. Accounting fees has two entries from the Purchase Journal, and you know that you have only had your inventory counted. No other accounting work has yet been done.
  • Depreciation expense has an entry from the Payroll Journal. This strikes you as odd. Also, you know that you book depreciation once a year.
  • Salaries and Wages has no entry for December.
  • You were surprised to see a transaction in one of the Unemployment tax expense accounts (FUTA or SUTA). You thought this was already expensed fully.
  • For the above accounts, you should go back to the journals and trace the entries to learn whether there is a posting error. If so, you should fix these errors using the General Journal.
  1. Use Miscellaneous Income and Expense if you need to make a small off-setting journal entry.
  2. Once the General Ledger is cleaned up, you are ready to prepare the Adjusted Trial Balance.

What to turn in: NOTE: All answers must be in Excel

You should submit the following for a grade:

  1. Financial Schedules - (Start with the original file provided and add your adjustments based on the activities above to each schedule where appropriate)
  2. Post-Adjusted Trial Balance – 12/31/17 – Final TB after all adjustments
  3. General Ledger – updated with your new journals.
  4. General Journal – showing all your journals. Each journal numbered as GJ1, GJ2, etc and with a description.
  5. Reports and Schedules ( You will need to prepare these schedules yourself for the month of December, using November’s statements as a guide)
  6. Bank Reconciliations -12/31/17
  7. Operating Bank Account
  8. Payroll Bank Account
  9. A/R Aging Report – 12/31/17
  10. Inventory Reconciliation – 12/31/17
  11. Fixed Asset Report – 12/31/17
  12. A/P Aging Report – 12/31/17

Each of the above items should be a separate worksheet in the Excel workbook (the Financial Schedules workbook with additional worksheets added) turned in.

STUDENT INSTRUCTIONS - Xero Part II - S18.docx1 | Page