Task
Financial statements, analysing business transactions and financial statement analysis
Sunbay Sales is a small sales consultancy business and began operation on 1 January 2017. It is owned and operated by Karin Nilson and employs one other consultant.
Karin has asked her accountant, Megan Gordon to complete financial statements and write a business report on the financial health of Sunbay Sales in its first year of operation.
Sunbay Sales has the following account balances as at 31 May 2017;
|
Cash at bank
|
$72,900
|
Accounts payable
|
$17,300
|
|
Account receivable
|
$26,150
|
Loan
|
$15,000
|
|
Office equipment
|
$10,700
|
Capital
|
$100,000
|
|
Prepayments
|
$0
|
Income
|
$40,400
|
|
Sales solution software
|
$24,500
|
Expenses
|
($38,450)
|
The following transaction occurred in the last month of the financial year- June 2017.
|
June
|
Transaction Description
|
|
1
|
Paid $24,000 for a 12 month business insurance policy.
|
|
1
|
Paid rent of $1,600 in cash for the month.
|
|
2
|
Received $9,400 payment for outstanding invoice (sent 12 May) from Delta Start-Up.
|
|
3
|
Paid $220 to the internet service provider account, outstanding from May.
|
|
6
|
Purchased $1,050 worth of office stationary for the business on account.
|
|
4
|
Paid $850 cash for internet advertising during the month of June.
|
|
8
|
Sent a $6,700 invoice for consultancy services performed during the first week of June for Game Chase Group.
|
|
9
|
Karin paid $1,080 for an emergency hospital visit from the business bank account.
|
|
12
|
Received $10,200 in cash from customers for sales consultancy services.
|
|
16
|
Made a loan repayment of $10,000 (includes $1,000 interest expense).
|
|
17
|
Paid the business telephone bill $320.
|
|
18
|
Paid the invoice received on 6 June.
|
|
20
|
With the exception of one small $530 invoice (which remains outstanding) received payment from all remaining outstanding May invoices.
|
|
23
|
Sent a $4,320 invoice for services performed in the second week of June for Altitude Fitness.
|
|
25
|
Karin injected $15,500 of her own cash into the business bank account.
|
|
26
|
Received $7,820 in cash from a business named Wagga Hat for sales consultant service in June.
|
|
28
|
Sent a $3,200 invoice for services performed in the third week of June for Glass & Glaze.
|
|
30
|
An advanced sales software was purchased to improve the quality of consultancy services. The complete cost of the software was $43,500. $20,000 was paid from the business bank account and the balance was financed via a loan.
|
|
30
|
Paid $5,000 in wages for June.
|
Additional Information
- The cash flow from operating activities for the period ending 30 June 2017 was $2,700.
Required:
Part A: Worksheet
You are required to complete the worksheet in the ‘Assignment 2 Template' for Sunbay Sales. Using the sheet labelled ‘Worksheet' identify Sunbay Sales' business transactions for June and construct an accounting worksheet.
Note:
1. Illustrative example 4.4 on pg. 126 of the text can be used as a guide to the format required for the worksheet. You should not limit yourself to this Illustrative example only. You should also review the tutorial questions we have provided the solutions to, as well as any other subject material available.
2. Opening balances as at 31 May 2017 should be used as your starting point in your accounting worksheet.
3. In your worksheet, you must demonstrate the use of the following Excel functions:
- 'sum'
- bordering
- cell merging
- wrap text
- date.
4. Ensure that you ‘sum' each side of your worksheet to prove the accounting equation.
Part B: Financial statements
Using the same Excel file that you used for Part A, you need to prepare the following financial statements of Sunbay Sales from the accounting worksheet for the 2017 financial year:
- Income statement.
- Statement of changes in equity.
- Balance sheet.
Note:
1. Illustrative example 4.5 on pp.126-127 of the text can be used as a guide to the format required for the income statement and balance sheet. Illustrative example 6.8 on the bottom of p. 245 of the text can be used as a guide to the format required for the statement of changes in equity. You should not limit yourself to these illustrative examples only. You should also review the tutorial questions we have provided the solutions to, as well as any other subject material available.
2. Each financial statement should be prepared using the named worksheets provided in the Excel template. You are required to use formulas only to transfer the figures from the worksheet into the statements (do not type figures directly into the statements, with the exception of note 4. below).
3. In your financial statements, you must demonstrate the use of the following Excel functions:
- formulas for each subtotal and total
- bordering
- cell merging
- wrap text.
4. Assume expenses incurred up to 31 May 2017 consisted of: $350 for supplies, $25,000 for wages, $880 for internet services, $8,000 for rent and $4,220 for advertising.
PART C: Ratio analysis
In the same Excel template that you used for Part A and B use the sheet labelled ‘Ratio Analysis', to complete the following:
- the table labelled 'Ratio Figures'. You are required to use formulas only to transfer the figures needed from the statements into this table (do not type answers directly into this table).
- the column labelled ‘formula'. You are required to provide the written formula for each of the ratio's provided (first one completed for you as an example).
- the column labelled ‘2017'. You are required to use formula only by pulling figures from the table completed above 'Ratio Figures' to show the ratio calculation. (Do not type ratio answer directly into the table).
Note:
In completing the ‘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 Karin Nilson, on behalf of Megan Gordon, on the financial health of Sunbay Sales for the 2017 financial year. Your report should contain the following details:
- Executive summary
- 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 Sunbay Sales.
Note:
1. In writing your report, you should refer to the lecture/tutorial 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 600-700 words, excluding the cover page, contents page and references.
Rationale
This assessment task covers topics 3, 4 and 5. It has been designed to ensure that you are engaging with the subject content on a regular basis. More specifically it seeks to assess your ability to:
- analyse and interpret a range of accounting and financial transactions and reports;
- use a computer spreadsheet to assist in the preparation of business information and be able to generate and analyse a range of business results and reports; and
- employ research and report writing skills.