FIT1013 Digital Futures: IT for Business
Assignment 1 Data Analysis and Data Visualisation (25%)
Submission Deadline: Friday, 1 September 2023, 11:55 PM
Learning Objectives
�� By completing this assignment, students will be able to perform data analysis and data visualisation
using Excel, these include creating a structured range of data using a PivotTable, PivotChart, Excel
formulas and functions. Also, students will be able to create an Excel application using macros.
�� This is an individual assignment, no group work will be permitted.
Submission Requirements
�� Your assignment should be submitted to Moodle/Turnitin.
�� The file names should contain Unit Code, assignment number and your student ID number, similar to
the example: FIT1013A1_StudentID.xlsm where StudentID is your student ID.
Late Submissions:
�� The late-submission penalty is 10 percent of the available marks in that task, not the marks you
received. For instance, an assignment has 100 marks, and you submitted the assignment one day late
and received 65 marks. In this case, the penalty is 10 marks deduction (10% of the total available
marks).
o 65 - (10% of 100) = 55 of 100 marks
Scenario1
LuxDrive - Revolutionizing Car Rental in Australia
Introduction:
LuxDrive is an innovative car rental start-up in Australia, aiming to transform the traditional car rental
industry. Unlike conventional car rental companies, LuxDrive offers customers the opportunity to rent high-
end luxury cars at a fraction of the original cost. With a vast fleet of prestigious automotive brands, customers
can experience driving their dream car without the heavy financial burden.
Business Model:
LuxDrive adopts a unique business model by purchasing luxury cars wholesale directly from manufacturers
and dealers. By doing so, they can negotiate better prices, allowing them to rent out these high-end vehicles
at significantly reduced rates. For instance, customers can enjoy cruising in a Porsche that typically costs
hundreds of thousands of dollars for just AUD 500 per day.
Jason, your manager at LuxDrive, regularly creates reports about the business operations. Given the data file
(FIT1013 A1_2023 Data.xlsx in Moodle), he would like you to use Excel functions and features to help him
1 This assignment promotes students' problem-solving skills. You are given a scenario and client��s requirements. Complete the
tasks creatively by applying the knowledge and skills obtained in the first four weeks of this unit.
2
analyse the data for valuable insights and make the file more user-friendly, which, in turn, inform business
strategies to ensure continuous growth and success in the competitive market.
1. Quick Analysis Using Excel Functions
a) To understand better about the data, you would like to do a quick analysis using Excel functions to get
the No. of Rentals, rentals amount in 2021 and 2022 for each particular car model, similar to the
following table. You should do this on a separate worksheet without messing up the original data.
Remove any duplicate data if necessary.
Table 1: Quick Analysis using simple functions e.g. references
b) Due to the ongoing prevalence of inflation in Australia, the costs of car maintenance and fuel keep
rising, making it challenging for LuxDrive to manage expenses effectively. Assume that Jason has
decided to raise the rental price at the beginning of the next year, so the total rentals of 2023 go up by
20%. Add a new column to reflect the projected price change for 2023. Design such as a way that
Jason can update the price change easily by changing only one cell i.e., percentage.
c) Assuming all other variables are constant, what should the new percentage of the total rentals rise be,
so that the grand total in 2023 will reach AUD 500,000?
3
Note: Your answer provided should also include steps to illustrate how to achieve the solution in
Excel.
2. Implement Filter and Sort
Copy the original data worksheet (Car Rental) into a separate worksheet. With the headers from the given
data file (i.e. CarMake, CarModel, CarYear, TotalRentals, RentDate) to allow selectively view the data
dynamically, e.g. only show records from a certain year, a certain design, and so on. Also sort the data
(e.g. sort by the CarMake, CarModel, TotalRentals). For the selected data, show the total amount in the
last row. (For assessment purposes, implement the filter and sorting as shown in the following figure).
Figure 1: Selected and sorted data
3. Applying Conditional Formatting
Jasons wants to highlight certain values or make particular cells easy to identify on the sorted data.
Based on the worksheet in task 2, using Excel conditional formatting to:
a. Highlight the entire row in Yellow if the year of the car is 2021 or newer.
b. Highlight the entire row in Green if the Total Rentals is greater than AUD 15,000.
c. Highlight the entire row in Red if both the above conditions are met.
4
Figure 2: Applying Conditional Formatting
4. Create PivotTable and PivotChart
Once you have done the quick analysis on the data, you want to create a user-friendly worksheet for Jason
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, but he knows what he wants to see, e.g. the no. of rentals
by car makes and models, the total rentals for each year according to car models, etc.
First try to create separate charts to
i. View the no. of rentals by car makes and models
ii. View the total rentals by year and car models.
Suggest if there is a better view to include all this information. You could think of presenting the
information in one single PivotTable and a PivotChart that include car make and model, no. of rental and
total rentals by year.
To improve usability, you should create slicers that can be used to filter the data in pivot table and pivot
chart.
5. Advanced Functions
Jason also wanted to check if any of the rental vehicles are experiencing high demand with extensive travel
mileage, indicating a need for service and maintenance. The vehicles are identified based on the following
table.
Table 2: Service Status table
Mileage (km) Status
Below 5000 No service needed
5000 �C 15000 Regular maintenance
15001 �C 30000 Intermediate service
30001 - 45000 Major service
Above 45000 Extensive service
5
a) He is asking you to create a new worksheet (similar to the following table), where he can input the
mileage in KM (column 3, assuming the table range is D4:D9), then the status will be shown
automatically in the Status (column 4). You need to use nested IF functions to construct an Excel
formula in the Status column to determine the service needed. These formulas can be copied to
subsequent cells without modifications. When the formula is copied to the rows with an empty
record (i.e. no mileage input), it should show blank.
Your input should cover the testing of all the status that mentioned in Table 2.
Table 3: Rental Vehicle Service Status
Car Make Car Model Mileage (km) Status
Example:
Aston Martin
Example:
Rapide
Example:
1000
Example: No
Service needed
Aston Martin Rapide
Bentley Continental GT
Porsche 911
Maserati Ghibli
Mercedes-Benz AMG GT
Rolls-Royce Phantom
b) The VLOOKUP function can be used to achieve the above-mentioned outcome, but with a minor
modification of Table 2. Create a new worksheet that consists of modified Table 2 and Table 3, and
complete the formula in column 4 (Status) using the VLOOKUP function.
6. Macros
Jason also wants the workbook to provide some automatic features based on the table in task 5.
a. Copy the completed worksheet of task 5 into a separate worksheet named ��Macros��.
b. Create a button named ��Create Chart�� on the Marcos worksheet.
c. When the button is clicked, a 3D bar chart (similar to the below) will be automatically created on
a new worksheet showing the Rental Vehicle Mileage.
d. Create a button named ��Protect Sheet�� on the Marco worksheet.
6
e. When the button is clicked, it will create validation rules on the cell range D4:D9, so that only a
number between 0 and 50000 is allowed, otherwise, an error message will be displayed to the
user.
f. The entire worksheet will then be protected except the input cell range D4:D9.
g. Create a button named ��Unprotect Sheet�� on the Marco worksheet.
h. When the button is clicked, the entire worksheet will be unprotected.
7. Documentation and Presentation
Jason 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. Add a menu that provides linking to each of the tasks, and a brief
instruction in the Documentation worksheet to describe how to use this workbook.
7
Assessment Criteria
Marking rubric will be provided on Moodle.
Tasks Marks Descriptions
1 4 New worksheet, use appropriate functions, correct references and value. Correct use of Goal Seek function.
2 3 New worksheet, correct table, correct value (filter & sort).
3 2 Correct apply of Conditional Formatting.
4 4 New worksheets, appropriate pivot tables and charts, correct slicers, correct values.
5 5
New worksheets, correct values, use appropriate functions with
correct attributes. Effective use of functions, e.g. require minimum
maintenance, correct nested functions, correct VLOOKUP
function.
6 5
New worksheet, button associated with the correct macro. Correct
macro functionality e.g. chart generation, data validation and form
protection.
7 2
New Documentation worksheet, and completed.
Overall correct format, e.g. date, currency, etc. and appropriate
presentation style, usability, etc.