FINAL Revised Draft
Due dates: September 5 2014.
All Submissions must be via EASTS.
The assignment based on a commercial operation based on the Mid-North Coast of NSW.
You will be provided with accounting and financial details of the business and you will be asked to complete certain activities and requirements.
Mid North Coast Transport Pty Ltd.
You have just been engaged as a consulting accountant to assist a new client in the preparation of their financial statements for the first full year of their operations and to provide an independent assessment of the performance of their company.
You have been asked to set up and apply excel spreadsheets to produce these financial reports because the owners wish to use excel in the future.
The business provides wholesale delivery services to the food service industry. Apart from the two proprietors it employs two staff on a casual basis. The warehouse from which they operate is rented for $20,000 pa.
The business owners (each own 50% of the share capital of the Pty Company) have provided you with two files of data. The first is a list of assets and liabilities. This was left by the previous accountant who has travelled overseas and cannot be contacted.
The second set of data is a list of activities and transactions for the past 12 months.
List 1 â€“ Assets and Liabilities as at June 30 2013.
â€¢ Cash at Bank $750
â€¢ Prepaid Expenses $5300
â€¢ Trade Debtors $19600
â€¢ Inventory $65000
â€¢ Motor Vehicles at Cost $12000 (accumulated depreciation $2500)
â€¢ Accrued expenses $1250
â€¢ Trade creditors $22000
â€¢ Paid-up Capital $50000
â€¢ Retained earnings $26900
List 2 – activities and transactions for the year ended June 30 2014.
â€¢ The two owners withdrew $10000 each during the year
â€¢ Rent of $15000 was paid
â€¢ Rates of $1300 were paid to cover the period October 1 2103 to September 30 2014
â€¢ A new delivery vehicle was purchased on July 1 2013 for $13000. It is expected to be used for 4 years and then sold for $3000.
â€¢ Wages of $36700 were paid during the year to the casual staff. On June 30 2014 wages of $860 were owed for the last week of the year.
â€¢ Electricity bills for the first 3 quarters of the year totalling $1820 were paid during the year. On July 1 2014 the bill for the last quarter was received. It was for $690.
â€¢ Inventory totalling $67000 was bought on credit inventory totalling $8000 was bought for cash
â€¢ Sales on credit totalled $179000 and cash sales totalled $54000
â€¢ Cost of sales on credit sales was $89000 and on cash sales was $25000
â€¢ Receipts from trade debtors was $178000 â€¢ Payments to trade creditors was $71000
â€¢ Vehicle running expenses were $16200.
After a short meeting with the clients and a review of the financial data you decide to proceed as follows:
Requirement 1 for Assessment Item 2.
Prepare a excel spreadsheet format which will enable you generate the necessary statements. You decide the best way to proceed is to set up a data sheet, and a worksheet for a trial balance sheet with adjustment columns feeding into a worksheet for the Profit and Loss Statement and a Balance Sheet.
You also decide to enter the base data above and ensure that all linking formulae are highlighted to enable you to explain your â€œAccounting Modelâ€.
Whilst your clients have specified an excel format you are aware of other accounting systems and models which could be used eg MYOB. You decide to prepare a report for your clients. This report will explain two alternative accounting systems with their various advantages and disadvantages. Because your clients are busy people you resolve that this report should not exceed one page.
You have never dealt with a transport company before so you believe it will be worthwhile to do some research on this part of the industry (wholesale food service). You will prepare a brief report for your own benefit which will help you understand your clientâ€™s operations.
Requirement 2 for Assessment No 2 .
Your manager, after providing you with some constructive feedback, gives you approval to continue your project which involves the following.
1. Using your excel model generate reports for
Profit and Loss, and Balance Sheet
Ensure these are formatted to enable them to be easily printed â€“ one report per page.
2. When you completed the reports one of your client principalâ€™s phoned to ask if you could explain several accounting terms he had heard about during the week. Specifically he asked if you could provide an explanation of
The matching principle (and what do prepayments mean?) The difference between financial position and financial performance Current versus fixed assets and what does depreciation really mean.
You decide to prepare a one page report answering your clientâ€™s queries.
Requirement 3 for Assessment No 2 Due September 5 2014.
Your manager was pretty happy with your work to date and has given you the go-ahead to finalise your report for the clients and arrange a meeting on September 10. Your manager emphasises that your comprehensive report should include all of the key elements covered in Requirements 1 and 2 above.
One of your clients has heard about Pacioli and Double-Entry Accounting. Your manager suggests you should prepare a half-page file note and take it to the meeting and be ready to discuss the history of accounting.
You agree with your manager that the report should have the following format (Word document â€“ this will require key financial statements to be imported from excel)
1. Executive Summary
3. Commentary on Financial Position and Financial Performance including
a. Ratio analysis â€“ working capital, debt/equity ratios
4. Recommendation re Accounting System
a. Excel v identified alternatives
5. Review of Pacioli and Double-Entry Accounting
Your manager requires the report by September 5. Your manager has asked that you provide all excel files for review by the manager.
You will be able to submit these by EASTS on or before the required date. You should submit your Assignment 2 in 3 parts in accordance with Requirements 1, 2 and 3 above
FINAL Revised Draft