Car Inventory You have been tasked with creating an Excel workbook that will allow sales persons the ability to search existing inventory by VIN. They 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. Populate Calculated Fields You 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 200 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 125 and 200 days will earn the seller one raffle ticket in the end of the month $1000 bonus. On the Inventory worksheet, insert a function in H7:H206 to calculate the number of days each vehicle has been on the lot using 1/1/2018 as todays date. Insert a function in I7:I206 that will display Bonus Eligible for cars that have been on the lot more than 200 days, Raffle Eligible for cars that have been on the lot between 125 and 200 days, and nothing for the remaining vehicles. Search Inventory You need to create a search by VIN and an advanced search of any criteria and return the required results. In cell B2, enter the value from A7. Insert a function in D2 that will return the arrival date of any vehicle based on which VIN is entered in B2. Insert a function in F2 that will return the Special Notes of any vehicle based on which VIN is entered in B2. Enter the following values for the advanced search criteria in the range K3S3: Year = 2018 Type = Sedan GPS = Yes Create an advanced search using the search criteria in the range K2:S3 and the inventory data where the results will be displayed in the search results (starting in K14). Verify results populate as expected. Calculate Summary Data You 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. Insert a function in L6 that displays the number of results returned by the advanced search (K2:S3). Insert a function in N6 that displays the minimum MSRP of results returned by the advanced search (K2:S3). Insert a function in P6 that displays the maximum MSRP of results returned by the advanced search (K2:S3). Insert a function in R6 that displays the average Days on Lot of results returned by the advanced search (K2:S3). Average Days On Lot You need to calculate the Average Days on Lot based on MSRP and/or Types using the Inventory data. Enter functions into the Average Days on Lot chart area that calculate the following: Insert a function in K10 that display the Average Days on Lot of all Types Insert a function in L10 that display the Average Days on Lot of all Types that have a color of Red Insert a function in M10 that displays the Average Days on Lot of all Types that have a GPS Insert a function in N10 that displays the Average Days on Lot of all Types that have a MSRP that is higher than 20000 and have a Color of White Save the Workbook Calculate a Loan The company is comfortable paying $3000 a month for the next 5 years. Calculate the maximum loan they can obtain using the 3.75% APR rate quote the bank provided. On the Loan worksheet, enter the data from above into the appropriate boxes for the loan Input. Insert a formula in E3 to calculate the Periodic Rate for the loan. Insert a formula in E4 to calculate the # of Payments for the loan. Insert a formula in E2 to calculate the Loan Amount they can afford based on criteria provided. Insert a formula in E2 to calculate the Loan Amount they can afford based on criteria provided. Use What-If Analysis The company wants to evaluate the Loan cost based on different APRs. You will be using What-If analysis to aid in this. Ensure the Loan worksheet is active. Create the following three scenarios using the Scenario Manager. The scenarios should change the cells B3, B4, and E2. Create a scenario named Best Case using the following pricing: B3 = .0235 B4 = 5 E2 = 126900.00 Create a scenario named Most Likely using the following pricing: B3 = .0375 B4 = 5 E2 = 163900.00 Create a scenario named Worst Case using the following pricing: B3 = .0700 B4 = 5 E2= 200000.00 x. Create a Scenario Summary Report based on the value in E2. y. Format the new report appropriately and save the workbook Generate an Amortization Table You need to use the loan information to generate an amortization table for the life of the loan. Add the reference to the initial Beginning Balance in B8. Add the reference to the Monthly Payment in C8. Insert a formula in D8 to calculate the Interest Paid. Insert a formula in E8 to calculate the Principal Payment. Insert a formula in F8 to calculate the Ending Balance. Insert a formula in B9 to populate the new Beginning Balance. Use the fill handle to copy the formulas to the remainder of the table. Save and close the file.

