You are the Shipper for Aero Logistics based at the listed address. A customer has requested a quote for a 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 V$7$B $1$L$5$

Complete the following tasks

Customer Information: This is not an actual address. Use this address$/$information to invoice the client.

Market Systems Manufacturing

$20450$ Champlain Road

Langley, BC

V$6$Y $2$L$9$

Contact Person: Mr$.$ 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 using 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: The exact address where the goods are to ship. Research and find an actual address in your destination city. US Google Maps. IE: Every town 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 $$995$ 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,700$ per engine.

PLEASE READ the instructions carefully.

Shipping Rate Information:

Driver Rate: $$300\mathrm{.}00$ per day. One day equals $7\mathrm{.}5$ hours. Federal transportation regulations restrict drivers from exceeding the $7\mathrm{.}5$ hours of driving time maximum.

Fuel Surcharge: $$0\mathrm{.}90$ per mile.

Use $45$ miles an hour for your average driving speed.

Instructions:

$1.$ Calculate the exact mileage from the Aero Logistics depot $($your address$)$ to the address of your assigned city.

$2.$ Find an exact address in your city where the shipment will be delivered. Every town 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 driver's Fee on the Invoice.

$6.$ The legal drive time for all truckers is a Maximum of $7\mathrm{.}5$ hours.

$7.$ Use $45$ miles an hour as your average driving speed.

$8.$ Apply a $10\mathrm{.}55\%$ 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.

$12.$ Clearly describe your calculations' units, i$.$e$.$ lb or kg$,$ and identify the currency and distance.

State Transportation Fees

$1500$ lbs$.$ State Fee $$1\mathrm{.}06$ Per Lbs

$2000$ lbs$.$ State Fee $$1\mathrm{.}29$ Per Lbs

$2800$ lbs$.$ State Fee $$1\mathrm{.}37$ Per Lbs

$3500$ lbs$.$ State Fee $$1\mathrm{.}49$ Per Lbs

$4900$ lbs$.$ State Fee $$1\mathrm{.}56$ Per Lbs

$5700$ lbs$.$ State Fee $$1\mathrm{.}66$ Per Lbs

$7400$ lbs$.$ State Fee $$1\mathrm{.}73$ Per Lbs

$9800$ lbs$.$ State Fee $$1\mathrm{.}84$ Per Lbs

$10,500$ lbs$.$ State Fee $$1\mathrm{.}92$ Per Lbs

$11\mathrm{.}900$ lbs$.$ State Fee $$2\mathrm{.}19$ Per Lbs

$13\mathrm{.}500$ lbs$.$ State Fee $$2\mathrm{.}30$ Per Lbs

$15,000$ lbs$.$ State Fee $$2\mathrm{.}57$ Per Lbs

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

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

Insurance Rates:

Value of goods: Insurance Rates:

$$4,000$ $$14\mathrm{.}00$

$$6,000$ $$16\mathrm{.}00$

$$10,000$ $$20\mathrm{.}00$

$$15,000$ $$25\mathrm{.}00$

$$25,000$ $$30\mathrm{.}00$

$$35,000$ $$39\mathrm{.}00$

$$50,000$ $$45\mathrm{.}00$

$$65,000$ $$54\mathrm{.}00$

$$85,000$ $$75\mathrm{.}00$

$$100,000$ $$90\mathrm{.}00$

$$150,000$ $$98\mathrm{.}00$

$$175,000$ $$125\mathrm{.}00$

$$195,000$ $$175\mathrm{.}00$

$15.$ 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 $0\mathrm{.}30$ Cents for each mile exceeding the threshold driven.

$16.$ Create an invoice for the total shipping costs for your client. You may use the attached sample invoice on eLearn.

$17.$ For an alternative to the payment options. Use the PMT function to calculate monthly instalments for the total shipping fees. Assume the client will pay it in one year and the rate is $7\%.$ Show your work on the calculation sheet. Do not include this cal