Excel VBA Regulatory Frameworks & Compliance 5 — Questions and Answers
Question 1: A VBA workbook is part of a system undergoing FedRAMP authorization. Which security control directly applies to the workbook's data handling?
- Using Excel's default file format (.xlsx) without additional controls
- Ensuring data at rest is encrypted and access is limited to users with the appropriate FedRAMP-defined clearance level (Correct answer)
- Storing data in unprotected CSV files for interoperability
- Disabling macro security to ensure smooth operation in the cloud
Correct answer: Ensuring data at rest is encrypted and access is limited to users with the appropriate FedRAMP-defined clearance level
FedRAMP requires federal cloud services to meet NIST 800-53 controls including data encryption at rest and strict access controls aligned with authorization levels.
Question 2: Under OSHA recordkeeping regulations (29 CFR 1904), a VBA tool tracks workplace incidents. Which validation rule is compliance-critical?
- Allowing backdating of incident records to smooth reporting cycles
- Enforcing that incident records are entered within 7 days of occurrence and cannot be modified after supervisor sign-off (Correct answer)
- Permitting supervisors to delete minor incidents to improve safety metrics
- Aggregating incident types to reduce the number of reportable records
Correct answer: Enforcing that incident records are entered within 7 days of occurrence and cannot be modified after supervisor sign-off
OSHA requires workplace injuries and illnesses to be recorded accurately and within specified timeframes; VBA validation prevents late entry and unauthorized modification.
Question 3: A VBA solution supports COSO Internal Control framework documentation. Which feature best supports the Control Activities component?
- Allowing all users to override calculated values with manual entries
- Implementing segregation of duties by restricting data entry, review, and approval actions to separate user roles enforced in VBA (Correct answer)
- Using a single shared login for all finance team members
- Removing all input validation to speed up data entry
Correct answer: Implementing segregation of duties by restricting data entry, review, and approval actions to separate user roles enforced in VBA
COSO's Control Activities component includes segregation of duties; VBA user-role enforcement ensures that no single person can initiate, record, and approve the same transaction.
Question 4: A compliance analyst needs a VBA macro to support GDPR Article 30 Record of Processing Activities (RoPA). What should the macro maintain?
- A simple list of all Excel files on the network
- A structured log recording the purpose, legal basis, data categories, retention period, and recipients for each data processing activity (Correct answer)
- Monthly screenshots of active worksheets
- A count of how many rows contain personal data
Correct answer: A structured log recording the purpose, legal basis, data categories, retention period, and recipients for each data processing activity
GDPR Article 30 requires controllers to maintain detailed records of processing activities including purposes, legal bases, data categories, and retention schedules.
Question 5: Which VBA practice aligns with the SEC's Rule 17a-4 requirement for electronic record preservation?
- Allowing broker-dealers to delete records after internal review
- Writing finalized trade records to a WORM (Write Once Read Many) compliant output and preventing any VBA routine from modifying archived data (Correct answer)
- Storing records in editable Excel format in a shared folder
- Archiving records to a password-protected zip file
Correct answer: Writing finalized trade records to a WORM (Write Once Read Many) compliant output and preventing any VBA routine from modifying archived data
SEC Rule 17a-4 requires broker-dealers to preserve electronic records in a non-rewritable, non-erasable format; VBA must enforce read-only status on archived records.
Question 6: A VBA workbook is used in a SOX-audited environment. An auditor asks for evidence of IT General Controls (ITGCs) over the spreadsheet. Which VBA artifact best provides this evidence?
- A printed screenshot of the completed spreadsheet
- A VBA-generated change log showing every modification to formulas or data, including user, timestamp, before-value, and after-value (Correct answer)
- The workbook's AutoRecover folder contents
- A summary email sent monthly to the controller
Correct answer: A VBA-generated change log showing every modification to formulas or data, including user, timestamp, before-value, and after-value
ITGCs require evidence of controls over financial applications; a detailed VBA change log with user attribution and before/after values directly supports ITGC documentation for auditors.
Question 7: A VBA macro is used to generate reports for a company subject to the Consumer Financial Protection Bureau (CFPB) fair lending rules. Which anti-discrimination control should be built in?
- Hiding demographic fields after data entry to avoid bias claims
- Flagging any loan pricing or decisioning output where similarly qualified applicants in protected classes received materially different outcomes for human review (Correct answer)
- Removing all demographic data before any analysis begins
- Applying uniform pricing to all applicants regardless of risk score
Correct answer: Flagging any loan pricing or decisioning output where similarly qualified applicants in protected classes received materially different outcomes for human review
CFPB fair lending rules require lenders to identify and remediate disparate impact; VBA logic that flags differential outcomes for protected class members supports compliance monitoring.
Excel VBA Regulatory Frameworks & Compliance 5
A VBA workbook is part of a system undergoing FedRAMP authorization.
Which security control directly applies to the workbook's data handling?