AAT L3 Spreadsheets for Accounting 3 — Questions and Answers
Question 1: The PMT function in Excel calculates:
- The future value of an investment
- The periodic payment for a loan or annuity given interest rate, term, and principal (Correct answer)
- The present value of a series of cash flows
- The number of periods required to repay a loan
Correct answer: The periodic payment for a loan or annuity given interest rate, term, and principal
PMT(rate, nper, pv) calculates the fixed periodic payment needed to repay a loan (or receive from an annuity) at a given interest rate over a specified number of periods.
Question 2: When using Excel for accounting purposes, why is it important to separate input data from formulas in different areas?
- To make the spreadsheet look tidier
- To reduce file size
- To make it easier to update assumptions without changing formulas, reducing errors (Correct answer)
- To enable the data to be printed more easily
Correct answer: To make it easier to update assumptions without changing formulas, reducing errors
Separating input cells (data/assumptions) from formula cells follows good spreadsheet design practice; it makes the model easier to update, audit, and check, and reduces the risk of errors when changing inputs.
Question 3: In Excel, a macro is used to:
- Create a pivot table automatically
- Record and replay a sequence of actions, automating repetitive tasks (Correct answer)
- Apply conditional formatting to a range
- Protect a worksheet from changes
Correct answer: Record and replay a sequence of actions, automating repetitive tasks
A macro records a series of actions (keystrokes and commands) that can be replayed to automate repetitive tasks, such as formatting reports or running regular data processing routines.
Question 4: What is the purpose of 'freezing panes' in Excel?
- To prevent changes to protected cells
- To keep row or column headers visible when scrolling through a large spreadsheet (Correct answer)
- To fix the print area for a worksheet
- To lock the spreadsheet against editing
Correct answer: To keep row or column headers visible when scrolling through a large spreadsheet
Freeze Panes locks specified rows and/or columns in place so they remain visible as you scroll through a large dataset, making it easier to see column or row headings at all times.
Question 5: Which of the following is the correct Excel formula to calculate VAT (at 20%) on a net amount in cell A1?
- =A1*0.2 (Correct answer)
- =A1+0.2
- =A1/1.2
- =A1*20
Correct answer: =A1*0.2
VAT at 20% on a net amount is calculated as net × 0.20 (i.e., the net amount × 20%). To find the gross amount, the formula would be =A1*1.2; to extract VAT from a gross amount, use =A1/1.2*0.2 or =A1-(A1/1.2).
Question 6: The Excel IFERROR function is used to:
- Prevent users entering incorrect data
- Return a specified value if a formula produces an error, otherwise return the formula result (Correct answer)
- Identify cells that contain errors in a range
- Validate data entered into a cell
Correct answer: Return a specified value if a formula produces an error, otherwise return the formula result
IFERROR(value, value_if_error) evaluates a formula; if it produces an error (such as #DIV/0! or #N/A), it returns the specified alternative value instead of displaying the error code.
AAT L3 Spreadsheets for Accounting 3
The PMT function in Excel calculates: