Assignment title: Information

CIS5100 ASSIGNMENT 2 MARKING SCHEME Student Name: Click here to enter text. Student ID: Click here to enter text. Marker Initials: Choose an item. Date Marked: Click here to enter a date. This document is intended to both grade and provide feedback on CIS5100 Assignment 2. Please note the mark given for each activity and the areas that were of issue in your submission. MARKING SUMMARY Spreadsheet Creation and Table Import / 2 Data Validation / 3 Index Worksheet / 3 Data Input Worksheet / 5 Calculations Worksheet / 10 Report Worksheet – Cell References / 2 Tables Worksheets – Range Names / 2 Report Worksheet – VLOOKUP Functions / 5 Report Worksheet – IF Functions (Cost Price AU) / 5 Report Worksheet – IF Functions (Selling Price) / 10 Report Worksheet – IF Functions (Freight Cost) / 10 Report Worksheet – IF/AND Function (Discount) / 5 Report Worksheet – Purchases, Sales and Order Discount Formulas / 3 Documentation Worksheet / 10 Scenario Manager Development / 5 Scenario Manager Summary Output / 5 Overall Professional Presentation of All Worksheets / 5 Scenario Manager Analysis Essay / 10 Final Mark / 100 Note: Major Academic Misconduct will incur loss of all marks for the assignment and may lead to failure of the course. Students usually perform well in the assignment but a satisfactory performance in the exam is also required for students to pass this course. ASSIGNMENT TASK 1 Demonstrate Problem Solving Skills by creating an Excel Spreadsheet and Importing Data from Assignment 1 Access 2007/2010/2013 Database into four Table Worksheets as per Task 1. SPREADSHEET CREATION AND TABLE IMPORT Logical development of four Table Worksheets as per instructions. 2 1 0 Pass: Fail: Not Submitted: Satisfactorily meets the criteria for creation of Spreadsheet. Did not sufficiently meet the criteria to pass. No attempt made or diverse from what is acceptable. Issues detected about your Spreadsheet Data Import and Table Worksheets Development: ASSIGNMENT TASK 2 Check the imported data in the CustomersTable, ItemsTable, SuppliersTable and OrdersTable as per Task 2. TABLES WORKSHEETS – VALIDATION CHECK Demonstrate knowledge of data validation by checking the imported data in the CustomersTable, ItemsTable, SuppliersTable and OrdersTable. 3 2 1 0 Outstanding: Pass: Fail: Not Submitted: Data contained in the four (4) worksheets shows clear validation checking resulting in complete, accurate and reasonable data values in these tables. Satisfactorily meets the criteria with minor issues. Did not sufficiently meet the criteria to pass. No attempt made or diverse from what is acceptable. Issues detected about your Validation Checking of the Spreadsheet Data Values: ASSIGNMENT TASK 3 Develop the Index Worksheet as per Task 3. INDEX WORKSHEET Demonstrate written communication skills by creating an Index Worksheet; ensuring that it contains all the recommended data for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials – Spreadsheet design considerations. 3 2 1 0 Outstanding: Pass: Fail: Not Submitted: Competently meets the criteria for development of all elements required for this worksheet. Satisfactorily meets the criteria with minor issues. Did not sufficiently meet the criteria to pass. No attempt made or diverse from what is acceptable. Issues detected about your Index Worksheet Development: ASSIGNMENT TASK 4, 10, 16 & 18 Develop the Data Input Worksheet as per Tasks 4, 10, 16 & 18. DATA INPUT WORKSHEET Demonstrate Problem Solving Skills by developing a Data Input worksheet; ensuring that it conforms to the guidelines for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials – Spreadsheet design considerations. 5 4 ½ 4 3 ½ 2 ½ 1 ½ 0 Outstanding: High Distinction: Distinction: Credit: Pass: Fail: Not Submitted: An outstanding attempt – well developed containing all elements required for this worksheet. An excellent piece of work that meets all the specified criteria. More than competently meets the criteria specified with only minor mistakes or omissions. Competently meets the criteria as specified with few minor mistakes or omissions. Satisfactorily meets the criteria. Did not sufficiently meet the criteria to pass. No attempt made or diverse from what is acceptable Issues detected about your Data Input Worksheet Development: ASSIGNMENT TASK 5 & 17 Develop the Calculations Worksheet as per Tasks 5 and 17. CALCULATIONS WORKSHEET Demonstrate Problem Solving Skills by developing a Calculations worksheet, ensuring that it contains functions to calculate Total Purchases, Total Freight, Total Discount for Orders and Total Sales. Formulas to calculate Total Fixed and Variable Expenses, Total Profit and Number of Orders where a Discount was Applied. Ensuring that they conform to the guidelines for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials – Spreadsheet design considerations. 10 9 8 7 5 3 0 Outstanding: High Distinction: Distinction: Credit: Pass: Fail: Not Submitted: An outstanding attempt – well developed containing all elements required for this worksheet. An excellent piece of work that meets all the specified criteria. More than competently meets the criteria specified with only minor mistakes or omissions. Competently meets the criteria as specified with few minor mistakes or omissions. Satisfactorily meets the criteria. Did not sufficiently meet the criteria to pass. No attempt made or diverse from what is acceptable Issues detected about your Calculations Worksheet Development: ASSIGNMENT TASKS 6, 7, 8, 9, 11, 12, 13, 14 & 15 Develop the Report Worksheet, including all formulas and functions as per Tasks 6, 7, 8, 9, 11, 12, 13, 14 and 15. REPORT WORKSHEET – CELL REFERENCES Demonstrate Problem Solving Skills by developing a Report worksheet, ensuring that it contains cell references of CustID, ItemID, OrderDate and OrderQty for all rows of data on the Report worksheet with data drawn from the OrdersTable worksheets. Ensure that it conforms to the guidelines for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials – Spreadsheet design considerations. 2 1 0 Pass: Fail: Not Submitted: Satisfactorily meets the criteria for development of the Cell References. Did not sufficiently meet the criteria to pass. No attempt made or diverse from what is acceptable. Issues detected about your Report Worksheet Cell References Development: TABLES WORKSHEETS - RANGE NAMES Demonstrate Problem Solving Skills by developing the required three (3) Cell Range Names on the CustomersTable, ItemsTable and SuppliersTable worksheets. 2 1 0 Pass: Fail: Not Submitted: Satisfactorily meets the criteria for development of the Range Names. Did not sufficiently meet the criteria to pass. No attempt made or diverse from what is acceptable. Issues detected about your Spreadsheet Range Names Development: REPORT WORKSHEET - VLOOKUP FUNCTIONS Demonstrate Problem Solving Skills by developing a Report worksheet, ensuring that it contains VLOOKUP functions using Cell Range Names, for all rows of data on the Report worksheet with data drawn from the CustomersTable, ItemsTable and SuppliersTable worksheets. Ensure that it conforms to the guidelines for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials – Spreadsheet design considerations. 5 4 ½ 4 3 ½ 2 ½ 1 ½ 0 Outstanding: High Distinction: Distinction: Credit: Pass: Fail: Not Submitted: An outstanding attempt – well developed and logically thought out VLOOKUP functions. An excellent piece of work that meets all the specified criteria. More than competently meets the criteria specified with only minor mistakes or omissions. Competently meets the criteria as specified with few minor mistakes or omissions. Satisfactorily meets the criteria. Did not sufficiently meet the criteria to pass. No attempt made or diverse from what is acceptable Issues detected about your Report Worksheet VLOOKUP Function Development: REPORT WORKSHEET - IF FUNCTIONS (COST PRICE (AU)) Demonstrate Problem Solving Skills by developing a Report worksheet, ensuring that it contains Nested IF functions to determine the Cost Price (AU) based on the Exchange Rate Type in cell D7 of the Data Input Worksheet. Ensure that it conforms to the guidelines for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials – Spreadsheet design considerations. 5 4 ½ 4 3 ½ 2 ½ 1 ½ 0 Outstanding: High Distinction: Distinction: Credit: Pass: Fail: Not Submitted: An outstanding attempt – well developed and logically thought out IF functions. An excellent piece of work that meets all the specified criteria. More than competently meets the criteria specified with only minor mistakes or omissions. Competently meets the criteria as specified with few minor mistakes or omissions. Satisfactorily meets the criteria. Did not sufficiently meet the criteria to pass. No attempt made or diverse from what is acceptable Issues detected about your Report Worksheet IF Function (Cost Price (AU)) Development: REPORT WORKSHEET - IF FUNCTIONS (SELLING PRICE) Demonstrate Problem Solving Skills by developing a Report worksheet, ensuring that it contains Nested IF functions to determine the Selling Price based on the MarkUp Type in cell D4 of the Data Input Worksheet. Ensure that it conforms to the guidelines for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials – Spreadsheet design considerations. 10 9 8 7 5 3 0 Outstanding: High Distinction: Distinction: Credit: Pass: Fail: Not Submitted: An outstanding attempt – well developed and logically thought out IF functions. An excellent piece of work that meets all the specified criteria. More than competently meets the criteria specified with only minor mistakes or omissions. Competently meets the criteria as specified with few minor mistakes or omissions. Satisfactorily meets the criteria. Did not sufficiently meet the criteria to pass. No attempt made or diverse from what is acceptable Issues detected about your Report Worksheet IF Function (Selling Price) Development: REPORT WORKSHEET - IF FUNCTIONS (FREIGHT COST) Demonstrate Problem Solving Skills by developing a Report worksheet, ensuring that it contains Nested IF functions to determine the Freight Cost based on the Freight Type in cell D5 of the Data Input Worksheet. Ensure that it conforms to the guidelines for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials – Spreadsheet design considerations. 10 9 8 7 5 3 0 Outstanding: High Distinction: Distinction: Credit: Pass: Fail: Not Submitted: An outstanding attempt – well developed and logically thought out IF functions. An excellent piece of work that meets all the specified criteria. More than competently meets the criteria specified with only minor mistakes or omissions. Competently meets the criteria as specified with few minor mistakes or omissions. Satisfactorily meets the criteria. Did not sufficiently meet the criteria to pass. No attempt made or diverse from what is acceptable Issues detected about your Report Worksheet IF Function (Freight Cost) Development: REPORT WORKSHEET – IF/AND FUNCTIONS (DISCOUNT) Demonstrate Problem Solving Skills by developing a Report worksheet, ensuring that it contains IF/AND functions to determine the Discount amount based on the Order Qty and the Selling Price. Ensure that it conforms to the guidelines for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials – Spreadsheet design considerations. 5 4 ½ 4 3 ½ 2 ½ 1 ½ 0 Outstanding: High Distinction: Distinction: Credit: Pass: Fail: Not Submitted: An outstanding attempt – well developed and logically thought out IF/AND functions. An excellent piece of work that meets all the specified criteria. More than competently meets the criteria specified with only minor mistakes or omissions. Competently meets the criteria as specified with few minor mistakes or omissions. Satisfactorily meets the criteria. Did not sufficiently meet the criteria to pass. No attempt made or diverse from what is acceptable Issues detected about your Report Worksheet IF/AND Function (Discount) Development: REPORT WORKSHEET – PURCHASES, SALES AND ORDER DISCOUNT FORMULAS Demonstrate Problem Solving Skills by developing a Report worksheet, ensuring that it contains three (3) simple formulas to calculate the Purchases, Sales and Order Discount Formulas. Ensure that it conforms to the guidelines for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials – Spreadsheet design considerations. 3 2 1 0 Outstanding: Pass: Fail: Not Submitted: Competently meets the criteria for development of the Purchases and Sales formulas. Satisfactorily meets the criteria with minor issues. Did not sufficiently meet the criteria to pass. No attempt made or diverse from what is acceptable. Issues detected about your Report Worksheet Formulas (Purchases, Sales and Order Discount) Development: ASSIGNMENT TASK 19 Develop the Documentation Worksheet, including all formulas and functions as per Task 19. DOCUMENTATION WORKSHEET Demonstrate written communication skills by creating a Documentation worksheet; ensuring that it contains all the recommended data for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials – Spreadsheet design considerations. 10 9 8 7 5 3 0 Outstanding: High Distinction: Distinction: Credit: Pass: Fail: Not Submitted: An outstanding attempt – well developed containing all elements required for this worksheet. An excellent piece of work that meets all the specified criteria. More than competently meets the criteria specified with only minor mistakes or omissions. Competently meets the criteria as specified with few minor mistakes or omissions. Satisfactorily meets the criteria. Did not sufficiently meet the criteria to pass. No attempt made or diverse from what is acceptable Issues detected about your Documentation Worksheet Development: ASSIGNMENT TASK 20 & 21 Develop the Scenario Summary as per Tasks 20 and 21. SCENARIO MANAGER DEVELOPMENT Demonstrate Problem Solving Skills by developing the eight (8) Scenarios as outlined in the assignment based on the changing cells on the Input Data worksheet. 5 4 ½ 4 3 ½ 2 ½ 1 ½ 0 Outstanding: High Distinction: Distinction: Credit: Pass: Fail: Not Submitted: An outstanding attempt – well developed and named scenarios. An excellent piece of work that meets all the specified criteria. More than competently meets the criteria specified with only minor mistakes or omissions. Competently meets the criteria as specified with few minor mistakes or omissions. Satisfactorily meets the criteria. Did not sufficiently meet the criteria to pass. No attempt made or diverse from what is acceptable Issues detected about the Development of your Scenarios: SCENARIO MANAGER SUMMARY OUTPUT Demonstrate Problem Solving Skills by developing a Scenario Summary for the eight (8) Scenarios outline in the assignment based on the results cells found on the eight (8) results cells from the Report worksheet. Ensuring that the output is user friendly and professionally presented. 5 4 ½ 4 3 ½ 2 ½ 1 ½ 0 Outstanding: High Distinction: Distinction: Credit: Pass: Fail: Not Submitted: An outstanding attempt – well developed, logically throughout and presented scenario manager summary. An excellent piece of work that meets all the specified criteria. More than competently meets the criteria specified with only minor mistakes or omissions. Competently meets the criteria as specified with few minor mistakes or omissions. Satisfactorily meets the criteria. Did not sufficiently meet the criteria to pass. No attempt made or diverse from what is acceptable Issues detected about the Development of your Scenario Summary Report: OVERALL PROFESSIONAL PRESENTATION OF ALL WORKSHEETS Demonstrate communication skills by modifying all worksheets to a professional level of presentation, making sure that the headings are in English, and that the data is formatted correctly. Use of bold, italics, font size, font colours, shading, lines and borders is essential to give the worksheets a professional look and feel. 5 4 ½ 4 3 ½ 2 ½ 1 ½ 0 Outstanding: High Distinction: Distinction: Credit: Pass: Fail: Not Submitted: An outstanding attempt – well developed and professionally presented piece of work. An excellent piece of work that meets all the specified criteria. More than competently meets the criteria specified with only minor mistakes or omissions. Competently meets the criteria as specified with few minor mistakes or omissions. Satisfactorily meets the criteria. Did not sufficiently meet the criteria to pass. No attempt made or diverse from what is acceptable Issues detected about your Spreadsheet Professional Development and Presentation: ASSIGNMENT TASK 22 Develop an Analytical Essay based on the findings from the Scenario Summary as per Task 22. SCENARIO MANAGER ANALYTICAL ESSAY: Demonstrate written communication skills by creating a well-structured analytical essay based on the findings from the Scenario Summary outlining the major issues that it has highlighted to the business. 10 9 8 7 5 3 0 Outstanding: High Distinction: Distinction: Credit: Pass: Fail: Not Submitted: An outstanding attempt – well developed and logically throughout analysis of the scenario summary. An excellent piece of work that meets all the specified criteria. More than competently meets the criteria specified with only minor mistakes or omissions. Competently meets the criteria as specified with few minor mistakes or omissions. Satisfactorily meets the criteria. Did not sufficiently meet the criteria to pass. No attempt made or diverse from what is acceptable Issues detected about your Analytical Essay: