1




FIT1013 Digital Futures: IT for Business
Assignment 2 (20%)
Submission Deadline: 18th October 2019 11pm
Developing an application using Excel (VBA)
Group Assignment (Maximum of 3 students)

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: TeamID_FIT1013A2 where TeamID is
the assignment team’s ID assigned by your tutor, e.g. 007_FIT1013A2.xlsm.
● Application file format: Microsoft Excel VBA workbook, other documents in PDF format:
Meeting Minutes, Timesheet (from each of the team members).
● This is a group assignment, you will complete a peer evaluation at the end of the assignment
using CATME – more details to be posted on Moodle.
● 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.
● Your assignment will be marked using one of school’s lab computers. Hence you should
ensure that you test your application in Microsoft Excel 2016 (English version).
● You must discuss any extensions with your admin tutor/lecturer via the in-semester special
consideration process: http://www.infotech.monash.edu.au/resources/student/equity/special-
consideration.html. Please email your scanned documents to fit1013.allcampuses-
[email protected].
● See also link for help in completing this assignment: http://www.monash.edu/it/current-
students/resources-and-support/style-guide
Learning Objectives

Upon successful completion of this assignment, you should be able to:
i. Write Macros (sub procedures) using VBA in Microsoft Excel
ii. Use appropriate data types, declare and use variables and/or constants
iii. Write event procedures for some Excel and VBA objects.
iv. Use repetition and selection structures in VBA code
v. Use the Workbook, Worksheet and Range objects
vi. Use other objects as necessary
2


vii. Perform data validation on user input
viii. Construct arithmetic expressions in VBA code
ix. Follow appropriate rules relating to the scope of variables
x. Design user forms using a variety of controls
xi. Apply other useful worksheet functions where appropriate, e.g. Vlookup() and Format()
functions in VBA code.

Assessment Criteria
The assessment will give attention to how well you demonstrate your skills to complete the tasks – e.g.
ensure all assignment requirements are met, fulfil the functional requirements of the scenario, the
development is robust and maintainable.

It is important that each team member to contribute and participate equally, otherwise, you will be
marked based on the amount of effort and quality of the work that you produce. You should not divide
the tasks among yourselves – the assignment will be assessed as a group.

Demonstrate your in-progress application to your tutors during the tutorials (Week 9 to Week 11). This
will allow you to explain your design and what you’ve achieved in this assignment so far. Any member
who fails to turn up to any of the demonstrations will fail that component of the assignment.
Scenario
Sanitised Air Mattress (SAM) is a specialized mattress company that lease different size of mattress
for patients in several hospitals and medical centres. Due to the different medical conditions of each
patient, sometimes the standard mattresses provided in hospital are not suitable for the patients, e.g.
the standard mattresses could be too small, too hard, too soft, etc. The patient can hire these sanitised
air mattresses for the duration they required, to achieve better comfortability and even for medical
needs. There are three categories of mattresses available; namely Basic, Economy, and Deluxe. For
each customer - i.e. the hospital/medical centre, there is a contact person for mattress ordering – a.k.a
requestor. A requestor can make an order by phone or by email. When requestor place an order, SAM
delivers the mattress to the destinated ward in the hospital – unpack and inflate. At the end of the rental
period ( i.e. when a mattress is no longer needed), the requestor will inform SAM to collect the mattress
from the same location. These mattresses will then be sanitized (e.g. cleaned, sterilized, disinfected),
repacked and stored in SAM’s store room, ready for next use.

So far, all orders are recorded using simple Excel sheets. Due to the increases of order, SAM requires
a computer application to help them to manage the product information, customer details, orders placed
by the customers as well as deliveries and collections of the mattress. This application should be written
in Excel (VBA), so that they can improve their current rental management system. The CEO of SAM,
Mark is now contracting you to develop an Excel application (file can be saved in “.xlsm” extension)
that integrates what they currently have into a more user-friendly interfaces, in order to maintain their
day-to-day business.

The CEO has provided the Excel spreadsheets that they currently used to maintain their daily orders –
“FIT1013 A2_2019.xlsx”. Please note that this file does not contain all their rental data (for business
privacy), but they are enough for you to understand the business scenario. Some of the data in the file
are de identified due to privacy issue but they maintain the same structure. Some of these data are
imported from the deliver-collect tracking system (the development of the deliver-collect tracking
system is not in the scope of this assignment). Your application should work the same using the
provided actual data. In short, do not change any format or structure of the original data in the
3


