Olympus Tours (OT) Programming Project
R: 6/15/12 3 pm dd
Assume you have just been hired by Olympus Tours (OT), a bus-rental company located
in Orem, Utah. OT provides busses for groupssuch as schools, family reunions, travel
clubs, etc. The smallest group OT will bus is 20 people and the largest group is 120
people. If the user enters the number of users as less than 20 or more than 120 display
“Enter a number of people between 20 and 120” on the screen.
Two types of busses are available for use: small busses (25 maximum riders per bus) and
large busses (60 maximum riders per busYou may assume that OT has access to all of the
small and large busses it needs.
# of Busses
Option Riders Small Big
1
Min 20 1 0
Max 25
2
Min 26
2 0
Max 50
3
Min 51 0 1
Max 60
4
Min 61 1 1
Max 85
5
Min 86 0 2
Max 120
Table 1. Bus Combinations
OT’s pricing structure is as follows. The base price (BP) is calculated by multiplying the
number of people (P) included in the trip by a per-person base rate (PPBR).
BP = P * PPBR
The base price covers trips up to and including five hours in duration. Customers are
charged extra for tours that last longer than 5 hours. For example, a bus rented for 6.5
hours would have 1.5 overtime hours(OH). The maximum number of overtime hours per
day is four, which means any trip lasting nine hours or more has the same number of
overtime hours regardless of the duration of time that exceeds 9 hours. 2
The overtime charge (OC) is calculated by multiplying the base price (BP) by the
overtime hours (OH) multiplied by the extra hourly percentage (EHP) as follows:
OC= BP * OH * EHP
Table 2 shows some example price calculations that include partial hours.
Number
of
People
PerPerson
Base
Rate
Base
Price Hours
Overtime
Hours
Extra
Hourly
Percent
Overtime
Charge
Total
Price
P PPBR BP H OH EHP OC TP
35 $10 $350 5.4 0.4 25% $35 $385
35 $10 $350 6.6 1.6 25% $140 $490
35 $10 $350 11.3 4.0 25% $350 $700
50 $9 $450 5.4 0.4 30% $54 $504
50 $9 $450 6.6 1.6 30% $216 $666
50 $9 $450 11.3 4.0 30% $540 $990
Table 2. Example Pricing Calculations
Your task is to create a flowchart and program that will pick the right number of busses
and that will calculate the correct price for the number of people and hours specified.
Because the pricing parameters are somewhat stable, they are stored in the setup section.
When the program runs, it should obtain the values from the Setup section. Your code
should still work properly if the user changes the values in setup.
Based on the values in the setup section and the number of people and hours specified,
your program should calculate the correct outcomes. Your program should work correctly
for single-trip estimates and for a batch of estimates.
Single-Trip Estimates. The program should work when the user wants to estimate one
trip at a time. For a single trip, the user enters inputs directly into the [User Form] tab and
presses the Run Single button. In this scenario the user will enter the number of people
(P) and the tour duration in hours (H) into the appropriate cells in the User Form. Your
program should then use the pricing parameters to calculate the amount the customer will
be charged per day. Figure 1 shows the [User Form] tab.
If there is a multi-day tour, the program will be run for each day of the multi-day tour.
Thus your program does not need to support calculations for more than one day at a time. 3
Figure 1. [User Form] tab
Batch Estimates. OT often wants to estimate batches of trips. In this case, the program
should take all of the tours shown as inputs on the [Batch Input] tab, estimate them one at
a time, and write the results to the [Batch Output] tab. To estimate all trips within a
batch, the user presses the Run Batch button on the [User Form] tab.
The [Batch Input] tab. Figure 2 explains the data on the [Batch Input] tab.
Figure 2. Batch input tab
Number of people
Hours
Name
Trip Date4
The [Batch Output] tab. Figure 3 shows how your program should organize the outputs
on the [Batch Output] tab.
Assignment
1. (25 pts)Create a flowchart for the process of completing an estimate for a singletour. You are not required to create a flowchart for the Run Batch process.
2. (100 pts) download the Olympus Tours Programming Project Worksheet from the
class schedule website. Do not make your own workbook. Add VBA code to
complete the project.
Instructions
1. Your VBA code should work with the cells that are already positioned on the
[User Form] tab of the workbook. Do not change the cell locations or layout of
either the [Instructions] tab or the [User Form] tabs within the workbook. In
addition, do NOT add or delete any columns or rows on these two tabs.
2. Use VBA to do all of the calculations. Do not use spreadsheet functions.
3. Add your VBA code to the two existing sub routinesthat have been started in
Module1 already within the workbook. Add your code to the sub routine named
EstSingle() that will run when the user presses the Run SingleButton. Add your
code to the sub routine named EstBatch() that will run when the user presses the
Run Batch Button. Connect the appropriate subroutine to the respective buttons,
so that the correct sub routine will run when the button is pushed.
4. In your code, use the Option Explicit setting for both subroutines. (This should
already be set.) Verify this by ensuring that the first line of code says “Option
Explicit.”
5. Use Dim statementsto declare all variablesthat you use in your code. Declare the
variables as the data typesshown on Column D of the [User Form] tab.
6. Your EstBatch()script should not delete or alter the data on the [Batch Input] tab.
7. Test your program with a variety of inputs to ensure it works properly before you
turn in your assignment.5
D