Instructions: This assignment involves completing the accounting records for a small sole trader business for one month and completing the year-end procedures. It is worth 15% of your final grade. It is due by 5 pm on Friday, 25 September, 2020. A rubric is available in Myola under the Assessments tab.
To begin the assignment, you should open the Excel Proforma spreadsheet that is available under Content/Assessments. Save it immediately with your name as part of the filename. If you are not familiar with Excel, a recording is available under the Assessments area to help you with basic Excel skills and this assignment. This is an individual assignment.
Under no circumstances should you share your workings or any part of your assignment with other students – this constitutes academic misconduct and actions will be taken. In previous years, several students were reported to the Head of School for academic misconduct in BFA104 with regard the assignment so please do your own work. You must complete ALL the assignment using the Excel spreadsheet proforma and this is the file you will submit on MyLO. Basic proformas have been created for you. Across the bottom of the Excel file you will see different worksheets for each journal, ledger, trial balance, etc. Where possible, you should attempt to use some basic formula such as the SUM function when totaling journals. Some marks will be allocated for setting out and use of formula. If you are having issues with the use of Excel you should, in the first instance, watch the Week 10 recording on Excel. You can also use the Help function in Excel, or search Google and watch YouTube videos. You may ask some questions about issues with Excel on MyLO discussion boards. You should not discuss how to do the actual accounting in the assignment on MyLO. You are required to: 1. Record accurately all transactions in the correct journals. You will need to complete the Inventory Subsidiary Ledger cards for the two items of inventory as you go so that you can calculate the Cost of Goods Sold for each lot of sales. (Topic 5 and Topic 7) 2. Complete adjusting entries but not closing entries at this stage. (Topic 3 and 9 for depreciation methods) 3. Complete a bank reconciliation as at 30th June before totaling the cash journals. (Topic 8 – There was a “how to do a bank reconciliation” handout.) 4. Post the journals to the general ledger at the end of the month (and post to the subsidiary ledger daily). The ledger accounts have been created for you but you will firstly need to enter the opening balances in each account on 1st June. NOTE: the income and expense accounts will have an opening balance at this point as they are only closed off using closing entries at the end of the financial year. You should get these from the Trial Balance and Schedules above. (Topic 5 – There was a “how to post” special journals handout.) 5. Reconcile your accounts receivable, accounts payable and inventory subsidiary records with control accounts. (Topic 5 and 7)
6. Complete the adjusted Trial Balance (do this BEFORE completing and posting closing entries). 7. Complete closing entries necessary at the end of the accounting period and post them to the Ledger. Balance the Balance Sheet accounts. (Topic 4) 8. Complete an internal Income Statement for period 1-30th June, and Balance Sheet as at 30th June. You should put aggregated amounts and a “Note” for the following items: • Sales and Sales Returns • Cost of Sales • Depreciation Expense (add the two together) • Accounts Receivable and Allowance for Doubtful Debts • Fixed assets and their respective accumulated depreciation • S O’Brien, Capital (incorporate drawings and profit in the note) (See Topic 4 and Topic 7 for examples) Submission of Assignment: Upload your completed spreadsheet file to the MYLO drop box. You may only submit your assignment to the drop box once. The date stamp on the electronic submission will determine if your assignment has been submitted on time. Late work WILL be penalized unless you have been granted an extension by the Unit Coordinator.
Information Concerning the Business: Summer O’Brien is a sole trader who operates Summer’s Wilderness Packs, a wholesale business selling hiking backpacks to retail outdoor and camping shops all over Australia, and on the internet. Summer operates her business from a small, rented warehouse in Hobart. She employs a casual worker for four days a week. Summer’s bookkeeper has just retired and she asks you if you can help her to record the transactions for June. As you are studying accounting, she also asks you to finish off the books for the year and prepare some financial statements for her. She is thinking of expanding and needs the statements to apply for a bank loan. The bookkeeper has been keeping the records manually but Summer is keen for you to use Excel to complete the various records each month. Ignore GST. Other Information: • Summer sells two products, the Summer’s Wilderness Day Pack (known as the “Day Pack”) and the Summer’s Wilderness Hiking Pack (known as the “Hiking Pack”). • She uses a perpetual inventory system and the First-in, First-out method to assign costs to Cost of Goods Sold. • Prepayments (prepaid expenses or unearned revenue) are treated as an Asset or Liability when paid. • Assume transactions are on credit unless it is stated otherwise. • All transactions are recorded on the day they occur. • For convenience, round all figures to the nearest dollar (you can get Excel to do this for you by showing no cents). Schedule of Accounts Receivable at 31 May 2020 Acc No Account Amount 101 Q Quoll 7 227 102 P Possum 4 221 103 W Wombat 1 055 12 503 Schedule of Accounts Payable at 31 May 2020 Acc No Account Amount 201 L Lake 4 678 202 T Tarn 6 557 203 M Mountain 5 360 16 595 Inventory Subsidiary Ledger at 31 May 2020 Item No Product Name Units Unit Cost Amount 01 Day Pack 42 45 1 890 02 Hiking Pack 39 112 4 368 6 258
The May Bank Reconciliation Statement is as follows: Summer’s Wilderness Packs Bank Reconciliation Statement As at 31 May 2020 Balance as per Bank Statement 76 645 Cr Add Outstanding Deposit 2 940 79 585 Less Unpresented cheques No 891 3 125 No 901 480 No 903 1 450 5 055 Balance as per Cash at Bank Account 74 530 Dr The Trial Balance as at 31 May 2020 prepared by the old bookkeeper is as follows: Account Name Acc No Debit Credit Cash at Bank 1-1100 74 530 Accounts Receivable Control 1-1104 12 503 Allowance for Doubtful Debts 1-1105 565 Inventory Control 1-1115 6 258 Packaging Materials 1-2121 2 480 Prepaid Insurance 1-2125 1 890 Warehouse Fixtures and Fittings 1-3175 42 000 Aksum Depn – Warehouse Fixtures and Fittings 1-3176 15 000 Delivery Truck 1-3180 65 000 Accum Depn – Delivery Truck 1-3181 47 000 Accounts Payable Control 2-1200 16 595 Accrued Expense Payable 2-1215 S O’Brien, Capital 3-1300 131 600 S O’Brien, Drawings 3-1310 48 205 Profit and Loss Summary 3-9000 Sales 4-1000 154 705 Sales Returns and Other Allowances 4-1005 410 Interest Revenue 4-8410 75 Discount Received 4-8420 74 Cost of Goods Sold 5-1000 89 540 Salaries and Wages Expense 6-2540 18 350 Telephone and Internet Expense 6-2545 2 145 Electricity Expense 6-2547 2 035 Insurance Expense 6-2550 Packaging Materials Expense 6-2556 Depn Expense – Warehouse Fixtures and Fittings 6-2563 Depn Expense – Delivery Truck 6-2564 Bank Fee Expense 6-3580 150 Bad Debts Expense 6-3590 Discount Allowed 6-3595 118 $365 614 $365 614
Transactions for the month of June are as follows: June 1 Received $1 000 from W Wombat for full payment of his debt as at 31 May, Receipt No 103 (hint, we may have given him a discount). 1 Paid the annual insurance premium of $1 956, Cheque 904. 2 Cash Sales, 12 Hiking Packs @ $180, Receipt No 104. 4 Paid M Mountain the amount owing to them less 5% cash discount for early payment, Cheque 905. 6 Credit sales to P Possum of 20 Day Packs @ $78, Invoice No. 56. 6 Paid L Lake the amount owing at 31 May and received a 7.5% discount for early payment, Cheque 906. 7 Paid wages owing for the fortnight $420, Cheque 907. 8 Paid T Tarn the full amount owing as at 31 May, Cheque 908. 9 Received $7,100 from Q Quoll as full payment of her debt as at 31 May, Receipt No 105. 10 Received a quote from Trucks R Us for $90 000 for a new delivery truck. 14 Paid Electricity Account $310, Cheque 909. 15 Purchased 30 Day Packs @ $48 from T Tarn, Invoice 1290. 16 Purchased 25 Hiking Packs @ $113 from M Mountain, Invoice 101. 18 Credit sales to W Wombat of 33 Day Packs @ $80 each, Invoice No 57. 18 Cash sales, 15 Day Packs @ $78, Receipt No 106. 19 Credit sales to Q Quoll 25 Hiking Packs @ $180 each, Invoice No. 58. 21 Q Quoll returned 5 Hiking Packs as she decided she only wanted 20. Summer issued Credit Note 19 and gave Q Quoll a credit. As the packs were not damaged, they were returned to the inventory. 22 Paid wages owing for the fortnight $420, Cheque 910. 22 Appointed Tim Brady as Sales Representative starting on 8th July 2020. His retainer wage was agreed at $1 666 per month with a commission of 5% on products sold. 23 Received $3,500 from P Possum, Receipt No 107. 24 Credit sales to W Wombat of 15 Hiking Packs @ $180, Invoice No. 59. 25 Purchased 40 Day Packs @ $49 each from L Lake, Invoice 277. 26 Cash Sales, 10 Day Packs @ $80 and 10 Hiking Packs @ $182, Receipt No 108. 27 Provided a wholesale sales quote to Hobart Hardware for 500 Day Packs @ $65. 28 Received $3 600 from Q Quoll, Receipt No 109.
29 Paid M Mountain $1 200, Cheque 911. 30 Traded in the delivery truck for $13 000 in part payment for a new delivery truck costing $74 000. The remaining $61 000 was paid in cash, Cheque 912 (see depreciation information below). 30 End of Month adjustments • Insurance is paid once a year in advance on 1 June each year. No adjustment has been made during the year. • Wages owing at 30 June $220. • A stock take in the office reveals that Packaging Materials on Hand are $640. • Depreciation: ➢ Warehouse Fixtures and Fittings depreciation is based on 15 years usage and the straight-line method (residual value $6 000) ➢ The old Delivery Truck sold on 30 June is depreciated at 20% using the diminishing balance method (residual $8 000). ➢ The new Delivery Truck bought on 30 June is to be depreciated at 25% using the diminishing balance method (residual $10,000). • The Telephone and Internet account for June for $250 has not been received or paid. • Allowance for Doubtful Debts needs to be adjusted to equal 8% of Accounts Receivable Control at 30 June. • A stock take revealed the following closing balances: Day Pack 34 Hiking Pack 7