OKC Tours Programming Project

Assume you have just been hired by the manager of OKC Tours (OKCT), a small

company located in Oklahoma City. OKCT specializes in providing tour busses and

guides to tour groups desiring to travel through some of Oklahomas historic sites.

OKCT holds tours for groups ranging from 15 to 110 people (including 15 people and

110 people tours), so for the purpose of this assignment, you can assume that the program

user will know to only enter values between 15 and 110 people (inclusive). (This means

you dont have to worry about error-checking.)

Two types of busses are available for use: small busses (35 maximum tourists per bus) and

large busses (55 tourists per bus). OKCT has two small busses and two large busses; tour

groups can use only combinations of two busses at a time.

There are five tour bus options with each option using a different bus pairing as

shown in Table 1. # of Busses

Option

1

2

3

4

5 Min and Max Tourists

min tourists

max tourists

min tourists

max tourists

min tourists

max tourists

min tourists

max tourists

min tourists

max tourists 15

35

36

55

56

70

71

90

91

110 Small Big Per Bus Price Base * Qty 1 0 350 350 0 1 450 450 2 0 350 700 1 1 350

450 800 0 2 450 900 Table 1. Bus Combinations and Associated Pricing To help you better understand the information summarized in the table, OKCT provided

you with a description of their pricing structure. For each bus, there is a base usage price

that includes a driver and tour guide for any tour up to and including five hours in

duration. (All hour calculations are rounded to the nearest whole hour.) Each small bus

has a base price (SBP) of $350 and each large bus has a base price (LBP) of $450. For

tours longer than 5 hours in duration, typically 25% of the base price is added for each

hour above 5 hours, but no more than 4 hours above the 5 hours base are charged per day.

Again, even for tours lasting longer than 5 hours, the tour length is rounded to the nearest

whole hour. 1 The table below shows a few example price calculations. The base prices and additional

hourly percentage rates are subject to change at any time, thus your spreadsheet and code

should allow for the modification of these rates. Duration

in hours Total Price Calculation 3 base price 5 base price 6 base price + (1 * 0.25 * base price) 8 base price + (3 * 0.25 * base price)

Table 2. Example Pricing Calculations Your task is to create a flowchart and program that will pick the least-price bus

combination. It should also calculate the final price for a tour group. Your program

should accept the number of tourists (P), the tour duration (you can assume this will

always be entered as an integer), the base rates for each bus size, and the extra hourly

percentage (EHP) and then calculate the price components and total price for the most

efficient tour bussing option per day. 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.

Your program should display the number of small busses (NSB) needed and the number

of large busses (NLB) needed, including the small bus price extension (SE = NSB * SBP)

and large bus price extension (LE = NLB * LBP). The program should also show the

extra charged hours (ECH), if any, over five. It should also display the extra- small-bus

price (ESBP) and the extra-large-bus price (ELBP) based on the hours over five hours. It

should also display the total small bus price (TSBP) and total large bus price (TLBP), and

the total price of the tour (TP). 2 Note that the user will enter the number of people (P) and the number of hours (H) each

time the program is run. But the pricing parameters are usually stable, so the values for

these parameters will not be entered by the user each time. The visual basic code should

accept the values of P and H into variables. It should do the same for the value of the

pricing parameters from the pricing parameters cells as well. Figure 1. User Interface (with sample information)

In your code, use the variable abbreviations defined above and shown on the user

interface. For example, use TSBP and TLBP for total-small-bus price and total-largebus price respectively.

3 ASSIGNMENT

1. Create a flowchart that demonstrates how to calculate the total price of a tour

based on the group size and length of tour information provided by the customer.

(10 points)

2. Create a Visual Basic program to work with a user interface that you create in an

Excel spreadsheet. Your spreadsheet should correspond with the program that

implements the flowchart created in the question above. Use the same cell

locations for your interface as shown in Figure 1. Do not forget to test your

program to ensure it is working properly. (20 points).

Instructions for Preparing the Assignment Deliverables:

Flowchart. Include your name at the top of your flowchart. The flowchart should be

computer generated from LucidChart.com. The flowchart will be graded for its

adherence to flowcharting rules (symbols and logic.) This flowchart (exported from

LucidChart and placed in a word document) will be uploaded to D2L as a part of your

deliverable.

Visual Basic Code. Include a visual basic comment line near the top of your code that states

your name. Copy this code into a Word document and include the document as a part of

your deliverable on D2L.

Excel File. Upload your Excel file containing the spreadsheet and VBA program. (Make

sure to save is as a macro-enabled Excel file - with extension .xslm!)

Calculation Results Shown in the User Interface. Type your name and the appropriate

cell (B1). You will enter 5 different tour scenarios into your program, have your

program calculate the answers, and print (to PDF) or take a screenshot of each scenario

to turn in. The scenario information is as follows:

1.

2.

3.

4. 22 people for 4 hours;

55 people for 7.5 hours;

68 people for 5.2 hours;

99 people for 10 hours with an increase EHP set to 33% (a purposeful variation

from the 25% rule);

5. 84 people for 6 hours with an EHP back to 25% but a LBB of $500 and SBB of

$375.

After running the program to calculate the number of busses needed and the total

price for each tour scenario, print (to PDF) or take a screenshot of the programs user

interface. For example, start and run the program with the information of 22 tourists

on a tour for 4 hours. Then, capture your results via print to PDF or a screenshot.

Repeat this process using the other information sets, making changes to the pricing

information when necessary.

4 Final Deliverable:

All parts of the assignment should be uploaded to D2L by the stated deadline. The

various things you need to turn in include:

1.

2.

3.

4. Flowchart (Word file)

Visual Basic code (Word file)

Macro-enabled Excel file (.xslm)

Five different views of your user interface (in PDF or screenshots). One for each

data set:

4.1. 22 people, 4 hours

4.2. 55 people, 7 hours

4.3. 68 people, 5 hours

4.4. 99 people, 10 hours, EHP = 33%

4.5. 84 people, 6 hours, EHP = 25%, Large-Bus-Base Price (LBBP) = $500,

Small-Bus-Base Price (SBBP) = $375 5