WGU C268 Study Guide for EXCEL PA Exam
Questions With Correct 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)
Use |the |OR |function |to |complete |the |"Outcome |Bonus?" |column |of |table |1. |An |employee |
earns |an |outcome |bonus |if |the |difficulty |of |a |job |is |greater |than |3 |or |if |the |quality |of |their |
work |is |equal |to |3.
=OR(G16>3,H16=3)
Questions With Correct 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)
Use |the |OR |function |to |complete |the |"Outcome |Bonus?" |column |of |table |1. |An |employee |
earns |an |outcome |bonus |if |the |difficulty |of |a |job |is |greater |than |3 |or |if |the |quality |of |their |
work |is |equal |to |3.
=OR(G16>3,H16=3)
