VBA Team Decision Making Tool

Summary

As part of my 1A Management Sciences class, we were given a term project to work with an alumni student, who would be our client to come up with a decision support tool which they could have used during their final year design project. The project was done using Visual Basic for Applications, and required us to go through an extensive documentation process.

Problem Addressed

The problem our client wanted us to address was the lack of organization and time management within the team, leading to rushed work, inconsistent meeting times, and inability to keep track of who had to complete what task by which date. She was looking for an automated generator for meeting dates, assigning tasks, and an overview of all deadlines within a certain timeframe.

Solution Presented

Across the span of four months, my team and I worked to create a fully functional Agile based decision making tool which automatically assigned tasks to each team member based on their weekly workload/skill level, outlined each team member’s individual suggested weekly hours for the project, generated times for team meetings, provided an alert with upcoming tasks, and had an automatically updating Calendar and Gantt Chart. All of the functions were fully created using VBA for Excel, and are fully functional at the time this is written.

My Role

Since this was a team project, all of us wore many hats throughout the project. During the planning stages of the project I was the Lead UI/UX Designer. I conducted user research to determine the importance of each system requirement, and created a low-fidelity wireframe for how we wanted the product to look. In the production stage, I was a part of the development team and wrote code for various functionalities in the project, acting as one of the Developers. Throughout the project, I kept track of deliverables, ensured that we were on track, and helped write the various documentation we provided, such as our Functional Requirements, Test Specifications, Design Specifications, User Manual, and Project Plan Updates.

Timeline

Sept 2020 — Dec 2020

Research

Client Meetings

Prior to finalizing the scope of the project, we had multiple meetings with our client where we discussed the main problem she was facing, where the root of the problem came from, and any side effects that needed to be mitigated as a result of the problem. We then worked together to hone in on how our solution would solve her problem and created a two-part system where one side handled project management, and the other was an alert system.

The project management system would ensure that the team members are kept up to date regarding all the tasks that need to be handed in, and schedule meetings.

The alert system would provide weekly schedules and reminders to each team member to guide them through the week.

After presenting this idea to the client, we worked to create a plan for the minimum viable product we wanted to create, as well as determine which of the features we wanted to present could be add-ons. In the duration of the project we had 4 meetings to conduct preliminary research, and 3 meetings for getting feedback on various product models during different stages of production.

User Research

Since the primary user was going to be our client, I worked with her to prioritize/map out all the parts of the System Requirements that we set out, and understand how in-depth she wanted each function. Doing this provided the team with a central focal point for the project which we were able to refer back to when creating each function.

As the function of the project was a team management application, we conducted internal testing by having members who were developing one part of the application test out different parts. We also shared our project with another group, and used their feedback to improve user accessibility for certain features.

Ideation

Scope

The solution was focused on two primary sections, which are explained in the following processes:

Project Management

1. Sorting tasks to each team member based on their weekly workload
2. A calendar that updates with every input
3. A Gantt chart
4. Outlining each team member’s individual suggested weekly hours for the team project
5. Automatically suggesting times when the team has meetings

Alert System

1. Upcoming deadlines displayed on a dashboard for each team member
2. Automatic message box that pops up when the tool starts to let the user know about upcoming tasks coming up

Sitemap

In order to plan out how we wanted the system to function, and the commands we needed to develop, we created a sitemap of the entire product, going through each of the possible paths a user could take, and how they would go to/from them. Doing this was very beneficial as it allowed us to understand how many moving/static parts we had, and which ones relied on the others.

Low Fidelity Wireframing

Prior to starting development, we set out the design specifications for the project by creating a data flow diagram and low-fidelity mockups of how the system would look. It has a brief overview of the various functions the system will have and how we want the data to flow between them.

Dashboard

Calendar

Team Member Page

Datasheet

Deliverable Criteria

In order to ensure that the program works as per its functionalities, we set out a test plan identifier to ensure the success of each feature. Throughout the process we focused on unit testing, performance testing, repression testing and usability testing. In order to maintain usability we also tested for consistency, conscience and functionality. The following criteria was set out for individual feature functionality testing:

