CGS2518 EXAM 1, PT 2, EXCEL PORTION
Enter a lookup function to display the interest rate of the loan. The function should
reference the borrowers credit rating and the table array in range A15:C18. Include
the range_lookup argument to ensure an exact match. - answer-
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Actual example)
=VLOOKUP(C7, A15:C18, 3, FALSE)
*only use false if he asks for an EXACT match, if he does not mention it, just do not
include the range lookup
Insert a lookup function that uses the credit rating to determine the minimum down
payment based on the table array A15:C18. Include the range lookup argument to
ensure an exact match. Multiply the function result by the negotiated cost of the
vehicle. - answer-=VLOOKUP(lookup_value, table_array, col_index_num,
[range_lookup])
Actual example)
=VLOOKUP(C7, A15:C18, 2, FALSE)*C4
He gives most of the information in the instruction, just match up the credit rating
cell, table array thats given. column index number is the number of the column that
the down payment was in, which was B, or 2. FALSE for exact match, and multiple
by negotiated cost of the vehicle which is in cell C4
Multiply the negotiated cost of the vehicle by the sales tax rate to determine the
sales tax - answer-Literally just used =C4*C10, which was the negotiated cost
number times the sales tax rate number
in cell F10, add the minimum down payment located in cell C5 with the additional
down payment in F5 to determine the total down payment - answer-=SUM(F5, C5)
In cell F11, enter a formula to determine the difference between the negotiated cost
of of the vehicle and the total down payment - answer-=C4-F10
C4= negotiated cost of the vehicle and F10 is total down payment
In cell F12, use the PMT function to determine the periodic loan payment. format the
results to appear as a *positive number* - answer-PMT function calculates the
payment for a loan based on constant payments and a constant interest rate.
=PMT(rate, nper, pv, [fv], [type])
rate= interest rate for the loan.
= APR based on credit rating/ the number of payments paid per year
nper= the total number of payments for the loan
, pv= present value, or total amount that a series of future payments is worth now
fv= optional
EXAMPLE)
=PMT(F4/12, C11*C12, F11)*-1
^F4/12 = APR percent based on credit rating/ number of payments per year
C11*C12 = Term of loan in years (5) * the number of payments per year.
F11= "the amount of the loan"
To make it as a positive number, multiple the parentheses by a -1
enter a formula to calculate the monthly sales tax based on the total sales tax
located in cell F6, and the total number of payments (C11*C12) -
answer-=F6/(C11*C12)
=Sales Tax/(Term of loan in years * # of payments per year)
enter a formula to determine the total monthly payment by adding the monthly
payment in cell F12 and the monthly sales tax in cell F13. - answer-=F12+F13
create a formula that calculate the Profit Margin % and copy that formula down to
E28. Format the values to show the percent value with zero decimal places (i.e.24%)
- answer-profit margin = (price-cost)/price
EXAMPLE) = (D4-C4)/D4
=(selling price to dealer - mfg. cost per vehicle) / selling price to dealer
In cell F4, create a formula that calculates the Total Cost and copy that formula down
to F28. - answer-total cost = (cost * volume)
EXAMPLE) =(C4*B4)
C4= cost per vehicle, B4= sales volume
In cell G4, create a formula that calculates the Total Sales to Dealer using the logic
above and copy that formula down to G28 - answer-total sales = (sales volume *
selling price)
(amount * price)
For % of Total Volume in column H, first calculate the total volume of all cars sold in
cell B30. Then in cell H4, for that model, find the % of its volume as compared to the
Total Volume now located in B30. For full credit, you should write your formula in H4
using proper cell referencing (hint: Mixed referencing puts the $ sign in front of either
Enter a lookup function to display the interest rate of the loan. The function should
reference the borrowers credit rating and the table array in range A15:C18. Include
the range_lookup argument to ensure an exact match. - answer-
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Actual example)
=VLOOKUP(C7, A15:C18, 3, FALSE)
*only use false if he asks for an EXACT match, if he does not mention it, just do not
include the range lookup
Insert a lookup function that uses the credit rating to determine the minimum down
payment based on the table array A15:C18. Include the range lookup argument to
ensure an exact match. Multiply the function result by the negotiated cost of the
vehicle. - answer-=VLOOKUP(lookup_value, table_array, col_index_num,
[range_lookup])
Actual example)
=VLOOKUP(C7, A15:C18, 2, FALSE)*C4
He gives most of the information in the instruction, just match up the credit rating
cell, table array thats given. column index number is the number of the column that
the down payment was in, which was B, or 2. FALSE for exact match, and multiple
by negotiated cost of the vehicle which is in cell C4
Multiply the negotiated cost of the vehicle by the sales tax rate to determine the
sales tax - answer-Literally just used =C4*C10, which was the negotiated cost
number times the sales tax rate number
in cell F10, add the minimum down payment located in cell C5 with the additional
down payment in F5 to determine the total down payment - answer-=SUM(F5, C5)
In cell F11, enter a formula to determine the difference between the negotiated cost
of of the vehicle and the total down payment - answer-=C4-F10
C4= negotiated cost of the vehicle and F10 is total down payment
In cell F12, use the PMT function to determine the periodic loan payment. format the
results to appear as a *positive number* - answer-PMT function calculates the
payment for a loan based on constant payments and a constant interest rate.
=PMT(rate, nper, pv, [fv], [type])
rate= interest rate for the loan.
= APR based on credit rating/ the number of payments paid per year
nper= the total number of payments for the loan
, pv= present value, or total amount that a series of future payments is worth now
fv= optional
EXAMPLE)
=PMT(F4/12, C11*C12, F11)*-1
^F4/12 = APR percent based on credit rating/ number of payments per year
C11*C12 = Term of loan in years (5) * the number of payments per year.
F11= "the amount of the loan"
To make it as a positive number, multiple the parentheses by a -1
enter a formula to calculate the monthly sales tax based on the total sales tax
located in cell F6, and the total number of payments (C11*C12) -
answer-=F6/(C11*C12)
=Sales Tax/(Term of loan in years * # of payments per year)
enter a formula to determine the total monthly payment by adding the monthly
payment in cell F12 and the monthly sales tax in cell F13. - answer-=F12+F13
create a formula that calculate the Profit Margin % and copy that formula down to
E28. Format the values to show the percent value with zero decimal places (i.e.24%)
- answer-profit margin = (price-cost)/price
EXAMPLE) = (D4-C4)/D4
=(selling price to dealer - mfg. cost per vehicle) / selling price to dealer
In cell F4, create a formula that calculates the Total Cost and copy that formula down
to F28. - answer-total cost = (cost * volume)
EXAMPLE) =(C4*B4)
C4= cost per vehicle, B4= sales volume
In cell G4, create a formula that calculates the Total Sales to Dealer using the logic
above and copy that formula down to G28 - answer-total sales = (sales volume *
selling price)
(amount * price)
For % of Total Volume in column H, first calculate the total volume of all cars sold in
cell B30. Then in cell H4, for that model, find the % of its volume as compared to the
Total Volume now located in B30. For full credit, you should write your formula in H4
using proper cell referencing (hint: Mixed referencing puts the $ sign in front of either
