The Challenge
Task 1: Defining Problem, Audience, and Scope
Problem Statement
Ecommerce FP&A teams spend days manually navigating complex, multi-tab financial models in Google Sheets to answer strategic business questions, trace formula dependencies, and run scenario analyses — work that is tedious, error-prone, and hard to scale across multiple brands or models.
Audience
The primary user is an FP&A Analyst or CFO at a mid-market ecommerce company (typically $5M–$500M revenue) who manages financial planning across direct-to-consumer (Shopify), marketplace (Amazon), and wholesale channels.
Their job function involves building and maintaining financial models, running scenario analyses for board decks, forecasting cash flow, and recommending strategic changes to ad spend, pricing, and channel mix. The part of their job we are automating is the formula-chain tracing, sensitivity analysis, and goal-seek optimization that currently requires deep manual spreadsheet work.
Evaluation Questions (Input / Expected Output)
These question-answer pairs form the basis for evaluating the agent's capabilities:
| User Input | Expected Output |
|---|---|
| What is the Cash Conversion Cycle, how do you calculate it? | Gives the formula for Cash Conversion Cycle by searching the web (Tavily) |
| What is EBITDA and how is it calculated in this model? | Explains EBITDA = EBIT + Depreciation + Amortization, references operations row 194. |
| What are the Business Levers for this business? | Lists Orders, AoV, CaC, Orders Per Customer, Repeat Purchase Rate, Discounts %, Refunds %, Shipping Income %. |
| I want to increase EBITDA by 10% while keeping cash above $1M. What should I change? | Runs goal-seek with 3 scenario combinations adjusting Business Levers, returns ranked solutions. |
| What if I raised product prices by $5 starting in June 2025? | Traces the AoV impact through Gross Sales, Net Revenue, Gross Profit, and EBITDA formulas. |
| How should I optimize my Amazon ad spend for a powdered drink brand? | Retrieves strategic guidance from RAG knowledge base on Amazon advertising strategy. |
| What are the latest trends in DTC ecommerce for CPG brands? | Uses Tavily to search current web sources for DTC ecommerce trends and summarizes findings. |
| Run a sensitivity analysis on Ad Spend vs Product Price impact on Gross Profit. | Generates a 2-variable sensitivity table showing Gross Profit at different Ad Spend and Price levels. |
| What is my current cash position and forecast? | Reads cash row from operations, returns current Actual and Forecast values. |