Preparation of accounting records for a retailer - using Xero

‘Need for Speed Cycle Centre’ began business on 1 March 2021. The business is a partnership, between Mr and Mrs Speedy. The business will sell bicycles via an online store. The business is not registered for GST.

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

Date:	Details:
1 March	Mr and Mrs Speedy each deposited $20,000 into the business bank account ($40,000 in total).
1 March	The business purchased bicycles (10 small bicycles at $90 each, 10 medium bicycles at $95 each, and 20 large bicycles at $110 each) from Top Notch Bikes Ltd on 30-day credit terms.
1 March	The business paid Elite Real Estate Ltd $2,000, for business premises rent for the period 1 March – 30 June 2021.
1 March	The business paid GIO Insurance $2,400, for business insurance for the period 1 March 2021 – 28 February 2022.
1 March	The business purchased office equipment for $3,500 from The Good Guys, paid for from the business bank account.
6 March	The business sold 4 bicycles (2 medium bicycles at $195 each, and 2 large bicycles at $220 each) to Mr Jacobs, via its online store. The customer paid via direct payment on the same day.
12 March	The business paid Top Notch Bikes Ltd for the purchases made on 1 March 2021, by electronic funds transfer.
14 March	The business paid Snap Printing Ltd $600 for some advertising flyers to be printed and distributed to home letterboxes in the local area.
15 March	The business sold 8 small bicycles to a Happy Kids Preschool, at $190 each, on 30-day credit terms.
18 March	The business purchased stationery from Officeworks for $80 (all of the stationery was used during the month of March).
22 March	Top Notch Bikes Ltd had a sale on their bikes, so the business purchased bicycles (20 small bicycles at $80 each, 20 medium bicycles at $85 each, and 30 large bicycles at $95 each) from Top Notch Bikes Ltd on 30-day credit terms.
24 March	The business sold 5 bicycles (1 small bicycle at $190, 2 medium bicycles at $195 each, and 2 large bicycles at $220 each) to Mr Smith, via its online store. The customer paid via direct payment on the same day.
25 March	The business sold 20 bicycles (5 small bicycles at $190 each, 10 medium bicycles at $195 each, and 5 large bicycles at $220 each) to PCYC Sydney, on 30-day payment terms.
31 March	The business received an invoice from Direct Freight Express Ltd, for freight costs to deliver the bicycles to customers (from the Need for Speed Cycle Centre business premises) for the month of March 2021. The amount payable is $740, and payment is due on 15 April 2021.
31 March	The business received an invoice from Telstra, for business telephone and internet costs for the month of March 2021. The amount payable is $180, and the due date for payment is 26 April 2021.

Additional information:

• Depreciation expense to be recognised for the month of March is $80.

• In relation to prepaid expenses, Mr and Mrs Speedy 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.

Mr and Mrs Speedy have come to you for assistance, as they would like to use Xero to prepare their accounting records, but are not sure how to set this up. They also do not have any knowledge of the perpetual inventory system and average-cost method (which is used in Xero), and would like you to explain to them how the cost of goods sold, gross profit and value of closing inventory is calculated using this system and costing method. As you prepare to meet with Mr and Mrs Speedy, you need to complete the following tasks.

Required:

i. Prepare an Excel worksheet for each of the bicycle lines for March (one for the small bicycles, one for the medium bicycles and one for the large bicycles) using the perpetual inventory system and average-cost method, to keep track of the number of bicycles purchased, bicycles sold, bicycles on hand, cost of goods sold and gross profit made. Paste a copy of your worksheets into your assignment file (in Word). (5 marks)

ii. Set up a Xero file for Mr and Mrs Speedy’s business - ‘Need for Speed Cycle Centre’. When you are setting up the files, some points to note include:

• The business is not registered for GST.
• When adding the business bank account in Xero, use the account name “Business Bank Account” followed by your student number – eg. “Business 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 three different inventory items (small bicycles, medium bicycles, and large bicycles). The standard selling price of each bicycle is: $190 for a small bicycle, $195 for a medium bicycle, and $220 for a large bicycle. Mr and Mrs Speedy would like Xero to be able to track (or manage) inventory.
• 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 2021 in the Xero file, including any adjusting entries that may be needed as at 31 March 2021. 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’).

Generate the following reports for March 2021, and include a copy of each report in your Assignment file (in Word):

1. Trial Balance
2. Journal Report
3. Account Transactions Report
4. Profit and Loss
5. Balance Sheet
6. Aged Payables (detailed)
7. Aged Receivables (detailed)
8. Inventory Item Summary

iii. Review the Profit and Loss Statement and Balance Sheet for the business’s first month of operations. Prepare some discussion notes for your meeting with Mr and Mrs Speedy (this can be in bullet point form – outlining key aspects of what you plan to discuss with them) . Your discussion notes should address the following:

• How has the business performed?
• How well have Mr and Mrs Speedy managed inventory levels? What should Mr and Mrs Speedy consider when determining how many bicycles to purchase at a time?
• Provide two suggestions that you have for Mr and Mrs Speedy, to help them as they grow their business in the coming months. These should be your own personal suggestions, given your knowledge and understanding of the business.