Description
Accounting Simulation in which you will use appropriate journals, ledgers and produce financial reports based on correct accounting records.
Theaimof this task is for you to develop skills to use computer software to identify, analyse and solve problems using accounting knowledge to complete the accounting cycle which examines a business involving a sole trader, perpetual inventory with limited number of products, minimal non-current assets and depreciation.
Resources
The MS EXCEL document contains templates that should be used to complete the assignment. The MS WORD document contains the necessary information and instructions for this task.
ACC106 Task 2 Assignment 2018 ACC106 Task 2 Assignment Business Scenario and Instructions Country Mile Bikes - Business Background 1.1 Introduction Country Mile Bikes is a proprietorship of Mr Teal Wheeler who established this new business on 1st June 2017. Country Mile Bikes is a GST-registered bicycle sales business that sells a range of bicycles to the public. As well, the business provides a basic bicycle repairs and maintenance service. The business currently sells three models of bicycle, a road racer, a mountain bike, and a BMX freestyle bike: Teal Wheeler, sole proprietor, prides himself on having excellent connections in his regional community with plans to grow his business and ultimately reach out to the professional cycling scene. The business has enjoyed good initial sales and has employed a number of staff including a sales assistant, technician, administration officer, and floor manager. Being an astute businessperson, Teal is using Microsoft EXCEL software to maintain the accounting records of the business transactions during the month of June, 2017 which is the last month of the Australian financial year. 1.2 Accounting Policies 1.2.1 Country Mile Bikes uses accrual accounting for recording transactions and adjustments. 1.2.2 Country Mile Bikes uses a perpetual inventory system with weighted average 1.2.3 Country Mile Bikes prepares quarterly Business Activity Statements (BAS) and remits the net GST liability due to the ATO in the month following the end of each quarter. (Note: as this is a new business, Teal has not yet prepared a BAS and has no liability at the start of June.) 1.2.4 Country Mile Bikes has the following depreciation policies: · Equipment: depreciated over 5 years using straight line method (no residual value and can be pooled together) · Furniture: depreciated over 8 years using straight line method (can pool purchases together or keep separate) · Motor vehicles: depreciated using reducing balance method at the rate of 35% over 5 years 1.2.5 Country Mile Bikes pays employee wages on a weekly basis. Wages are paid on the Wednesday for the previous week. The administration officer and floor manager are full time, earn a weekly wage and work Monday to Friday. The technician and sales assistant are employed on a casual basis and are paid hourly. All employees are paid employer superannuation contribution to their nominated superannuation funds at the rate of 9.5% of gross salary (before deductions). All employees are being paid into the same superannuation fund through an online portal (this means you can account for all superannuation together). 1.2.6 Country Mile Bikes records a provision for doubtful debts that equates to 5% of Accounts Receivable balance at the end of the year. 1.2.7 Country Mile Bikes records all prepaid expenses as assets (prepayments), and then expenses the portion used as end of year adjustments. Accounting System 2.1 Journal and Ledgers Country Mile Bikes uses the following journals and ledgers: Cash Receipts Journal - to record all cash receipts of the business including cash sales and cash received from debtors Cash Payments Journal- to record all cash payments and cheques issued by the business including cash purchases, cash payments to creditors Sales Journal - to record all credit sales of Inventory Purchases Journal - to record all credit purchases of Inventory General Journal - to record all other transactions not recorded in other journals (eg adjusting entries & closing entries) General Ledger- separate ledger accounts are maintained for each asset, liability, equity, revenue and expense account Subsidiary Ledgers- maintained for accounts receivable and accounts payable, and for inventory, then reconciled against relevant general ledger accounts 2.2 The business prepares the following Financial Statements: Balance Sheet – Assets, Liabilities, Owners’ Equity Income Statement – Revenue, Expenses, Gross/Net profit or loss Statement of Changes in Equity – Capital, Drawings 2.3 Chart of Accounts Country Mile Bikes Chart of Accounts is shown below. 2.4 Employee Payroll The Schedule of Employees and their pay rates are shown below along with relevant deductions (eg private health and employer superannuation guarantee). Staff ID Staff Name Position Private Health ESG Weekly salary Hourly rate EMP001 D Spokes Floor Manager $25/wk 9.5% $1,522 EMP002 E Wheeler Administration Officer $8/wk 9.5% $1,150 EMP003 F Lane Sales Assistant $0 9.5% $38 EMP004 G Swift Technician $0 9.5% $57 Calendar of relevant months for calculating payroll: Assume all employees claim the Tax-free threshold and use the calculator included in the EXCEL Student Templates file (Payroll calculator sheet) to calculate the withholding tax. This can also be found on the ATO site: https://www.ato.gov.au/Rates/Weekly-tax-table/ Business Transactions for June 2017 Adjustments for end of financial year 2017 4.1 A physical stocktake of supplies revealed supplies on hand at 30 June totalling $380. 4.2 Calculate and record the adjusting entry for rent for the end of the month given that June rent has been used up. 4.3 Calculate the amounts for depreciation expense for each of the three types of non-current Assets: vehicles, furniture and equipment. (Estimate expense as though assets held for whole of month of June.) 4.4 Calculate and record the adjusting entry for insurance for the end of the month given that the June portion has been used up. (Estimate by weeks.) 4.5 Calculate and record the adjusting entry for wages payable for employee earnings not yet paid as at 30 June. Apportion weekly salary earners by day; F Lane & G Swift both worked 10 hours each between Mon 26/6 and Fri 30/6. 4.6 Calculate the Provision for Doubtful Debts based on 5% of accounts receivable at the end of the year and make the necessary adjusting entry. Accounting Requirements and Instructions 5.1 Spreadsheet software The file on the course Blackboard site (ACC106 Task 2 Student Templates) contains a series of worksheets that you should use to complete the requirements below. Make changes as required including formulas and links. 5.2 Completing the Accounting Cycle 5.2.1 Record all the business transactions from section 3 above in the relevant special journals and post the Account totals to the relevant ledgers using EXCEL linking function. Use the accounts listed in the Chart of Accounts and enter names of the ledger accounts as required. In the ledgers, use formulas to create running totals. DO NOT POST ADJUSTMENTS yet. 5.2.2 Prepare an ‘Unadjusted’ Trial Balance as at 30 June 2017 by linking balances in the ledger accounts to the Trial Balance. It will be a ‘Live’ Trial Balance but at this point it is unadjusted as the Adjustments have not yet been posted. When completed, check the debit and credit columns are equal in the Trial Balance. Then copy and paste (as values only) the Trial Balance into the Worksheet columns ‘Unadj Trial Balance’. These should not change now as they are raw figures. (Tip: Use Paste Special to paste in as values.) 5.2.3 Record the end of year Adjustments, listed above at section 4, in the General Journal AND in the Worksheet. Post the adjustments to the General Ledger using new accounts as necessary. (This will change the figures in your Trial Balance but not your Unadjusted Trial Balance in your Worksheet. Check this. Check again that the debit and credit columns are equal in the ‘Live’ Trial Balance.) 5.2.4 Finalise the Worksheet by calculating balances in the Adjusted Trial Balance columns. (Check again that the debit and credit columns in the Adjusted Trial Balance are equal. Check also that the Adjusted Trial Balance figures in the Worksheet match the ‘Live’ Trial Balance figures as these should be the same at this point.) Then complete the Income Statement and Balance Sheet columns in the Worksheet. 5.2.5 Prepare the closing entries in the General Journal, and post these to the General Ledger. (As with adjustments, this will change your Trial Balance figures if you have linked correctly, but not your Unadjusted Trial Balance. But NOW, your Adjusted Trial Balance figures in the Worksheet will NOT match the ‘Live’ Trial Balance figures for the Revenue and Expense accounts and totals.) Remember: for closing, close revenue and expense accounts to the P&L Closing Account, then close P&L Closing Account to the Capital account and finally, close drawings to the Capital account. 5.2.6 Finally, prepare the Financial Statements for 30 June 2017 by copying or linking the appropriate figures into your Financial Statements. Refer to your textbook or course Learning Materials for guidance on the format of these Statements. For Balance Sheet, use the Classified Balance Sheet in report form as demonstrated on p.184 of the text; and Income Statement and Statement of Changes in Equity as demonstrated on p.240 of the text. (Note, for the Income Statement, there will be three revenue streams including discount received.) Additional Information 6.1 Number formats All amounts should be rounded to two decimal places – round to nearest cent eg $x.xx. 6.2 Journals and Ledgers Adjustment entries (but NOT closing entries) should be posted from the General Journal to the respective General Ledger accounts at the end of the financial year (June 30) Total columns in the special journals should be posted to the respective General Ledger accounts at June 30. Note: In the special journals, all entries that do not belong in one of the account columns as listed in the template, should be included in the “Other Accounts” columns. These should then be posted on an individual basis to the respective ledger accounts. (Hint: the journal templates contain all the account columns as required for this business. However, not all accounts have been included in the general ledger template – please add as required.) Remember: When special journals are used, do NOT also use the General Journal for the same transaction. 6.3 Inventory Record Inventory purchases and sales in the Subsidiary Ledger Inventory AS THEY OCCUR. As well, record purchases and sales in the relevant journals (using figures in the Inventory record) and post the Inventory totals from these journals to the General Ledger Inventory account at June 30. 6.4 Wages There are two payroll worksheets that can be used to calculate wages. The payroll calculator from the ATO should be used to estimate weekly PAYG withholding tax for each employee. Use the table in the payroll template to relevant amounts for each employee each week. 6.5 Depreciation There is a depreciation worksheet with a table that can be used to calculate depreciation for the non-current assets. (Tip: depreciation expense is based on the net cost of the asset.) 9 Account NameAccount Type Usual BalanceAccount Type