FIT1013 Digital Futures: IT for Business Assignment 2 (total mark: 100 marks, final mark: 20%)
Submission Deadline: 13th October @5pm
The MoveFast Company
A VBA Programming Task in Excel (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, 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. Note: You need to accept the cover sheet statement in Moodle before you can submit this assignment.
• You must name your Excel file as the format: TutorName_TeamNo_FIT1013A2 where TutorName is your tutor’s name (Cheng, Eehui, Joe, Jonny, Peter or Saira) and TeamNo is the team number given by your tutor during the class.
• In order to ensure that the assignment file is 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.
• You must negotiate any extensions with your tutor/lecturer via the in-semester special consideration process: http://www.infotech.monash.edu.au/resources/student/equity/special-consideration.html
• Please always check Moodle Forums for updated information about the assignment, and/or before posting any questions on the forum.
Upon successful completion of this assignment, you should be able to:
• Write Macros (sub procedures) using VBA in Microsoft Excel
• Use appropriate data types, declare and use variables and/or constants
• Write event procedures for some Excel and VBA objects.
• Use repetition and selection structures in VBA code
• Use the Workbook, Worksheet and Range objects
• Use other objects as necessary
• Perform data validation on user input
• Construct arithmetic expressions in VBA code
• Follow appropriate rules relating to the scope of variables
• Design user forms using a variety of controls
• Apply other useful worksheet functions where appropriate, e.g. Vlookup() and Format()
functions in VBA code.
Page 1 of 6
Assessment of the assignment will give attention to how well you demonstrate that you can perform the tasks above. Other assessments will be based on whether you fulfil the functional requirements as described below. Note that in order to receive a HD or above, you must ensure all requirements are met and that your development is both robust and maintainable.
It is important that each member in the team must contribute and participate equally, otherwise, you will be marked based on the amount of effort and quality of the work that you produce.
Background Information
Established in 2013, MoveFast is a well-known company that specialises in business office removal that has 12 internal staff (including 5 supervisors), 5 drivers and 20 crews. Currently, due to its reputation for quality service, more and more companies are now using MoveFast. The administration staffs are now complaining that due to the increasing number of jobs, they have to spend lots of time doing the job scheduling. In addition, over-allocation of resources to some jobs have also occurred due to the tracking problem of resources.
The CEO, Tony Amber, of the company is aware of the problems and is now hiring you to develop an Excel program that integrates what they currently have into more user-friendly interfaces for them to maintain their resources and easily allocate resources to the jobs.
The CEO has provided the Excel spreadsheet that contains data that they currently use to maintain their daily jobs. The name of the file is called “FIT1013 S2 2017 Assignment 2 Data.xlsx”.
Please note that Tony would like your application to be developed in Microsoft Excel 2016 (English version) so that he and his employees can run the application smoothly.
Documentation
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
• Brief instructions on how to use the application
Page 2 of 6
CS Help, Email: tutorcs@163.com
Required Functionality:
You need to develop an Excel VBA solution which provides the following 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-6). If the user closes the form, it should be possible to bring the form back by running a macro named ‘Show Menu’ attached to a button on the ‘Menu’ worksheet. This form should display the following options:
• Maintain Resource Details (Add/Delete/Modify)
• Display Job Details
• Add New Job
• Generate Job Summary
• Import and Export Client Data (from Ms Access database)
• Close Menu
(2 marks – need to complete and demonstrate this part during week 9’s tutorial)
Each of the functionalities below should each have a corresponding button in the main menu (see above) for user to access.
2. A User Form named “Maintain Resource Details” that allows the details of a Resource to be added, modified and deleted. This form should allow user to cancel and close the form, or to confirm before the details are changed to the “Resources” worksheet. If it is a new resource, then the details should be added below any existing resources.
Note: The Resource ID is generated automatically by the system and increments by 1 each time.
(6 marks – need to complete and demonstrate this part in week 10’s tutorial)
3. A User Form named “Display Job Details” that allows user to input a job date and display the job details on a worksheet called “Job Detail” (you may pre-create this worksheet). A button is to be included on the worksheet for user to print preview this worksheet. The worksheet must display the following details:
• Job Code, Company Name, Job Date, Job Time(s), Pickup Address, Delivery Address,
Notes etc.
(4 marks– need to complete and demonstrate this part in week 11’s tutorial)
All the tasks (Task 1 to 7) need to be completed and submitted by Friday, 13th of October. However, all group members need to be present in week 12’s tutorial to demonstrate your system (assignment) to your tutor. 10 marks will be allocated for the demonstration. That means if you don’t attend week 12 tutorial, you will lose 10 marks.
Page 3 of 6
4. A User Form named “Add New Job” that allows the details of a new job to be entered:
i. Once the New Job form is opened, a new worksheet (named as “Temp Job”) should
automatically be created to temporarily hold details of a new job.
ii. The form should display an auto-generated Job Code (in chronological order) when it is opened; a list of clients and a list of resources from which to select. Other necessary information to be entered by user can be found in the Job and Job Resources sheets.
iii. The form should provide a ‘Calculate’ button, which when clicked, would calculate the charge of the job to the client. For simplicity, assume that the job is charged daily:
fixed cost of $150 plus resource costs.
iv. The form should contain an ‘Accept’ button, which when clicked, will add the details
of the job to the (“Temp Job”) list created in step 1.
v. The form should allow the user to cancel and return to the previous form (e.g. Main
Menu form.). The temporary worksheet “Temp Job” will also be deleted.
vi. The form should include a ‘Transfer’ button that transfers the details of the new job(s) from the temporary job sheet to the Job and Job Resources sheets, updates/add resource availability in the Resource Availability sheet, and then deletes the
temporary sheet and returns to the previous form.
(17 marks)
Challenge: Design your system in such a way that the user can create more than one job for a given client. Each new job should have a unique job code and should be added to the existing list in the “Temp Job” sheet each time the user clicks on ‘Accept’ button.
5. A User Form named ‘Generate Job Summary’, which allows the user to input a start date and end date, and creates a Summary worksheet that contains details of all jobs between those dates. The information should be obtained from the relevant worksheets. The required information is described below.
Summary Sheet
This sheet presents a summary of the jobs done for clients in a nominated period. For instance, in the period 20/9/2017, through to 3/10/2017, there are three items shown on the Job Information sheet. The information to be shown on the summary worksheet is as follows:
The total number of jobs for each client.
The total income from all jobs in the period.
The start date and end date which were used in calculating the above.
The date on which the summary report was produced. A button for user to print job summary to a pdf file
(13 marks)
Page 4 of 6
6. A User Form named “Import and Export Client Data” that allows the user to import client details every morning from an external database (in Ms Access format), and export the client data to the same database at the end of the day. During importing, any discrepancies (between the records in the worksheet and the database) should be recorded and reported in a separated worksheet called “Discrepancies”.
This is a scenario only, you do not need to include a trigger to execute the program at certain time of the day.
A sample Access database named “FIT1013 S2 2017 Assignment 2.accdb” has also been provided by Tony.
(10 marks)
7. In order to maintain the system on a long term basis, Tony would also like you to prepare some documentations on the system so that others could refer to it later when maintaining the system. He has heard about structure charts and asks you to include this in the documentation. Prepare a structure chart and include a brief explanation on your structure chart.
Other requirements: (18 marks)
• Demonstrate your in-progress application to your tutor during the tutorials (week 9 to week
11). The aim is for 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.
• You must make use of decision structures and repetition structures.
• You must include data validation to ensure the user only enters valid information, and
report any meaningful error messages.
• You should use appropriate indentation in your code so that it is easily readable.
• You should include appropriate documentation (or comments) in your code.
You should also consider the following issues: (8 marks)
• Quality of solution, in terms of:
o Simplicity – is the code concise, easy to read and understand?
o Generality/flexibility – does the solution work with valid data that the marker will
enter when testing your program?
o Robustness – does the solution cope well with human errors (e.g. entering invalid or inconsistent data)?
• Appropriateness of variable and constant declarations and usage, e.g. are conventions followed, are variables declared in suitable places, etc.?
• Appropriate use of graphic controls and consistency in the design of your user forms. Page 5 of 6
Computer Science Tutoring
Assumptions and notes:
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.
Pickup Address and Delivery Address can be different from the company address.
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, (and NOT VB.NET which is available in Office 2016).
Please check with your tutor before making any assumptions yourself.
Please note that we have provided you the following files for this assignment:
o FIT1013 S2 2017 Assignment 2 Data.xlsx which contains the initial data for each sheet in the Excel file.
o FIT1013 S2 2017 Assignment 2.accdb which contains client data in Ms Access format.
Note: you should not change any of the original data.
You should not change your filename before uploading your final submission!!!
Page 6 of 6
浙大学霸代写 加微信 cstutorcs