ecommerce-budgeting-forecasting

What this skill does

# Ecommerce Budgeting & Forecasting

## Overview

A rolling operating budget is the financial backbone of any well-run ecommerce business. Unlike a static annual budget, a rolling budget is continuously updated — typically a 12-month forward view that advances one month with each passing period. Combined with variance analysis comparing actuals to plan, it gives operators the visibility to make confident resource allocation decisions.

For ecommerce, the most operationally sensitive budget categories are marketing spend (which drives immediate revenue) and inventory purchases (which requires significant lead time). This skill guides you through building and maintaining these budgets using your platform's data and a connected accounting system.

## When to Use This Skill

- When building the annual operating plan for an ecommerce business
- When allocating a monthly marketing budget across channels with accountability
- When managing an inventory open-to-buy budget and tracking against it
- When replacing a spreadsheet-based budget process with an automated system
- When producing variance reports (budget vs. actuals) by the 3rd business day of each month
- When presenting a rolling forecast to your board or investors

## Core Instructions

### Step 1: Connect your platform data to your accounting system

Budgeting starts with accurate actuals. Connect your ecommerce platform to an accounting system to get the foundational data:

---

#### Shopify

1. **Connect to QuickBooks Online:** Go to the Shopify App Store and install **QuickBooks Online by OneSaas** or **Bench** — syncs Shopify orders, payouts, refunds, and fees into QuickBooks chart of accounts automatically
2. **Connect to Xero:** Install **Xero Bridge by Amaka** from the Shopify App Store — maps Shopify sales, refunds, shipping, and taxes to Xero accounts
3. **Built-in Shopify Finances:** Even without a separate accounting system, go to **Analytics → Finances summary** for monthly revenue, COGS (if costs entered per product), and gross profit — exportable to CSV for manual budget tracking

**For dedicated ecommerce financial tracking:**
- **Finaloop** (Shopify App Store): Automated bookkeeping app designed for Shopify merchants; generates P&L, balance sheet, and cash flow statements automatically
- **A2X** (Shopify App Store): Reconciles Shopify payouts to your accounting system with accurate period-matched journal entries

---

#### WooCommerce

1. **Connect to QuickBooks:** Use the **WooCommerce Payments** + QuickBooks integration or the **Zapier WooCommerce → QuickBooks** connection
2. **Connect to Xero:** Use the **WooCommerce Xero** extension ($79/yr) from WooCommerce.com — syncs orders, refunds, and customer invoices
3. **Export for manual budgeting:** Go to **WooCommerce → Analytics → Revenue** and export monthly revenue data to CSV; use this as your actuals baseline for budget vs. actuals comparison

---

#### BigCommerce

1. **Connect to QuickBooks:** BigCommerce App Marketplace → **QuickBooks Online by Webgility** — automates order sync and accounting entry posting
2. **Connect to Xero:** BigCommerce App Marketplace → **Xero by Amaka** or **Synder**
3. **Use BigCommerce Analytics:** Go to **Analytics → Store Overview** for monthly revenue actuals; export to CSV for budget tracking

---

### Step 2: Build the revenue budget

The revenue budget is the foundation — all other budget lines are derived from or constrained by revenue projections.

**Method: Growth from prior year with seasonality adjustment**

1. **Pull last 12 months of monthly revenue** from your platform (by channel if multi-channel)
2. **Calculate seasonal indices:** Divide each month's revenue by the annual average monthly revenue
   - Example: If average monthly revenue is $100K and December is $200K, December's seasonal index is 2.0
3. **Apply growth assumptions by channel:**
   - Your website: 20% YoY growth (based on marketing investment plan)
   - Amazon: 10% YoY growth
   - Wholesale: 5% YoY growth
4. **Multiply annual budget by seasonal index for each month**

