Assignment Task:
Financial statements, analysing business transactions and financial statement analysis
Andrew Baird started a new sole trader business called A1 Outdoor Training in the financial year ending 30 June 2015. The business' focus is on group personal training in local parks and beaches 7 days per week. Andrew has asked his accountant, BWS Accounting, to complete financial statements and write a business report on the financial health of A1 Outdoor Training in its first year of operation.
A1 Outdoor Training has the following account balances in their accounting worksheet as at 31 May 2015.
|
Assets
|
Liabilities
|
Equity
|
|
|
Cash at bank
|
Accounts Receivable
|
Equipment
|
Accounts
Payable #
|
Loan
|
Income
|
Expenses #
|
Description
|
|
Opening
balances
|
7,000
|
20,000
|
45,000
|
2,000
|
45,000
|
30,000
|
- 5,000
|
# Fuel expenses
|
The following transactions occurred in the last month of operation June 2015:
|
June
|
Transaction Detail
|
|
3
|
Paid cash to a local marketing firm for business cards and advertisement in the local paper $1,450.
|
|
5
|
Sent an invoice for personal training services performed at a team building day for a corporate client Mal's Fast Food $1,500
|
|
6
|
In order to help the business out Andrew Baird, injected $20,000 in cash into the business bank account of A1 Outdoor Training.
|
|
7
|
A van was purchased to transport the personal training equipment to different locations around the region. The complete cost of the motor vehicle was $30,000. $10,000 was paid from the business bank account and the balance was financed via a loan.
|
|
11
|
A large local retailer paid $4,000 in cash for a weeks' worth of personal training with A1 Outdoor Training.
|
|
16
|
Paid $1,000 towards petrol account with Franks Fuel.
|
|
19
|
Andrew is taking a trip to Hawaii with friends and withdrew $4,000 from the business account to pay for his flights.
|
|
21
|
Paid $710 for membership to Personal Trainers Australia organisation.
|
|
26
|
Made loan repayment instalment of $12,000 (Includes $3,000 interest expense)
|
|
30
|
Received a cheque from Mal's Fast Food for $500 towards the invoice sent on the 5th of June. The note contained with the cheque said the balance would be paid in July.
|
Additional information:
Cash received from personal training fees for 2014-2015 financial year was $14,500
Cash paid for fuel expenses for 2014-2015 financial year was $4,000
Opening cash balance 1 July 2014 was $0
Required
Part A: Worksheet
In the ‘Assignment 2 Template' complete an accounting worksheet for A1 Outdoor Training for the 2015 year. Using the sheet labelled ‘Worksheet' identify A1 Outdoor Training's business transactions for June and construct an accounting worksheet.
Notes
1. Illustrative example 4.4 on pg. 126 of the text should be used as a guide to the format required.
2. Opening balances as at 31 May should be used as your starting point in your accounting worksheet but additional accounts may be required.
3. In your worksheet you must demonstrate the use of the following Excels functions:
- 'sum'
- bordering
- cell merging
- wrap text
4. Ensure that you ‘sum' each side of your worksheet to prove the accounting equation.
5. For guidance on using the Excel functions and features required for this assignment refer to the Armelenos Excel tutorials located in your online study guide on the ACC100 interact site.
Part B: Financial statements
Using the same Excel file that you used for Part A, prepare the following financial statements of A1 Outdoor Training from the accounting worksheet for the 2015 financial year:
- Income statement.
- Statement of changes in equity.
- Balance sheet.
- Cash flow statement.
Notes
1. Illustrative example 4.5 on pp. 126 and 127 of the text should be used as a guide to the format required for the income statement and balance sheet. Illustrative example 6.8 on the bottom of pg. 245 of the text should be used as a guide to the format required for the statement of changes in equity. Illustrative example 7.16 on the bottom of pg. 282 of the text should be used as a guide to the format required for the cash flow statement. I have attached file with images of this book.
2. Use the additional information listed in the question above to assist you in preparing the cash flow statement.
3. Each financial statement should be prepared using the named worksheets provided in the Excel template.
4. In your financial statements you must demonstrate the use of the following Excel functions:
- formula for each subtotal and total.
- bordering
- cell merging
- wrap text
PART C: Ratio analysis
In the same Excel template that you used for PART A and B but on the sheet labelled ‘Ratio Analysis', complete the columns labelled:
- ‘formula' with the written formula for each ratio provided (first one completed for you as an example).
- ‘2014' with the ratio calculation (Strictly use formula only by pulling figures from the financial statements sheets. (Do not type ratio answer directly into the table)
Notes
1. In completing ‘Ratio Analysis' Sheet in the Excel template, you must demonstrate that you have used the following Excel functions:
- '=/+/-'
- bordering
- merging cells
- wrap text
PART D: Business report
Write a report to Andrew Baird on behalf of BWS Accounting on the financial health of A1 Outdoor
Training for the 2015 financial year. Your report should contain the following details:
- Executive summary
- Description of operations
- Evaluation and comparison of ratios calculated in Part C for the following areas (a minimum of two ratios for each category below).
o Profitability
o Asset efficiency
o Liquidity
o Capital structure
- Limitations of the analysis
- Conclusion, including recommendations on areas of improvement for A1 Outdoor Training.
Notes
1. In writing your report, you should refer to the online meeting in week 2, which will focus on a report writing presentation provided by the Academic Literacy Learning and Numeracy (ALLaN) team.
2. Your report should be between 2-3 pages, excluding cover page, contents page and references.