Spreadsheets, software, and information security

Learning goals

• Compare manual, spreadsheet, and accounting-software workflows.
• Audit a spreadsheet formula, range, account mapping, and output.
• Match information risks to preventive, detective, and recovery controls.
• Use AI tools only with authorized data, verification, documentation, and human judgment.

Prerequisite check

Why can a spreadsheet be mathematically correct but accounting-wrong? Give an example involving account classification or period cutoff.

Vocabulary

• Input control: check on data entering a system, such as required fields or approved account codes.
• Processing control: check on calculations or transformations, such as balanced batch totals.
• Output control: review of reports for completeness, authorization, and reasonableness.
• Access control: restricts systems and data to authorized users and roles.
• Backup: protected copy of data; useful only if restoration is tested.
• Audit log: record of system activity or changes.
• Formula view: display of spreadsheet formulas rather than calculated values.
• AI-generated output: draft produced by a model; it remains subject to authorization, confidentiality, verification, and human accountability.

Core idea

Technology changes speed, scale, visibility, and the location of risk. It does not remove the need to understand accounts, protect evidence, review exceptions, or take responsibility for decisions.

Why this treatment makes sense

A manual system can make each step visible but is slow and prone to copying errors. A spreadsheet can link schedules flexibly but a wrong range can spread silently. Accounting software can enforce account structures and integrated ledgers, but wrong setup, permissions, imports, or automated rules can scale an error.

A repeatable method

Trace IPOSAR:

1. I — Input: source, completeness, validity, authorization.
2. P — Processing: formulas, rules, mappings, duplicates, cutoff.
3. O — Output: totals, reasonableness, user, version.
4. S — Storage: retention, encryption where appropriate, approved location.
5. A — Access: unique accounts, least privilege, multifactor authentication where available.
6. R — Recovery: backups, tested restore, incident route.

Worked example

A spreadsheet contains weekly sales before HST:

Correct totals are Sales $6,100, COGS $3,690, Gross Profit $2,410.

The Sales total cell displays $4,000 because its formula is =SUM(B2:B4) while Week 4 is in row 5. The arithmetic engine worked exactly as instructed; the range was wrong.

Repair process:

1. Compare row count with source batch count: four weeks should produce four lines.
2. Show formula view and change to =SUM(B2:B5).
3. Independently add the source values or compare a control total.
4. Recalculate gross profit and review the affected report.
5. Preserve version history and document the correction.
6. Add a structured table or check such as “detail count = source count.”

Journal, ledger, and statement connection

A spreadsheet schedule may feed journal entries or reconcile ledger accounts; accounting software may post imports directly. Before posting, verify date, account, debit/credit direction, tax code, customer/vendor, amount, and batch totals. After posting, compare the ledger and statements with independent control totals.

Common mistakes

• Checking displayed values without formula ranges or hidden rows.
• Hard-coding a total over a broken formula.
• Assuming balanced imported debits/credits mean correct accounts.
• Sharing customer, payroll, bank, or login data with an unapproved tool.
• Treating AI output as an authoritative source or final professional judgment.
• Having backups but never testing restoration.
• Using shared accounts that destroy individual accountability.

Guided practice

1. A formula is =B2-C2, but B is COGS and C is Sales. Diagnose.
2. A balanced journal import assigns every expense to Supplies Expense. Which controls find it?
3. An email asks the bookkeeper to change a supplier's bank account immediately. Design a safe response.
4. A public AI tool is asked to summarize a real customer ledger. Identify the first concern.

Independent practice

Design a spreadsheet check block for a 40-line journal import. Include at least six controls covering source count, debit/credit totals, dates, duplicate IDs, valid account codes, and post-import ledger agreement. Explain what each can and cannot prove.

Self-check and solutions

Guided practice:

1. Gross profit should be Sales − COGS, so with COGS in B and Sales in C the formula is =C2-B2; verify headers rather than guessing letters.
2. Account-code validation, exception report by account, budget/comparative review, document sampling, and manager approval can reveal the classification error; equality cannot.
3. Do not use the emailed details directly. Verify through an independently known contact and approved change process, require authorization, record the change, and review the first payment.
4. Confidentiality and authorization: real customer data should not enter an unapproved system. Use fictional/de-identified data only if policy allows, and verify all output.

Independent practice: A strong block includes: line count = 40; source total debits = import debits; import debits = import credits; dates within authorized period; unique source IDs/no duplicates; every code in approved chart; blank required fields = 0; post-import batch total agrees; exception list reviewed and signed. These find defined exceptions but cannot prove documents are genuine, accounts are economically correct, or omitted transactions do not exist.

Retrieval practice

Write IPOSAR from memory. Give one manual advantage, one software advantage, and one new risk introduced by automation.

Exam-style application

An AI assistant produces a convincing bank-reconciliation explanation with a $900 plug called “timing difference.” Assess it.

Answer outline: A label is not evidence. Recalculate both sides, trace every item to bank/book/source records, reject the unexplained plug, protect confidential data, document whether AI was authorized, and retain human responsibility. Use AI for an approved draft or checklist only after independent verification.

Lesson summary

Technology is valuable when its inputs, rules, outputs, access, and recovery are controlled. Transparency, independent totals, confidentiality, and human judgment remain essential.