You are going to buy a new car and would like to find out the options for cars, loans and calculate the monthly payment schedule. Create a workbook with 3 worksheets:
1. Car information: This sheet includes columns like Manufacturer name, Model name, Manufacturer price, Gas mileage, Color and Picture. Go online to find out the information you need. List 4 cars of your interest. The cars should be first sort by manufacturer name, and then by model name. The car pictures should be inserted as images in the picture column.
2. Dealer information: This sheet includes columns like Dealer name, Contact Person, Contact phone, Address, City, State, Zip code. You should have at least 3 records in this table. The dealer information should be sorted by dealer name.
3. Car finance: This sheet includes columns like Manufacture name, Model Name, Dealer price, Year of Finance, Interest rate, Down payment, Monthly payment. Use the financial function PMT to calculate the monthly payment. Make sure the interest rate, number of payment and periodical payment match together.
Requirements:
1. Use conditional formatting to mark out the top 3 high mileage cars using red color in the car information table.
2. For your a car of your choice, prepare a scenario analysis for the car finance using high, current and low interest rate and put it on a separate sheet. You can also have other changing factors in the scenario analysis.
3. All data should be formatted properly (data type, decimal place etc.). Add sheet labels, and format the sheets in a professional layout.
4. Add your name to the center of the footer of all the worksheets. Put a date (function) in the top right header section of all your worksheets.