EXCEL FUNDAMENTALS - PART 2

Car Loan Practical Exercise #2

(to be completed before our next class meeting)

 

 When the national economy slows and new car inventories rise, auto manufacturers frequently offer special financing deals to try to get people to buy cars.  These "deals" usually involve rebates and/or special financing rates.

 

Assume you work for a car dealer who has asked you to use Excel to develop a computer model to assist potential buyers in analyzing the various alternatives available for purchasing a new car.  Presently the dealership is offering "incentives" of a $1,000 rebate (or discount) on the price of a new car or special financing rates.  If a buyer takes the rebate they will have to pay a higher interest rate on their loan.  If a buyer does not take the rebate they can get special, "low cost" financing rates.  These rates are summarized in Figure 1 on the following page.

 

Your job  is to modify the model you created in Part I of this assignment to accommodate these different financing options. Your main spreadsheet should look something like the one shown in Figure 2 on the following page.

 

Notice that the example shown in Figure 2 assumes this person is taking the rebate so the numbers in the "Finance Rate" row of the table are the higher interest rates. You should set up the spreadsheet so that the proper finance rates will appear on this row and be used in the various calculations depending on whether or not the buyer takes the rebate.  (Use the VLOOKUP() function in the row labeled "Finance Rate" to obtain the correct interest rates from the "Finance Rate Table".)

 

Since the only valid entries for the "Rebate" cell are $0 and $1,000 your spreadsheet should display an error message next to this cell if it contains any other value.  Use an IF() function to do this.

 

Make sure your spreadsheet is set up so that it will calculate the correct answers regardless of what purchase price, down payment and trade-in values are "input" by the user.  That is, make sure you use formulas that depend on these "input" cells wherever possible.  Your formulas should use appropriate relative and absolute cell adressing.  You should NOT "hard code" the purchase price, down payment or trade-in amount into formulas.  Similarly, if the finance rates are changed in the "Finance Rate Table" the new values should automatically be incorporated into your analysis.  So again, do not "hard code" finance rates into your formulas. Instead, build formulas that refer to the cells containing the finance rate values wherever possible.

 

WHAT TO TURN IN

 

When you are confident your model works correctly, save your worksheet in a file called Carloan_your_name.xls and be prepared to turn it in on a virus-free disk.

 

 

Figure 1

 

Figure 2

 

Skills Checklist

Excel Fundamentals - Part II

 

By the end of this session you should know:

=IF(logical_test,value_if_true,value_if_false)

 

=SUM(number1,number2,...)   or   =SUM(range1, range2, ...)

 

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

 

=HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)