Preparation of accounting records using spreadsheets The trial balance for Petes Handyman Services (a small business operated by Pete Smith, as a sole trader) as at 30 June 2019 is as follows (after closing entries prepared on 30 June 2019) Pete's Handyman Services Trial Balance as at 30th June 2019 Debit Credit $ $ Bank 3,600 Accounts receivable 800 Prepaid expenses 300 Supplies 400 Equipment 1,800 Less Accumulated depreciation 800 Motor vehicle 12,000 Less Accumulated depreciation 3,600 Accounts payable 300 Unearned revenue 600 Pete Smith, capital 13,600 Pete Smith, drawings Service revenue Advertising Depreciation Insurance Interest expense Motor vehicle expenses Repairs Supplies expense Telephone Total 18,900 18,900 During July 2019, the following transactions took place Date Detail 1 Jul Purchased a tool trailer for $4,200 Pete took out a business loan to pay for the trailer (repayable within 12 months) Interest is charged at 8 p a to the loan account, and monthly repayments are $400, due on the last day of each month 2 Jul Paid $300 owing to a supplier in relation to June 2019 expenses, from the bank account 4 Jul Provided handyman services to Mrs Jones on credit, for $700 7 Jul Received $500 in the bank from a customer in relation to services provided in June 2019 8 Jul Provided handyman services to Mrs Smart for $600 Mrs Smart had paid for these services in June 2019 9 Jul Purchased supplies from Bunnings for $350 (paid from the bank account) 10 Jul Provided handyman services to Mr Budd on credit, for $1,300 12 Jul Withdrew $2,500 from the bank account for personal expenses 14 Jul Provided handyman services to Mrs Hunt for $1,250 Mrs Hunt paid by electronic funds transfer 15 Jul Purchased fuel for the business vehicle, for $80 (paid from the bank account) 16 Jul Received $1,300 in the bank from Mr Budd in relation to services provided on 10 July 22 Jul Provided handyman services to Mr Diaz on credit, for $500 23 Jul Received $2,600 from a customer, Mr Hatter, for services to be provided in August 23 Jul Entered into a contract with Hit Radio, for radio advertising for August October, for $1,200 $1,200 was paid from the bank account 23 Jul Received $700 in the bank from Mrs Jones in relation to services provided on 4 July 26 Jul Withdrew $2,500 from the bank account for personal expenses 26 Jul Provided handyman services to Mr Catt for $280 Mr Catt paid by electronic funds transfer 30 Jul Paid $320 for business insurance, for the month of August 31 Jul Interest is charged on the business loan, and the $400 loan repayment for July is paid from the bank account 31 Jul Received an invoice from Mower Repairs Ltd, for repairs to equipment, for $320 The due date for payment 14 August 31 Jul Received an invoice from Telstra, for July telephone, for $150 The due date for payment 20 August Additional information as at 31 July 2019 Depreciation to be recognised for July is $80 for the equipment, and $100 for the vehicle There were supplies on hand on 31 July of $260 In June 2019, $300 was paid for insurance relating to the month of July 2019 Ignore any GST Required i) Prepare journal entries for July 2019 transactions listed above (including any adjusting entries) Include dates, references and narrations ii) Prepare T accounts in an Excel spreadsheet, and enter the opening balances from the Trial Balance from 30 Jun 2019 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 the July 2019 You are not required to prepare closing entries iii) Prepare the Adjusted Trial Balance in an Excel spreadsheet as at 31 st July 2019 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 When preparing the balance sheet, classify assets and liabilities as current or non current v) Calculate and evaluate the businesss current ratio and debt ratio

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jul 28, 2024

Preparation of accounting records using spreadsheets The trial balance for Petes Handyman Services (a small business operated by Pete Smith, as a sole trader) as

Preparation of accounting records using spreadsheets

The trial balance for Petes Handyman Services (a small business operated by Pete Smith, as a sole trader) as at 30 June 2019 is as follows (after closing entries prepared on 30 June 2019):

Pete's Handyman Services
Trial Balance
as at 30th June 2019
	Debit	Credit
	$	$
Bank	3,600
Accounts receivable	800
Prepaid expenses	300
Supplies	400
Equipment	1,800
Less: Accumulated depreciation		800
Motor vehicle	12,000
Less: Accumulated depreciation		3,600
Accounts payable		300
Unearned revenue		600
Pete Smith, capital		13,600
Pete Smith, drawings
Service revenue
Advertising
Depreciation
Insurance
Interest expense
Motor vehicle expenses
Repairs
Supplies expense
Telephone
Total	18,900	18,900

During July 2019, the following transactions took place:

Date	Detail
1-Jul	Purchased a tool trailer for $4,200. Pete took out a business loan to pay for the trailer (repayable within 12 months). Interest is charged at 8% p.a. to the loan account, and monthly repayments are $400, due on the last day of each month.
2-Jul	Paid $300 owing to a supplier in relation to June 2019 expenses, from the bank account.
4-Jul	Provided handyman services to Mrs Jones on credit, for $700.
7-Jul	Received $500 in the bank from a customer in relation to services provided in June 2019.
8-Jul	Provided handyman services to Mrs Smart for $600. Mrs Smart had paid for these services in June 2019.
9-Jul	Purchased supplies from Bunnings for $350 (paid from the bank account).
10-Jul	Provided handyman services to Mr Budd on credit, for $1,300.
12-Jul	Withdrew $2,500 from the bank account for personal expenses.
14-Jul	Provided handyman services to Mrs Hunt for $1,250. Mrs Hunt paid by electronic funds transfer.
15-Jul	Purchased fuel for the business vehicle, for $80 (paid from the bank account).
16-Jul	Received $1,300 in the bank from Mr Budd in relation to services provided on 10 July.
22-Jul	Provided handyman services to Mr Diaz on credit, for $500.
23-Jul	Received $2,600 from a customer, Mr Hatter, for services to be provided in August.
23-Jul	Entered into a contract with Hit Radio, for radio advertising for August - October, for $1,200. $1,200 was paid from the bank account.
23-Jul	Received $700 in the bank from Mrs Jones in relation to services provided on 4 July.
26-Jul	Withdrew $2,500 from the bank account for personal expenses.
26-Jul	Provided handyman services to Mr Catt for $280. Mr Catt paid by electronic funds transfer.
30-Jul	Paid $320 for business insurance, for the month of August.
31-Jul	Interest is charged on the business loan, and the $400 loan repayment for July is paid from the bank account.
31-Jul	Received an invoice from Mower Repairs Ltd, for repairs to equipment, for $320. The due date for payment 14 August.
31-Jul	Received an invoice from Telstra, for July telephone, for $150. The due date for payment 20 August.

Additional information as at 31 July 2019:

Depreciation to be recognised for July is $80 for the equipment, and $100 for the vehicle.
There were supplies on hand on 31 July of $260.
In June 2019, $300 was paid for insurance relating to the month of July 2019.
Ignore any GST.

Required:

i) Prepare journal entries for July 2019 transactions listed above (including any adjusting entries). Include dates, references and narrations.

ii) Prepare T-accounts in an Excel spreadsheet, and enter the opening balances from the Trial Balance from 30 Jun 2019. 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 the July 2019. You are not required to prepare closing entries.

iii) Prepare the Adjusted Trial Balance in an Excel spreadsheet as at 31^st July 2019. 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. When preparing the balance sheet, classify assets and liabilities as current or non-current.

v) Calculate and evaluate the businesss current ratio and debt ratio.