AAT L3 Spreadsheets for Accounting 5 — Questions and Answers
Question 1: Which feature in Excel allows you to protect the formulas in a spreadsheet from being accidentally changed?
- Conditional formatting
- Sheet protection (with locked cells) (Correct answer)
- Data validation
- Freeze panes
Correct answer: Sheet protection (with locked cells)
Cell locking combined with sheet protection prevents users from editing specified cells (typically formula cells); input cells can remain unlocked, so data can be entered without risking formula changes.
Question 2: The Excel TEXT function is used to:
- Count the number of characters in a cell
- Format a number as text with a specified format (Correct answer)
- Convert text to a number
- Search for text within a cell
Correct answer: Format a number as text with a specified format
TEXT(value, format_text) converts a number to text with a specified format code — for example, =TEXT(A1,'dd/mm/yyyy') formats a date serial number as a readable date string.
Question 3: When preparing a payroll spreadsheet, which function could be used to look up an employee's pay rate from a separate table based on their employee code?
- COUNTA
- VLOOKUP (Correct answer)
- SUMIF
- AVERAGE
Correct answer: VLOOKUP
VLOOKUP is ideal for retrieving employee pay rates from a lookup table using the employee code as the lookup value — a common use of VLOOKUP in payroll and HR spreadsheets.
Question 4: Which of the following describes the purpose of the 'Goal Seek' function in Excel?
- It finds the goal in the most recent audit
- It calculates what input value is needed to achieve a specific output value in a formula (Correct answer)
- It searches cells for specific text
- It automatically creates charts from data
Correct answer: It calculates what input value is needed to achieve a specific output value in a formula
Goal Seek (under What-If Analysis) finds the input value needed for a formula to produce a desired result — for example, what sales level is needed to achieve a target profit.
Question 5: In a well-designed accounting spreadsheet, a control total is used to:
- Limit the number of rows in a worksheet
- Verify that data entered equals expected totals, providing a check on completeness and accuracy (Correct answer)
- Format the spreadsheet for printing
- Create a summary of all worksheets
Correct answer: Verify that data entered equals expected totals, providing a check on completeness and accuracy
Control totals are independent checks built into a spreadsheet — for example, checking that the sum of individual entries equals an independently calculated total, catching entry errors or omissions.
Question 6: Which of the following is a risk of using spreadsheets for critical accounting data?
- They are too slow to use for large datasets
- Formula errors, version control problems, and lack of audit trail can lead to material errors (Correct answer)
- They cannot produce charts
- They are not compatible with any accounting software
Correct answer: Formula errors, version control problems, and lack of audit trail can lead to material errors
Spreadsheet risks include formula errors (which may go undetected), lack of access controls, version control issues (wrong version used), and limited audit trails — all of which can result in material errors in financial data.
AAT L3 Spreadsheets for Accounting 5
Which feature in Excel allows you to protect the formulas in a spreadsheet from being accidentally changed?