The AI Auditor

Exploratory Data Analysis Simulation

0 Acquire the Dataset

You have been handed the Accounts Payable ledger for "Company X". Your job is to find the fraud hiding in these transactions.

DATA PRIVACY ALERT

Do NOT paste this dataset into ChatGPT or any AI.

In a real audit, uploading client ledgers to public AI is a security violation. For this simulation, you must practice strict data hygiene. Copy the data only to Excel.

Instruction: Click "Copy CSV". Open Excel. Paste into cell A1. Use Data > Text to Columns (Comma delimited) if needed.

1 Step 1: Configure Your AI Assistant

To learn the process, you must instruct your AI to act as a generic Excel Tutor, not a fraud analyst.

Mandatory System Prompt

"I am an auditing student analyzing a CSV file in Excel. I want you to act as my Excel Technical Coach.

RULES:
1. Do NOT ask for the data values.
2. Do NOT analyze the data for me.
3. Only provide Excel formulas and step-by-step guides for tools like Pivot Tables and Conditional Formatting.
4. I will only provide you with the Column Headers.

Please confirm you are ready to help me write formulas."

Action: Copy the text above and paste it into your AI chat before asking any questions.

2 Phase 2: The Client Interview

Now that your AI is configured, ask it to help you identify risks based only on the headers.

Prompt Strategy

"I have an Excel sheet with these headers: Invoice_ID, Vendor_Name, Invoice_Date, Amount, Description. What are the top 5 fraud risks I should look for in this specific type of data? For each risk, tell me what Excel tool detects it."

Hint: The AI should suggest looking for Duplicate Invoices, Round Numbers, and violations of Benford's Law.

3 Phase 3: Technical Execution

Now, ask the AI how to find these red flags in Excel.

Task A: Duplicate Payments

Ask AI: "How do I find double payments in Excel where Vendor and Amount are the same?"

=COUNTIFS(B:B, B2, D:D, D2) > 1

Task B: Round Numbers

Ask AI: "How do I filter for amounts that end in .00 (no cents) in Excel?"

=MOD(D2, 1) = 0

Task C: Weekend Activity

Fraudsters often process fake invoices when the office is empty (Sat/Sun). Ask AI: "How do I highlight dates that fall on a weekend in Excel?"

=WEEKDAY(C2, 2) > 5
(This checks if Day is Sat/Sun)

Task D: Structuring

To avoid approval limits (e.g., $10k), fraudsters bill just under the limit. Ask AI: "How do I filter for amounts between $9,000 and $10,000?"

Use Filter > Number Filters > Between...
OR =AND(D2>9000, D2<10000)

4 Phase 4: The Benford's Law Trap

Origin Story

History: Originally observed by Simon Newcomb in 1881 and formalized by physicist Frank Benford in 1938. They noticed that pages in logarithm tables starting with "1" were more worn than others.

The Law: In natural datasets, the leading digit is 1 about 30% of the time. The frequency drops as digits get larger, with 9 appearing less than 5% of the time.

Limitations: Works best on large, natural datasets (rivers, sales). Does NOT work on constrained numbers (phone numbers, zip codes) or psychological pricing ($9.99).

Video Tutorial: Benford's Law in Excel

Recommended Watch: 0:00 to 4:13

Ask the AI

  1. "What is the standard '4-Step Model' for using Benford's Law in fraud detection?"
  2. "How do I extract the first digit of the Amount column in Excel?"
  3. "What is the expected frequency of digits 1-9 in Benford's Law?"
  4. "Create a Pivot Table to count my actual first digits."
Check the digit 9. Benford's law says '9' should appear about 4.6% of the time. Does a specific vendor have way more '9's than that?

5 The Verdict

Submission Instructions: Upload the downloaded Fraud_Audit_Report.txt AND your Excel workbook to the Course Canvas page.