Assessment item 3Assignment 2Value:25%Due date:02-May-2018Return date:23-May-2018Submission method optionsEASTS (online) Task Financial statements, analysing business transactions and financial...

Assessment item 3Assignment 2Value:25%Due date:02-May-2018Return date:23-May-2018Submission method optionsEASTS (online)
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;

The following transaction occurred in the last month of the financial year- June 2017.

Additional Information

• The cash flow from operating activities for the period ending 30 June 2017 was $2,700.

Required:

Part A: Worksheet [5%]

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 [5%]

Using the same Excel file that you used for Part A, you need to prepare the following financial statements ofSunbay Salesfrom 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 expensesincurred up to 31 May 2017 consisted of: $350 for supplies, $25,000 for wages,$880 forinternet services, $8,000 for rent and $4,220 for advertising.



PART C: Ratio analysis [5%]

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 [10%]



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 recommendationson areas of improvement for Sunbay Sales.

Note:

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 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.

Requirements

Parts A – C must be submitted in one Excel file.

As per the marking criteria for this assignment, you must reference your work using the APA guidelines. These can be found in the CSU Referencing websitehttp://student.csu.edu.au/study/referencing-at-csu