Exam 2: Modules 5-7 (100 pts)

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 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.

a.Open exam2_data and save as CIS180_exam2_LastFirst.

b.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 today's date.

c.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 earch by VIN and an advanced search of any criteria and return the required results.

d.In cell B2, enter the value from A7.

e.Insert a function in D2 that will return the arrival date of any vehicle based on which VIN is entered in B2.

f.Insert a function in F2 that will return the Special Notes of any vehicle based on which VIN is entered in B2.

g.Enter the following values for the advanced search criteria in the range K3S3:

Year = 2018

Type = Sedan

GPS = Yes

h.Create n 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).

i.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.

j.Insert a function in L6 that displays the number of results returned by the advanced search (K2:S3).

k.Insert a function in N6 that displays the minimum MSRP of results returned by the advanced search (K2:S3).

l.Insert a function in P6 that displays the maximum MSRP of results returned by the advanced search (K2:S3).

m.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.

n.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

o.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.

p.On the Loan worksheet, enter the data from above into the appropriate boxes for the loan Input.

q.Insert a formula in E3 to calculate the Periodic Rate for the loan.

r.Insert a formula in E4 to calculate the # of Payments for the loan.

s.Insert a formula in E2 to calculate the Loan Amount they can afford based on criteria provided.

t.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.

u.Ensure the Loan worksheet is active.

v.Create the following three scenarios using the Scenario Manager. The scenarios should change the cells B3, B4, and E2.

Create scenario named Best Case using the following pricing:

oB3 = .0235

oB4 = 5

oE2 = 126900.00

Create scenario named Most Likely using the following pricing:

oB3 = .0375

oB4 = 5

oE2 = 163900.00

Create scenario named Worst Case using the following pricing:

oB3 = .0700

oB4 = 5

oE2= 200000.00

x.Create 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.

a.Add the reference to the initial Beginning Balance in B8.

b.Add the reference to the Monthly Payment in C8.

c.Insert a formula in D8 to calculate the Interest Paid.

d.Insert a formula in E8 to calculate the Principal Payment.

e.Insert a formula in F8 to calculate the Ending Balance.

f.Insert a formula in B9 to populate the new Beginning Balance.

g.Use the fill handle to copy the formulas to the remainder of the table.

h.Save and close the file. Based on your instructor's directions submit e07_exam_chap_LastFirst.

