题意：对床垫公司订单信息管理
解析：使用VBA对订单信息进行分析。主要需要完成以下的功能：
Maintain Customer Details：允许对客户信息进行增删改操作
Display Rental Details：显示所有的出租详情。需要包括的信息有：客户、请求、PUID、床垫类型、床垫代码、交货日期和时间、采集日期和时间、租用时间、成本。
Add Delivery：添加一个新的交付信息。
Add Collection：添加一个特定的集合。
Generate Periodic Report：允许生成一个指定日期之间的信息汇总表。
Import and Export Customer Data：允许导入客户的信息至当前数据库。 涉及知识点：Excel，VBA更多可加微信讨论微信号：IT_51zuoyejunpdf
FIT1013 Digital Futures: IT for BusinessAssignment 2 (20%)Submission Deadline: 18th October 2019 11pmDeveloping 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 marksgained per late day will be applied. (For example, that means if you got 70%, but are 2 dayslate, your mark will be reduced by 10% of 70 (the marks gained) x 2 (two days late), 14marks).● Assignments are to be submitted online to Moodle.● Please name your Excel file according to this format: TeamID_FIT1013A2 where TeamID isthe 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 assignmentusing CATME – more details to be posted on Moodle.● To ensure that the assignment files are uploaded successfully, please download the assignmentfile (after uploading to Moodle) and check if it works as expected. Failing to do so may resultin 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 shouldensure 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 specialconsideration process: http://www.infotech.monash.edu.au/resources/student/equity/specialconsideration.html. Please email your scanned documents to [email protected].● See also link for help in completing this assignment: http://www.monash.edu/it/currentstudents/resources-and-support/style-guideLearning ObjectivesUpon successful completion of this assignment, you should be able to:i. Write Macros (sub procedures) using VBA in Microsoft Excelii. Use appropriate data types, declare and use variables and/or constantsiii. Write event procedures for some Excel and VBA objects.iv. Use repetition and selection structures in VBA codev. Use the Workbook, Worksheet and Range objectsvi. Use other objects as necessary2vii. Perform data validation on user inputviii. Construct arithmetic expressions in VBA codeix. Follow appropriate rules relating to the scope of variablesx. Design user forms using a variety of controlsxi. Apply other useful worksheet functions where appropriate, e.g. Vlookup() and Format()functions in VBA code.Assessment CriteriaThe 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, thedevelopment is robust and maintainable.It is important that each team member to contribute and participate equally, otherwise, you will bemarked based on the amount of effort and quality of the work that you produce. You should not dividethe 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). Thiswill allow you to explain your design and what you’ve achieved in this assignment so far. Any memberwho fails to turn up to any of the demonstrations will fail that component of the assignment.ScenarioSanitised Air Mattress (SAM) is a specialized mattress company that lease different size of mattressfor patients in several hospitals and medical centres. Due to the different medical conditions of eachpatient, 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 sanitisedair mattresses for the duration they required, to achieve better comfortability and even for medicalneeds. There are three categories of mattresses available; namely Basic, Economy, and Deluxe. Foreach customer – i.e. the hospital/medical centre, there is a contact person for mattress ordering – a.k.arequestor. A requestor can make an order by phone or by email. When requestor place an order, SAMdelivers the mattress to the destinated ward in the hospital – unpack and inflate. At the end of the rentalperiod ( i.e. when a mattress is no longer needed), the requestor will inform SAM to collect the mattressfrom 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 requiresa computer application to help them to manage the product information, customer details, orders placedby the customers as well as deliveries and collections of the mattress. This application should be writtenin 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 theirday-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 businessprivacy), but they are enough for you to understand the business scenario. Some of the data in the fileare de identified due to privacy issue but they maintain the same structure. Some of these data areimported from the deliver-collect tracking system (the development of the deliver-collect trackingsystem is not in the scope of this assignment). Your application should work the same using theprovided actual data. In short, do not change any format or structure of the original data in the3worksheets, unless stated otherwise. Mark reminded you that the application is to be developed inMicrosoft Excel 2016 (English version) so that he and his employees can run the application smoothlyin 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 reducedpayments or cancellation of the project.RequirementsFunctionality1. A User Form named “Main Menu” is presented upon opening the workbook. The user formwill contain buttons to perform the key activities described in the points below (2-7). If the usercloses 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 Detailsiii. Add Deliveryiv. Add Collectionv. Generate Periodic Reportvi. Import and Export Customer Data(5 marks – ALL group members need to complete and demonstrate this part in Week 9tutorial)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 beadded, modified and deleted. This form should allow user to cancel/close the form, or toconfirm before the details are added/changed to the “Customers” worksheet. If it is a newcustomer, then the details should be added below existing customers.Note: The Customer ID is generated automatically by the system and increments by 1 eachtime. 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 10tutorial)3. A User Form named “Display Rental Details” that allows user to display all rental for a selectedcustomer. The details should be presented on a worksheet called “Rental Details” (thisworksheet can be pre-created). A button is to be included on the worksheet for user to have aprint preview of this worksheet. The worksheet must display the following details:Customer, Requestor, PUID (Patient Unique Identifier), Mattress Type, Mattress Code,D
elivery Date & Time, Collection Date & Time, Rented Duration, Cost.(8 marks – ALL group members need to complete and demonstrate this part in Week 10tutorial)44. A User Form named “Add Delivery” that allows the details of a new delivered order, i.e. arental to be entered.i. The form should display an auto-generated Rental ID (in chronological order) whenit is opened; a list of customers, requestors and a list of mattress types, mattresscodes will be made available for selection. The PUID and delivery date/time mustbe entered manually.ii. Please note that more than one product can be ordered at a time (more than onemattress – obviously for more than one patient in a single delivery, see existingorders in the Rentals worksheet for examples).iii. Design your application in such a way that the user can add more than one order forthe same customer.iv. The form should include a ‘Complete’ button that transfers the details of the newdelivered order to the Rentals worksheet, updates mattress type availability in theProduct Type sheet, and mattress status in Product sheet, then returns to the previousform i.e. “Main Menu” form.v. Highlight the collection date & time, rented duration and cost columns for this rentalin 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 ofa 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 forselection. The collection date/time can be entered manually.iii. The rental duration will be calculated and stored in Rented Duration column in theRentals worksheet.iv. The cost of the rental (including the delivery charge) will be calculated and storedin the Cost column.The cost of rental is calculated based on the type of mattress, duration of rental and a deliverycost. Rental less than 24 hours will be charged for a day and delivery fee. For rental morethan 24 hours, the due time is 12 noon. For example, a basic mattress is delivered at 8pm on1st September, and collected on 3rd September 11am will be charged for 1 day only, cost ofmattress 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 thecorresponding row in the Rentals worksheet, updates mattress type availability inthe Product Type sheet, and mattress status in Product sheet, then returns to theprevious form i.e. “Main Menu” form.vi. Remove the highlights (e.g. red colour) for the collection date & time, rentedduration and cost columns for this rental.vii. The deliver-collect tracking system used by SAM’s driver is a mobile applicationthat can scan the mattress code during collection, and the collection data can besaved as an Excel file. An example file is given to you – “FIT1013A2_2019_track_collect.xlsx”. Mark would like to have a button that can importthese collection data from the Excel file and transfer to the Rental worksheet (sameas 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 dateand end date, then creates a Summary worksheet (which can be pre-created) that contain asummary of all completed rentals between those dates. The information should be obtainedfrom other relevant worksheets. The required information is described below:Summary SheetThis sheet presents a summary of the rental made to customers in a nominated period. Forinstance, in the period 1/8/2019, through to 31/8/2019, there are three rentals completed. Theinformation 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 thefollowing 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 aPDF file.For demonstration purpose, you can provide some dummy data for week 11 demonstrationwithout completing task 4 and 5.(10 marks – ALL group members need to complete and demonstrate this part in Week 11tutorial)7. A User Form named “Import and Export Customer Data” that allows the user to importcustomer details every morning from an external file and export the customer data to the samedatabase 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 thedatabase) 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 theday, 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 providedby Mark.(7 marks)DocumentationsYour 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 assumptionsmade)6Other documents need to be submitted are meeting minutes and individual timesheets for paymentpurposes (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 2folder.(8 marks)DemonstrationsDemonstrate your in-progress application to Mark and his assistant Matthew (role played by yourtutors) during the tutorials (week 9 to week 11). Any member who fails to turn up to any of thedemonstrations will fail that component of the assignment. In addition to the marks allocated for thetasks that require demonstration, some marks are allocated for demonstration, e.g. for clarity andcompleteness.(5 marks)Quality of SolutionSome 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 whentesting your program?3. Robustness – does the solution cope well with human errors, e.g. protected the sheet or rangethat are supposed to be read only by data entry clerk?4. Appropriateness of variable and constant declarations and usage, e.g. are conventionsfollowed, 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 anymeaningful 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 ProvidedThe 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.xlsx1. Customers information worksheetDelivery information for customers is kept in a worksheet named “Customers”. Theinformation includes:o Customer ID – auto numbero Customer – name of the hospital or medical centreo Requestor – contact person first nameo Email Address – email of the contact persono Delivery addresso Phone – phone contact of the customer2. Product Type information worksheet7This worksheet provides information about the mattress that are available. The information inthis list includes:o Mattress type – there are three types of mattress currentlyo Rent cost per dayo Delivery costo Quantity on hand3. Product (individual mattress) information worksh
eetThis worksheet provides the status of each mattress. The information in this list includes:o Mattress type – there are three types of mattress currentlyo Mattress code – each mattress has a unique codeo Status – in or out4. Rentals information worksheetThe detailed information for each rental includes:o Rental IDo Customer IDo PUID – Patient Unique Identifiero Mattress Code – it’s unique for each mattresso Delivery Dateo Delivery Timeo Collection Dateo Collection Timeo Rented Duration – in dayso CostNotes and Assumptions:1. Please note that some of the details may not be normalised, as our intention is to cover mainlyon 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 thematerials, please do not use them (or speak to your tutor first). You must be using VBAversion 6.0, NOT VB.NET, which is available in Office 2016.3. Please check with your tutors if you have any assumptions.
