WGU C268 Study Guide for EXCEL PA questions with verified
|| || || || || || || || || ||
detailed answers ||
Calculate the payment amount for the loan in cell C15. Reference the cells containing the
|| || || || || || || || || || || || || || ||
appropriate loan information as the arguments for the function you use. Cells C20-C67 in
|| || || || || || || || || || || || || ||
the "Payment" column are populated with the payment amount from cell C15.
|| || || || || || || || || || ||
=PMT(C13/12,C12,C11)
Calculate, in cell D20, the interest amount for period 1 by multiplying the balance in period
|| || || || || || || || || || || || || || || ||
0 (cell F19) by the loan interest rate (cell C13) divided by 12. Dividing the interest rate by 12
|| || || || || || || || || || || || || || || || || ||
results in the monthly interest rate. This formula is reusable. The interest for a given period
|| || || || || || || || || || || || || || || ||
is always the monthly interest rate times the balance from the previous period.
|| || || || || || || || || || || || ||
f
=F19*C$13/12
Copy the Interest amount calcualtion down to complete the "interest" column of the
|| || || || || || || || || || || || ||
amortization table. ||
Paste down column . || || ||
Calculate, in cell E20, the principal amount for period 1. The principal amount is the
|| || || || || || || || || || || || || || ||
difference between the payment amount (cell C20) and the interest amount (cell D20) for
|| || || || || || || || || || || || || ||
period 1. Construct your formula in such a way that it can be reused to complete the
|| || || || || || || || || || || || || || || || ||
"principal" column of the amortization table. || || || || ||
=C20-D20
,Copy the principal amount calculation down to complete the "principal" column of the
|| || || || || || || || || || || || ||
amortization table. ||
Copy and paste down.
|| || ||
Calculate, in cell F20, the balance for period 1. The balance is the difference between the
|| || || || || || || || || || || || || || || ||
balance for period 0 (cell F19) and the principal amount for period 1 (cell E20). This
|| || || || || || || || || || || || || || || ||
formula is reusable. The balance is always calculated as the difference between the balance
|| || || || || || || || || || || || || ||
from the previous period and the principal amount for the current period.
|| || || || || || || || || || ||
=F19-E20
Copy the balance amount calculation down to complete the balance column of the
|| || || || || || || || || || || || ||
amortization table. ||
Copy and paste down.
|| || ||
Calculate, in cell G12, the total amount paid by multiplying the payment amount (cell C15)
|| || || || || || || || || || || || || || ||
by the term of the loan (cell C12).
|| || || || || || ||
=C15*C12
Calculate the total interest paid in cell G13. The total interest paid is the sum of all interest
|| || || || || || || || || || || || || || || || || ||
paid in the "Interest" column of the amortization table.
|| || || || || || || ||
=SUM(D20:D67)
, Check to see if the total interest calculation in the amortization table is correct. The total
|| || || || || || || || || || || || || || || ||
interest paid is also equal to the difference between the total amount paid over the course of
|| || || || || || || || || || || || || || || ||
the loan and the original loan amount. Insert a formula into cell G14 to calculate the
|| || || || || || || || || || || || || || || || ||
difference between the total amount paid and the original loan amount. Notice the negative
|| || || || || || || || || || || || || ||
sign associated with the original loan amount. This value should equal the total interest
|| || || || || || || || || || || || || ||
calculated using the amortization table. || || || ||
=G12+C11
Assume you have made the first 36 payments on your loan. You want to trade the car in for
|| || || || || || || || || || || || || || || || || || ||
a new car. You believe that you can sell your car for $4000. Will this cover the balance
|| || || || || || || || || || || || || || || || || ||
remaining on the car in period 36? Answer either "Yes" or "No" in cell G15 from the drop-
|| || || || || || || || || || || || || || || || ||
down menu. ||
No
Use the HLOOKUP function to complete the "Hourly Wage" column of table 1. Use the
|| || || || || || || || || || || || || || ||
"Employee" column of table 1 as the lookup_value and the "Employee Wage Information"
|| || || || || || || || || || || || ||
above table 1 as your reference table.
|| || || || || ||
=HLOOKUP(D16, E$11:H$12, 2,False) || ||
Use the AND function to complete the "Time Bonus?" column of table 1. An employee
|| || || || || || || || || || || || || || ||
earns a time bonus if the project's "Hours Worked" are fewer than the "Estimated Hours"
|| || || || || || || || || || || || || || ||
and if the work "Quality" is greater than 1.
|| || || || || || || ||
=AND(E16<C16,H16>1)
|| || || || || || || || || ||
detailed answers ||
Calculate the payment amount for the loan in cell C15. Reference the cells containing the
|| || || || || || || || || || || || || || ||
appropriate loan information as the arguments for the function you use. Cells C20-C67 in
|| || || || || || || || || || || || || ||
the "Payment" column are populated with the payment amount from cell C15.
|| || || || || || || || || || ||
=PMT(C13/12,C12,C11)
Calculate, in cell D20, the interest amount for period 1 by multiplying the balance in period
|| || || || || || || || || || || || || || || ||
0 (cell F19) by the loan interest rate (cell C13) divided by 12. Dividing the interest rate by 12
|| || || || || || || || || || || || || || || || || ||
results in the monthly interest rate. This formula is reusable. The interest for a given period
|| || || || || || || || || || || || || || || ||
is always the monthly interest rate times the balance from the previous period.
|| || || || || || || || || || || || ||
f
=F19*C$13/12
Copy the Interest amount calcualtion down to complete the "interest" column of the
|| || || || || || || || || || || || ||
amortization table. ||
Paste down column . || || ||
Calculate, in cell E20, the principal amount for period 1. The principal amount is the
|| || || || || || || || || || || || || || ||
difference between the payment amount (cell C20) and the interest amount (cell D20) for
|| || || || || || || || || || || || || ||
period 1. Construct your formula in such a way that it can be reused to complete the
|| || || || || || || || || || || || || || || || ||
"principal" column of the amortization table. || || || || ||
=C20-D20
,Copy the principal amount calculation down to complete the "principal" column of the
|| || || || || || || || || || || || ||
amortization table. ||
Copy and paste down.
|| || ||
Calculate, in cell F20, the balance for period 1. The balance is the difference between the
|| || || || || || || || || || || || || || || ||
balance for period 0 (cell F19) and the principal amount for period 1 (cell E20). This
|| || || || || || || || || || || || || || || ||
formula is reusable. The balance is always calculated as the difference between the balance
|| || || || || || || || || || || || || ||
from the previous period and the principal amount for the current period.
|| || || || || || || || || || ||
=F19-E20
Copy the balance amount calculation down to complete the balance column of the
|| || || || || || || || || || || || ||
amortization table. ||
Copy and paste down.
|| || ||
Calculate, in cell G12, the total amount paid by multiplying the payment amount (cell C15)
|| || || || || || || || || || || || || || ||
by the term of the loan (cell C12).
|| || || || || || ||
=C15*C12
Calculate the total interest paid in cell G13. The total interest paid is the sum of all interest
|| || || || || || || || || || || || || || || || || ||
paid in the "Interest" column of the amortization table.
|| || || || || || || ||
=SUM(D20:D67)
, Check to see if the total interest calculation in the amortization table is correct. The total
|| || || || || || || || || || || || || || || ||
interest paid is also equal to the difference between the total amount paid over the course of
|| || || || || || || || || || || || || || || ||
the loan and the original loan amount. Insert a formula into cell G14 to calculate the
|| || || || || || || || || || || || || || || || ||
difference between the total amount paid and the original loan amount. Notice the negative
|| || || || || || || || || || || || || ||
sign associated with the original loan amount. This value should equal the total interest
|| || || || || || || || || || || || || ||
calculated using the amortization table. || || || ||
=G12+C11
Assume you have made the first 36 payments on your loan. You want to trade the car in for
|| || || || || || || || || || || || || || || || || || ||
a new car. You believe that you can sell your car for $4000. Will this cover the balance
|| || || || || || || || || || || || || || || || || ||
remaining on the car in period 36? Answer either "Yes" or "No" in cell G15 from the drop-
|| || || || || || || || || || || || || || || || ||
down menu. ||
No
Use the HLOOKUP function to complete the "Hourly Wage" column of table 1. Use the
|| || || || || || || || || || || || || || ||
"Employee" column of table 1 as the lookup_value and the "Employee Wage Information"
|| || || || || || || || || || || || ||
above table 1 as your reference table.
|| || || || || ||
=HLOOKUP(D16, E$11:H$12, 2,False) || ||
Use the AND function to complete the "Time Bonus?" column of table 1. An employee
|| || || || || || || || || || || || || || ||
earns a time bonus if the project's "Hours Worked" are fewer than the "Estimated Hours"
|| || || || || || || || || || || || || || ||
and if the work "Quality" is greater than 1.
|| || || || || || || ||
=AND(E16<C16,H16>1)