worksheets, unless stated otherwise. Mark reminded you that the application is to be developed in
Microsoft Excel 2016 (English version) so that he and his employees can run the application smoothly
in their computer. He would also like you and your group to show him your work progress (milestone)
so that he can be sure that the application is completed on time. Failing to do so may result in reduced
payments or cancellation of the project.
Requirements
Functionality
1. A User Form named “Main Menu” is presented upon opening the workbook. The user form
will contain buttons to perform the key activities described in the points below (2-7). If the user
closes the form, it should be possible to bring the form back by clicking on the button named
“Show Menu” in the ‘Menu’ worksheet. This form should display the following options:

i. Maintain Customer Details (Add/Delete/Modify)
ii. Display Rental Details
iii. Add Delivery
iv. Add Collection
v. Generate Periodic Report
vi. Import and Export Customer Data

(5 marks – ALL group members need to complete and demonstrate this part in Week 9
tutorial)

Each button in the main menu is corresponding to a functionality (2-7) described below.

2. A User Form named “Maintain Customer Details” that allows the details of a customer to be
added, modified and deleted. This form should allow user to cancel/close the form, or to
confirm before the details are added/changed to the “Customers” worksheet. If it is a new
customer, then the details should be added below existing customers.

Note: The Customer ID is generated automatically by the system and increments by 1 each
time. E.g. if the ID of the last customer (last row) is 5, then the new customer ID is 6.

(7 marks – ALL group members need to complete and demonstrate this part in Week 10
tutorial)

3. A User Form named “Display Rental Details” that allows user to display all rental for a selected
customer. The details should be presented on a worksheet called “Rental Details” (this
worksheet can be pre-created). A button is to be included on the worksheet for user to have a
print preview of this worksheet. The worksheet must display the following details:

Customer, Requestor, PUID (Patient Unique Identifier), Mattress Type, Mattress Code,
Delivery Date & Time, Collection Date & Time, Rented Duration, Cost.

(8 marks – ALL group members need to complete and demonstrate this part in Week 10
tutorial)

4


4. A User Form named “Add Delivery” that allows the details of a new delivered order, i.e. a
rental to be entered.
i. The form should display an auto-generated Rental ID (in chronological order) when
it is opened; a list of customers, requestors and a list of mattress types, mattress
codes will be made available for selection. The PUID and delivery date/time must
be entered manually.
ii. Please note that more than one product can be ordered at a time (more than one
mattress - obviously for more than one patient in a single delivery, see existing
orders in the Rentals worksheet for examples).
iii. Design your application in such a way that the user can add more than one order for
the same customer.
iv. The form should include a ‘Complete’ button that transfers the details of the new
delivered order to the Rentals worksheet, updates mattress type availability in the
Product Type sheet, and mattress status in Product sheet, then returns to the previous
form i.e. “Main Menu” form.
v. Highlight the collection date & time, rented duration and cost columns for this rental
in light colour (e.g. red).
(15 marks)

5. A User Form named “Add Collection” that allows the details of a collection, i.e. the return of
a specific mattress to be recorded.

i. Each collection is corresponding to a specific mattress rented.
ii. A list of mattress codes for currently rented mattress will be made available for
selection. The collection date/time can be entered manually.
iii. The rental duration will be calculated and stored in Rented Duration column in the
Rentals worksheet.
iv. The cost of the rental (including the delivery charge) will be calculated and stored
in the Cost column.

The cost of rental is calculated based on the type of mattress, duration of rental and a delivery
cost. Rental less than 24 hours will be charged for a day and delivery fee. For rental more
than 24 hours, the due time is 12 noon. For example, a basic mattress is delivered at 8pm on
1st September, and collected on 3rd September 11am will be charged for 1 day only, cost of
mattress is $35 x 1 day + delivery cost $15, will come to a total cost of $50.

v. The form should include a ‘Complete’ button that transfers the details of the
corresponding row in the Rentals worksheet, updates mattress type availability in
the Product Type sheet, and mattress status in Product sheet, then returns to the
previous form i.e. “Main Menu” form.
vi. Remove the highlights (e.g. red colour) for the collection date & time, rented
duration and cost columns for this rental.
vii. The deliver-collect tracking system used by SAM’s driver is a mobile application
that can scan the mattress code during collection, and the collection data can be
saved as an Excel file. An example file is given to you – “FIT1013
A2_2019_track_collect.xlsx”. Mark would like to have a button that can import
these collection data from the Excel file and transfer to the Rental worksheet (same
as the above steps). Name the button “Import from Collection”.
5


(20 marks)

6. A User Form named “Generate Periodic Report”, which allows the user to input a start date
and end date, then creates a Summary worksheet (which can be pre-created) that contain a
summary of all completed rentals between those dates. The information should be obtained
from other relevant worksheets. The required information is described below:

Summary Sheet
This sheet presents a summary of the rental made to customers in a nominated period. For
instance, in the period 1/8/2019, through to 31/8/2019, there are three rentals completed. The
information to be shown on the Summary worksheet is as follows:

● The date on which the summary report was produced.
● The start date and end date which were specified in the nominated period.
● The customer i.e. name of the hospital/medical centre. It will be good to group the
following data by customer.
o The subtotal for number of mattress rented in the period.
o The subtotal of charges, i.e. total amount from rental in the period.
● Grand total for number of mattress rented and amount from rental.

A button named “Print Summary”, on the User Form to allow user to print the summary to a
PDF file.

For demonstration purpose, you can provide some dummy data for week 11 demonstration
without completing task 4 and 5.

(10 marks – ALL group members need to complete and demonstrate this part in Week 11
tutorial)

7. A User Form named “Import and Export Customer Data” that allows the user to import
customer details every morning from an external file and export the customer data to the same
database at the end of the day. The external file is in MS Access database format.
i. During importing, any discrepancies (between the records in the worksheet and the
database) should be recorded and reported in a separate worksheet called “Discrepancies”.
ii. Notes: you do not need to include a trigger to execute the program at certain times of the
day, Mark or his assistant, Matthew will do that by clicking this button.
iii. A sample Access database named “FIT1013 A2_2019_Data.accdb” has also been provided
by Mark.

(7 marks)

Documentations
Your application should be briefly documented on the first worksheet (which should be named
‘Menu’). The documentation should include:
● Team Number
● Authors’ details (Student ID & Name)
● Date of completion
● Instructions on how to use the application (including any features used or assumptions
made)
6



Other documents need to be submitted are meeting minutes and individual timesheets for payment
purposes (assumed it’s from Mark). Failure to do so may result in penalties like reduced payments (i.e.
marks). The templates for the meeting minutes and timesheet are available in Moodle Assignment 2
folder.
(8 marks)
Demonstrations
Demonstrate your in-progress application to Mark and his assistant Matthew (role played by your
tutors) during the tutorials (week 9 to week 11). Any member who fails to turn up to any of the
demonstrations will fail that component of the assignment. In addition to the marks allocated for the
tasks that require demonstration, some marks are allocated for demonstration, e.g. for clarity and
completeness.
(5 marks)
Quality of Solution
Some considerations:
1. Simplicity - is the code concise, easy to read and understand?
2. Generality/flexibility - does the solution work with valid data that the marker will enter when
testing your program?
3. Robustness - does the solution cope well with human errors, e.g. protected the sheet or range
that are supposed to be read only by data entry clerk?
4. Appropriateness of variable and constant declarations and usage, e.g. are conventions
followed, are variables declared in suitable places, etc.?
5. Appropriate use of graphic controls and consistency in the design of your user forms.
6. Make use of decision structures and repetition structures.
7. Include data validation to ensure the user only enters valid information, and report any
meaningful error messages.
8. Use appropriate indentation in your code so that it is easily readable.
9. Include appropriate documentation (or comments) in your code.
(15 marks)
Files Provided
The following files are provided in this assignment:
● FIT1013 A2_2019.xlsx which contains the initial data for each sheet in the Excel file.
● FIT1013 A2_2019_Data.accdb which contains customer data in MS Access format.
● FIT1013 A2_2019_track_collect.xlsx

1. Customers information worksheet
Delivery information for customers is kept in a worksheet named “Customers”. The
information includes:
o Customer ID - auto number
o Customer - name of the hospital or medical centre
o Requestor - contact person first name
o Email Address - email of the contact person
o Delivery address
o Phone - phone contact of the customer

2. Product Type information worksheet
7


This worksheet provides information about the mattress that are available. The information in
this list includes:
o Mattress type – there are three types of mattress currently
o Rent cost per day
o Delivery cost
o Quantity on hand

3. Product (individual mattress) information worksheet
This worksheet provides the status of each mattress. The information in this list includes:
o Mattress type – there are three types of mattress currently
o Mattress code – each mattress has a unique code
o Status – in or out

4. Rentals information worksheet
The detailed information for each rental includes:
o Rental ID
o Customer ID
o PUID – Patient Unique Identifier
o Mattress Code – it’s unique for each mattress
o Delivery Date
o Delivery Time
o Collection Date
o Collection Time
o Rented Duration – in days
o Cost

Notes and Assumptions:
1. Please note that some of the details may not be normalised, as our intention is to cover mainly
on the functionalities rather than the data recorded in the sheets.
2. If you are an experienced VBA programmer, and know things that are not covered in the
materials, please do not use them (or speak to your tutor first). You must be using VBA
version 6.0, NOT VB.NET, which is available in Office 2016.
3. Please check with your tutors if you have any assumptions.