FIT1013  Assignment 1  Solution

Learning Objectives

Submission Requirements
● The file names should contain Unit Code, assignment number and your student ID number, similar to the example: FIT1013A1_StudentID.xlsx where StudentID is your student ID.


Scenario
SSS (Simon’s Smart Home Service) is a new start-up in Victoria that sells and installs smart home products, as well as providing home automation services and supports. Your friend, Simon working in SSS, regularly creates reports about the business operations. Given the data file (FIT1013 A1_2021 Data.xlsx in Moodle), he would like you to use Excel functions and features to help him analyse the data and make the file more userfriendly 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 to get the number of hours worked for each type of service, similar to the following table. You will do this on a separate worksheet without messing up the original data.

Table 1: Quick Analysis


Figure 1: Selected and sorted data

3. Once you have done the quick analysis on the data, you want to create a user-friendly worksheet for Simon that allows him to navigate and visualise the data easily. You will use a pivot table and a pivot chart to show his data so that he can quickly identify any trends or patterns from his data. He is not fussy about the types of charts, so you will decide that for him. He wants to see the number of hours worked by each technician, for each month, and for each job. To improve usability, you will create slicers that can be used to filter the data in pivot table and pivot chart:
i. View the number of hours worked by technician
ii. View the number of hours worked by month
iii. View the number of hours worked by job


4. Simon also wanted to check if any of the technicians are overloaded or underloaded. His calculation is based on the following table.
Table 2: Workload-Status table

He is asking you to create a new worksheet (similar to the following table), where he can input the number of hours worked (column 2), then a status will be shown automatically in column 3. You need to construct an Excel formula in the status column to determine the workload of his technicians. These formulas can be copied to subsequent cells without modifications. When the formula is copied to the rows with an empty record (no Number of hours worked per week), it should show blank.




Table 3: Technicians Workload
StaffID Number of hours worked per week Status
Example: 3041 28.75 Normal
9839
5074
5788
9045
5934

5. 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. You can add a brief instruction in the Document worksheet to describe how to use this workbook.
Assessment Criteria

See marking rubric in Moodle.

1 2 New worksheet, use appropriate functions, correct references and value.
2 2 New worksheet, correct table, correct value (filter & sort).
3 2 New worksheet, appropriate pivot table and chart, correct slicers, correct values.
4 2 Correct value, use appropriate functions with correct attributes. Effective use of functions, e.g. require minimum maintenance, correct nested functions.
5 2 Document worksheet completed.
Overall correct format and appropriate presentations.
Total 10