Audit Preparation Income Statement (One Page) Excel Template

Sheet Names

• Income Statement (Audit Ready): The main worksheet containing all financial data, calculations, and audit-ready formatting.
• Audit Trail Log: A secondary sheet (optional) for tracking changes, date stamps, reviewer notes, and source document references—critical for audit compliance.

Table Structure & Layout

• Header Section: Company name, fiscal year, period end date (e.g., December 31, 2023), and preparation date. Includes a “Status: Draft/Audit Pending/Audit Complete” indicator.
• Revenue Section: Breakdown of total revenue by category (e.g., Product Sales, Service Revenue, Other Income).
• Cost of Goods Sold (COGS) & Operating Expenses: Organized into subcategories such as Salaries, Rent, Marketing, Utilities.
• Key Financial Metrics: Calculated lines including Gross Profit, Operating Income (EBIT), Pre-Tax Income, and Net Income.
• Audit Flags & Notes Section: A dedicated area at the bottom for audit observations, discrepancies, or follow-ups.

Columns and Data Types

• Description (A): Text field. Contains line item names (e.g., “Sales Revenue”, “Salaries & Wages”). Limited to 50 characters for consistency.
• Period 1 Value (B): Currency format. Input for the first period’s values (e.g., Q1, Jan–Mar).
• Period 2 Value (C): Currency format. Input for second period (e.g., Q2, Apr–Jun).
• Change (%) (D): Percentage format. Automatically calculated as: ((C - B) / B) * 100. Negative values appear in red.
• Audit Flag (E): Drop-down list with options: “Clean”, “Review Required”, “Discrepancy Found”, “Documentation Pending”. Conditional formatting applies based on selection.
• Notes (F): Text field. For auditors or accountants to add comments, source references, or explanations.

Formulas Required

• =SUM(B6:B15): Total revenue (auto-calculates from row 6 to 15).
• =B6 - B7: Gross Profit (Revenue – COGS).
• =B8 - SUM(B9:B14): Operating Income (Gross Profit – Operating Expenses).
• =B15 * (1 - Tax Rate): Net Income. Tax rate is a fixed input in a designated cell (e.g., $C$3) for consistency.
• =IF(ABS((C8-B8)/B8)>0.2, "High Variance", "Normal"): Flags revenue/expense changes exceeding 20%.
• =IF(E6="Discrepancy Found", "⚠️ Check Documentation", ""): Triggers audit warning in notes column.

Conditional Formatting Rules

• Red Text: Any change (%) above 50% or below -30% in revenue/expenses.
• Yellow Background: If the Audit Flag is “Review Required” or “Documentation Pending”.
• Green Highlight: For net income growth over 10% compared to prior period.
• Bold Font: Total values (e.g., Gross Profit, Net Income) for emphasis.

User Instructions

1. Data Entry: Enter actual figures in Period 1 and Period 2 columns. Do not alter formulas or locked cells.
2. Validation: Use the “Audit Flag” column to self-check entries. If uncertain, select “Review Required”.
3. Currency Formatting: Ensure all values are formatted as currency (e.g., $1,234.56). Avoid entering text or symbols.
4. Audit Trail: In the Audit Trail Log sheet, document who updated what, when, and why—essential for audit trails.
5. Review: Before submission to auditors, run a full data validation check using the built-in formula checks.

Example Rows

Recommended Charts & Dashboards (One Page)

• Bar Chart (Top Right Corner): Compares Period 1 vs. Period 2 revenue and operating income side-by-side for quick trend analysis.
• Progress Gauge (Bottom Center): Displays net income variance from budget or prior year in real time, using color-coded thresholds (green: ≤5%, yellow: >5%–10%, red: >10%).

Conclusion