You need to solve this business problem using Excels If and Vlookup Functions as well as applying your practical business decision making process

You are responsible for the transportation of 20, 40-foot semi trailers of stage sets, lighting, sound equipment and backgrounds. Etc. to each city on the tour.

You will need to organize the concert tour using the listed information

1. You select the first two concert cities on the East Coast of the United States. (The touring manager normally takes care of this.) For now, you will decide which cities to go to.
2. Calculate the driving distance from the docks at Galveston to the first city on the tour.
3. The first city on the tour MUST be somewhere on the East Coast of the United states. You select the city.
4. Select the Second East Coast City and calculate the driving distance.
5. The Tour now goes to the West Coast of USA. Select the Cities.
6. The Third and Forth Cities MUST be somewhere on the West Coast of the United States. You select the city.
7. Calculate the driving distance from the Last City on the East Coast.
8. Calculate the driving distance from the Third city to the Forth city on the tour.
9. The Fifth and Sixth City must be either one of these Canadian cities: You select the cities
   1. Toronto
   2. Ottawa
   3. Montreal
   4. Quebec City

Contract Details: Rolling Stones Touring Manager has hired your Logistics company to transport all the stage sets, lighting, sound equipment and backgrounds on their North American Tour.

You are responsible for the transportation of 20, 40-foot semi trailers of stage sets, lighting, sound equipment and backgrounds. Etc.

Driver Information: Shomotion supplies 20 drivers (One driver per Semi trailer). Each driver is paid a day rate of $245.00 per driving day. A drivers day is 7.50 Hours. The average driving speed 45 miles per hour.

Project Instructions

You must calculate the driving distance from the Port of Galveston to the first city on the tour. How many days will it take to drive to the first concert city? You must calculate the driving distance from the first city to the second, third, forth, fifth, and sixth city on the tour. How many days will it take to drive to all the cities on the concert tour.

You also need to calculate the total mileage for the entire tour. Calculate the total number of driving days for the entire tour. There is Mileage Sur-Charge: Each driver receives .011 cents per mile. Driver Bonus: What is the Total Mileage after the Sixth concert City? Apply an Excel If Function to determine if a driver qualifies for Mileage bonus.

Mileage Bonus Information:

You must demonstrate the use of an Excel IF Function

Mileage >= 45000 Miles: If the Drivers achieve this they will receive a Bonus $2500.00 Calculate the Total Driving distances for each Semi-trailer Driver for all 6 concert cities, Mileage Sur-Charges and Drives Bonuses. You must calculate the total driving distances from the Port of Galveston to each of the six different cities.

You must create and use the VLookup Table

Shomotion insures the clients cargo

Trailer Rental & Insurance Rates

Per Trailer Rental Day Rate	*Total Rental Days	Total Value of Client Equipment	Insurance Rate Per Day
$1600.00	3	$100,000	$60.00
$1400.00	6	$500,300	$90.00
$ 1200.00	9	$1,100,700	$100.00
$1000.00	12	$2,700,000	$120,00
$900.00	15	$3,500,000	$135.00
$700.00	18	$5,00,000	$140.00
$650.00	20	10,500,000	$150.00

*More than 20 Days Rental is the Maximum Rental Rate #Value of clients transported equipment for insurance purposes. *Maximum Insurance Day Rate for Equipment Value over $10,000,00

Calculate the following information using the appropriate Excel function. You need to demonstrate the use of Formulas and Functions for the project.

I need to see formulas and functions in your calculations in a single Workbook NO RAW calculation numbers.

Create the necessary look up tables in your Excel workbook for the following instructions

1. Calculate the Trailer Rentals for the entire tour.
2. Calculate the Insurance Costs of the Value of the Equipment for the entire tour.
3. Calculate the total costs and clearly display them on your worksheet.

Rolling Stones Trailer Equipment Inventory Values:

Item	Trailer No.	Equipment Value
1	1120	$660,500
2	2201	$775,400
3	1202	$339,400
4	1003	$994,000
5	1222	$669,000
6	1304	$1,890,000
7	2230	$750,000
8	1001	$1,900,00
9	1440	$400,000
10	1155	$210,000
11	1311	$220,000
12	1009	$567,000
13	1010	$335,000
14	1104	$409,000
15	2209	$653,000
16	1766	$299,000
17	1105	$780,000
18	2005	$190,000
19	2000	$303,000
20	2290	$450,000

Use Google Maps to help you calculate the driving distances.