So I can make the flowchart but not sure how to make the VBA code. It's all super foreign to me

13.5 VBA Project: Olympus Tours 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, and travel clubs. 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 Min 20 1 1 0 Max 25 Min 26 2 2 0 Max 50 Min 51 3 0 1 Max 60 Min 61 4 1 1 Max 85 Min 86 5 0 2 Max120 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 HoursExtra Hourly PercentOvertime Charge Total Price Table 2 Example Pricing Calculations $220 Number of People Per-Person Base RateBase Price Hours Overtime Hours Extra Hourly Percent Overtime Charge Total Price P PPBR" BP H OH EHP OC 20 $10 $200 5.4 0.4 25% $20 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. These are just example values you can use to test your outputs afterward. Your task is to 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. Because the pricing parameters (PPBR and EHP) are somewhat stable, they are stored in the setup section. When the program runs (both the single and the batch), it should obtain the values for PPBR and EHP from the Setup section. The value of PPBR and EHP should be whatever the value of cells C22, and C23 are at the time it runs (this can be any values you choose). Do not hardcode these values in your code. 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. Your program should only pull values from the worksheet. You should not be using input boxes. 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 (PPBR and EHP) to calculate the amount the customer will be charged per day. Note: if the user requests fewer than 20 or greater than 120 passengers, the user should see a pop-up message that indicates that they've selected too few or too many passengers and the outputs should all be set to zero. Figure 1 shows the [User Form] tab. Do not worry too much about the format of your numbers in cells C13:C18just be sure to declare your variables as the specified type. 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. Figure 1: [User Form] Tab A B D E 2 3 4 Olympus Tours Figure 1: [User Form] Tab A B D E 2 3 4 Olympus Tours 5 Inputs 6 Customer Name 7 Date of Tour N Stanfield Grop 1/5/2017 Date D P 120 10 Integer Single H Run Single 8 9 Number of People 10 Number of Hours 11 12 Outputs 13 Number of Small Busses 14 Number of Large Busses 15 Base Price 16 Overtime Hours 17 Overtime Charge 18 Total Price Run Batch NS NL OH OC TP 0 Integer 2 Integer 1200 Currency 4 Single 1200 Currency $2,400.00 Currency 19 20 21 Setup 22 Per-person Base Rate PPBR 23 Extra Hourly Percent EHP $10 25% Currency Single 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. All passenger inputs for Batch Estimates will be between 20 and 120 people. Figure 2: [Batch Input) Tab Name B 1/5/2017 Trip Date A 1 Davis Schools 2 61 3 6 Number of people Hours 4 5 Willie Wrestling 1/6/2017 The (Batch Output) tab. Figure 3 shows how your program should organize the outputs on the [Batch Output] tab. Figure 3: [Batch Output] Tab. J H NL 1 BP OH 1 B 1 Customer Date 2 Davis Schools 1/5/2017 3 Willie Wrestling 1/6/2017 4 Dahle Reunion 1/6/2017 5 Stanfield Group 1/7/2017 Instructions User Form D E F People Hours PPBR EHP 61 6 $10.00 25% 20 4 $10.00 25% 55 7 $10.00 25% 10 $10.00 25% Batch Input Batch Output G NS 1 1 0 0 K OC $152.50 $762.50 $0.00 $200.00 $275.00 $825.00 $1,200.00 $2,400.00 0 $610.00 $200.00 $550.00 $1,200.00 0 1 2 120 4 READY Summary of the Project Assignments and Details: 1. Create a flowchart for the process of completing an estimate for a single tour. You are not required to create a flowchart for the Run Batch process. Upload this flowchart separately from your completed workbook in the file upload box below (to facilitate grading). 2. Download the Olympus Tours Programming Project Worksheet below. Do not make your own workbook. Add VBA code to complete the project. 3. 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 on the [User Form] tab within the workbook. In addition, do not add or delete any columns or rows. 4. Use VBA to do all of the calculations. Do not use spreadsheet functions. 5. Add your VBA code to the two existing subroutines that have been started in Module1 already within the workbook. Add your code to the subroutine named EstSingle that will run when the user presses the Run Single Button. Add your code to the subroutine 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 subroutine will run when the button is pushed. 6. 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." 7. Use Dim statements to declare all variables that you use in your code. Declare the variables as the data types shown on Column D of the [User Form] tab. However, if this results in an overflow error, you are welcome to use the Variant variable type. This erroneous overflow error is due to some internal VBA issue that Microsoft has not yet fixed in some versions of Excel. 8. Test your program with a variety of inputs to ensure it works properly before you turn in your assignment. 9. You do not need to calculate PPBR or EHP. Rather, your sub should take whatever values of PPBR and EHP are provided by the user in the setup variables section in the single estimate user form. 10. Upload your completed Excel workbook in the appropriate upload box below. 11. Do not forget to handle user inputs that are invalid (i.e., 120 passengers) for single-trip estimates as indicated in the description above. 12. Be sure your batch Sub can handle extra batches of data (same format as existing batches) added to the bottom of the batch input tab. 13. Be sure your batch Sub can handle extra spaces (1 or more empty rows) between batches of data. 14. Please comment your code so that it is easy for a grader follow. Code without comments may be penalized. Partial credit is also much easier to grant if there are comments in the code. Be sure to write your code in a module in the workbook you will submit, not in your personal workbooks or sheets. To access the correct module, go to your "Visual Basic," right-click Microsoft Excel Objects in the workbook you are submitting, and then click Insert and Module. Specific grading criteria for this assignment can be found on the Grading Criteria sheet of your project file. 13.5 VBA Project: Olympus Tours 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, and travel clubs. 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 Min 20 1 1 0 Max 25 Min 26 2 2 0 Max 50 Min 51 3 0 1 Max 60 Min 61 4 1 1 Max 85 Min 86 5 0 2 Max120 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 HoursExtra Hourly PercentOvertime Charge Total Price Table 2 Example Pricing Calculations $220 Number of People Per-Person Base RateBase Price Hours Overtime Hours Extra Hourly Percent Overtime Charge Total Price P PPBR" BP H OH EHP OC 20 $10 $200 5.4 0.4 25% $20 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. These are just example values you can use to test your outputs afterward. Your task is to 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. Because the pricing parameters (PPBR and EHP) are somewhat stable, they are stored in the setup section. When the program runs (both the single and the batch), it should obtain the values for PPBR and EHP from the Setup section. The value of PPBR and EHP should be whatever the value of cells C22, and C23 are at the time it runs (this can be any values you choose). Do not hardcode these values in your code. 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. Your program should only pull values from the worksheet. You should not be using input boxes. 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 (PPBR and EHP) to calculate the amount the customer will be charged per day. Note: if the user requests fewer than 20 or greater than 120 passengers, the user should see a pop-up message that indicates that they've selected too few or too many passengers and the outputs should all be set to zero. Figure 1 shows the [User Form] tab. Do not worry too much about the format of your numbers in cells C13:C18just be sure to declare your variables as the specified type. 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. Figure 1: [User Form] Tab A B D E 2 3 4 Olympus Tours Figure 1: [User Form] Tab A B D E 2 3 4 Olympus Tours 5 Inputs 6 Customer Name 7 Date of Tour N Stanfield Grop 1/5/2017 Date D P 120 10 Integer Single H Run Single 8 9 Number of People 10 Number of Hours 11 12 Outputs 13 Number of Small Busses 14 Number of Large Busses 15 Base Price 16 Overtime Hours 17 Overtime Charge 18 Total Price Run Batch NS NL OH OC TP 0 Integer 2 Integer 1200 Currency 4 Single 1200 Currency $2,400.00 Currency 19 20 21 Setup 22 Per-person Base Rate PPBR 23 Extra Hourly Percent EHP $10 25% Currency Single 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. All passenger inputs for Batch Estimates will be between 20 and 120 people. Figure 2: [Batch Input) Tab Name B 1/5/2017 Trip Date A 1 Davis Schools 2 61 3 6 Number of people Hours 4 5 Willie Wrestling 1/6/2017 The (Batch Output) tab. Figure 3 shows how your program should organize the outputs on the [Batch Output] tab. Figure 3: [Batch Output] Tab. J H NL 1 BP OH 1 B 1 Customer Date 2 Davis Schools 1/5/2017 3 Willie Wrestling 1/6/2017 4 Dahle Reunion 1/6/2017 5 Stanfield Group 1/7/2017 Instructions User Form D E F People Hours PPBR EHP 61 6 $10.00 25% 20 4 $10.00 25% 55 7 $10.00 25% 10 $10.00 25% Batch Input Batch Output G NS 1 1 0 0 K OC $152.50 $762.50 $0.00 $200.00 $275.00 $825.00 $1,200.00 $2,400.00 0 $610.00 $200.00 $550.00 $1,200.00 0 1 2 120 4 READY Summary of the Project Assignments and Details: 1. Create a flowchart for the process of completing an estimate for a single tour. You are not required to create a flowchart for the Run Batch process. Upload this flowchart separately from your completed workbook in the file upload box below (to facilitate grading). 2. Download the Olympus Tours Programming Project Worksheet below. Do not make your own workbook. Add VBA code to complete the project. 3. 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 on the [User Form] tab within the workbook. In addition, do not add or delete any columns or rows. 4. Use VBA to do all of the calculations. Do not use spreadsheet functions. 5. Add your VBA code to the two existing subroutines that have been started in Module1 already within the workbook. Add your code to the subroutine named EstSingle that will run when the user presses the Run Single Button. Add your code to the subroutine 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 subroutine will run when the button is pushed. 6. 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." 7. Use Dim statements to declare all variables that you use in your code. Declare the variables as the data types shown on Column D of the [User Form] tab. However, if this results in an overflow error, you are welcome to use the Variant variable type. This erroneous overflow error is due to some internal VBA issue that Microsoft has not yet fixed in some versions of Excel. 8. Test your program with a variety of inputs to ensure it works properly before you turn in your assignment. 9. You do not need to calculate PPBR or EHP. Rather, your sub should take whatever values of PPBR and EHP are provided by the user in the setup variables section in the single estimate user form. 10. Upload your completed Excel workbook in the appropriate upload box below. 11. Do not forget to handle user inputs that are invalid (i.e., 120 passengers) for single-trip estimates as indicated in the description above. 12. Be sure your batch Sub can handle extra batches of data (same format as existing batches) added to the bottom of the batch input tab. 13. Be sure your batch Sub can handle extra spaces (1 or more empty rows) between batches of data. 14. Please comment your code so that it is easy for a grader follow. Code without comments may be penalized. Partial credit is also much easier to grant if there are comments in the code. Be sure to write your code in a module in the workbook you will submit, not in your personal workbooks or sheets. To access the correct module, go to your "Visual Basic," right-click Microsoft Excel Objects in the workbook you are submitting, and then click Insert and Module. Specific grading criteria for this assignment can be found on the Grading Criteria sheet of your project file