WGUC268StudyGuideforEXCELPA
QuestionsWithCorrectAnswersLatest
100%Verified2024
CalculatethepaymentamountfortheloanincellC15.Referencethecells
containingtheappropriateloaninformationastheargumentsforthefunctionyou
use.CellsC20-C67inthe"Payment"columnarepopulatedwiththepayment
amountfromcellC15.[34Points]-CorrectAnswer-=PMT(C13/12,C12,C11)
Calculate,incellD20,theinterestamountforperiod1bymultiplyingthebalancein
period0(cellF19)bytheloaninterestrate(cellC13)dividedby12.Dividingthe
interestrateby12resultsinthemonthlyinterestrate.Thisformulaisreusable.The
interestforagivenperiodisalwaysthemonthlyinterestratetimesthebalancefrom
thepreviousperiod.-CorrectAnswer-=F19*$C$13/12
Calculate,incellE20,theprincipalamountforperiod1.Theprincipalamountisthe
differencebetweenthepaymentamount(cellC20)andtheinterestamount(cellD20)
forperiod1.Constructyourformulainsuchawaythatitcanbereusedtocomplete
the"principal"columnoftheamortizationtable.-CorrectAnswer-=C20-D20
Calculate,incellF20,thebalanceforperiod1.Thebalanceisthedifference
betweenthebalanceforperiod0(cellF19)andtheprincipalamountforperiod1
(cellE20).Thisformulaisreusable.Thebalanceisalwayscalculatedasthe
differencebetweenthebalancefromthepreviousperiodandtheprincipalamount
forthecurrentperiod.-CorrectAnswer-=F19-E20
Calculate,incellG12,thetotalamountpaidbymultiplyingthepaymentamount(cell
C15)bythetermoftheloan(cellC12).-CorrectAnswer-=C15*C12
CalculatethetotalinterestpaidincellG13.Thetotalinterestpaidisthesumofall
interestpaidinthe"Interest"columnoftheamortizationtable.-CorrectAnswer-
=SUM(D20:D67)
Checktoseeifthetotalinterestcalculationintheamortizationtableiscorrect.The
totalinterestpaidisalsoequaltothedifferencebetweenthetotalamountpaidover
thecourseoftheloanandtheoriginalloanamount.InsertaformulaintocellG14to
calculatethedifferencebetweenthetotalamountpaidandtheoriginalloanamount.
Noticethenegativesignassociatedwiththeoriginalloanamount.Thisvalueshould
equalthetotalinterestcalculatedusingtheamortizationtable.-CorrectAnswer-
=G12-ABS(C11)
Assumeyouhavemadethefirst36paymentsonyourloan.Youwanttotradethe
carinforanewcar.Youbelievethatyoucansellyourcarfor$4000.Willthiscover
thebalanceremainingonthecarinperiod36?Answereither"Yes"or"No"incell
G15fromthedrop-downmenu.-CorrectAnswer-No UsetheHLOOKUPfunctiontocompletethe"HourlyWage"columnoftable1.Use
the"Employee"columnoftable1asthelookup_valueandthe"EmployeeWage
Information"abovetable1asyourreferencetable.-CorrectAnswer-
=HLOOKUP(D16,$E$11:$H$12,2,FALSE)
UsetheANDfunctiontocompletethe"TimeBonus?"columnoftable1.An
employeeearnsatimebonusiftheproject's"HoursWorked"arefewerthanthe
"EstimatedHours"andifthework"Quality"isgreaterthan1.-CorrectAnswer-
=AND(E16<C16,H16>1)
UsetheIFfunctiontocompletethe"TimeBonus$"columnoftable1.Ifan
employeeearnsatimebonus(i.e.,thecorrespondingcellinthe"TimeBonus?"
columnisTRUE),then"TimeBonus$"isthe"JobPay"forthatprojecttimesthe
bonuspercentageincellM11.Otherwise"TimeBonus$"is0.-CorrectAnswer-
=IF(I16,K16*$M$11,0)
UsetheORfunctiontocompletethe"OutcomeBonus?"columnoftable1.An
employeeearnsanoutcomebonusifthedifficultyofajobisgreaterthan3orifthe
qualityoftheirworkisequalto3.-CorrectAnswer-=OR(G16>3,H16=3)
UsetheIFfunctiontocompletethe"OutcomeBonus$"columnoftable1.Ifan
employeeearnsanoutcomebonus(i.e.,thecorrespondingcellinthe"Outcome
Bonus?"columnisTRUE),then"OutcomeBonus$"isthe"JobPay"forthatproject
timestheoutcomebonuspercentageincellM12;otherwise,"OutcomeBonus$"is0.
-CorrectAnswer-=IF(J16,K16*$M$12,0)
UsetheIFfunctiontocompletethe"Comments"columnoftable1.Display"Good
Job"ifboththe"HoursWorked"arelessthanorequaltothe"EstimatedHours"fora
projectandtheassessed"Quality"ofthatprojectisgreaterthan1.Display"Too
MuchTime"ifthe"HoursWorked"onaprojectexceedthe"EstimatedHours"forthat
project;otherwise,display"PoorQuality."-CorrectAnswer-
=IF(AND(E16<=C16,H16>1),"GoodJob",IF(E16>C16,"TooMuchtime","Poor
Quality"))
UsetheVLOOKUPfunctiontocompletethe"Employee"columnoftable2.Use"Job
ID"fromtable2asyourlookup_value(s)andtable1asthereferencetable.-Correct
Answer-=VLOOKUP(B40,$B$16:$O$35,3,FALSE)
UsetheVLOOKUPfunctiontocompletethe"Difficulty"columnoftable2.Again,use
"JobID"fromtable2asthelookup_value(s)andtable1asthereferencetable.-
CorrectAnswer-=VLOOKUP(B40,$B$16:$O$35,6,FALSE)
UsetheCOUNTIFfunctiontocompletethe"#ofJobs"columnintable3.Reference
theappropriatefieldintable1asyourrangeandthe"Employee"namesintable3as
yourcriteria.-CorrectAnswer-=COUNTIF($D$16:$D$35,G39)
UsetheSUMIFfunctiontocompletethe"TotalHours"columnintable3.Reference
theappropriatefieldintable1asyourrangeandthe"Employee"namesintable3as
yourcriteria.-CorrectAnswer-=SUMIF($D$16:$D$35,G39,$E$16:$E$35)
QuestionsWithCorrectAnswersLatest
100%Verified2024
CalculatethepaymentamountfortheloanincellC15.Referencethecells
containingtheappropriateloaninformationastheargumentsforthefunctionyou
use.CellsC20-C67inthe"Payment"columnarepopulatedwiththepayment
amountfromcellC15.[34Points]-CorrectAnswer-=PMT(C13/12,C12,C11)
Calculate,incellD20,theinterestamountforperiod1bymultiplyingthebalancein
period0(cellF19)bytheloaninterestrate(cellC13)dividedby12.Dividingthe
interestrateby12resultsinthemonthlyinterestrate.Thisformulaisreusable.The
interestforagivenperiodisalwaysthemonthlyinterestratetimesthebalancefrom
thepreviousperiod.-CorrectAnswer-=F19*$C$13/12
Calculate,incellE20,theprincipalamountforperiod1.Theprincipalamountisthe
differencebetweenthepaymentamount(cellC20)andtheinterestamount(cellD20)
forperiod1.Constructyourformulainsuchawaythatitcanbereusedtocomplete
the"principal"columnoftheamortizationtable.-CorrectAnswer-=C20-D20
Calculate,incellF20,thebalanceforperiod1.Thebalanceisthedifference
betweenthebalanceforperiod0(cellF19)andtheprincipalamountforperiod1
(cellE20).Thisformulaisreusable.Thebalanceisalwayscalculatedasthe
differencebetweenthebalancefromthepreviousperiodandtheprincipalamount
forthecurrentperiod.-CorrectAnswer-=F19-E20
Calculate,incellG12,thetotalamountpaidbymultiplyingthepaymentamount(cell
C15)bythetermoftheloan(cellC12).-CorrectAnswer-=C15*C12
CalculatethetotalinterestpaidincellG13.Thetotalinterestpaidisthesumofall
interestpaidinthe"Interest"columnoftheamortizationtable.-CorrectAnswer-
=SUM(D20:D67)
Checktoseeifthetotalinterestcalculationintheamortizationtableiscorrect.The
totalinterestpaidisalsoequaltothedifferencebetweenthetotalamountpaidover
thecourseoftheloanandtheoriginalloanamount.InsertaformulaintocellG14to
calculatethedifferencebetweenthetotalamountpaidandtheoriginalloanamount.
Noticethenegativesignassociatedwiththeoriginalloanamount.Thisvalueshould
equalthetotalinterestcalculatedusingtheamortizationtable.-CorrectAnswer-
=G12-ABS(C11)
Assumeyouhavemadethefirst36paymentsonyourloan.Youwanttotradethe
carinforanewcar.Youbelievethatyoucansellyourcarfor$4000.Willthiscover
thebalanceremainingonthecarinperiod36?Answereither"Yes"or"No"incell
G15fromthedrop-downmenu.-CorrectAnswer-No UsetheHLOOKUPfunctiontocompletethe"HourlyWage"columnoftable1.Use
the"Employee"columnoftable1asthelookup_valueandthe"EmployeeWage
Information"abovetable1asyourreferencetable.-CorrectAnswer-
=HLOOKUP(D16,$E$11:$H$12,2,FALSE)
UsetheANDfunctiontocompletethe"TimeBonus?"columnoftable1.An
employeeearnsatimebonusiftheproject's"HoursWorked"arefewerthanthe
"EstimatedHours"andifthework"Quality"isgreaterthan1.-CorrectAnswer-
=AND(E16<C16,H16>1)
UsetheIFfunctiontocompletethe"TimeBonus$"columnoftable1.Ifan
employeeearnsatimebonus(i.e.,thecorrespondingcellinthe"TimeBonus?"
columnisTRUE),then"TimeBonus$"isthe"JobPay"forthatprojecttimesthe
bonuspercentageincellM11.Otherwise"TimeBonus$"is0.-CorrectAnswer-
=IF(I16,K16*$M$11,0)
UsetheORfunctiontocompletethe"OutcomeBonus?"columnoftable1.An
employeeearnsanoutcomebonusifthedifficultyofajobisgreaterthan3orifthe
qualityoftheirworkisequalto3.-CorrectAnswer-=OR(G16>3,H16=3)
UsetheIFfunctiontocompletethe"OutcomeBonus$"columnoftable1.Ifan
employeeearnsanoutcomebonus(i.e.,thecorrespondingcellinthe"Outcome
Bonus?"columnisTRUE),then"OutcomeBonus$"isthe"JobPay"forthatproject
timestheoutcomebonuspercentageincellM12;otherwise,"OutcomeBonus$"is0.
-CorrectAnswer-=IF(J16,K16*$M$12,0)
UsetheIFfunctiontocompletethe"Comments"columnoftable1.Display"Good
Job"ifboththe"HoursWorked"arelessthanorequaltothe"EstimatedHours"fora
projectandtheassessed"Quality"ofthatprojectisgreaterthan1.Display"Too
MuchTime"ifthe"HoursWorked"onaprojectexceedthe"EstimatedHours"forthat
project;otherwise,display"PoorQuality."-CorrectAnswer-
=IF(AND(E16<=C16,H16>1),"GoodJob",IF(E16>C16,"TooMuchtime","Poor
Quality"))
UsetheVLOOKUPfunctiontocompletethe"Employee"columnoftable2.Use"Job
ID"fromtable2asyourlookup_value(s)andtable1asthereferencetable.-Correct
Answer-=VLOOKUP(B40,$B$16:$O$35,3,FALSE)
UsetheVLOOKUPfunctiontocompletethe"Difficulty"columnoftable2.Again,use
"JobID"fromtable2asthelookup_value(s)andtable1asthereferencetable.-
CorrectAnswer-=VLOOKUP(B40,$B$16:$O$35,6,FALSE)
UsetheCOUNTIFfunctiontocompletethe"#ofJobs"columnintable3.Reference
theappropriatefieldintable1asyourrangeandthe"Employee"namesintable3as
yourcriteria.-CorrectAnswer-=COUNTIF($D$16:$D$35,G39)
UsetheSUMIFfunctiontocompletethe"TotalHours"columnintable3.Reference
theappropriatefieldintable1asyourrangeandthe"Employee"namesintable3as
yourcriteria.-CorrectAnswer-=SUMIF($D$16:$D$35,G39,$E$16:$E$35)