Fle Review view Hep OSearch share Comments m Insert Page Layout Formu as Deta AutaSum Cur == . G... 11 A A Normal Bad Good Neutral il Copy Cancitional Fnemat as Calralation Check Cell Sort &Find Sa Flter Sdect Ideas DMange & Cami volangtary A Input mat Panter Fomattng Table Aignn Cipbcrs sunbe lteas S200 L M 4 E G H N R VIN Scarch IN Arrival Date Special Notes | Color VIN Year Arrival Date Days un Lot Special Notes Type GPS 4. Summary Uatal Inventory Days on Lot Max MSRP Min MSRP Ave Days an lat ival Date G/18/2017 11/10/2017 GP sep Special Notes Count ype oulsedse GRTI64 Yes 01234473 Average Days Un Lox ColanRed w/GPS 201a a5.300 Oup Overall Cambined NO 01zTuck 23477138 lwhit 10/9/2017 9/4/2017 $ 22.848 2018 SUV Gray Yes 11 5170/43 19.542 NO 2/20/2012 18 suv lwhit s 30.940 Arlval Dato Speclal Notes 3 VIN # Year Typa Color SPS MSRP Days on Lot - 2017 Sedss Yes 2/8/2017 s 19.918 4 DuslScds 34948740 1/21/2017 10/3/2017 17.511 Blach Black 2017 Truck 17. 41 Yes 24432 Dis Scds s 33 95 TROCarOo IBed 8/7/2017 12/10/201 white 2017 Truck 2430900a 20.00 3a563a07 017 Van lwhit 5/24/2017 8/21/2012 2 31.193 Red 2017 Truck No 3 21.916 7/25/2017 1/20/2011 usUV G811924s 35.004 Blac No $ 26,548 24103191 U Seds 018lvan 19653072 Gray 9/16/2017 /23/2017 31443 2017 SUV Blue $ 28,285 No 24307000 Dislvan Biack G/17/2017 2/13/2012 2/27/2017 $ 38433 31177417 Blac Red No Yes 2017 Var 1Cu 32 4400823 20,257 48126466 1.60 mlSeder 51210e 65868741 Black White /27iz011 Yes Na 27-8H 7/4/2017 201R Caupe 26.761 /1/201 7 201 Sedan NO Inventory Losn - share Flr view Comments Home Insert Page Layout Formulas Deta Review Hep Search & cut L Copy AutSum Wp Te Ganal Normal Calri 11 A A =_ Bad Good Neutral Fil Clear Concitional Eormata Calaulatinn Part Inrer Osiate Forms Sot & Find S Ides Check Cell Cxplanatory A Merge &Cantar Input Fli Sdect- Farmst Panter Founng- T.ble unbe Styles edting Iteas Loan Amount D2 F A. D E Y Z Output put Paymenta Loan Amount 4 Yoars: 5Pmts per Year 6 Princi pal Beginning Monthly Pavmen Encing 7 Payment A Payment alanew Balance Interest Paid 21. 22 12 - 4 25 27 28 20 31 34 2f - 40 43 43 - 45 47 40 4% 42 (4 Loan Inventery -- -100% Fle Review view Hep OSearch share Comments m Insert Page Layout Formu as Deta AutaSum Cur == . G... 11 A A Normal Bad Good Neutral il Copy Cancitional Fnemat as Calralation Check Cell Sort &Find Sa Flter Sdect Ideas DMange & Cami volangtary A Input mat Panter Fomattng Table Aignn Cipbcrs sunbe lteas S200 L M 4 E G H N R VIN Scarch IN Arrival Date Special Notes | Color VIN Year Arrival Date Days un Lot Special Notes Type GPS 4. Summary Uatal Inventory Days on Lot Max MSRP Min MSRP Ave Days an lat ival Date G/18/2017 11/10/2017 GP sep Special Notes Count ype oulsedse GRTI64 Yes 01234473 Average Days Un Lox ColanRed w/GPS 201a a5.300 Oup Overall Cambined NO 01zTuck 23477138 lwhit 10/9/2017 9/4/2017 $ 22.848 2018 SUV Gray Yes 11 5170/43 19.542 NO 2/20/2012 18 suv lwhit s 30.940 Arlval Dato Speclal Notes 3 VIN # Year Typa Color SPS MSRP Days on Lot - 2017 Sedss Yes 2/8/2017 s 19.918 4 DuslScds 34948740 1/21/2017 10/3/2017 17.511 Blach Black 2017 Truck 17. 41 Yes 24432 Dis Scds s 33 95 TROCarOo IBed 8/7/2017 12/10/201 white 2017 Truck 2430900a 20.00 3a563a07 017 Van lwhit 5/24/2017 8/21/2012 2 31.193 Red 2017 Truck No 3 21.916 7/25/2017 1/20/2011 usUV G811924s 35.004 Blac No $ 26,548 24103191 U Seds 018lvan 19653072 Gray 9/16/2017 /23/2017 31443 2017 SUV Blue $ 28,285 No 24307000 Dislvan Biack G/17/2017 2/13/2012 2/27/2017 $ 38433 31177417 Blac Red No Yes 2017 Var 1Cu 32 4400823 20,257 48126466 1.60 mlSeder 51210e 65868741 Black White /27iz011 Yes Na 27-8H 7/4/2017 201R Caupe 26.761 /1/201 7 201 Sedan NO Inventory Losn - share Flr view Comments Home Insert Page Layout Formulas Deta Review Hep Search & cut L Copy AutSum Wp Te Ganal Normal Calri 11 A A =_ Bad Good Neutral Fil Clear Concitional Eormata Calaulatinn Part Inrer Osiate Forms Sot & Find S Ides Check Cell Cxplanatory A Merge &Cantar Input Fli Sdect- Farmst Panter Founng- T.ble unbe Styles edting Iteas Loan Amount D2 F A. D E Y Z Output put Paymenta Loan Amount 4 Yoars: 5Pmts per Year 6 Princi pal Beginning Monthly Pavmen Encing 7 Payment A Payment alanew Balance Interest Paid 21. 22 12 - 4 25 27 28 20 31 34 2f - 40 43 43 - 45 47 40 4% 42 (4 Loan Inventery -- -100%