Finance & Operations Analytics

Learn budget variance analysis, expense classification with AI, and cash flow forecasting using sample finance data.

Time: 15-20 minutes Difficulty: Beginner-friendly

---

About This Tutorial

In this tutorial, you'll analyze financial and operational data for a growing company. You'll learn to:

• Track budget vs actual performance and identify variances
• Automatically categorize expenses using AI Tables
• Forecast cash flow and identify potential shortfalls

No SQL knowledge required—just follow the prompts and see what Shadowfax creates.

---

Get the Sample Data

Download the Dataset

We've prepared realistic finance and operations data for you:

Download Finance Sample Data (placeholder link)

What's included:

• transactions.csv - 15,000 financial transactions with vendors, amounts, dates
• budgets.csv - Annual budgets by department and category
• departments.csv - Department metadata and cost centers

Import the Data

1. Download and unzip the files
2. In your Shadowfax Workbook, click "Import Data"
3. Upload all three CSV files
4. Confirm they appear under "Sources" in your sidebar

Check the Schema

Click the Graph tab to see relationships:

---

Choose Your Path

Pick an analysis path based on what you want to learn. Each path is independent—do one, two, or all three!

Path A: Budget vs Actual Variance Analysis

Goal: Compare actual spending to budgets and identify overruns.

Path B: Expense Classification with AI

Goal: Automatically categorize messy expense data using AI Tables.

Path C: Cash Flow Forecasting

Goal: Project future cash positions and identify potential shortfalls.

---

Path A: Budget vs Actual Variance Analysis

Goal: Compare actual spending against budgets to identify variances and take corrective action.

Step 1: Calculate Actual Spend by Department and Category

Prompt:

@[transactions] @[departments]
For each department and expense category, calculate total actual spend
for the current year

What the AI creates:

• View name: actual_spend (Classification: Combine + Aggregate)
• Joins transactions with departments
• Groups by department and category
• Sums amounts

Sample data:

department	category	actual_spend
Engineering	Salaries	$1,245,800
Engineering	Software & Tools	$89,400
Engineering	Travel	$24,100
Sales	Salaries	$892,500
Sales	Marketing	$156,200
...	...	...

Step 2: Compare Actual to Budget

Prompt:

@[actual_spend] @[budgets]
Join actual spend with budgets and calculate:
- Budget amount
- Actual spend
- Variance (actual - budget)
- Variance percentage
- Flag items over budget

What the AI creates:

• View name: budget_variance (Classification: Combine + Calculate Columns)
• Joins actual with budgets
• Calculates variance metrics
• Adds over_budget flag

Sample data:

department	category	budget	actual	variance	variance_pct	over_budget
Engineering	Salaries	$1,200,000	$1,245,800	$45,800	+3.8%	Yes
Engineering	Software	$95,000	$89,400	-$5,600	-5.9%	No
Sales	Marketing	$140,000	$156,200	$16,200	+11.6%	Yes
...	...	...	...	...	...	...

Step 3: Summarize Budget Performance by Department

Prompt:

@[budget_variance]
Roll up to department level and calculate:
- Total budget per department
- Total actual spend per department
- Overall variance
- Count of line items over budget

What the AI creates:

• View name: dept_budget_summary (Classification: Aggregate)
• Groups by department
• Calculates totals and counts

Step 4: Visualize Budget Performance

Prompt:

/visualize @[budget_variance]
Create a diverging bar chart showing variances by category.
- Bars extending right (green): under budget
- Bars extending left (red): over budget
- Sort by variance magnitude
- Show top 15 variances

Step 5: Flag At-Risk Categories

Prompt:

@[budget_variance]
Create a view of categories that are:
- More than 10% over budget, OR
- More than $25,000 over budget
These require immediate attention.

What the AI creates:

• View name: at_risk_spending (Classification: Select Filter)
• Filters to significant variances
• Sorted by variance descending

Actionable output for leadership team:

department	category	budget	actual	variance	action_needed
Sales	Marketing	$140,000	$156,200	+$16,200 (+11.6%)	"Overspent on Q3 campaigns. Review ROI and adjust Q4 spending."
Engineering	Cloud Infrastructure	$85,000	$108,300	+$23,300 (+27.4%)	"Usage surge from new product launch. Evaluate optimization opportunities."

What You Learned

