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 wo
rksheet 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 custom
ers 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.
