Preparation of accounting records - using spreadsheets

Harrison Smith commenced a new personal assistant business for people in a nearby retirement village. He will provide shopping and errand services, and meal preparation for clients (in their homes), in return for payment for his time.The business commenced on 1 June 2020, and is called 'At Your Service'.Harrison will operate the business as a sole trader.

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

Date

Detail

1 Jun

Harrison contributed $25,000 of his personal funds into the business bank account.

1 Jun

The business paid $600 for business insurance, for the period 1 June 2020 - 30 November 2020.

2 Jun

Harrison went to Officeworks, and paid $300 from the business bank account for some business flyers to be printed. He visited each home in the retirement village that afternoon, and gave each home one of his flyers.

4 Jun

The business purchased a new ipad from Harvey Norman, for $800. The ipad will be used for business purposes.

6 Jun

The business purchased a motor vehicle for $17,000 from Sydney Motors. $10,000 was paid from the business bank account, and the remainder was financed via a 12-month business loan.

7 Jun

The business paid $900 for motor vehicle insurance, for the period 7 June 2020 - 6 June 2021.

10 Jun

Harrison provided services to three clients in the retirement village, and received $300 cash, which was banked into the business bank account.

17 Jun

Harrison provided services to five clients in the retirement village, and received $600 cash, which was banked into the business bank account.

19 Jun

The business received an advance payment of $400 from a client, for services that will be provided in July. The money was banked into the business bank account.

20 Jun

The business paid the first loan repayment of $300 on the 12-month business loan.

22 Jun

The business paid $60 for fuel for the motor vehicle.

24 Jun

Harrison provided services to six clients in the retirement period, and received $900 cash, which was banked into the business bank account.

25 Jun

The business paid $780 from the bank account for some of Harrison's personal expenses.

30 Jun

The business received an invoice from Telstra, for telephone and internet used by the business in June. The amount payable on the invoice is $88, and payment is due by 12 July 2020.

30 Jun

A was client was invoiced $500 for services provided up to 30 June (services were provided on credit terms). The client is required to pay the invoice by 14 July.

30 Jun

Harrison withdrew $200 from the business bank account for personal expenses.

30 Jun

Interest of $23 was charged on the 12-month business loan, for the period up to 30 June.

Additional information as at 30 June 2020:

• Depreciation to be recognised in the financial statements up to 30 June 2020 is: $120 for the motor vehicle, and $25 for the ipad.
• Ignore any GST.

Required:

i)Prepare journal entries for the June 2020 transactions listed above in an Excel spreadsheet (including any adjusting entries needed as at 30 June 2020).Include dates, references and narrations.

ii)Prepare T-accounts in an Excel spreadsheet.Post all of the above journal entries to the T-accounts.Include dates and references for each entry.Total all of the T-accounts to determine their balances at the end of June 2020.

iii)Prepare the 'Adjusted Trial Balance' in an Excel spreadsheet as at 30^thJune 2020.Use formulas to generate all of the figures in the 'Adjusted Trial Balance' from the balances in the T-Accounts.

iv)Prepare the income statement, balance sheet, and statement of changes in equity in Excel. Use formulas to generate all of the figures in the financial statement reports from the 'Adjusted Trial Balance'.

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