Preparation of accounting records for a service business using spreadsheets

Kent Kingsley has recently commenced a new legal practice, providing legal services in relation to family, housing and criminal law. The legal practice commenced on 1 January 2023, and is called Kents Legal. Kingsley 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: 1 January
Detail: Kingsley contributed $21,000 of his personal funds into the legal practice.

Date: 1 January
Detail: Kents Legal paid $2400 for insurance for the legal practice. This insurance will cover the period 1 January 2023 31 December 2023.

Date: 1 January
Detail: Kents Legal paid $1500 in rent. This rent will cover the period 1 January 2023 - 31 March 2023.

Date: 2 January
Detail: Kents Legal purchased advertising flyers for $400.

Date: 3 January
Detail: Kents Legal purchased office equipment for cash for $5,000.

Date: 10 January
Detail: Kent agreed to represent a client in court who is facing traffic offence charges. The services (court appearance) will be provided in February. Kent has received $3200 in advance which he has banked into the business bank account.

Date: 15 January
Detail: Kent provided legal services for a client who is getting divorced. The payment was for $700 which Kent banked into the business bank account.

Date: 18 January
Detail: Kent withdrew $700 from the business bank account to cover some personal expenses.

Date: 20 January
Detail: Kent received a bill from Advanced Tax Accountants, in relation to some tax advice he received regarding the formation of his legal practice. The amount payable on the invoice is $800 which Kent has to pay by 8 February 2023.

Date: 24 January
Detail: Kent provided legal services to a client who is buying a house. Kent received $1000, which he banked into the business bank account.

Date: 25 January
Detail: Kent provided legal services to a client involved in a rental dispute on credit terms for $750. The payment is due from the client by 18 February.

Date: 27 January
Detail: Kent paid Advanced Tax Accountants in relation to the 20 January invoice.

Date: 31 January
Detail: Kents Legal received an invoice from Optus for telephone and internet used by the business in January. The amount payable on the invoice is $100 with the payment due by 28 February 2023.

Date: 31 January
Detail: Bank fees of $11 were deducted form the business bank account for January 2023.

Date: 31 January
Detail: Interest revenue of $2 was added to the business bank account for interest earned up to 31 January 2023.

Additional information as at 31 January 2023

• Depreciation to be recognised in the financial statements up to 31 January 2023 relates to office equipment. It is expected to last for 50 months.
• The business will prepare monthly financial statements.
• Ignore any GST.

Required:

i) In an Excel worksheet, prepare journal entries for the January 2023 transactions (including any adjusting entries needed as at 31 June 2023). Include dates, references (journal numbers) and narrations. (3 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 (journal numbers) for each entry. Total all of the T-accounts to determine their balances at the end of January 2023. You are not required to prepare closing entries in this part of the question. (1 mark)

iii) Prepare the Adjusted Trial Balance in an Excel worksheet as at 31 January 2023. Use formulas to generate all of the figures in the Adjusted Trial Balance from the balances in the T-Accounts. (1 mark)

iv) Prepare the income statement, balance sheet, and statement of changes in equity for January 2023 in an Excel worksheet. Use formulas to generate all of the figures in the financial statement reports from the Adjusted Trial Balance. (3 marks)

v) For the purposes of this part of the question, assume that the financial statements prepared in part (iv) were for an entire year (ending on 31 January 2023). In a new worksheet, prepare closing entries that would be required to close the accounts as at 31 January 2023. (1 mark)

vi) Making reference to the finacial statements you prepared in part iv) identify one highlight and one potential area of concern for the month of January for Kent's Legals' performance as a business. Explain why they are highlights/areas of concern. (1 mark)

Please also refer to the 'Requirements' section below for additional submission and spreadsheet requirements.