These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Wednesday, April 11, 2012

Schedules Creator

Executive Summary

Business Problem

I work at the Missionary Training Center (MTC) as a supervisor. In our area we specialize in language tutoring of senior missionaries and mission presidents. Our missionaries come to the MTC for at most two weeks. During their stay, we arrange language lessons were we would have at most two senior missionaries per class. Their schedules are created on excel by our department secretaries. They create the schedules one by one (this includes formatting), entering the data for each individual missionary. They then print four copies of each schedule. These four copies go to the following people:  the missionaries, the tutors, the service desk, and the general scheduling office at the MTC. The following is the way in which the schedules are created:
·         The secretaries get a list of the incoming missionaries which usually ranges between 20 to 35
·         They then open an excel sheet that has the format that they want an copy paste it onto another workbook in excel
·         Then they enter the following data for each missionary:
o   Name
o   Classroom number
o   Dates they will be at the MTC for their first and second weeks respectively
§  Every missionary couple receives at least one week of tutoring. This first week only includes classes from Wednesday to Friday of their first week of their stay. If they will also be staying for a second week, then the missionaries will specify which days they want to receive tutoring. For second week, tutoring missionaries can receive tutoring on all days, but on Tuesdays since there is a devotional on those days.
o   Accounting for the dates in which the missionaries would like to have lessons (before they arrive, missionaries will specify how many days they would like to have lessons while they are at the MTC)
o   the secretary will print four copies of each schedule, and re-use the newly created template for the remainder schedules
o   Finally, since they were just re-using their newly created template they would just dispose of it after they are done with the last schedule


The secretaries have to create each schedule individually each time. Even though they have a template (which they re-create each week) they don’t save it and since they write over it; if they make a mistake on a previews schedule, they will have to re-create the entire schedule.
The secretaries cannot save each schedule; thus, if a missionary loses it, they just write the schedule information on a post-it note. In addition, this task takes them 4 ½ hours to complete.
In summary, we are not only losing money by wasting so many hours a week, but we are also being very inefficient with our procedure. This procedure forces the secretaries to not have saved schedules; thus, if a missionary ever loses their schedule they would have to re-create it.

Overview of the system

The solution that satisfied the business need, was to create a user interface using excel that will allow user input. Through programing logic, the program would automatically create the desire schedules. The following is a summary of the way the system works:
                Missionary couple attends MTC for only one week (First week tutoring)
The user will enter or select the following information: Missionary’s Name, date of first Monday of the incoming week of the missionary, and classroom number for 1st week. The user will then click the create schedule button to finalize the procedure.
Missionary couple attends MTC for two weeks
User will repeat the all the steps of the first week tutoring, but will not click the create schedule button until they have included the following steps:
The user will check mark the days of the second week when the missionary will want classes; at this point, the program will calculate the dates for each week respectively. Once the user finishes adding the information, they will click the create schedule button. The program will open a new excel workbook, and it will create the desire schedule with the user’s input on the first sheet of the newly opened workbook.
At this point if the user is finished they can click on the “Exit” button to end the program. If they need to continue creating schedules; the user can click on the “Clear” button to clear all the previously entered values, and allow the user to input new values to create another schedule. 

1 comment:

  1. eToro is the ultimate forex broker for newbie and pro traders.


Blog Archive