Financial statements, analysing business transactions and ﬁnancial statement analysis
Jessica Smith started a new sole trader business called Tech Solutions on 1 January 2019. The business provides telecommunication services to start-up businesses who want to enhance their network and customer reach. Jessica has asked her accountant, Megan Brown, to complete ﬁnancial statements and write a business
report on the ﬁnancial health of Tech Solutions in its ﬁrst year of operation.
Next Step has the following account balances as at 31 May 2019;
Fixtures and Fittings
The following transactions occurred in the last month of the ﬁnancial year- June 2019.
Transaction Description 1
Paid the business telephone bill $1,300. 2
Made a loan repayment of $5,000. 3
Received $6,600 payment for outstanding invoice (sent 15 May) from Ace Communications.
Paid $120 internet service provider account, outstanding from May.
Sent a $5,240 invoice for services performed during the ﬁrst week of June for Jones Partners.
Megan paid $1,500 for a 5 star restaurant meal with friends from the business account.
Received $9,400 in cash from customers for services.
17A water leak damaged some ﬁttings which had to be immediately disposed with no salvage value. The original cost of the damaged ﬁttings was $3,000. 18Paid plumber $1,000 to to perform urgent repairs on building plumbing.
Megan injected $8,300 of her own cash into the business bank account.
With the exception of one $555 invoice (which remains outstanding) received payment from all remaining outstanding May invoices.
Received payment for the last outstanding May invoice.
Sent a $3,333 invoice for services performed on 23 June for Quick Marketing.
Advanced telecommunication infrastructure was purchased to improve the quality of PR services. The complete cost of the project was $23,500. $8,500 was paid from the business bank account and the balance was ﬁnanced via a loan.
Sent a $7,530 invoice for services performed in the third week of June for Green Messenger.
29Received half payment for invoice sent to Green Messenger.
Paid $7,000 in accounting fees for June. 30
Paid $14,000 in rent for the period 1 June 2019 to 31 December 2019.
30Paid interest expense of $200 for June. Required:
Part A: Worksheet [5%]
You are required to complete the worksheet in the
‘Assignment 2 Template’ for Tech Solutions. Using the sheet labelled ‘Worksheet’ identify the business transactions for June 2019 and construct an accounting worksheet.
1. Illustrative example 4.4 on pg. 132 of the text can be used as a guide for 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. Balances as at 31 May 2019 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:
• cell merging
• wrap text
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 of Tech Solutions from the accounting worksheet for the 2019 financial year:
• Income statement.
• Statement of changes in equity.
• Balance sheet.
1. Illustrative example 4.5 on pp.132-133 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. 242 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 (donot 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
• cell merging
• wrap text.
4. Assume expenses incurred up to 31 May 2019 consisted of: $200 for supplies, $12,000 for accounting fees, $700 for internet services, $10,000 for rent and $3,000 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 ‘2019’. 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).
In completing the ‘Ratio Analysis’ Sheet in the Excel template, you must demonstrate that you have used the following Excel functions:
• ' =/+/-'
• merging cells
• wrap text.
PART D: Business report [10%]
Write a report to Jessican Smith, on behalf of Megan Brown, on the financial health of Tech Solutions for the 2019 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 Asset efficiency
o Capital structure
• Limitations of the analysis
• Conclusion, including recommendations on areas of improvement.
Your report should be between 650 +/- 10% words, excluding the cover page, contents page and references. Online submission via Turnitin is required for this assignment. Details will be provided by your subject lecturer.
back to top
This assessment task will assess the following learning outcome/s:
be able to analyse and interpret a range of accounting and financial transactions and reports.
be able to 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.
be able to employ research and report writing skills. 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.
The following pages summarise the financial health of Tech Solutions as of June 30, 2019. The scope of evaluation includes several types of ratios that have been calculated basis available data for June 2019. The types of ratios covered include profitability ratios, asset efficiency ratios, liquidity ratios and capital structure related ratios. These will form the basis of analysis and noting down points of concern for the business entity.
The return on equity stands at 6.1% which is reasonable, considering that it is a new business and we still have to increase our sales.
The profit margin stands at 42.7% which is very good as it indicates that the expenses are well within control such that large part of sales can be converted to profits.
Asset turnover is very low at 0.11 times which indicates that assets can be put to better use to generate more sales. Since this is beginning of a new business, the small sales figure is reasonable but we need to focus on marketing such that our sales increase for the coming periods.
Times debtors turnover stands at 1 times, indicating that the debtors are paying well within a month and are on track. However, at the same time, we should focus on less reliance on credit sales that account for more than 60% of our total sales.
The current ratio is at 6.98 times which indicate that assets can be put to better use as the level of current liabilities is covered almost 7 times at the current level of current assets. We can afford to reduce this further by efficient use of available assets.
The interest coverage ratio is also quite high at 54.42 times indicating that we have buffer to absorb higher level of interest expense as well.
The debt ratio is at 15.2% indicating that the level of debt in our capital structure is minimal and we can leverage lower rate of financing by increasing debt component. However, this will also increase fixed obligation and sudden jump in debt is not advisable at initial stages of business.
This is reflected in low debt coverage ratio which stands at 0.40 times. It indicates that the current operating income is insufficient to pay off current level of debt. Hence, we must be careful to increase and stabilise sales and profit levels before we increase debt burden on the company. With debt burden, a fixed obligation on profit is also created in form of interest expense that needs to be paid irrespective of profit situation of the company.
The above analysis is limited in nature because only June 2019 data was available. Further, we do not have comparative data from previous month to compare the growth or points of concern. Also, the industry averages or peers averages data was unavailable that could have made this analysis much more comprehensive.
Basis above evaluation and analysis, we can assess the financial health of Tech Solutions. While doing this, we need to keep in mind that it is a new business that still needs to stabilize itself. However, the above presents an overall positive picture for the company. The company is earning profit in initial stages, indicating good control over expenses. The capital structure is leveraged through debt so as to benefit from low cost debt financing but still, the level of debt is reasonable in the capital structure.
The main takeaway is to increase and stabilize higher sales level that will be reflected in higher profits as well as better utilization of available assets. Further, effort should be to encourage cash sales as far as possible.
A point of concern is operating cash flow level which is dwindling. The bank account has a negative balance which can be taken care of through depositing some cash. However, the cash levels need to be tracked continuously, especially since it’s a blooming business that will definitely demand investments in form of cash and loans.