Budget vs actual comparisons Variance calculations (absolute and percentage) Aggregation hierarchies (category → department → total) Diverging bar charts (positive/negative visualizations) Exception reporting (filtering to actionable items)

Next: Try Path B: Expense Classification!

---

Path B: Expense Classification with AI

Goal: Automatically categorize messy, unstructured expense data using AI Tables.

The Problem

Your transactions table has vendor names and memo fields, but categorization is inconsistent:

• Some expenses have categories, many don't
• Vendor names are messy ("AWS INC", "Amazon Web Services", "aws.amazon.com")
• Memo fields have useful context but aren't structured

Traditional approach: Manual categorization (slow, inconsistent) Shadowfax approach: AI Tables (fast, accurate, scales)

Step 1: Review Uncategorized Transactions

Prompt:

@[transactions]
Show transactions that are missing a category or have category = 'Other'
Include: transaction_id, date, vendor_name, memo, amount

What the AI creates:

• View name: uncategorized_txns (Classification: Select Filter)
• Filters to missing or vague categories
• Includes relevant columns for context

Sample uncategorized data:

transaction_id	vendor_name	memo	amount	category
TX10045	AMZN WEB SERVICES	EC2 compute Q2	$8,450	Other
TX10082	Expedia Group	Team offsite SF	$3,200	NULL
TX10134	SALESFORCE.COM	Enterprise license	$12,500	Other
TX10198	THE UPS STORE	Shipping samples	$145	NULL

Step 2: Create AI Table for Smart Categorization

Prompt:

@[uncategorized_txns]
Create an AI Table that categorizes each transaction.
Use the vendor name, memo, and amount to determine the most appropriate category.

Categories to use:
- Software & SaaS
- Cloud Infrastructure
- Travel & Entertainment
- Office Supplies
- Professional Services
- Marketing & Advertising
- Facilities & Rent
- Other

For each transaction, generate:
- category: the best-fit category
- subcategory: more specific classification
- confidence: High/Medium/Low based on clarity of context
- reasoning: brief explanation of why this category was chosen

What the AI creates:

• AI Table name: txn_categorization_ai
• Input columns: transaction_id, vendor_name, memo, amount
• AI output columns: category, subcategory, confidence, reasoning

Step 3: Refresh the AI Table

Click Refresh to process all rows with the AI.

After refresh, you'll see intelligent categorizations:

transaction_id	vendor_name	memo	category	subcategory	confidence	reasoning
TX10045	AMZN WEB SERVICES	EC2 compute Q2	Cloud Infrastructure	Compute	High	"Clear AWS EC2 compute expense based on vendor and memo"
TX10082	Expedia Group	Team offsite SF	Travel & Entertainment	Lodging	High	"Expedia booking for team event, clearly travel-related"
TX10134	SALESFORCE.COM	Enterprise license	Software & SaaS	CRM	High	"Salesforce enterprise license, clearly SaaS subscription"
TX10198	THE UPS STORE	Shipping samples	Office Supplies	Shipping	Medium	"UPS shipping, likely samples based on memo, could be marketing or supplies"

Step 4: Review and Validate AI Categories

Prompt:

@[txn_categorization_ai]
Summarize AI categorization results:
- Count of transactions per category
- Total $ amount per category
- Distribution of confidence levels
- Flag any "Low" confidence items for manual review

What the AI creates:

• View name: categorization_summary (Classification: Aggregate)
• Groups by category and confidence
• Shows counts and amounts

Results:

category	count	total_amount	high_confidence_pct
Cloud Infrastructure	845	$124,500	92%
Software & SaaS	623	$89,200	88%
Travel & Entertainment	412	$56,800	79%
Office Supplies	289	$18,400	85%
Professional Services	156	$245,600	71%

Insight: 86% overall confidence rate—AI is highly accurate! Review the 14% low-confidence items manually.

Step 5: Apply AI Categories Back to Transactions

Prompt:

@[transactions] @[txn_categorization_ai]
Join AI-generated categories back to the full transactions table.
For transactions that already had categories, keep the original.
For uncategorized, use the AI category.
Create a clean, fully-categorized transaction table.

What the AI creates:

• View name: transactions_categorized (Classification: Combine + Calculate Columns)
• Merges AI categories into transactions
• Uses COALESCE to prefer existing categories
• Result: Complete categorization

Step 6: Analyze Spend by AI-Generated Category

Prompt:

