StudyDaddy Science

Answered You can hire a professional tutor to get the answer.

QUESTION

Apr 03, 2018

I am not looking for the VBA code but for the flowchart only, I just need a starting point.

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 Oklahoma's 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 don't 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

Min and Max Tourists

Small

Big

Per Bus Price

Base * Qty

min tourists

350

max tourists

min tourists

450

max tourists

min tourists

350

700

max tourists

min tourists

350

800

max tourists

450

min tourists

450

900

max tourists

110

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.

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

base price

base price + (1 * 0.25 * base price)

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).

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.

Student Name:

Matt Jensen

OKC Tours

# of People (P):

# of Hours (H):

Extra Charged Hours (ECH):

Small Busses

Large Busses

Number of Busses

(NSB):

(NLB):

Extension

(SE):

$375.00

(LE):

$0.00

Extra Hours Charges

(SEHC):

$0.00

(LEHC):

$0.00

Total Bus Prices

(TSBP):

$375.00

(TLBP):

$0.00

Total Price (TP):

$375.00

Pricing Parameters

5-Hour Base Rates

Small-Bus Base Price (SBP):

$375.00

Large-Bus Base Price (LBP):

$500.00

Other

Extra Hourly Percentage (EHP):

25%

Figure 1. Example 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-large- bus price respectively.

ASSIGNMENT

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. (20 points)

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. (80 points).

Instructions for Preparing the Assignment Deliverables:

Flowchart. Include your name at the top of your flowchart. [The flowchart could 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 Canvas 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 Canvas.

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, paste them into a word document (properly label them) to turn in. The scenario information is as follows:

21 people for 3 hours;

52 people for 7.3 hours;

65 people for 5.2 hours;

99 people for 10 hours with an increase EHP set to 35% (a purposeful variation from the 25% rule);

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

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 program's 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.