Preparation of accounting records - using Xero

This question requires you to use Xero accounting software (30-day free trial version, available at: https://www.xero.com/au/).

Dylan Grimes commenced a new watch repair business, called GrimesTimes. For the purposes of this activity, the business is not registered for GST.

The business commenced on 1 March 2022, and Dylan operates the business as a sole trader. The business provides watch repair services to clients for service fee. The business did not initially sell any watch related items like new watches or straps - however, as the business has been growing very successfully in the first year of operation, Dylan has decided to expand the business from 1 March 2023. The business will now sell watch related items, in addition to providing watch repair services. The business will firstly stock two products lines: (1) cloth straps and (2) leather straps. Dylan plans to expand the product range slowly over the coming months.

The adjusted trial balance at 1 March 2023, the start of the financial year, contained the following balances:

Grimes Times: Adjusted Trial Balance

Grimes Times: as at 1 March 2023

Grimes Times:

Grimes Times:

Grimes Times: Bank

Grimes Times: Prepaid insurance

Grimes Times: Prepaid rent

Grimes Times: Office equipment

Grimes Times: Less: Accumulated depreciation

Grimes Times: Dylan Grimes, Capital - 1 March 2023

Grimes Times: Total

The following transactions occurred during March 2023. Note: all payments were made from the business bank account, and all money received was deposited into the business bank account.

Date:: 1 March Details:: The business purchased and paid for 20 cloth straps at $9 each from its supplier, Strapz Ltd.

Date:: 5 March Details:: Dylan repaired Mr. Raines' watch and received $400 cash, which was banked into the business bank account.

Date:: 6 March Details:: Dylan serviced Mrs Brown's watch for $800, and sold two cloth watch straps to her at $30 each. The sale was made on 10-day credit terms

Date:: 9 March Details:: Mrs Brown paid $860 into the business account, in relation to the money owing from the sale made to her on 6 March.

Date:: 10 March Details:: The business purchased 40 leather watch straps at $50 each from its supplier, Leather Goods Ltd on 14-day credit terms.

Date:: 11 March Details:: The business purchased (and paid for) stationery from Big W for $90 (all of the stationery was used during March).

Date:: 12 March Details:: The business sold 3 cloth straps at $30 each to Mr Knobel, sold on 7-day credit terms.

Date:: 13 March Details:: The business purchased 10 cloth straps at $12 each from its supplier, Strapz Ltd on 10-day credit terms.

Date:: 14 March Details:: The business paid Leather Goods Ltd for the purchases made on 10 March, by electronic funds transfer.

Date:: 15 March Details:: The business paid Capital Advertising Ltd $1200 for some advertising brochures to be delivered to homes that are nearby to the business.

Date:: 18 March Details:: The business sold 3 leather straps at $90 each, to Mr Polo. The sale was made on 21-day credit terms.

Date:: 20 March Details:: The business received $200 from Mr Polo, being part-payment for the straps that he purchased on 18 March.

Date:: 21 March Details:: Leather Goods Ltd offered GrimesTimes a special price of $40 for each leather strap purchased (a saving of $10 per leather strap), with a minimum order of 50 straps. The business purchased 50 straps at $40 each from Leather Goods Ltd, purchased on 14-day credit terms.

Date:: 25 March Details:: The business sold 5 leather straps at $90 each, to Mr Schulz. The sale was made on 3-day credit terms.

Date:: 27 March Details:: The business provided watch repair services to Mrs Coughlan, for $1000, on 14-day credit terms.

Date:: 28 March Details:: The business received the $450 from Mr Schultz, being payment for the watch straps that he purchased on 25 March.

Date:: 28 March Details:: The business purchased 15 cloth straps at $13 each from its supplier, Strapz Ltd, on 7-day credit terms.

Date:: 29 March Details:: The business sold 11 cloth straps to Rob's Jewellers at $30 each, 2 leather straps at $90 each and provided watch repair services for $1500. The sale was made on 30-day credit terms.

Date:: 30 March Details:: The business received an invoice from Optus, for business telephone and internet costs for the month of March 2023. The amount payable is $250, and the due date for payment is 30 April 2023.

Date:: 31 March Details:: The business paid Strapz Ltd $120 that was owing in relation to the straps that were purchased on 13 March.

Additional information:

Dylan Grimes would like to prepare monthly financial statements, and hence would like to prepare journal entries at the end of each month to recognise the monthly expense.

In relation to the balance of $4,500 in the 'Prepaid insurance' account at 1 March 2023, this relates to prepaid insurance for the period 1 March 2023 - 30 May 2023.

In relation to the balance of $7,000 in the 'Prepaid rent' account at 1 March 2023, this relates to prepaid rent for the period 1 March 2023 - 31 July 2023.

The business will use the perpetual inventory system and average-cost method. For the purposes of the inventory records in this scenario, the cloth straps that are purchased from Strapz Ltd are treated as one product line, and the leather straps purchased from Leather Goods Ltd are treated as another product line.

Depreciation expense for the office equipment for March 2023 needs to be included. The office equipment has an expected useful life of 10 years.

Dylan Grimes has come to you for assistance, as he is considering whether he should continue using Excel to prepare his accounting records or start using Xero. As you prepare to meet with Dylan, you need to complete the following tasks.

Required:

i) Prepare an Excel worksheet for each of the product lines for March 2023 (one worksheet for the cloth straps, and one for the leather straps) using the perpetual inventory system and the average-cost method, to keep track of the number and dollar values of units purchased, units sold, units on hand, cost of goods sold and gross profit made.

