ACC1AIS – Individual Assignment 2020ACC1AIS – Individual Assignment 2020 This assignment requires you to create a new business, input transactions, and generate financial statements and reports using Xero. This assignment question sheet should be used in conjunction with the weekly Xero help-sheets which explain in more detail the steps required to complete the transactions below. Task Overview The company for which you will be working in this assignment distributes, sell and repairs bicycles. You will be recording transactions for the month of September 2020. The assignment containsfive separate sections, one for each accounting period during month of September. You should work through these sections sequentially. Once you have completed all the transactions you should prepare the requirementslisted on page 12. Finally, you are required to submit your assignment files and reports via the assignment submission box on LMS. You are required to submit your assignment by Sunday (4 October 2020), 11.59 pm. Set up Company Your first task is to set up a new business in Xero - you must use your Student ID Number and FULL Name as the Business name. You will be doing this assignment via registering for a trial company and not using the Demo company. You are the accountant and owner of a business which is registered for GST purposes. You previously used to use anothersoftware as your main accounting system. However, as a result of changes in technology, you have decided to move onto a cloud-based accounting software platform, Xero. To transfer from your previous accounting package to Xero you are required to transfer balances in your previous system onto Xero. The conversion date (date when you stop using your previous system and start using Xero) is 1 September 2020, and the Trial Balance as at 31 August 2020 is as below: Page 1 of 16Page 2 of 16 Trial Balance as at 31 August 2020 Account Debit Credit Cash at bank $166,483.00 Accounts receivable $1,148,178.00 Prepaid Fire insurance $30,000.00 Inventory $1,325,800.40 Store Building $700,000.00 Accumulated Depreciation – Store building $125,000.00 Delivery Van $80,000.00 Accumulated Depreciation – Delivery Van $16,800.00 Accounts payable $705,120.00 Loan payable – Building Head Office $117,604.88 Unearned delivery revenue $49,600.00 Capital $1,497,039.94 Sales revenue $1,043,798.20 Interest expense – 8% annually $1,568.06 Fire Insurance expense $6,000.00 Salaries expense $50,000.00 Warehouse rent expense $4,400.00 telecommunication expense $1,360.00 GST $197,743.30 Allowance for Doubtful Debts – 10% $104,379.82 Provision for warranty – 5% $52,189.92 $3,711,532.76 $3,711,532.76 All figures included within the Trial Balance are GST exclusive except for accounts receivable and accounts payable.Page 3 of 16 Accounts receivable: 100 items of SKU112820, 150 items of SKU112821, 80 items of SKU112822, 70 items of SKU112823, 100 items of SKU112824, 110 items of SKU112825, 110 items of SKU112826 are sold on 15 August 2020 on credit to Bike Friday. Due date is 18 September 2020. Items sold on 15 August 2020 Sales price (incl. GST) Total receivable (incl. GST) SKU112820 – 100 items $1,698.00 $169,800 SKU112821 – 150 items $3,198.00 $479,700 SKU112822 – 80 items $1,718.00 $137,440 SKU112823 – 70 items $2,398.00 $167,860 SKU112824 – 100 items $1,098.00 $109,800 SKU112825 – 110 items $359.90 $39,589 SKU112826 – 110 items $399.90 $43,989 Total incl. GST $1,148,178 Accounts payable: 150 items of SKU112820, 160 items of SKU112821, 110 items of SKU112822, 100 items of SKU112823, 100 items of SKU112824, 140 items of SKU112825, 140 items of SKU112826 are purchased on 20 August 2020 on credit from Bike Works. Due date is 20 September 2020. Items purchased on 20 August 2020 Purchase price (incl. GST) Total payable (incl. GST) SKU112820 – 150 items $849.00 $127,350 SKU112821 – 160 items $1,599.00 $255,840 SKU112822 – 110 items $859.00 $94,490 SKU112823 – 100 items $1,199.00 $119,900 SKU112824 – 100 items $549.00 $54,900 SKU112825 – 140 items $178.00 $24,920 SKU112826 – 140 items $198.00 $27,720 Total incl. GST $705,120Page 4 of 16 Inventories on hand at 31 August 2020: 450 items of SKU112820, 210 items of SKU112821, 330 items of SKU112822, 130 items of SKU112823, 200 items of SKU112824, 280 items of SKU112825, 150 items of SKU112826, and 140 items of SKU112827. Items on hand 31 August 2020 Purchase price (excl. GST) Total excl. GST SKU112820 – 450 items $771.82 $347,319.00 SKU112821 – 210 items $1,453.64 $305,264.40 SKU112822 – 330 items $780.90 $257,697.00 SKU112823 – 130 items $1,090.00 $141,700.00 SKU112824 – 200 items $499.10 $99,820.00 SKU112825 – 280 items $161.82 $45,309.60 SKU112826 – 150 items $180.00 $27,000.00 SKU112827 – 140 items $726.36 $101,690.40 Total excl. GST $1,325,800.40 Further Information Salaries expense $25,000 per month Warehouse rent expense $2,420 per month incl. GST Telecommunication expense $748 per month incl. GST Allowance for doubtful debts 10% of the Accounts Receivable Provision for warranty 5% of the sales revenue Interest expense on loan (Building Head Office) Calculated at 8% per annum Delivery Van Purchased on 1 July 2019 at $88,000 incl. GST, with a useful life of 5 years, and $8,000 residual value. It is depreciated using the straight-line method. Store Building Purchased on 1 July 2016, at $770,000 incl. GST with a useful life of 20 years, and $100,000 residual value. It is depreciated using the straight-line method. Fire insurance On 1 st July 2020, you paid $39,600 incl. GST for a twelve-month fire insurance policy to Berkshire Group.Page 5 of 16 Items Refer to the Workshop 2 Help-sheet if you need detailed help to create new items. Item code Item name Buy price (Incl. GST) Sell price (Incl. GST) SKU112820 Road Bikes $849.00 $1,698.00 SKU112821 Commuter & Recreation Bikes $1,599.00 $3,198.00 SKU112822 Mountain Bikes $859.00 $1,718.00 SKU112823 Cyclocross Bikes $1,199.00 $2,398.00 SKU112824 Folding Bikes $549.00 $1,098.00 SKU112825 Touring Bikes $178.00 $359.90 SKU112826 BMX Bikes $198.00 $399.90 SKU112827 Cruiser Bikes $799.00 $1,598.00Page 6 of 16 Period one: 1 September to 5 September 2020 Date Details 1 September You borrowed $104,000 from the Northern Bank on a short-term loan. The money is to be used to purchase welding machinery. The principle, plus 7.5% annual interest, will be paid in 3 months. 1 September You purchased 20 items of SKU112820, 10 SKU112821, 15 SKU112822, and 12 SKU112823 from Focus Bikes. You received all the stocks ordered and paid 20% of the purchase price together with the freight of $253 incl. GST. You agreed to pay Focus Bikes the balance on 5 th October. 1 September You paid $11,440 incl. GST for a three-month product liability insurance policy to Society Insurance. 1 September You received $46,750 incl. GST cash in advance from clientsfor a one-year subscription to your company featured magazine, “RideOn”. 1 September You received $76,560 incl. GST in advance from Gocycle for repair of bicycles. 2 September You spent $1,496 incl. GST on miscellaneous office supplies at Officeworks. You paid for these from your bank account. Consider purchase of supplies as a prepaid supplies account. 2 September You spent $102,850 incl. GST on a welding machinery and paid for it by using your bank account. You estimate that the machinery will have a useful life of 5 years, during which you are planning to produce 40,000 units of new bike pedals. The residual value of the machinery is $13,500. Actual number of units of pedals that will be produced within the next twelve months will be 2,400 units. Record the purchased of machinery on this date. Note that the trial Company in Xero does not allow you to add an asset to the register or create asset types as we did in Demo Company in Week 6 so you’ll need to just record this transaction as an asset purchase. 3 September You sold 40 SKU112820 and 20 SKU112822 to Ideal Bikes. Ideal Bikes paid you the full amount into your bank account. 4 September You sold 80 SKU112826 and 60 SKU112825 to Road Riders. Road Riders paid you a deposit of 75% of the total sale into your bank account and agreed to pay the remaining 25% on 4 th October. Details of transactions for Period 1: 1 to 5 September 2020Page 7 of 16 Period two: 6 September to 12 September 2020 Date Details 6 September You spent $5,500 incl. GST on a computer at Digi-Com and paid for it from your bank account. You estimate it will have a useful life of 3 years and be worth $713.61 at the end of the 3 years. Computer is depreciated using the diminishing balance method and the rate of depreciation is 41%. Record the purchase of the computer on this date. 6 September You transferred surplus cash out of the Bank Account to an Investment Account. You realised that you don’t need all your capital sitting in thelow interest working bank account so have transferred $70,000 into a higher interest-bearing account. Record this movement in a journal entry. This account will pay you 2.5% interest per annum, credited monthly on the 6 th of each month into your account. 7 September You sold 40 SKU112822 and 60 SKU112823 Road Eagles on account, term 2/8, n/30. 8 September You purchased 25 SKU112824, 15 SKU112825, and 15 SKU112827 on credit from Blissful Pedals. 9 September You sold 36 SKU112824 and 28 SKU112820 to Urban Tour. Urban Tour paid you a deposit of 85% of the total sale into your bank account and agreed to pay the remaining 15% on 16 th October. 10 September You sold 30 SKU112821 and 40 SKU112825 to Just Cycles. Just Cycles paid you a deposit of 75% of the total sale into your bank account and agreed to pay the remaining 25% on 10 th October. 10 September You received $8,192 credit (incl. GST) for damaged items including 4 SKU112820 and 4 SKU112823 returned to Focus Bikes. 10 September You sold 40 SKU112823 and 48 SKU112827 to Champion Wheels. Champion Wheels paid you a deposit of 90% of the total sale into your bank account and agreed to pay the remaining 10% on 27 th September. 10 September You opened an account with Apache Petrol to pay for fuel for the delivery van. 11 September You sold 20 SKU112820 to Bicycle Workshop on account. Bicycle Workshop agreed to pay the amounts due on 12 September 2020. Details of transactions for Period 2: 6 to 12 September 2020Page 8 of 16 Period three: 13 September to 19 September 2020 Date Details 13 September You sold 60 SKU112822, 60 SKU112825 and 30 SKU112827 to The Cycle Gear. The Cycle Gear paid you a deposit of 85% of the total sale into your bank account and agreed to pay the remaining 15% on 25 th September. 14 September You received the full payment less discount from Road Eagles for their purchase on 7 th September. 15 September You paid Ad Ready $7,959.6 incl. GST for online advertisements. These ads will run over the next 3 months, with an end date of 15 th December 2020. 16 September You sold 30 SKU112825 and 30 SKU112827 to Bike Gallery. Bike Gallery paid you the full amount into your bank account. 17 September You sold 20 SKU112821 and 24 SKU112825 to Bike & Run. Bike & Run paid you a deposit of 90% of the total sale into your bank account and agreed to pay the remaining 10% on 22 September. 17 September You paid an invoice for the current period’s fuel bill of $165 incl. GST from Apache Petrol plus 1% transaction fee. Transaction fee includes GST. 17 September Paid Blissful Pedals in full for the purchase on 8 th September. 18 September After several attempts to collect receivables from Bicycle Workshop you finally write off Bicycle Workshop accounts as bad. 18 September You received 90% of the receivables from Bike Friday. Bike Friday agreed to pay the remaining 10% on 18 October. Details of transactions for Period 3: 13 to 19 September 2020Page 9 of 16 Period Four: 20 September to 26 September 2020 Date Details 20 September You sold 70 SKU112825 and 106 SKU112824 to Bike Town on account, term 3/10, n/30. 20 September You paid 35% of the accounts payable to Bike Works. You promised Bike Works to pay the remaining 65% on 20 October. 21 September You sold 80 SKU112822 and 100 SKU112820 to Buzz Bikes on account, term 2/10, n/30. 22 September You granted Buzz Bikes $10,308 credit (incl. GST) for 6 SKU112822 returned. 23 September You sold 20 SKU112822 and 60 SKU112821 to Rockrider. Rockrider paid you the full amount into your bank account. 24 September You sold 22 SKU112821 and 58 SKU112820 to Cycle City. Cycle City paid you a deposit of 85% of the total sale into your bank account and agreed to pay the remaining 15% on 16 th October. 24 September You paid an invoice for this period’s fuel bill of $165 incl. GST from Apache Petrol plus 1% transaction fee. Transaction fee includes GST. Details of transactions for Period 4: 20 to 26 September 2020Page 10 of 16 Period Five: 27 September to 29 September 2020 Date Details 27 September You sold 30 SKU112822 and 60 SKU112820 to Bike Lane on account, term 3/6, n/30. 27 September You received the full payment less discount from Bike Town for their purchase on 20 th September. 28 September You granted Bike Lane $8,590 credit Incl. GST for 5 SKU112822 returned. 28 September You sold 80 SKU112820 and 24 SKU112823 to Race Time. Race Time paid you the full amount into your bank account. 28 September You paid $4,235 cash to satisfy warranty claims on faulty products. 29 September You sold 40 SKU112822 to Bike Buddies. Bike Buddies paid you the full amount into your bank account. 29 September You sold 40 SKU112824 and 28 SKU112820 to Bike Palace. Bike Palace paid you a deposit of 90% of the total sale into your bank account and agreed to pay the remaining 10% on 8 th October. 29 September You received the full payment from Buzz Bikes for the sales on 21 st September, less discount. 29 September You received the full payment from Bike Lane for the sales on 27 th September, less discount. 29 September You are informed that Bike Palace is gone into liquidation and as such you are not able to collect the remaining receivables from Bike Palace. You write off the remaining receivables as bad. 29 September You sold the computer that your purchased on 6 September for $4,500 cash incl. GST. Details of transactions for Period 5: 27 to 29 September 2020Adjusting Journal Entries 30 th September This is the final day of the calendar month, and your main task is to prepare the month end accounting entries. For any adjustments required you will need to prepare a general journal entry. The adjusting journals that you will need to enter are as follows - round to TWO decimal places: 1. Interest expense – short-term loan (Northern Bank). (0.25 marks) 2. Insurance expense – product liability insurance policy (Society Insurance). (0.25 marks) 3. First month of subscription revenue earned from the company featured magazine, “RideOn”. (0.25 marks) 4. You completed 30% of repairs for Gocycle during September. (0.25 marks) 5. Office supplies on hand at 30 th September is $275 incl. GST. (0.25 marks) 6. Depreciation expense – welding machinery, using the units of production method. (0.25 marks) 7. Interest earned at month end. (0.25 marks) 8. Record the adjusting entry for fuel bill payable of $165 incl. GST from Apache Petrol for the final period i.e., period 5. (0.25 marks) 9. Advertising expense amount for the month of September. (0.25 marks) 10. Provision for warranties is equal to 5% of sales. (0.25 marks) 11. You realise that the economy has significantly deteriorated which is observed through the number of customers’ debts that are written off during this month. As such you decide to increase the allowance from 10% to 35% of the accounts receivable (as of 30 th September 2020). Record the adjusting entry for the allowance for doubtful debts account at 30 September 2020. (0.5 marks) 12. Salaries expense accrued for September. (0.25 marks) 13. Warehouse rent expense accrued for September. (0.25 marks) 14. Depreciation expense Delivery Van, using the straight-line method. (0.25marks) 15. Depreciation expense Store Building, using the straight-line method. (0.25marks) 16. Interest expense accrued on the loan for Building of the Head Office. (0.25marks) 17. 40% of deliveries are performed during September. (0.25 marks) 18. Telecommunication accrued for September. (0.25 marks) 19. Insurance expense, Fire insurance. (0.25 marks) Note all month end adjusting journals must be dated 30 September 2020. Page 11 of 16Assignment requirements (Please ensure all reports have correct dates) 1. A Trial Balance report dated 29 th September 2020. This is your ‘unadjusted’ Trial Balance. Use the classic version of the Trial Balance. (4.5 marks) 2. A Journal Report for 30 th September 2020. An example from Demo Company is shown below. (5 marks) Please note that: you MUST change the date from 30 September 2020 to 30 September 2020 and click onto “Show Posted Manual Journals Only”. Students who submit a Journal report that includes all journals of the whole month of September 2020 may lose 50% of their mark for this deliverable. 3. A Profit and Loss (P&L) report for the period 1 September 2020 to 30 September 2020. Make sure that you illustrate the comparison between your September P&L with the previousmonth i.e., August. Use the classic version ofthe Profit and Loss. (0.25 marks) 4. A Balance Sheet report as at 30 September 2020. Make sure that you illustrate the comparison between your September Balance Sheet the previous month i.e., August. Use the classic version of the Balance Sheet. (0.25 marks) 5. A professionally presented short written business report (maximum 1,500 words) designed for an internal user of the accounting information. Your written report is required to be classified as below. Page 12 of 16Written Report – Financial performance factors (3 marks) Your report explains the financial results and performance of your company in September 2020. You are required to provide two discussion factorsto analyse the financial performance of the company. These can be items taken from either balance sheet or profit and loss that you feel had an impact on the financial performance of the company. You are required to calculate ratios to support your discussion of each factor you have chosen tohighlight. Your report explains how the company managed its inventories during September by providing an appropriate inventory report from Xero. You are required to indicate how many units of inventory is purchased and sold from each type of bicycles during September. How many units of inventories remain on hand from each type of bicycles at month-end, etc? Your report explains how the company managed its Accounts Receivables and Payables during September by providing appropriate reports from Xero. Written report – Benchmarking (3 mark) You are required to compare the company results with that of at least one competitor as well with the average performance of the Bicycle Retailing and Repair Industry in Australia. To compare the company results with a competitor, you require to use the annual reports of the competitor (e.g., Mountain Bike Australia) as your source for benchmarking purposes and compare your company with the competitor using at least two (2) benchmarkingratios. To compare the company results with the average performance of Bicycle Retailing and Repair Industry in Australia refer to the IBISWorld Database. IBISWorld is available through the La Trobe University Library. Through IBISWorld you will be able to investigate the performance of Bicycle Retailing and Repair, investigate the industry operating conditions, industry outlook and compare the performance of your company with that of major players in the Australian market including 99 Bikes, Bicycle Superstore, and Bike Force Australia Pty Ltd. You can get access to the IBISWorld Database through the following link: https://www.lib.latrobe.edu.au/databases/title.php?l=I Page 13 of 16Written report – Recommendations (2 mark) you are required to provide two recommendations for the business moving forward and include supporting evidence through analysis to justify your recommendations. You are required to be creative and think about both financial and non-financial factors e.g., you can discuss media reports, current global financial climates, international demand, expansions or contractions of departments/product lines, internal factors, risk and liability etc. Written report – Visualisation (1.5 marks) Provide four (4) graphs that visually illustrate the company’s overall financial situation, performance, and inventory management. Include a description for each graph that describes the results or trends. You are required to provide meaningful, effective, easy to read, and well formatted graphs for use in managerial decision making. To draw graphs, you are required to use the MS Excel skills you gained in this subject. Do not use the ready-to-use graphs provided in Xero. Written report - Format (0.25 marks) You are required to write your report according to the business report format. The following link, taken from Business School of Victoria University of Wellington, New Zealand, should assist you with writing a business report according to the business report format. https://www.victoria.ac.nz/vbs/teaching/resources/VBS-Report-Writing-Guide-2017.pdf Referencing (0.25 marks) All submissions will be reviewed by a program called “Turnitin” and this will identify instances of plagiarism (copying). This is a very serious offence and can result in you receiving no marks for the assessment task. Please ensure if you have copied information you use quotation marks and appropriate referencing. If you reword someone else’s work, you still need to refer to the author’s in your sentence. For example, Hines(1991) discusses how……. – whilst thisis not a quote you need to reference the idea. Late Submission Penalty “The standard penalty for late submission of assessment tasks is 5% of the marks for that task for each delay in submission of a day or partial day up to a maximum of five (5) working days after the due date.” Page 14 of 16Assignment Instructions Here I provide you with further information on how to approach this assignment. 1. Follow the below instructions to add the required accounts in your chart of accounts: ➢ In the Accounting menu, select Advanced, then click Chart of accounts. ➢ Click Add Account. ➢ Enter the components of each required account. 2. Follow the below instructions to add the new inventory items: ➢ In the Business menu, select Products and services. ➢ Click New Item. ➢ Complete the Item Code and Item Name fields. ➢ Select the I purchase this item checkbox and complete each of the fields you wantto set a default value for. Repeat this process with the I sell this itemcheckbox. ➢ Do NOT track inventory items at this stage. 3. Follow the below instructions to enter your conversion (opening) balances manually through the trial balance: ➢ Click on the Accounting Tab. ➢ Click on Conversion balances ➢ Click on Conversion Date. Conversion Date is “September 2020”. ➢ Return to Trial balance and entre all the required figures into your opening Trial Balance. ➢ When entering conversion balances in Xero, enter all balances as positive numbers, whether they are debits or credits. ➢ Click on Save. ➢ The balances for accounts receivable and accounts payable need to be backedup by the corresponding unpaid invoices and bills. ➢ Entre the unpaid invoices. ➢ Entre the unpaid bills. 4. Follow the below instructions to entre the opening inventories on hand at 1 st of September 2020. ➢ Click on products and services. ➢ Click on each inventory item. ➢ Click on Edit item. ➢ Tick the box for “I track this item” for each inventory item. ➢ Under products and services click on “Import”. ➢ Click on Opening Balances. ➢ Under “Update opening balances” choose 1 September 2020 as the date of your opening balances. ➢ Download existing opening balances. Page 15 of 16➢ Complete the Excel template and record the opening balances within the Excel template. ➢ Upload the updated template. ➢ Click on Continue. 5. Please make sure that you save your conversion (i.e. opening) balance beforeentering September transactions. 6. You can now continue with the assignment and record the required transactions. If you require any further help, please refer to the Xero help-sheets provided on weeklybasis. |