1. For the insert button we will determine if it passes our criteria if it can successfully implement the task/assignment into the correct places such as the individual member sheet and into the dashboard. We will consider it a fail if it either does not ask for the data or if the data is not implemented into the charts.
2. The edit button will be considered a pass if it allows us to choose which previously implemented task and go through it to change specifics about that task. We will consider the edit button a fail if it cannot make the changes to the task and implement the changes where necessary.
3. The update button needs to sort through the data and include the correct items to pass our testing.
4. For the calendar, it needs to extract data correctly for each individual and place it in their respective sheet. Additionally, it needs to take the key dates and information onto the dashboard for all members to view. The testing will be considered a fail if all information is not extracted correctly.
5. The Gantt Chart needs to be able to display the entirety of the assignment presented, this means showing the milestones and an overview of when to do each task. We will consider the Gantt Chart a fail if it cannot show an overview of the assignment and/or if the data implemented into it cannot be expressed into other sheets
6. All buttons acting as hyperlinks need to lead to the correct function in the program for them to be considered a pass.

Final Product

The system comprises five main workspaces, with 4 of them being used for the project management/alert system and one being used as a collection of information from where the data is extracted. Workspace I is used for setting up various parts of the project, Workspace II is the main landing page for the users, Workspace III and IV host the calendar and Gantt Chart respectively, and Workspace V has the information for each individual team member and compiles the information from the above sheets.

Workspace I — Data Sheet

The following are the different options which come up when the ‘Insert Task’ button is brought up. Each option provides various constraints and fits the boundaries which have been set out.

1. Insert Asynchronous Project
2. Insert Synchronous Project
3. Insert Asynchronous non-Project
4. Insert Synchronous non-Project
5. Edit Item
6. Delete Task

Workspace II — Dashboard

1. Insert Task — This brings you to a user form where you are able to input the type of task (related to project or unrelated), the synchronicity, and the details of the task which then shows up in both the raw data sheet and Dashboard.
2. View Upcoming Deadlines for Team — The tasks that the team has to complete within a given time frame will be shown on the dashboard.
3. View Upcoming Deadlines for each Member — The tasks that each individual group member has to complete within a given time frame will be shown on the dashboard.

Hyperlinks — These are “shortcuts” to every other sheet on the document. By clicking on these it would bring you to another sheet. The following are the hyperlinks available on the first Workspace.

• Calendar: This redirects to the overall calendar for the team.
• Individual Team Member Sheet: This redirects to the page for each individual team member

Workspace III — Calendar

Key dates and information are extracted from the data sheet and displayed on a calendar. Users will be able to view deadlines for each month.

1. Hyperlinks — The hyperlinks will bring you to the specific month selected or allow you to sort the dates by colour/date.

Workspace IV — Gantt Chart

1. Start Project — This brings up a UserForm which allows the Project Name, Start Date, and Duration to be inputted. Using this information, there is a timeline automatically brought up starting from the Sunday before the inputted date. This is used to set up the project and will only need to be used once at the beginning of the project.
2. Update Tasks — This brings in the most updated version of all the tasks, their duration, the start date, and end date from the data sheet. This is used to match with the dates in the timeline and block out time slots.

Workspace V — Individual Team Member Sheet

1. Insert Task — This brings up the aforementioned form which allows for individual members’ tasks to be added.
2. Edit Task — Allows the user to edit the information regarding the member tasks.
3. Hyperlinks — The hyperlinks in this sheet will help you move to specific months on the calendar. Each member will be able to view which tasks are coming up for them individually, either their daily schedule, upcoming deadlines, or weekly schedule.

Evaluation

Although the project tackled all of the intended issues and requirements, there are several improvements that we could make to increase user-friendliness and efficiency.

1. It would be beneficial if the textboxes in user forms were highlighted in a specific colour if they still had to be filled out, so the user would not have to wait for an error message to pop up on the document.
2. Another potential development is combining various aspects of the project into fewer sheets to minimize the amount of time the user spends navigating.
3. Having a more coherent view of the entire project as a whole to view the current health of the project as well as the status of each milestone in progress.

Since the product we created was the minimal viable product, we felt that the features we included satisfied the basic requirements of the client, however a after conducting further user testing there are improvements which we can make.