You have been tasked with creating an Excel workbook that will enable sales people to search the existing inventory by VIN. Sale people must also be able to define any combination of search criteria that returns a list of results and summary data pertaining to that result set. They have also asked that you create a separate sheet to help them calculate how large of loan they can afford and an amortization schedule for the loan.Perform CalculationsYou need to calculate the number of days each vehicle has been on the lot and if there are any special notes that need to be added to each vehicle. Any vehicle that has been on the lot more than $180$ days is eligible for a special employee bonus if they are able to sell that vehicle. Any vehicle that has been on the lot between $100$ and $180$ days will earn the seller one raffle ticket for the end of the month $$2,000$ bonus.a$.$Open Assignment $3$ and save as Assignment$3\_$LastFirst.b$.$Display the Inventory worksheet and insert a function in range H$7$:H$206$ to calculate the number of days each vehicle has been on the lot using $1/1/2021$ as todays date.c$.$Insert a function in range I$7$:I$206$ that will display Bonus Eligible for cars that have been on the lot more than $180$ days, Raffle Eligible for cars that have been on the lot between $100$ and $180$ days, and nothing for the remaining vehicles.Calculate Cumulative Statistics for One ConditionYou need to create a search by VIN and an advanced search of any criteria and return the required results.d$.$Enter the value from cell A$7$ in cell B$2.$e$.$Insert a function in cell D$2$ that will return the arrival date of any vehicle based on which VIN is entered in cell B$2$ and format as date.f$.$Insert a function in cell F$2$ that will return the Special Notes of any vehicle based on which VIN is entered in cell B$2.$g$.$Enter the following values for the advanced search criteria in the range K$3$:S$3$:Year $=2020$Color: BlackType $=4$ DoorGPS $=$ Yesh.Create an advanced search using the search criteria in the range K$2$:S$3$ and the inventory data where the results will be displayed in the search results $($starting in cell K$10).$i$.$Verify results populate as expected.Calculate Cumulative Statistics for Two ConditionsYou need to generate summary data for the search results to show the number of results returned, the minimum and maximum MSRP$,$ and average days on lot.j$.$Insert a function in cell L$6$ that displays the number of results returned by the advanced search $($range K$2$:S$3).$k$.$Insert a function in cell N$6$ that displays the minimum MSRP of results returned by the advanced search $($range K$2$:S$3).$l$.$Insert a function in cell P$6$ that displays the maximum MSRP of results returned by the advanced search $($range K$2$:S$3).$m$.$Insert a function in cell R$6$ that displays the average Days on Lot of results returned by the advanced search $($range K$2$:S$3).$Insert a MapThe Map worksheet contains locations, zip codes, and average sales for the five locations. You will insert a map chart to depict the average sales and format it appropriately.n$.$Display the Map worksheet, select range range B$1$:C$6$ and insert a map chart.o$.$Drag the chart so the top$-$left corner is in cell cell A$8.$ Set the height to $3\mathrm{.}5$ and width to $5\mathrm{.}5$ and edit the title to Average Sales by Location.p$.$Edit to Chart style $2$ and change the colors to Colorful Palette $3.$Loan AmortizationThe company is comfortable paying $$3500$ a month for the next five years. Calculate the maximum loan they can obtain using the $2\mathrm{.}35\%$ APR rate quote the bank provided. You need to use the loan information to generate an amortization table for the life of the loan.q$.$Display the Loan worksheet and enter the data from above into the appropriate boxes for the loan Input.r$.$Insert a formula in cell E$3$ to calculate the Periodic Rate for the loan.s$.$Insert a formula in cell E$4$ to calculate the # of Payments for the loan.t$.$Insert a formula in cell E$2$ to calculate the Loan Amount they can afford based on criteria provided.u$.$Add the reference to the initial Beginning Balance in cell B$8.$v$.$Add the reference to the Monthly Payment in cell C$8.$ Should be absolute cell reference.w$.$Insert a formula in cell D$8$ to calculate the Interest Paid.x$.$Insert a formula in cell E$8$ to calculate the Principal Payment.y$.$Insert a formula in cell F$8$ to calculate the Ending Balance.z$.$Insert a formula in cell B$9$ to populate the new Beginning Balance.aa$.$Use the fill handle to copy the formulas to the remainder of the table.Workbook CompletionComplete the workbook by adding a footer and identifying information. ab$.$ Create a footer with your name on left side, sheet name in center, and file name code on right side.ac$.$ Save and close the file. Based on your instructors directions submit e$07\_$exam$\_$chap$\_$LastFirst.