VIN Search VIN Arrival Date VIN # 69716460 61234473 91689100 23477138 95176743 51905311 24926742 34689659 65864983 24948740 50481896 70007632 79067608 24509603 13065281 33563307 87658210 96214106 68119249 24103191 29411112 19852677 74307606 13505926 31177417 44058293 48126466 77631568 51210965 65868741 18725724 67804054 78431600 58520909 49113888 64376988 49033711 16610947 99894980 Year Type 2018 Sedan 2017 Coupe 2017 Coupe 2017 Truck 2018 SUV 2018 Coupe 2018 SUV 2017 Sedan 2018 Van 2018 Sedan 2017 Truck 2018 Van 2018 Sedan 2017 Truck 2017 SUV 2017 Van 2017 Truck 2018 Truck 2017 SUV 2018 Sedan 2018 Coupe 2018 Van 2017 SUV 2017 Sedan 2018 Van 2017 Van 2018 Coupe 2018 Truck 2018 Sedan 2018 Coupe 2017 Sedan 2018 Sedan 2017 Sedan 2018 Coupe 2018 SUV 2018 SUV 2017 Truck 2017 Van 2018 Truck Special Notes Color Red Red White White Gray Blue White Red Red Black Black Black Red White Blue White Red Black Black Black Red Gray Blue White Black Black Red Gray Black White Gray Red Gray Black Gray Blue Red White Blue GPS Yes No No No Yes No No Yes Yes Yes Yes No No No No No No No Yes No Yes Yes No No Yes No Yes Yes Yes No Yes No No Yes Yes No Yes No No Inventory Arrival Date 6/18/2017 11/16/2017 12/23/2017 10/9/2017 9/4/2017 3/15/2017 2/20/2017 2/8/2017 7/15/2017 1/21/2017 10/3/2017 8/25/2017 8/7/2017 12/10/2017 4/4/2017 5/24/2017 8/21/2017 3/4/2017 7/25/2017 1/20/2017 6/14/2017 9/16/2017 7/23/2017 4/18/2017 6/17/2017 2/13/2017 2/27/2017 1/1/2017 8/27/2017 7/4/2017 12/20/2017 5/16/2017 9/11/2017 10/14/2017 1/20/2017 9/18/2017 6/19/2017 10/5/2017 3/28/2017 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ MSRP 24,989 35,903 23,623 22,848 19,841 24,463 30,946 19,918 34,718 17,511 24,452 37,516 33,695 26,057 26,534 31,193 21,916 17,702 35,004 26,548 33,197 31,443 28,285 26,751 38,433 20,257 31,604 31,686 27,818 26,781 21,504 38,427 28,706 21,781 27,162 28,167 28,967 32,796 20,012 23618128 36978661 55320488 99224221 17269254 45696089 65984002 49731234 25856260 35306925 90474544 59107441 42211347 97796485 98756792 12040895 63178024 91057251 62970930 17516090 52371431 82465301 61875614 42927981 20521913 40912916 64963543 41653457 90628577 32669604 97866097 11980056 55762421 51408968 59246631 98405937 69471806 56271906 37202161 14555905 49563786 76930446 84165525 82777905 93202466 2017 SUV 2018 Van 2018 Sedan 2017 SUV 2018 SUV 2018 Coupe 2018 Van 2017 Coupe 2018 Sedan 2018 Van 2017 SUV 2017 Coupe 2017 SUV 2017 Van 2018 Coupe 2018 Sedan 2018 Sedan 2017 SUV 2017 SUV 2018 Sedan 2017 Van 2017 Truck 2017 Sedan 2018 Truck 2017 Van 2018 Van 2018 Sedan 2018 Van 2018 Truck 2017 Van 2017 SUV 2017 Sedan 2017 Van 2017 Van 2017 Van 2017 Truck 2017 Van 2017 Sedan 2017 Sedan 2017 Coupe 2017 Coupe 2017 Van 2018 Sedan 2017 SUV 2018 Truck White Black Blue Blue Gray Blue Red Gray Black Black Red Blue Gray Black Gray Gray Gray Blue Blue Black Black Red Blue White Red Black Gray Gray Red Blue Black Black Red Blue Blue White Black Blue Gray Red Red Blue Gray Red Red Yes Yes Yes No No No Yes Yes Yes No Yes Yes No Yes Yes Yes Yes No No Yes No Yes No Yes No No No Yes Yes Yes No Yes No Yes No No Yes Yes No Yes Yes No No Yes Yes 2/4/2017 10/13/2017 5/12/2017 9/14/2017 1/20/2017 3/19/2017 7/13/2017 11/13/2017 11/5/2017 12/25/2017 11/26/2017 2/19/2017 9/24/2017 8/5/2017 4/3/2017 5/18/2017 3/7/2017 4/11/2017 10/17/2017 7/15/2017 10/23/2017 6/26/2017 3/22/2017 7/19/2017 3/21/2017 4/6/2017 3/15/2017 10/21/2017 12/2/2017 10/3/2017 10/5/2017 8/17/2017 5/18/2017 7/24/2017 3/11/2017 2/2/2017 2/8/2017 3/21/2017 3/27/2017 8/7/2017 12/18/2017 8/17/2017 10/2/2017 5/1/2017 7/18/2017 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 23,887 18,590 21,929 32,739 29,892 32,112 34,056 26,850 24,985 35,786 35,050 30,824 31,318 34,685 19,429 37,251 37,141 22,578 25,089 29,530 17,548 35,424 17,693 19,395 36,257 27,532 27,689 27,969 38,741 31,650 36,630 31,680 29,702 22,044 26,665 30,634 25,270 36,942 25,686 29,529 20,383 21,864 33,815 32,004 26,617 82651594 92661838 32628585 88012557 23065380 34882375 71948085 76667127 29641015 88529276 93453443 29176040 91838899 43022131 69653757 46577306 57079492 64516223 59704786 45887767 39030583 60521130 21808817 67843106 90110725 42369843 53179607 14088513 65457354 52165978 58996126 66501348 26287307 66751469 92750526 87639821 97458184 74258662 28391767 98151678 10207259 96152231 15121971 81878365 63464719 2017 Truck 2018 Sedan 2017 Truck 2017 Coupe 2018 Sedan 2018 Truck 2017 Truck 2018 SUV 2017 Sedan 2018 Van 2018 Coupe 2018 Van 2018 Sedan 2018 SUV 2017 Sedan 2018 Truck 2017 Truck 2018 Coupe 2018 SUV 2018 Truck 2018 Van 2018 Truck 2018 Van 2018 Truck 2018 Truck 2018 Coupe 2017 Van 2017 Truck 2017 Sedan 2017 Truck 2018 Truck 2018 SUV 2017 SUV 2017 Truck 2017 SUV 2017 Van 2017 Truck 2017 Truck 2017 Sedan 2018 Van 2017 Coupe 2018 Sedan 2018 Sedan 2018 Van 2018 Coupe Gray Black Blue Black Blue Blue Gray Black Black White Black Black Black Gray Red Blue Black Red Blue Black Black Blue Blue Red White Black White Gray Black White Blue Black White Gray Blue White Black Red Black Black Black Blue Gray Red Red No No No Yes No No No Yes Yes Yes Yes No No Yes No No No No No Yes Yes Yes Yes No No No Yes Yes Yes Yes No No No No No Yes Yes Yes Yes No Yes Yes No No Yes 8/1/2017 5/15/2017 2/9/2017 9/19/2017 7/20/2017 4/14/2017 11/12/2017 7/20/2017 9/1/2017 12/3/2017 2/20/2017 4/23/2017 12/5/2017 11/1/2017 12/18/2017 3/10/2017 12/16/2017 11/8/2017 3/25/2017 3/12/2017 12/23/2017 10/12/2017 3/17/2017 1/20/2017 9/12/2017 5/24/2017 1/27/2017 4/25/2017 4/1/2017 10/19/2017 5/5/2017 5/5/2017 9/25/2017 1/18/2017 5/25/2017 10/19/2017 5/23/2017 9/8/2017 5/9/2017 9/11/2017 4/25/2017 7/3/2017 5/20/2017 6/23/2017 11/16/2017 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 25,393 29,425 19,317 23,622 35,497 30,717 28,047 27,451 18,591 32,858 24,891 23,880 19,833 38,424 26,241 24,576 31,793 26,265 20,208 28,321 26,010 24,745 30,961 27,458 26,414 26,349 18,964 19,116 34,901 35,686 25,637 34,750 23,640 21,192 24,465 35,418 28,277 19,961 30,849 34,827 17,708 38,807 37,015 19,059 29,138 70288423 44064717 71172703 97803854 94196081 26550452 65993771 41035643 36244864 29072762 35592273 84308171 46735904 86833035 94613566 32012421 23686693 19154372 85876030 56014307 41915495 76084317 39756872 15828276 95446865 41680075 85247202 85270567 44261695 13401110 54326243 78873196 94758156 41514423 17158514 50853969 26517865 22614316 51150031 41517770 50846523 78715685 30269334 75403313 60930031 2017 SUV 2017 Sedan 2017 Sedan 2017 Truck 2017 Sedan 2018 Van 2018 Coupe 2018 Van 2017 SUV 2017 Coupe 2017 Truck 2017 Van 2017 Sedan 2017 Sedan 2017 Truck 2018 Truck 2018 Sedan 2017 SUV 2017 SUV 2018 Van 2017 Sedan 2017 Sedan 2018 SUV 2017 SUV 2017 Sedan 2018 Truck 2017 Sedan 2017 Sedan 2018 Sedan 2018 Van 2018 Coupe 2017 Sedan 2018 Sedan 2018 Van 2018 Van 2018 Van 2018 Truck 2018 Coupe 2017 SUV 2017 Truck 2017 SUV 2017 Sedan 2017 SUV 2017 Van 2017 Van Red Red Black Red Red Black Blue Gray Gray White Red Gray White Blue Red Black White Blue Blue Black White White Black Red Red White Black White Red Blue White Black Gray Gray Red Black Blue Red Red Red Red Black Black Gray Blue No Yes Yes No Yes No Yes Yes No Yes Yes Yes No No No No Yes Yes Yes Yes No No Yes No No Yes No No Yes No No Yes Yes Yes Yes Yes No Yes Yes Yes No No No No Yes 3/27/2017 11/22/2017 9/12/2017 12/2/2017 6/1/2017 12/19/2017 6/22/2017 5/28/2017 5/11/2017 1/21/2017 9/11/2017 8/22/2017 6/25/2017 10/8/2017 10/25/2017 10/20/2017 2/17/2017 6/22/2017 3/16/2017 11/24/2017 2/1/2017 6/22/2017 12/11/2017 6/18/2017 10/12/2017 11/28/2017 8/18/2017 8/18/2017 1/23/2017 3/17/2017 11/9/2017 11/6/2017 3/7/2017 5/13/2017 12/20/2017 7/20/2017 11/28/2017 1/13/2017 11/23/2017 7/5/2017 2/9/2017 4/9/2017 8/28/2017 5/19/2017 8/5/2017 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 27,244 26,047 23,034 23,549 23,047 30,991 30,742 36,951 19,848 29,605 20,733 21,174 23,287 36,845 18,292 33,282 36,340 34,290 38,021 25,125 35,735 24,164 27,284 36,980 21,088 24,914 36,868 27,771 24,559 24,740 25,046 23,743 35,272 34,955 21,540 35,023 35,962 30,601 20,657 33,522 24,489 35,680 27,075 24,458 17,949 54000316 48675320 80373348 25741165 42854612 70960387 40183159 78100932 46305403 16690439 37796166 96934792 12022316 57986940 45890165 57964864 19098204 62133486 67665764 58798838 84816885 22664474 76654749 65104725 33292064 34042614 2017 Sedan 2018 Sedan 2017 Van 2017 SUV 2017 SUV 2018 Truck 2017 Coupe 2017 Van 2017 Van 2018 Sedan 2018 Van 2018 Sedan 2018 Van 2018 Van 2017 Coupe 2018 Truck 2018 Coupe 2018 Sedan 2017 Truck 2017 Truck 2017 Truck 2018 Van 2017 Van 2017 SUV 2018 Truck 2018 Sedan Red Black Gray Black Red Red Red Black Blue Black Blue Red Gray Black Red Black Blue Gray Gray Gray Red Black Red White Red Black Yes Yes Yes No Yes No No Yes No No No Yes Yes No Yes Yes No No Yes Yes No No Yes No No No 8/6/2017 6/17/2017 8/3/2017 11/21/2017 10/13/2017 8/4/2017 6/6/2017 8/10/2017 11/4/2017 7/21/2017 2/17/2017 2/3/2017 6/25/2017 6/1/2017 5/28/2017 1/9/2017 12/28/2017 2/27/2017 12/24/2017 1/1/2017 7/17/2017 8/13/2017 6/26/2017 10/6/2017 3/11/2017 8/16/2017 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 35,020 37,195 37,985 27,797 19,933 29,200 36,035 32,235 28,331 36,750 38,491 30,544 31,515 31,085 28,352 30,512 27,565 17,915 26,309 32,095 24,873 27,395 29,108 24,448 32,800 24,205 VIN # Days on Lot Special Notes Year Count Overall VIN # Type Color Min MSRP Average Days On Lot Color:Red w/GPS Year Type Combined Color GPS Search Criteria Arrival Date MSRP Days on Lot Special Notes Days on Lot Special Notes Summary Data Max MSRP GPS Ave Days on Lot Search Results Arrival Date MSRP Input Output Payment: APR: Years: Pmts per Year: Loan Amount: Periodic Rate: # of Payments: Beginning Balance Payment # 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 Monthly Payment Interest Paid Principal Payment Ending Balance 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60