help me for this assignment please! thanks very much!

Assessment Information - Trimester 2, 2016 Subject Code: ACC 202 Subject Name: Accounting Information Systems Assessment Title: Individual: Excel Assignment Weighting: 10% of the overall mark for this subject Total Marks: 70 Marks Due Date: Friday 2nd Sept 2016 by 11:50 PM (AEST). Note: The above time is AEST. Brisbane and Adelaide must allow for the time difference. . Assessment Description Learning Outcome 5: Demonstrate technical knowledge with MYOB and Excel applications Introduction to the individual assignment: This is an individual assignment and must be completed using Microsoft Excel. These introductory excel skills you learn will be used in later subjects and in your professional life. Employers expect students to have good worksheet skills. Assignment Information: The assignment comprises 3 tasks. Each task must be completed on a separate worksheet. Worksheets must be named to clearly identify the question being answered. Marks will not be awarded for any manual calculations. Ensure you enter all required data at the beginning of your answer and use the required formulas and functions to present your answer. All dollar amounts must be provided in currency format and 2 decimal places. You will also be assessed on professional presentation. Ensure to use tables, borders and colours where appropriate. Submit this assignment as an electronic assignment via the ACC 202 subject page on the student portal. Instructions are on the front page of the portal. Ensure that your work can be clearly identified with your student ID and FULL student name. Late penalties will apply for all late submissions. This assignment must be your own work. Any work which has been copied or shared between students will result in a Fail grade. Please make sure you follow the guidelines especially those relating to the presentation of written work, late assignment policy and academic integrity. ACC 202 Accounting Information Systems Trimester 2, 2016 - ASSIGNMENT Page 1 of 4 Task 1: (8 + 4 + 4 + 4 = 20 marks) Gelato-to-go is a gelato ice cream making business specialising in large tubs of gelato for shops and cafes. The following table shows the expected number of gelato tubs to be sold between April and Jun 2016: Month Strawberries & Cream April 3400 1500 4100 May 6500 2500 6000 June 4500 1800 3800 Choc Chip Rum and Raisin The cost to produce each tub of gelato is as follows: Strawberries & Cream $5.70 Choc Chip $6.50 Rum and Raisin $9.30 The selling price is determined by applying a mark-up percentage on cost as follows: Strawberries & Cream gelato is marked up by 60% Choc Chip is marked up by 50% Rum and Raisin is marked up by 100% REQUIRED: A. B. C. D. Using the data above, prepare a Revenue table for gelato, for each month. Using the data above, prepare a Profit Summary per month, for each gelato flavour (must calculate the profit directly. You CANNOT add another table for total cost) Recalculate the profit for each for each product but this time using a different formula. (Double check - your two profit tables must be exactly the same) Overall presentation i.e. borders, shading, titles and alignment, Note: Only enter the information once as data. All three tables in requirement (A), (B) and (C) must be all formula driven. Use absolute referencing where appropriate. ACC 202 Accounting Information Systems Trimester 2, 2016 - ASSIGNMENT Page 2 of 4 Task 2: (4 + 4 + 4 + 4 + 4 = 20 marks) The enclosed worksheet ACC 202 Task 2 contains some of the scores of different teams from matches played in season 2014 at different venues. Required: Using suitable formulas (like IF, Count) to find and suitably present the following: A. \"Margin\" for each round (margin is always a positive number) - insert additional column/s as appropriate. B. The winning team for each round - insert additional column/s as appropriate. C. The losing team for each round - insert additional column/s as appropriate. D. Identify the team(s) that won maximum number of rounds, overall. E. Your work need be clearly presented and suitably labelled. Task 3: (30 marks) The following employees work for Bright Electricals Ltd, a public company specialising in production of electrical components. The employees are paid an hourly rate, based on their Job Classification level. If an employee works more than 40 hours, they will be paid the overtime rate for the additional hours. The following table summarises the classifications and level of pay per classification: Table A Level Hourly Pay 1 $15 2 $20 3 $25 4 $30 5 $35 6 $45 7 $50 8 $55 Overtime* $65 * The overtime hourly rate is $65 per hour for ALL employees, regardless of their classification level ACC 202 Accounting Information Systems Trimester 2, 2016 - ASSIGNMENT Page 3 of 4 Bright Lights Ltd has the following employees: Table B Staff name Level Hrs worked Simonette 5 32 Dat 4 41 Jemma 4 78 Rosemarie 5 20 Jessica 2 41 Nicole 8 56 Kellie 7 30 Alenna 3 58 Molomaa 8 47 Tara 3 44 Yifang 8 22 Thomas 4 68 Suriya 7 37 Xunyi 7 70 Noble 3 56 Rulande 1 49 REQUIRED: You are required to setup a table which includes the following details: Staff Name Level Hours worked Hourly pay Typed Overtime hours (if any) Regular pay Overtime pay Total pay Must use a formula in this column Please note: A. The above table must be sorted by level first and then by staff name. B. Marks will be awarded for presentation and correct use of cell referencing, use of functions and formulae (e.g. VLOOKUP, IF, Sum) C. All columns must be totalled where appropriate. D. No marks will be awarded if the correct function or formula is not used or if data has been manually entered. End of Assignment ACC 202 Accounting Information Systems Trimester 2, 2016 - ASSIGNMENT Page 4 of 4