Preparation of accounting records for a service business using spreadsheets

Jackson Smith commenced a new business for residents in a nearby retirement village. The business provides shopping, gardening, car washing and errand services, in return for payment for Jacksons time. The business commenced on 1 January 2022, and is called At Your Service. Jackson will operate the business as a sole trader.

During the first month of operations, the following events and transactions occurred. Note: all deposits/payments were made into/from the business bank account.

Date	Detail
1 Jan	Jackson contributed $10,000 of his personal funds into the business bank account.
1 Jan	The business paid $1,800 for business insurance, for the period 1 January 2022 31 December 2022.
2 Jan	The business paid Snap Printing Ltd $300 for some business flyers to be printed. Jackson visited each home in the retirement village that afternoon, and handed out his flyers.
3 Jan	The business purchased (and paid for) a new computer from Harvey Norman, for $2,800. The computer will be used for business purposes.
5 Jan	The business purchased (and paid for) some gardening equipment from Bunnings, for $2,400. The equipment will be used for business purposes.
6 Jan	Jackson agreed to provide services to a client during February, when they will be away on holidays. Jackson received $300 cash (for the services to be provided in February), which was banked into the business bank account.
9 Jan	Jackson purchased (and paid for) some stationery for the business, for $30.
10 Jan	Jackson provided services to three clients in the retirement village, and received a total amount of $300 cash, which was banked into the business bank account.
15 Jan	Jackson provided services to five clients in the retirement village, and received a total amount of $600 cash, which was banked into the business bank account.
19 Jan	The business received an advance payment of $450 from a client, for services that will be provided in February. The money was banked into the business bank account.
24 Jan	Jackson provided services to six clients in the retirement period, and received a total amount of $1,200 cash, which was banked into the business bank account.
25 Jan	Jackson withdrew $1,000 from the business bank account for some personal expenses.
30 Jan	The business received an invoice from Telstra, for telephone and internet used by the business in January. The amount payable on the invoice is $60, and payment is due by 21 February 2022.
30 Jan	A client was invoiced $300 for services provided up to 31 January (services were provided on credit terms). The client is required to pay the invoice by 31 March 2022.
30 Jan	Jackson paid $162 for some groceries, for his family, from the business bank account.

Additional information as at 31 January 2022:

• Depreciation to be recognised in the financial statements up to 31 January 2022 is: $15 for the computer, and $20 for the gardening equipment.
• Ignore any GST.

Required:

i) In an Excel worksheet, prepare journal entries for the January 2022 transactions (including any adjusting entries needed as at 31 January 2022). Include dates, references and narrations. Paste a copy of your journals into your assignment file (in Word). (4 marks)

ii) Prepare T-accounts in an Excel worksheet. Post all of the above journal entries to the T-accounts. Include the date and a journal reference for each entry. Total all of the T-accounts to determine their balances at the end of January 2022. You are not required to prepare closing entries in this part of the question. Paste a copy of your T-Accounts into your assignment file (in Word). (1 mark)

iii) Prepare the Adjusted Trial Balance in an Excel worksheet as at 31 January 2022. Use formulas to generate all of the figures in the Adjusted Trial Balance from the balances in the T-Accounts. Paste a copy of the normal view and also the formula view of this report into your assignment file (in Word). (1 mark)

iv) Prepare the income statement, balance sheet, and statement of changes in equity for January 2022 in an Excel worksheet. Use formulas to generate all of the figures in the financial statement reports from the Adjusted Trial Balance. Paste a copy of the normal view and also the formula view of these reports into your assignment file (in Word). (3 marks)