1 FIT1013 Digital Futures: IT for Business Assignment 1 (20%) Submission Deadline: Monday, 7 September 2020, 11:00 PM Learning Objectives By completing this assignment, students should be able to perform data analysis and data visualisation using Excel, these include creating a structured range of data using a PivotTable, Excel formulas and functions. 5% of this assignment consists of a Moodle quiz to be completed in Week 6. This is an individual assignment, no group work will be permitted. Submission Requirements Submission must be made by the due date otherwise a penalty of 10% reduction in the marks gained per late day will be applied. (For example, that means if you got 70%, but are 2 days late, your mark will be reduced by 10% of 70 (the marks gained) x 2 (two days late), 14 marks). Assignments are to be submitted online to Moodle. Please name your Excel file according to this format: LastName_ID_FIT1013A1 where LastName is your last name and ID is your student ID. In order to ensure that the assignment files are uploaded successfully, please download the assignment file (after uploading to Moodle) and check if it works as expected. Failing to do so may result in late submission if the file cannot be opened or is corrupted. In-semester special consideration application process – please see the following page for eligibility and instructions: https://www.monash.edu/exams/changes/special-consideration. See also link for help in completing this assignment: http://www.monash.edu/it/current- students/resources-and-support/style-guide Task I (15%) Scenario SDR (Simon’s Drones & Robotics) is a tech toy retail with several stores in Victoria. They sell different tech toys e.g. Camera Drone, Electric Scooter, Electric Gokart, Electric Transporter, and Coding Robot. Your friend, Simon working in SDR, regularly creates reports about the sales. Given the data file (FIT1013 A1_2020.xlsx), he would like you to use Excel functions and features to help him analyse the data and making the file more user-friendly for him in future analysis and data visualisations. 1. To understand better about the data, you would like to do a quick analysis using simple Excel functions and features, to get the quantity sold for each type of tech toys and the total sales, like the following table. You will do this on a separate worksheet without messing the original data. Table 1: Summary Analysis Types Number of Toys Sold Total Sales ($) Camera Drone Coding Robot Electric Gokart Electric Scooter Electric Transporter Total 2 2. A separate worksheet with the headers from the given data file (i.e. Date, Types, Sale, Store) to allow selectively view the data dynamically, e.g. only a certain type of toys; only a certain month’s data, and so on. Also sort the data e.g. sort the sales and store. For the selected data, show the total sales at the last row. (For assessment purpose, implement the filter and sorting as shown in the following figure). Figure 1: Selected and sorted data 3. Once you have done the quick analysis on the data, you want to create user-friendly worksheets for Simon using pivot tables and pivot charts. This will allow him to navigate and visualise the data easily. Below are some of the features and task descriptions that he would like from the data file. a. Simon wants to view his data in charts so that he can quickly identify any trends or patterns from his data. Use pivot tables and pivot charts (he is not fussy about the type of charts) to show the sale by different variables, i.e. for each month, what were the sales for each type of toys, and for each store, etc. b. To improve usability, he wanted slicers that can be used to filter the data in pivot tables and pivot charts. Create slicers for Simon to: i. View sales by type of toys ii. View sales by month iii. View sales by store You will decide for him the types of chart, the structures, and whether to use separate worksheets for various charts. c. Simon also wants the workbook to be user-friendly, e.g. overall presentation of data, design and format of outputs are easy to read and use. 3 4. Simon is also responsible to calculate the commissions for the salespersons. His calculation is based on the following table. Table 2: Commissions table Monthly sales Commissions 0 – $1,200 Nil $1,201 – $3,000 10% for each $1 over $1,200 $3,001 – $8,000 $180 plus 20% for each $1 over $3,000 $8,001 – $15,000 $1,180 plus 30% for each $1 over $8,000 $15,001 and over $3,280 plus 40% for each $1 over $15,000 He is asking you to construct an Excel formula in the Commissions column of the following table to determine the commissions amount based on the sales amount (column 2). This formula can be copied to subsequent cells without modifications. When the formula is copied to the rows with empty record (no sales amount), it should show blank. Table 3: Commissions for employees Salesperson Sales Commissions Example $4,398.00 $459.60 Peter $1,198.30 Andrew $3,934.00 James $18,662.00 John $9,225.40 Task II (5%) Moodle quiz will open from 3rd Sep 8am to 7th Sep 11pm. Once you start the quiz, you will have 10 Minutes to complete it. The Quiz will cover all topics from Week 1 to Week 5. See Assessment tile in Moodle for more information. Also see sample quiz for format and sample questions. Assessment Criteria Tasks Marks Descriptions 1 1 Correct value, use appropriate functions or features. 2 2 Correct table and value. 3a 2 Appropriate pivot table and chart. 3b 2 Correct slicers, use appropriate charts. 3c 3 Correct format and appropriate presentations. 4 5 Correct value, use appropriate functions. Total 15
