​​  Financial statements, analysing business transactions and financial statement analysis [60 Marks] 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.   Cash Assets Accounts Receivable at bank 7,000 20,000 45,000 2,000 45,000 30,000 - 5,000 # Fuel Liabilities Equity    Equipment Accounts Payable # Loan Incom e Expense Descriptio s # Openin g balance s 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 5 Sent an invoice for personal training services performed at a team building 6 In order to help the business out Andrew Baird, injected $20,000 in cash 7 A van was purchased to transport the personal training equipment to expenses the local paper $1,450. day for a corporate client Mal's Fast Food $1,500 into the business bank account of A1 Outdoor Training. 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. training with A1 Outdoor Training. 11 A large local retailer paid $4,000 in cash for a weeks' worth of personal 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 21 Paid $710 for membership to Personal Trainers Australia organisation. 26 Made loan repayment instalment of $12,000 (Includes $3,000 interest 30 Received a cheque from Mal's Fast Food for $500 towards the invoice sent the business account to pay for his flights. expense) on the 5 th 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 [8.5 Marks]   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: o  'sum' o  bordering o  cell merging o  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 [17 Marks]   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: o Income statement. o Statement of changes in equity. o Balance sheet. o 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.   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: o formula for each subtotal and total. o bordering o cell merging o wrap text            PART C: Ratio analysis [10.5 Marks]        In the same Excel template that you used for PART A and B but on the sheet labelled 'Ratio Analysis', complete the columns labelled: o 'formula' with the written formula for each ratio provided (first one completed for you as an example). o '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: o   '=/+/-' o   bordering o   merging cells o   wrap text       PART D: Business report [24 Marks]   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:   o Executive summary o Description of operations o Evaluation and comparison of ratios calculated in Part C for the following areas (a minimum of two ratios for each category below).  Profitability  Asset efficiency  Liquidity  Capital structure o Limitations of the analysis o 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