FIT1013代做、Java/c++程序语言代做

日期：2023-08-23 09:54

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 – 15000 Regular maintenance

15001 – 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.