Example spreadsheet structure:
```
Channel     | Annual Target | Jan Index | Jan Budget | Feb Index | Feb Budget | ...
Website     | $1,200,000    | 0.07      | $84,000    | 0.06      | $72,000    | ...
Amazon      | $600,000      | 0.06      | $36,000    | 0.05      | $30,000    | ...
Wholesale   | $200,000      | 0.10      | $20,000    | 0.08      | $16,000    | ...
```

**Tools for revenue forecasting:**
- **Google Sheets / Excel:** Build manually with SEASONALITY formulas or exponential smoothing
- **Shopify Analytics + Google Looker Studio:** Connect Shopify data to Looker Studio for visual trend analysis to inform growth assumptions
- **Float** (floatapp.com) or **Pulse** (pulseapp.com): Connect to accounting system; build revenue forecast by category

### Step 3: Build the marketing budget

Marketing budgets work best as a combination of variable spend (% of revenue) and fixed spend (brand/content).

**Variable marketing spend (performance channels):**
Set as a % of channel revenue. Typical ecommerce targets:
- Google Shopping/Search: 6–10% of website revenue
- Meta (Facebook/Instagram): 8–15% of website revenue
- TikTok Ads: 5–12% of website revenue
- Amazon Sponsored Products: 8–12% of Amazon revenue (TACOS)
- Affiliate: 4–6% of attributed website revenue

**Fixed marketing spend:**
- Email/SMS platform (Klaviyo, Postscript): Fixed monthly subscription cost
- Influencer/creator partnerships: Fixed monthly budget with seasonal multipliers (November: 2x, December: 1.5x, January: 0.5x)
- Content creation/agency retainer: Fixed monthly

**Budget rule:** Tie performance marketing budgets to revenue milestones. If revenue is tracking 20% above plan, the marketing team can spend more; if below plan, cut variable spend first before touching fixed costs.

### Step 4: Build the inventory open-to-buy budget

Open-to-buy (OTB) is the dollar amount of new inventory you are authorized to purchase in each period.

**Formula:** OTB = Planned Sales (at cost) + Planned End-of-Month Inventory - Beginning-of-Month Inventory

**Inputs needed:**
- Sales forecast (in units or at cost) by product category
- Current inventory on hand (from your platform's inventory report)
- Target weeks of cover (typically 8–12 weeks for standard products)
- Supplier lead time (typically 6–12 weeks for imports)

**Tools by platform:**
- **Shopify:** Use **Inventory Planner** (Shopify App Store) — automatically calculates OTB by product based on your sales forecast and current stock levels
- **WooCommerce:** Use **ATUM Inventory Management** (free plugin) — calculates OTB and reorder points; integrates with WooCommerce inventory
- **BigCommerce:** Use **SkuVault** or **Brightpearl** (App Marketplace) for OTB planning and purchase order management

### Step 5: Track budget vs. actuals monthly

Set up a monthly variance report that compares actuals (from your accounting system) to budget. This should be available by the 3rd business day of each month.

**Structure:**
```
Account         | Budget    | Actual    | Variance ($) | Variance (%) | Status
Website Revenue | $84,000   | $91,000   | +$7,000      | +8.3%        | Favorable
Amazon Revenue  | $36,000   | $31,000   | -$5,000      | -13.9%       | Unfavorable ⚠
Meta Ads Spend  | $12,600   | $14,200   | +$1,600      | +12.7%       | Watch ⚠
Payroll         | $45,000   | $45,000   | $0           | 0.0%         | On Budget
```

**Materiality thresholds for variance flags:**
- Flag variances > 10% AND > $5,000 in absolute dollars for management attention
- Flag variances > 20% for any line item regardless of dollar amount
- Require written commentary from budget owners on any flagged variance within 3 business days

**Tools for budget vs. actuals:**
- **QuickBooks Online:** Run **Reports → Profit & Loss vs. Budget** — automatically compares actuals to your entered budget
- **Xero:** Run **Reports → Budget Variance** — same functionality