You are the Shipper for Aero Logistics based at the listed address. A customer has requested a quote for long haul shipment to the city you've been assigned.READ the instructions carefully.

(A Real Address) Use this one for your Company

Aero Logistics Ltd. 3151 Aylmer Rd, Richmond, BC V7B 1L5

Complete the following tasks

Customer Information:This is not a real address.Use this address/information to invoice the client. Market Systems Manufacturing 20450 Champlain Road Langley, BC V6Y 2L9 Contact Person: Mr. Richards. J. Nelson VP International Sales

They have requested a quote from Aero Logistics, (your company) for the following shipment. Use the attached Invoice on Moodle to present your final quote. Copy and paste your Invoice onto your Master Workbook. Rename your Spreadsheet Tab as Invoice.

Upload 1 Workbook only. DO NOT ZIP YOUR FILES.

You must show all your calculations in an Excel workbook. You MUST demonstrate the use of a Vlookup and an IF Function in your Excel Spreadsheets. Do Not use a Calculator.

Resize your worksheets to a Legal-size Format, Landscape Orientation. All your calculations must fit onto a Legal-size sheet.

Apply Formatting your workbook.

Destination Information: This is the exact address where the goods are to ship. Research and find an exact address in your destination city. US Google Maps. IE: Every city has a Main Street or 1^st Street. Use it to determine the driving distance.

Cargo Information:

These are the items the client needs to be shipped by a 40 foot semi-trailer. These items will be shipped from your company address.

28 cartons @ 58 kilos per carton. Aircraft parts. Valued at $975 per carton. 14 crates @136 kilos per crate. Deep Rig Drilling Bits. Valued at $3258 per crate 3, Model 48 Diesel engines @ 2200 kilos per engine. Valued at $18,500 per engine.

PLEASE READ the instructions carefully.

Shipping Rate Information:

Driver Rate: $275.00 per day.1 day equals 7.5 hours. Federal transportation regulations restrict drivers from exceeding the 7.5 hours driving time maximum.

Fuel Surcharge: .86 per mile. Use 45 miles an hour for you average driving speed.

Instructions:

1. Calculate the exact mileage from the Aero Logistics depot (your address) to the address of your assigned city.
2. Research and find an exact address in your city where the shipment is to be delivered. Every city has a Main Street or a 1^st. Street or 1^st. Avenue. Be creative.
3. Calculate the Total Fuel Surcharges and indicate it on the Invoice.
4. Calculate the total number of driving days it will take to reach your destination and the total amount the driver will be paid. Round off your hours to half hours if necessary.
5. Indicate the Drivers Fee on the Invoice.
6. The legal drive time for all truckers is Maximum 7.5 hours.
7. Use 45 miles an hour as your average driving speed.
8. Apply a 8.75% State Tax on the Total Value of the goods.
9. Apply the State Transportation Fee to the total weight of the goods being shipped and include it on the invoice.
10. State Transportation Fees are based on each Pound carried.
11. Create a VLookup Table using the listed data to calculate the State Transportation Fees. Remember the Excel Rule of Mixing Text and Numbers.

State Transportation Fees

1500 lbs. State Fee $1.06 Per Lbs 2000 lbs. Sate Fee $1.29 Per Lbs 2800 lbs. State Fee $1.37 Per Lbs 3500 lbs. State Fee $1.49 Per Lbs 4900 lbs. State Fee $1.56 Per Lbs 5700 lbs. State Fee $1.66 Per Lbs 7400 lbs. State Fee $1.73 Per Lbs 9800 lbs. State Fee $1.84 Per Lbs 10,500 lbs. State Fee $1.92 Per Lbs 11.900 lbs. State Fee $2.19 Per Lbs 13.500 lbs. State Fee $2.30 Per Lbs 15,000 lbs. State Fee $2.47 Per Lbs

1. Calculate and apply insurance costs to the Total Value of the goods being shipped. Indicate this on the Invoice.

1. You will need to create an Insurance Lookup Table using the listed data table.

Insurance Rates: Value of goods: Insurance Rates:

$4,000 $14.00

$6,000 $16.00

$10,000 $20.00

$15,000 $25.00

$25,000 $30.00

$35,000 $39.00

$50,000 $41.00

$65,000 $54.00

$85,000 $62.00

$100,000 $88.00

$150,000 $98.00

$175,000 $125.00

$195,000 $175.00

1. Create an IF Function Driver's Bonus using the listed data.

Legend: Threshold: 950 miles Bonus: Mileage Distance if Greater than 950 miles. Driving Bonus Add .10 Cents for each mile exceeding the threshold driven.

1. Create an invoice for total shipping costs for your client. Use the attached invoice on Moodle.
2. Insert your student and course information in your Header.
3. Upload your completed Excel project workbook plus your Shipping Invoice (1 file) to Moodle.
4. DO NOT ZIP your files. Upload Excel workbooks only.
5. You should have created 2 worksheets in your workbook
6. Work the problem in stages.
7. Resolve one problem at a time.