@[transactions_categorized]
Now that everything is categorized, show monthly spend trends
by category over the past 12 months

What the AI creates:

• View name: monthly_spend_by_category (Classification: Aggregate)
• Groups by month and category
• Shows spend trends

Step 7: Visualize Category Trends

Prompt:

/visualize @[monthly_spend_by_category]
Create a stacked area chart showing spend by category over time.
Use a distinct color for each category.

Insights you might discover:

• Cloud Infrastructure spend growing 15% month-over-month (expected with growth)
• Travel & Entertainment spiked in September (annual conference?)
• Software & SaaS steady, opportunities for consolidation?

What You Learned

AI Tables for classification tasks (unstructured → structured data) Confidence scoring (knowing when to review manually) Data enrichment (merging AI outputs back to sources) Trend analysis (monthly patterns by category) Stacked area charts (composition over time)

Next: Try Path C: Cash Flow Forecasting!

---

Path C: Cash Flow Forecasting

Goal: Project future cash positions based on historical patterns and identify potential shortfalls.

Step 1: Calculate Daily Cash Flow

Prompt:

@[transactions]
Calculate daily cash flow:
- Total inflows (positive amounts)
- Total outflows (negative amounts)
- Net cash flow per day
Include only settled transactions (status = 'cleared')

What the AI creates:

• View name: daily_cash_flow (Classification: Aggregate + Calculate Columns)
• Filters to cleared transactions
• Groups by date
• Separates inflows and outflows

Sample data:

date	inflows	outflows	net_cash_flow	ending_balance
2024-10-01	$45,200	-$28,600	$16,600	$452,300
2024-10-02	$38,900	-$31,200	$7,700	$460,000
2024-10-03	$52,100	-$45,800	$6,300	$466,300
...	...	...	...	...

Step 2: Add Rolling Balance

Prompt:

@[daily_cash_flow]
Calculate running cash balance (starting from known balance on Jan 1)
Show daily ending balance

What the AI creates:

• View name: cash_balance (Classification: Calculate Groupwise)
• Uses window function (SUM() OVER (ORDER BY date))
• Calculates cumulative balance

Step 3: Calculate Cash Flow Trends

Prompt:

@[daily_cash_flow]
Calculate 7-day and 30-day moving averages for net cash flow
to smooth out daily volatility

What the AI creates:

• View name: cash_flow_trends (Classification: Calculate Groupwise)
• Adds moving averages using window functions
• Identifies trends beyond daily noise

Step 4: Visualize Cash Flow History

Prompt:

/visualize @[cash_flow_trends]
Create a combination chart:
- Area chart: daily net cash flow (light blue)
- Line: 30-day moving average (dark blue, thicker)
- Horizontal reference line at $0 (break-even)
- Shade negative cash flow days in light red

Step 5: Identify Seasonal Patterns

Prompt:

@[daily_cash_flow]
Analyze cash flow patterns by:
- Day of week (Mon-Sun)
- Week of month (1st, 2nd, 3rd, 4th)
- Month of year

Calculate average net cash flow for each period type

What the AI creates:

• View name: cash_flow_seasonality (Classification: Calculate Columns + Aggregate)
• Extracts date components
• Calculates averages per period

Sample insights:

period_type	period	avg_net_cash_flow
day_of_week	Monday	$8,200
day_of_week	Friday	$12,500 (payday!)
week_of_month	Week 1	$15,800 (monthly invoices paid)
week_of_month	Week 4	$3,200 (slowest)
month	December	$45,600 (year-end collections)
month	January	-$12,300 (post-holiday slump)

Step 6: Build Simple Forecast

Prompt:

@[cash_flow_trends] @[cash_flow_seasonality]
Create a 90-day cash flow forecast using:
- 30-day moving average as baseline
- Apply seasonal adjustments (day of week, week of month)
- Project from today forward
- Calculate projected ending balance each day

What the AI creates:

• View name: cash_flow_forecast (Classification: Combine + Calculate Columns)
• Generates future dates
• Applies seasonality patterns
• Projects balances

Sample forecast:

forecast_date	projected_inflows	projected_outflows	projected_net	projected_balance
2024-11-15	$42,100	-$35,800	$6,300	$482,600
2024-11-16	$39,200	-$38,100	$1,100	$483,700
2024-11-17	$45,800	-$41,200	$4,600	$488,300
...	...	...	...	...

