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