FIT1013 Digital Futures: IT for Business
Assignment 2 Developing an application using Excel (VBA) (35%)
Part 1: Friday, 22 September 2023, 4:30 PM
Part 2: Friday, 13 October 2023, 4:30 PM
Group Assignment (Maximum of 4 students)
Part 2 Requirements
Functionality
1. A user form named “Complete Rental” will be shown when the corresponding button in the main menu is
clicked. The form allows the details of a return (i.e. specifically the return of a vehicle) to be recorded.
i. Each return is corresponding to a specific vehicle rented (see RentalDetails worksheet). A list of
vehicles currently rented (not returned yet) will be made available for selection.
ii. The return date/time can be entered manually.
iii. Users can select the return condition – either Good, Fair, Damaged, and Dirty.
iv. The cost of the rental (including the delivery charge) will be calculated and updated to the
TotalRentalFees column.
The cost of rental is calculated based on the model of vehicle, duration of rental and a delivery
cost. Rental less than 24 hours will be charged for a day plus a delivery fee. For rental more than
24 hours, the due time is 12 noon.
For example, a 2021 Aston Martin Rapide is delivered at 11.30am on 23rd December, and returned
on 29th December 9am will be charged for 6-day, cost of rental is $618.75 x 6 days + $150 delivery
cost (per vehicle), will come to a total cost of $3712.5.
As this rental is more than 24 hours, this is how it looks like:
23/12/2021 11:30 - 24/12/2021 12:00 - 1 day - $618.75
24/12/2021 12:00 - 25/12/2021 12:00 - 1 day - $618.75
25/12/2021 12:00 - 26/12/2021 12:00 - 1 day - $618.75
26/12/2021 12:00 - 27/12/2021 12:00 - 1 day - $618.75
27/12/2021 12:00 - 28/12/2021 12:00 - 1 day - $618.75
28/12/2021 12:00 - 29/12/2021 9:00 - 1 day - $618.75
plus delivery cost $150
Another example, a 2017 Mercedes-Benz AMG GT is delivered at 15.30pm on 23rd December,
and returned on 24th December 11am will be charged for 1 day only, cost of rental is $450 x 1
days + $150 delivery cost (per vehicle), will come to a total cost of $600.
As this rental is less than 24 hours, this is how it looks like:
23/12/2021 15:30 - 24/12/2021 11:00 - 1 day - $450
plus delivery cost $150
2
v. The form should include a ‘Complete’ button that transfers the details to the corresponding row in
the RentalDetails worksheet, updates Add-ons quantity in the corresponding sheet, then returns to
the previous form i.e. “Main Menu” form.
vi. Remove the highlights (e.g. yellow colour) for the ReturnDate, ReturnCondition and
TotalRentalFees columns for this rental.
vii. In typical scenarios, customers are expected to return their rented vehicles to the designated return
centre. However, LuxDrive goes the extra mile to provide an exceptional experience by offering a
unique option. This means LuxDrive's staff can seamlessly collect the rented vehicle from the
customer's location through the Vehicle Return Tracking System (VRTS).
The Vehicle Return Tracking System (VRTS) used by LuxDrive’s staff is a mobile app that utilizes
AI technology to automatically recognize a vehicle’s number plate and return condition. The AI
compares the captured images with reference images of the vehicle's pristine condition, stored in
LuxDrive's database. This allows the app to detect discrepancies and assess the extent of damages.
The vehicle return data can be saved as an Excel file. An example file is given to you – “FIT1013
A2_2023_track_return.xlsx”. The CEO of LuxDrive, Alex would like to have a button that can
import these collection data from the Excel file and transfer to the RentalDetails worksheet (i.e. in
replacing the manual steps above). Name the button “Import from Return”. (9 marks)
2. A user form named “Display Rental” will be shown when the corresponding button in the main menu is
clicked. It allows users to choose a customer from a list, then input a start date and end date. It will then
present all completed rentals between those dates, for the selected customer. A button is to be included on
this user form for users to print the data to a PDF file (name it as Display,
e.g. 11203Display20230925). Design the user form in such a way that it will display the following details:
The date on which the invoice is produced, customer first and last name, car make and model, car
year, rent date, return date, return condition, add-ons, total rental fees for each vehicle, and a
grand total for the period. (3 marks)
Quality of Solution
Some considerations:
1. Appropriate use of graphic controls and consistency in the design of your user forms.
2. Appropriateness of variable and constant declarations and usage, e.g. are conventions followed, are
variables declared in suitable places, etc.? Is the code concise, easy to read and understand?
3. Excellent use of decision structures and repetition structures.
4. Include data validation to ensure the user only enters valid information, and report any meaningful
error messages.
5. Robustness - does the solution cope well with human errors, e.g. protected the sheet or range that are
supposed to be read only by a data entry clerk?
6. Use appropriate indentation in your code so that it is easily readable. Include appropriate
documentation (or comments) in your code.
(6 marks)
Demonstrations
Demonstrate your application to your tutors in Week 12. (2 marks)
Files Provided
● FIT1013 A2_2023_track_return.xlsx