Account number × month within each financial sheet
Actuals / Budget / Prior year
30 account rows in each sheet; FY2025 actuals and budget, FY2024 actuals. Balance-sheet rows include year-end-only values.
← All selected work
CASE 03 / Finance · AI-assisted reporting
Revenue beats budget. Profit does not.
A financial variance review connects the revenue result to delivery costs, operating profit and a reporting workflow with human review.
03
AdvisoryCo
Anonymized collaboration case
$10.46MFY2025 revenue
−$95.9KOperating profit vs. budget
67.8%Actual gross margin
THE BUSINESS QUESTION
Why is profit below budget when revenue is ahead?
THE KEY FINDING
Revenue is $91.7K above plan, but higher delivery and operating costs leave operating profit $95.9K below budget.
01 / THE INPUTS & THE WORK
From raw records
to a defensible answer.
Explain why revenue can beat budget while operating profit misses, then design a repeatable reporting process that preserves financial review controls.
3 × 30Financial sheets × account rows
12 monthsMonthly columns per sheet
18 accountsRevenue, COGS and operating expenses per sheet
6 operating metrics × 12 monthly values
Headcount & KPIs
Total FTE, billable FTE, available hours, billable hours, reported utilization and billing rate.
Named planning assumption
Assumptions
Revenue growth, gross-margin target and operating targets used as context for review.
A look at the source records +
| Account | Name | Jan actual (USD) | FY actual (USD) |
|---|---|---|---|
| 4000 | Consulting Revenue | 480,405 | 5,203,262 |
| 4100 | Advisory Revenue | 271,728 | 3,235,972 |
| 4200 | Implementation Revenue | 194,823 | 2,021,312 |
Selected Trial Balance rows. Analysis uses the matching account numbers in Budget and Prior Year, not row-position guesses.
DATA JUDGMENT
What needed attention before charting.
Financial sheets mix balance-sheet and P&L categories.
Handling decisionRestrict the operating-profit bridge to revenue, COGS and operating expenses: 18 accounts per sheet.
Why it mattersCash, assets and other-income accounts do not belong in this operating-profit calculation.
The meaning of a positive variance depends on the account.
Handling decisionShow revenue above budget as favorable; invert expense variance when showing profit effects.
Why it mattersA $119.1K increase in subcontractor cost reduces profit, even though actual minus budget is positive.
A target and a budget ratio are different benchmarks.
Handling decisionCompare actual gross margin of 67.8% against both the 62% stated target and the 69.2% calculated budget margin.
Why it mattersAbove target does not mean above budget.
Reported utilization is a separate source metric.
Handling decisionUse the workbook’s reported rates and label their 74.25% unweighted mean.
Why it mattersDo not imply the rate was reconciled from hours and staffing assumptions.
ANALYTICAL APPROACH
How the answer was built.
- 01
Align comparable accounts
Read actual, budget and prior-year sheets; key account records by account number and retain category definitions.
OUTPUTComparable financial inputs for the 18 operating P&L accounts.
- 02
Rebuild the financial result
Sum revenue, COGS and operating expenses separately. Calculate operating profit as revenue minus COGS minus operating expenses.
OUTPUT$10.46M revenue, $3.37M COGS and $2.22M operating profit.
- 03
Explain and reconcile the gap
Calculate favorable / unfavorable effects by account. Assert that their sum equals actual operating profit minus budget operating profit.
OUTPUTA reconciled −$95,906 profit bridge and ranked account drivers.
- 04
Design the reporting controls
Separate numerical calculation from narrative drafting. Flag input exceptions and require a financial reviewer before a report is approved.
OUTPUTA proposed AI-assisted variance reporting workflow with explicit review gates.
Inspect the Python calculation logic +
Representative logic from the website reconstruction. This code was added for the portfolio; it is not presented as the original project team’s source code.
# Website reconstruction: reconcile the profit bridge
profit_effects = []
for account_id, actual in actual_accounts.items():
if actual['category'] not in ['Revenue', 'COGS', 'Operating Expenses']:
continue
variance = actual['total'] - budget_accounts[account_id]['total']
sign = 1 if actual['category'] == 'Revenue' else -1
profit_effects.append(sign * variance)
assert sum(profit_effects) == actual_profit - budget_profitONE QUESTION, TRACED END TO END
Why is operating profit below budget despite higher revenue?
+$91,716 revenue − $174,858 additional COGS − $12,764 additional operating expenses = −$95,906 operating-profit variance.
The revenue beat is more than offset by costs. Subcontractors alone contribute a −$119,149 profit effect.
Review delivery capacity and project margins with Sales and Finance. Investigate account drivers before approving cost or pricing changes.
EXPLORE THE EVIDENCE
A closer look
at the numbers.
Switch views to explore the analysis.
Open the underlying values for more detail.
01 / PROFIT BRIDGE
From budget to actual profit
FY2025 operating profit. Revenue increases profit; higher COGS and operating expenses reduce it.
THE FINANCIAL READOUT−$95.9K
Higher delivery costs absorb the revenue beat.
Revenue is $91.7K above plan. COGS is $174.9K higher and operating expenses add a further $12.8K.
Proposed next step · delivery cost reviewView underlying values +
| Metric | Actual (USD) | Budget (USD) | Actual − budget (USD) |
|---|---|---|---|
| revenue | 10460546 | 10368830 | 91716 |
| cogs | 3372190 | 3197332 | 174858 |
| opex | 4867461 | 4854697 | 12764 |
| profit | 2220895 | 2316801 | -95906 |
SOURCE: AdvisoryCo_Financial_Variance_Analysis.xlsx · FY2025 actuals/budget; FY2024 comparison
03 / APPLYING AI TO THE BUSINESS PROCESS
Make the analysis
useful in the workflow.
Proposed AI extension.
The analysis is available; the AI workflow below is a design for a future pilot.
WORKFLOW DESIGN · NOT A DEPLOYED AI SYSTEM
A controlled variance-commentary copilot
INPUT CONTRACT
Reconciled actual / budget account totals, calculated profit effects, approved materiality rules and finance-provided explanations of business events.
- 01Code checks account mappings, totals and the profit reconciliation.
- 02Material exceptions are selected using finance-approved thresholds.
- 03AI drafts observations from supplied values and marks missing explanations as questions.
- 04The reviewer checks every numeric claim, resolves exceptions and approves the report.
What this case demonstrates +
- Supplied: financial data workbook, analytical workbook, executive dashboard and human-review workflow description.
- Built for this portfolio: account aggregation, reconciled profit bridge and interactive variance / operating KPI views.
- AI extension: workflow design only; no deployed model, automation time savings or business uplift is claimed.
Project context: graduate AI/analytics club industry collaboration. The technical walkthrough documents the reproducible portfolio implementation.
RECOMMENDATIONS BY TEAM
What happens next?
Specific actions. Clear measures.
Select a department to see its priorities.
RECOMMENDED ACTION / SALES
Build margin checks into the sales conversation.
Review implementation pipeline timing and scope. Add delivery-capacity and subcontractor-cost checks to proposal reviews.
THE EVIDENCE
Advisory revenue is $233.5K above budget, while implementation revenue is $110.8K below.
HOW TO MEASURE IT
Track implementation bookings, project gross margin and forecast conversion.
Recommendations derived from the case analysis. Business outcomes have not been measured.
METHOD & CONTEXT
Show the work.
Know its limits.
Selected work completed through a graduate AI/analytics club’s industry collaborations. Company names are portfolio pseudonyms.
- 01Match actual, budget and prior-year accounts by account number.
- 02Separate revenue, cost of goods sold and operating expenses; reconcile the profit bridge.
- 03Compare reported utilization against the stated 75% planning target.
- 04Design a reporting workflow with source checks, exception handling and human approval.
Definitions & data limitations +
The AI reporting workflow is a design proposal, not a deployed automation or a measured time-saving result. Utilization uses the workbook’s reported monthly rate, not a recalculation from hours. Cost variances are shown by their effect on operating profit; balance-sheet and other-income accounts are excluded.