Step 7: Flag Cash Flow Risks

Prompt:

@[cash_flow_forecast]
Identify risky periods where:
- Projected balance falls below $250,000 (minimum threshold)
- Net cash flow is negative for 5+ consecutive days
- Balance drops more than 20% from current level

Flag these as cash flow risks

What the AI creates:

• View name: cash_flow_risks (Classification: Calculate Groupwise + Select Filter)
• Identifies risk conditions
• Filters to problematic periods

Risk alert:

risk_period	projected_low_balance	days_below_threshold	risk_level
Dec 20-28	$235,400	9 days	High
Jan 8-15	$258,200	8 days	Medium

Action: Arrange $50K line of credit or accelerate receivables collection before Dec 20.

Step 8: Visualize Forecast with Confidence Bands

Prompt:

/visualize @[cash_flow_forecast]
Create a forecast visualization:
- X-axis: Date (historical + forecast)
- Y-axis: Cash balance
- Historical actual balance (solid line, dark blue)
- Forecasted balance (dashed line, blue)
- 80% confidence band (shaded light blue area)
- Horizontal line at minimum threshold ($250K, red)
- Vertical line marking "today" (separating actual from forecast)

Bonus: Scenario Analysis

Prompt:

Create three forecast scenarios:
- Best case: 20% higher inflows, 10% lower outflows
- Base case: current trends
- Worst case: 10% lower inflows, 20% higher outflows

Show all three projected balances side-by-side

What the AI creates:

• View name: cash_flow_scenarios (Classification: Calculate Columns)
• Calculates three projections
• Compares outcomes

Insight: Even in worst case, balance stays above threshold—company has adequate cushion!

What You Learned

Time series aggregation (daily, rolling averages) Running totals (cumulative window functions) Seasonality analysis (identifying patterns) Forecasting techniques (trend + seasonality) Risk identification (threshold-based alerts) Scenario planning (multiple projections) Confidence bands (uncertainty visualization)

---

Putting It All Together

You've now mastered three critical finance analytics workflows:

• Budget variance → Control spending
• Expense classification → Understand cost drivers
• Cash flow forecasting → Avoid shortfalls

Create a Finance Executive Dashboard

Prompt:

Create a comprehensive finance dashboard with:
- Budget vs actual summary by department
- Top over-budget categories requiring attention
- Spend breakdown by AI-categorized expense type
- 90-day cash flow forecast with risk flags
- Key metrics: burn rate, runway, liquidity ratio

---

What's Next?

Explore More

Try these follow-up analyses:

Budget Management:

• How does current run rate affect year-end projections?
• Which departments consistently underspend? (reallocate?)
• What's the correlation between budget variance and business outcomes?

Expense Optimization:

• Which vendor/category combinations have unusual pricing?
• Are there duplicate or redundant expenses?
• What's the spend per employee by department?

Cash Flow Improvements:

• How does receivables timing affect cash position?
• What's the impact of payment terms (net-30 vs net-60) on cash flow?
• Can we forecast cash flow impact of growth scenarios?

Apply to Your Data

Import your own financial data and:

1. Map your GL accounts or expense categories to these structures
2. Customize budget periods (monthly, quarterly, project-based)
3. Add company-specific forecasting rules (seasonality, growth rate)

Learn More

• Prompt Engineering Guide - Advanced techniques
• Use Cases - More examples
• FAQ - Common questions

---

Quick Reference: Prompts Used

Goal	Prompt Pattern
Budget vs actual	@[transactions/budgets] Join and calculate variance (actual - budget)
Variance analysis	@[variance] Calculate %, flag over budget, summarize by department
AI categorization	@[txns] Create AI Table that categorizes based on vendor/memo/amount
Merge AI categories	@[txns] @[ai_categories] Join and use COALESCE for complete categorization
Daily cash flow	@[txns] Group by date, sum inflows/outflows, calculate net
Running balance	@[daily] Calculate cumulative sum using window function
Moving averages	@[daily] Add 7-day and 30-day moving averages
Seasonality	@[daily] Extract date components, calculate averages by period
Forecast	@[trends] @[seasonality] Project future using baseline + seasonal adjustments

---

Congratulations! You've completed the finance analytics tutorial. Ready for another domain?

• E-commerce & Sales Analytics →
• SaaS & Subscription Analytics →
• Marketing Campaign Analytics →