ii) Dylan has asked you to prepare the following, which would be needed for the business's manual accounting records in Excel:

1. In an Excel worksheet, prepare the journal entries for the March 2023 transactions (including any adjusting entries needed as at 31 March 2023). Include dates, references (journal numbers) and narrations.
2. Prepare T-accounts in an Excel worksheet and enter the opening balances from the adjusted trial balance as at 1 March 2023. Post all of the above journal entries to the T-accounts. Include the date and a journal reference (journal numbers) for each entry. Total all of the T-accounts to determine their balances at the end of March 2023. You are not required to prepare closing entries.
3. Prepare the 'Adjusted Trial Balance' in an Excel worksheet as at 31 March 2023. Use formulas to generate all of the figures in the 'Adjusted Trial Balance' from the balances in the T-Accounts. (

iii) Dylan has also asked you to set up and prepare the accounting records for the business in Xero, so that he can compare the manual system that he has been using in Excel with Xero. When you are setting up the Xero file, some points to note include:

• The business is not registered for GST.
• When adding the business bank account in Xero, use the account name "Bank Account" followed by your student number - eg. "Bank Account - 112 333 444". You can select from any of the available banks, and make up your own BSB and Account number.
• The business will sell two different inventory items initially (cloth straps and leather straps). The standard selling price of each item is: $30 for a cloth strap and $90 for a leather strap. Dylan Grimes would like Xero to be able to track (or manage) inventory.
• You will need to enter the opening ledger account balances as at 1 March 2023 into Xero, from the Adjusted Trial Balance provided. To enter opening balances in Xero, go to 'Accounting', then 'Advanced', then 'Conversion Balances'.
• If required, new 'accounts' may be added in the 'Chart of Accounts', based on the needs of the business.

After setting up the Xero file, record all of the transactions for the month of March 2023 in the Xero file, including any adjusting entries that may be needed as at 31 March 2023.

When recording transactions in Xero:

• use the 'invoicing' function when selling goods to customers;
• use the 'bills' function for transactions involving purchases and business expenses incurred;
• use the 'receive money' and 'spend money' functions when recording any owner contributions ('receive money') and owner withdrawals ('spend money') and/or recording any interest revenue/interest expense.
• use 'journal entries' to record any adjusting entries needed (for example, in relation to depreciation, unearned revenue, prepaid expenses).

Generate the following reports for March 2023. make a Word file and include a screenshot of each report in your Word file:

• General Ledger (detailed) (note: when you run the General Ledger report, you will have an option to 'Export Detailed General Ledger to Excel'. You can then copy and paste a copy this detailed report from Excel into your assignment file (in Word))
• Trial Balance
• Profit and Loss
• Balance Sheet
• Aged Payables (detailed)
• Aged Receivables (detailed)
• Inventory Item Summary

iv) Review other reports that are available in Xero. Select two additional reports (other than those in part (ii) above) that you think Dylan should review regularly as he continues to operate and grow his business. Include a screenshot of each of these two reports in your Word file. Underneath each screenshot, explain how/why you think that this report could help Dylan evaluate the performance of his business and the effectiveness of the strategic decisons he has implemented.

v) During your meeting with Dylan Grimes (discussing all of the above), Dylan expressed hesitancy to use Xero. He has asked you for your thoughts about the pros and cons of using a manual accounting system (such as in Excel) compared to a cloud-based system like Xero. Given your experience preparing accounting records and preparing reports manually in Assessment Task 2 and this assessment task, and your experience preparing accounting records and reports in Xero in this assessment task, discuss the pros and cons of each. your response within your Word file.