- name
- officecli-financial-model
- description
- Use this skill when the user wants to build a financial model,
Financial Model Skill
Build formula-driven, multi-sheet financial models from scratch in Excel. Every number on every statement sheet is a formula referencing the Assumptions sheet. Output is a single .xlsx file with interconnected sheets -- Income Statement, Balance Sheet, Cash Flow Statement, and optional valuation or scenario analysis sheets.
BEFORE YOU START (CRITICAL)
Every time before using officecli, run this check:
if ! command -v officecli &> /dev/null; then
echo "Installing officecli..."
curl -fsSL https://raw.githubusercontent.com/iOfficeAI/OfficeCli/main/install.sh -o /tmp/officecli_install.sh && bash /tmp/officecli_install.sh && rm -f /tmp/officecli_install.sh
# Windows: irm https://raw.githubusercontent.com/iOfficeAI/OfficeCli/main/install.ps1 -OutFile "$env:TEMP\officecli_install.ps1"; & "$env:TEMP\officecli_install.ps1"
else
CURRENT=$(officecli --version 2>&1 | grep -oE '[0-9]+\.[0-9]+\.[0-9]+' | head -1)
LATEST=$(curl -fsSL https://api.github.com/repos/iOfficeAI/OfficeCLI/releases/latest | grep '"tag_name"' | sed -E 's/.*"v?([0-9.]+)".*/\1/')
if [ "$CURRENT" != "$LATEST" ]; then
echo "Upgrading officecli $CURRENT -> $LATEST..."
curl -fsSL https://raw.githubusercontent.com/iOfficeAI/OfficeCli/main/install.sh -o /tmp/officecli_install.sh && bash /tmp/officecli_install.sh && rm -f /tmp/officecli_install.sh
else
echo "officecli $CURRENT is up to date"
fi
fi
officecli --versionUse When
- User provides assumptions and asks for financial projections or a financial model
- User asks for an "income statement", "balance sheet", or "cash flow statement"
- User asks for DCF valuation, WACC calculation, or discounted cash flow analysis
- User asks for a cap table, waterfall analysis, or fundraising model
- User asks for scenario analysis, sensitivity table, debt schedule, or break-even model
Do NOT Use When
| User Request | Correct Skill |
|---|---|
| CSV data to dashboard / charts | officecli-data-dashboard |
| Edit/modify an existing .xlsx | officecli-xlsx (editing.md) |
| KPI dashboard or metrics summary | officecli-data-dashboard |
| 1-2 sheet visualization from existing data | officecli-data-dashboard |
| Word document or PowerPoint | officecli-docx / officecli-pitch-deck |
What This Skill Produces
A single .xlsx file with 4-10 interconnected sheets:
| Sheet Type | Purpose | Key Characteristic |
|---|---|---|
| Assumptions | All hardcoded inputs in one place | Blue font (0000FF) on every input cell |
| Income Statement | Revenue through Net Income | All rows are formulas referencing Assumptions |
| Balance Sheet | Assets, Liabilities, Equity | Must balance every period; includes check row |
| Cash Flow Statement | Operating, Investing, Financing | Ending Cash must equal BS Cash |
| DCF / Valuation | WACC, FCF, Terminal Value, Equity Value | Named ranges for key inputs |
| Sensitivity Table | 2-variable grid of implied values | Each cell is a self-contained formula |
| Scenarios | Dropdown-driven Base/Bull/Bear | IF/INDEX formulas reference dropdown |
| Error Checks | Balance, cash reconciliation, ISERROR scan | "ALL CLEAR" or "ERRORS FOUND" summary |
| Dashboard / Charts | Visual summary of model outputs | Charts use cell range references |
ALL values on statement sheets are formulas. The only hardcoded numbers are on the Assumptions sheet.
Core Concepts
- Assumptions-First Architecture -- ALL hardcoded inputs go on the Assumptions sheet. Every other sheet references Assumptions. Changing one assumption recalculates the entire model.
- Financial Color Coding --
font.color=0000FF(blue) for inputs,font.color=000000(black) for formulas,font.color=008000(green) for cross-sheet references. Non-negotiable convention. - Formula Chain Integrity -- Every derived value traces back to the Assumptions sheet through an unbroken chain of formula references.
- Error Checking -- Balance checks (Assets = Liabilities + Equity), cash reconciliation (CF ending cash = BS cash), and ISERROR scans on every sheet.
- Batch-First Workflow -- Use heredoc batch for ALL multi-cell operations, especially cross-sheet formulas. Verify after each batch.
Workflow Overview
Phase 1: Understand -- Identify model type (3-statement, DCF, cap table, scenario). Determine which sheets are needed and the formula dependency chain.
Phase 2: Plan -- Map sheet structure, column layout (labels + year columns), and cross-sheet formula dependencies. Define the build order.
Phase 3: Build -- Follow the mandatory 10-step build sequence in creating.md Section A.7. Build in layers: structure, assumptions, formulas (IS then BS then CF), formatting, charts, protection, raw-set, validate.
Phase 4: QA -- Run the QA checklist: validate, formula error scan, cross-sheet verification, balance check, cash reconciliation, chart data check.
Phase 5: Deliver -- Deliver the .xlsx file. Note that formulas recalculate on open (fullCalcOnLoad is set).
Quick Reference: Key Warnings
| Warning | Detail |
|---|---|
Cross-sheet ! escaping | Use heredoc batch for ALL cross-sheet formulas. Verify with officecli get after each batch. |
| Batch size limit | 8-12 operations per batch, non-resident mode. Larger batches have ~33% failure rate. |
| Batch JSON values | ALL values must be strings: "true" not true, "24" not 24 |
| fullCalcOnLoad + iterate | MANDATORY. Always use //x:definedNames --action insertafter (financial models always have named ranges) |
| Blue inputs / black formulas | font.color=0000FF on Assumptions inputs, font.color=000000 on all formula cells |
| Balance sheet must balance | Explicit check formula: =TotalAssets - TotalLiabilities - TotalEquity must equal 0 |
| Cash reconciliation | CF ending cash must equal BS cash for every period |
| No Excel Data Tables | Sensitivity tables must be manual formula grids. Each cell is an explicit self-contained formula. |
Number format $ quoting | Use heredoc batch or single quotes to prevent shell expansion of $ |
| Named ranges required | Define for all key assumptions (WACC, growth rates, tax rate). Required for auditability. |
| Column widths | No auto-fit. Set explicitly: labels=22-28, numbers=14-18, year headers=12-14 |
| formulacf no font.bold | Use fill + font.color only. font.bold causes validation errors. |
| raw-set ordering | activeTab and calcPr MUST be the absolute last commands |
| BS Cash = CF Ending Cash | BS Cash ALWAYS equals =Cash Flow!B19, including Year 1. Never use cash-as-plug or reference Assumptions directly. |
Chart title $ in shell | Use heredoc batch for chart titles containing $ to prevent shell expansion. |
Known Issues
| Issue | Workaround |
|---|---|
! escaping in cross-sheet formulas | Always use heredoc batch. Verify with officecli get. |
| Batch failure at scale | Keep batches to 8-12 ops. Non-resident mode. Retry individually on failure. |
| Cannot rename sheets | Plan sheet names upfront before creation. |
| Sensitivity tables are manual | Each cell needs an explicit formula. No Excel DATA TABLE support. |
| Chart series fixed at creation | Cannot add series later. Plan all series before add. |
| Formula cached values blank | view text shows blank for formula cells. This is normal. Set fullCalcOnLoad. |
| Waterfall chart totals | Cannot mark bars as totals programmatically. Use color convention. |
| Circular references | Use <calcPr iterate="1" ...>. Design model to avoid unnecessary circularity. |
Chart title $ stripping | Shell expands $ in --prop title. Use heredoc batch for chart titles with $, or omit $ from titles. |
Full Guide
Read creating.md and follow it step by step. It contains setup conventions, core financial statement patterns, advanced patterns (DCF, sensitivity, scenarios), chart recipes, QA checklist, and known issues with workarounds.
References
- creating.md -- Complete financial model creation guide
- xlsx SKILL.md -- General xlsx reading, editing, and QA reference
- data-dashboard creating.md -- Batch syntax, chart presets, and CF basics