Excel代写 | FIT1013 Digital Futures: IT for Business Assignment 2

这个作业是用Excel和VBA完成商业模型设计

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.

SDR (Simon’s Drones & Robotics) is now expanding their business to include leasing of tech toys.

Tech toys are commonly known for recreation, but they are also useful for educational purposes such
as, research, expeditions, and photography. They lease different types of tech toys to business
customers, e.g. research institutes, schools, party/event organisers. These customers usually rent in
bulk (five or more) and will come back again for rentals. SDR is hoping to maintain a good relationship
with these customers. There are several types of tech toys available, such as Camera Drone, Electric
Gokart, and Coding Robot. For each customer, e.g. a research institute, there is a contact person for
tech toy ordering a.k.a contact person (CP). A CP can make an order by phone or by email. When a
CP place an order, SDR delivers the tech toy to the destinated location (such as schools, event venues,
etc). At the end of the rental period (i.e. when a tech toy is no longer needed), the CP will inform SDR
to collect the tech toy, usually from the same location, but SDR also can collect from another location
if requested by customers. These tech toys will be inspected (check for damages), cleaned, repacked
and stored in SDR’s storeroom, ready for next use.

So far, all orders are recorded using simple Excel sheets. Due to the increases of order, SDR 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 tech toy. This application should be written
in Excel (VBA), so that they can improve their current rental management system. The CEO of SDR,
Simon 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_2020.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
worksheets, unless stated otherwise. Simon reminded you that the application is to be developed in
Microsoft Excel (English version) so that he and his employees can run the application smoothly in
their computer. He would also like you 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.

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-5). 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. Customer (Add/Delete/Modify)
ii. Rental
iii. Return
iv. Summary

ALL students need to complete and demonstrate Function #1 in Week 9 tute/lab.
You do not need to demonstrate the complete functions 2-5 in Week 9, you only need to show the
workable menu.

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

2. A User Form named “Customer” 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.
ALL students need to complete and demonstrate Function #2 in Week 10 tute/lab.

3. A User Form named “Rental” 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, CPs and a list of tech toy types, tech toy codes will
be made available for selection. The delivery date/time will be entered manually.

ii. More than one type of tech toy can be ordered at a time, and it can be more than five
tech toys, see existing orders in the Rentals worksheet for examples.

iii. The form should include a ‘Add’ button that transfers the details of the new
delivered order to the Rentals worksheet, updates tech toy type availability in the
Product Type sheet, and tech toy status in Product sheet, then returns to the previous
form i.e. “Main Menu” form.

iv. Highlight the collection date & time, rented duration and cost columns for this rental
in light colour (e.g. red).

4. A User Form named “Return” that allows the details of a collection, i.e. the return of a specific
tech toy to be recorded.

i. Each collection is corresponding to a specific tech toy rented.

ii. A list of tech toy codes for currently rented tech toy will be made available for
selection. The collection date/time can be entered manually.

iii. The rental duration will be calculated and stored in 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 tech toy, 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, 10 camera drones are delivered at 8pm
on 1st September, and collected on 3rd September 11am will be charged for 1 day only, cost
of renting a tech toy is $22 x 1 day + delivery cost $5. 10 drones will come to a total cost of
$270. Customer can return partial rental, e.g. take 10 items but return 5 on a day, then return
another 5 on other day. Apart from the rental changes for extra days, the second collection
will attract another delivery fee $5 per item.

v. The form should include a ‘Complete’ button that transfers the details of the
corresponding row in the Rentals worksheet, updates tech toy type availability in
the Product Type sheet, and tech toy 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 SDR’s driver is a mobile application
that can scan the tech toy code during collection, and the collection data can be
saved as an Excel file. An example file is given to you – “FIT1013
A2_2020_track.xlsx”. Simon 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 Track”.
ALL students need to complete and demonstrate Function #4 in Week 12 tute/lab.

5. A User Form named “Summary”, 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
delivered 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/2020, through to 31/8/2020, there are three deliveries. 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.
● Customer name. It will be good to group the following data by customer.
o The subtotal for number of tech toy delivered in the period.
o The subtotal of charges, i.e. total amount from collection in the period.
o Number of items not collected yet.
● Grand total for number of tech toy rented and amount from rental (collected).

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

Your application should be briefly documented on the first worksheet (which should be named
‘Menu’). The documentation should include:

● Authors’ details (Student ID & Name)
● Date of completion
● Instructions on how to use the application (including any features used or assumptions
made)

Demonstrate your in-progress application to Simon and his assistant Matthew (role played by your
tutors) during the tutorials and labs (week 9 to week 12). Any student 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.

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.