WGU C268 SPREADSHEETS NEW EXAM WITH COMPLETE VERIFIED
SOLUTIONS 100% CORRECT!!
2025-2026 NEW UPDATE
Periodic payment for a fixed-rate loan based on periodic fixed payments and a fixed
term of payment.
=PMT(rate,nper,pv)
Interest rate (divided by 12 months), the number of payments to be made to pay off the
loan, the original loan amount.
HLOOKUP
A lookup function that searches horizontally across the top row of the lookup table and
retrieves the value in the column you specify.
=HLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
Lookup information, the location of the reference table, the column in the table that
contains the information that is returned, true if an approximate match is OK and FALSE
if an approximate match is not okay.
In cell C15, calculate the amount of the payment for the loan using the cells containing
the appropriate loan information as arguments for the function you use. The "Payment"
column in cells C20-C67 are filled with the payment amount in cell C15. [34 Points] -
Answer =PMT(Rate/#months of term,LoanAmt)
=PMT(C13/12,C12,C11)
In cell D20 calculate the amount of interest for period 1. Balance in period 0, cell F19 to
be multiplied by loan interest rate, cell C13 divided by 12. The division by 12 converts
the interest rate to a monthly interest rate. This is a formula that can be reused. For any
period the interest is equal to the monthly interest rate times the previous balance. -
Answer =F19*$C$13/12
,In cell E20 calculate the amount of principal for period 1. Remember that principal is
equal to the difference between the amount paid for period 1 (cell C20) and interest for
period 1 (cell D20). Build the formula such that it will be able to be dragged/copied down
to fill in the rest of the "principal" column of the amortization table. Answer =C20-D20
In cell F20, calculate the balance for period 1. Balance is the difference between
Balance for Period 0, cell F19 and Principal Amount for Period 1 cell E20 This formula
can be reused. Once the balance in any one period is calculated by subtracting the
principal amount for that period from the balance in the previous period, that result can
be used to calculate the balance for the next period. Answer =F19-E20
In cell G12, calculate the total amount paid. The total amount paid should be the
payment amount in cell C15 multiplied by the term of the loan in cell C12. - Answer
=C15*C12
In cell G13, calculate the total interest paid. Total interest paid should be the total of all
interest paid in the "Interest" column of the amortization table. - Answer =SUM(D20:D67)
Check that the calculation for total interest at the bottom of the amortization table is
correct. The total interest paid also equals the total amount paid during the duration of
the loan minus the amount of the original loan. In cell G14, enter a formula to calculate
the total amount paid minus the original loan amount. Note the negative sign associated
with the original loan amount. The value should be equal to the total interest computed
by using the amortisation table. - Answer =G12-ABS(C11)
Assume you have made the first 36 payments on your loan. You want to trade the car in
for a new car. You think 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. - Answer No
Use the HLOOKUP function to populate 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. - Answer
=HLOOKUP(D16,$E$11:$H$12,2,FALSE)
, Use the AND function to complete the "Time Bonus?" column of table 1. There is a time
bonus for an employee when the project's "Hours Worked" are less than the "Estimated
Hours" and if the work "Quality" is greater than 1. - Answer =AND(E16<C16,H16>1)
Use the OR function to populate the "Outcome Bonus?" column of table 1. A worker gets
an outcome bonus if the job's difficulty is greater than 3 or if the quality of his/her work is
equal to 3. - Answer =OR(G16>3,H16=3)
Use the IF function to populate table 1's "Time Bonus $" column. If a worker gets a time
bonus, i.e., the "Time Bonus?" column is TRUE for that worker, then "Time Bonus $" is
"Job Pay" for that project times the bonus percentage in cell M11. Otherwise "Time
Bonus $" is 0. - Answer =IF(I16,K16*$M$11,0)
Use the IF function to populate the "Outcome Bonus $" column of table 1. A consultant
has an outcome bonus in column "Outcome Bonus? is TRUE, then "Outcome Bonus $" is
"Job Pay" times the outcome bonus percentage in cell M12. Otherwise "Outcome Bonus
$" is 0. - Answer =IF(J16,K16*$M$12,0)
Use the IF function to populate table 1's "Comments" column. If a project's both "Hours
Worked" are less than or equal to the "Estimated Hours" for that project and the
assessed "Quality" of that project is greater than 1 then display "Good Job". If the
"Hours Worked" on a project are greater than the "Estimated Hours" for that project
then display "Too Much Time", otherwise display "Poor Quality." Answer
=IF(AND(E16<=C16,H16>1),"Good Job",IF(E16>C16,"Too Much time","Poor Quality"))
Use the VLOOKUP function to fill in the "Employee" column of table 2. Use "Job ID" from
table 2 as your lookup_value(s) and table 1 as the reference table. - Answer
=VLOOKUP(B40,$B$16:$O$35,3,FALSE)
Use the VLOOKUP function to fill in the "Difficulty" column of table 2. Again, use "Job
ID" from table 2 as the lookup_value(s) and table 1 as the reference table. - Answer
=VLOOKUP(B40,$B$16:$O$35,6,FALSE)
Fill the column " # of Jobs" in table 3 with using COUNTIF function using table 1 for your
SOLUTIONS 100% CORRECT!!
2025-2026 NEW UPDATE
Periodic payment for a fixed-rate loan based on periodic fixed payments and a fixed
term of payment.
=PMT(rate,nper,pv)
Interest rate (divided by 12 months), the number of payments to be made to pay off the
loan, the original loan amount.
HLOOKUP
A lookup function that searches horizontally across the top row of the lookup table and
retrieves the value in the column you specify.
=HLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
Lookup information, the location of the reference table, the column in the table that
contains the information that is returned, true if an approximate match is OK and FALSE
if an approximate match is not okay.
In cell C15, calculate the amount of the payment for the loan using the cells containing
the appropriate loan information as arguments for the function you use. The "Payment"
column in cells C20-C67 are filled with the payment amount in cell C15. [34 Points] -
Answer =PMT(Rate/#months of term,LoanAmt)
=PMT(C13/12,C12,C11)
In cell D20 calculate the amount of interest for period 1. Balance in period 0, cell F19 to
be multiplied by loan interest rate, cell C13 divided by 12. The division by 12 converts
the interest rate to a monthly interest rate. This is a formula that can be reused. For any
period the interest is equal to the monthly interest rate times the previous balance. -
Answer =F19*$C$13/12
,In cell E20 calculate the amount of principal for period 1. Remember that principal is
equal to the difference between the amount paid for period 1 (cell C20) and interest for
period 1 (cell D20). Build the formula such that it will be able to be dragged/copied down
to fill in the rest of the "principal" column of the amortization table. Answer =C20-D20
In cell F20, calculate the balance for period 1. Balance is the difference between
Balance for Period 0, cell F19 and Principal Amount for Period 1 cell E20 This formula
can be reused. Once the balance in any one period is calculated by subtracting the
principal amount for that period from the balance in the previous period, that result can
be used to calculate the balance for the next period. Answer =F19-E20
In cell G12, calculate the total amount paid. The total amount paid should be the
payment amount in cell C15 multiplied by the term of the loan in cell C12. - Answer
=C15*C12
In cell G13, calculate the total interest paid. Total interest paid should be the total of all
interest paid in the "Interest" column of the amortization table. - Answer =SUM(D20:D67)
Check that the calculation for total interest at the bottom of the amortization table is
correct. The total interest paid also equals the total amount paid during the duration of
the loan minus the amount of the original loan. In cell G14, enter a formula to calculate
the total amount paid minus the original loan amount. Note the negative sign associated
with the original loan amount. The value should be equal to the total interest computed
by using the amortisation table. - Answer =G12-ABS(C11)
Assume you have made the first 36 payments on your loan. You want to trade the car in
for a new car. You think 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. - Answer No
Use the HLOOKUP function to populate 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. - Answer
=HLOOKUP(D16,$E$11:$H$12,2,FALSE)
, Use the AND function to complete the "Time Bonus?" column of table 1. There is a time
bonus for an employee when the project's "Hours Worked" are less than the "Estimated
Hours" and if the work "Quality" is greater than 1. - Answer =AND(E16<C16,H16>1)
Use the OR function to populate the "Outcome Bonus?" column of table 1. A worker gets
an outcome bonus if the job's difficulty is greater than 3 or if the quality of his/her work is
equal to 3. - Answer =OR(G16>3,H16=3)
Use the IF function to populate table 1's "Time Bonus $" column. If a worker gets a time
bonus, i.e., the "Time Bonus?" column is TRUE for that worker, then "Time Bonus $" is
"Job Pay" for that project times the bonus percentage in cell M11. Otherwise "Time
Bonus $" is 0. - Answer =IF(I16,K16*$M$11,0)
Use the IF function to populate the "Outcome Bonus $" column of table 1. A consultant
has an outcome bonus in column "Outcome Bonus? is TRUE, then "Outcome Bonus $" is
"Job Pay" times the outcome bonus percentage in cell M12. Otherwise "Outcome Bonus
$" is 0. - Answer =IF(J16,K16*$M$12,0)
Use the IF function to populate table 1's "Comments" column. If a project's both "Hours
Worked" are less than or equal to the "Estimated Hours" for that project and the
assessed "Quality" of that project is greater than 1 then display "Good Job". If the
"Hours Worked" on a project are greater than the "Estimated Hours" for that project
then display "Too Much Time", otherwise display "Poor Quality." Answer
=IF(AND(E16<=C16,H16>1),"Good Job",IF(E16>C16,"Too Much time","Poor Quality"))
Use the VLOOKUP function to fill in the "Employee" column of table 2. Use "Job ID" from
table 2 as your lookup_value(s) and table 1 as the reference table. - Answer
=VLOOKUP(B40,$B$16:$O$35,3,FALSE)
Use the VLOOKUP function to fill in the "Difficulty" column of table 2. Again, use "Job
ID" from table 2 as the lookup_value(s) and table 1 as the reference table. - Answer
=VLOOKUP(B40,$B$16:$O$35,6,FALSE)
Fill the column " # of Jobs" in table 3 with using COUNTIF function using table 1 for your
