create both 1) a flowchart and, 2) a VBA program in Excel that will pick the right number of buses and that will calculate the correct price for the number of people and hours specified.

Assume you have just been hired by Olympus Tours (OT), a bus-rental company located in Orem, Utah. OT provides buses for groups such as schools, family reunions, travel clubs, etc. The smallest group OT will bus is 20 people and the largest group is 120 people.

Two types of buses are available for use: small buses (25 maximum riders per bus) and large buses (60 maximum riders per bus). Assume that OT has access to all of the small and large buses it needs.

Table 1. Bus Combinations

			# of Buses
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

 

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.

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.

Table 2 Example Pricing Calculations

Number of People	Per-Person Base Rate	Base Price	Hours	Overtime Hours	Extra Hourly Percent	Overtime Charge	Total Price
P	PPBR*	BP	H	OH	EHP*	OC	TP
20	$10	$200	5.4	0.4	25%	$20	$220
25	$12	$300	6.6	1.6	25%	$120	$420
30	$8	$240	11.3	4.0	25%	$240	$480
35	$7	$245	5.4	0.4	30%	$29	$274
45	$15	$675	6.6	1.6	30%	$324	$999
50	$5	$250	11.3	4.0	30%	$300	$550
Note: *these numbers are not calculated in your code. Your sub should simply take whatever value for PPBR and EHP that is given in the setup portion of the single estimate and use that in your calculations