# Super Prompt: Build a 5-Year Operating Model (3-Statement) from a Template Use this prompt whenever building or refreshing a company operating model from the Financial Edge 3-statement template using the Felix connector. Paste it in full at the start of a new session, editing only the company name/ticker and file path. --- Using the Felix connector and the attached 3-statement model template (with the built-in interest/circularity mechanism), build a 5-year operating model for **[COMPANY NAME / TICKER]**, with the first 3 forecast years aligned to Felix consensus estimates (revenue, EBITDA, EPS) and the remaining 2 years extrapolated with a clearly flagged, disclosed assumption. Comply with the excel-formatting-standards skill and use the 3sm skill to help build and link the statements. Follow every requirement below; do not skip any of them. ## Data sourcing and verification - Pull 4 years of historical actuals from the company's last four 10-Ks via Felix (`get_financial_model`, `get_available_periods`, `list_filings`), plus consensus (`get_company_snapshot`) for the first 3 forecast years. - Felix's compact financial-model tool can return dimensional duplicates (e.g. two different "Total assets" values for the same period, from segment or member breakdowns). Do not guess which is correct. Cross-check every material historical figure against the actual filing: fetch the filing's `FilingSummary.xml` to find the R-file numbers for the Consolidated Balance Sheets / Statements of Income / Statements of Cash Flows, then fetch those R-files directly (`https://www.sec.gov/Archives/edgar/data///R#.htm`) and read the actual reported line items and totals. - Verify every hardcoded historical number this way before using it, not just the headline ones. In one build, this caught: a sign error on a noncontrolling-interest line (reported as a loss, not income), long-term debt double-counting current maturities that were already inside current liabilities, and an "other long-term liabilities" line that included noncontrolling interest when NCI belongs in equity under US GAAP (ASC 810), not liabilities. - Do not rely on a subagent's or an earlier pass's self-report that "the balance sheet ties to zero" without independently re-verifying it yourself. Recalculate and check. ## Balance sheet integrity - The balance sheet must tie to zero (Assets = Liabilities + Equity) in every historical period AND every forecast period. Check this explicitly, in a formula-driven check row, not by inspection. - For historical periods, hardcode non-current assets and total equity directly to each 10-K's reported totals (Total Assets less current assets; Total Equity including NCI). Do not build them as a roll-forward-plus-plug unless you can verify the plug ties exactly to the reported number. A roll-forward with a zeroed or estimated "other" line will silently drift from the real balance sheet. - LibreOffice's headless `--convert-to` recalculation does NOT reliably converge circular/iterative formulas (interest on average balances, revolver sizing). Do not trust its output for the circular part of the model. Instead, write an independent Python re-implementation of the circularity (interest <-> cash <-> revolver <-> net income <-> equity) and iterate it to convergence outside Excel to confirm the balance check is genuinely zero in every forecast year. Non-circular historical periods can be trusted from a LibreOffice recalculation. ## Formatting (font color / shading) - Blue font is reserved ONLY for cells the user could reasonably change: the forecast assumption block (rows/cells that drive projections) and the consensus-anchor inputs (e.g. hardcoded consensus revenue for the first 3 forecast years). Historical actuals are black, even though they are hardcoded, because they are fixed reported facts, not editable assumptions. Source them instead via a clickable hyperlink attached to the cell. - Attach source hyperlinks using the cell's `.hyperlink` attribute (in openpyxl or equivalent), never the `HYPERLINK()` formula function, on any cell that feeds a `SUM()` range elsewhere. `HYPERLINK()` turns the cell into text, which `SUM()` silently ignores, corrupting totals. `.hyperlink` is a purely visual/clickable attribute that does not change the cell's numeric value or type. - The "output area" (the actual income statement / balance sheet / cash flow rows) should never contain a bare hardcoded number for a forecast line unless it is a direct, disclosed input (like the consensus revenue anchor). Every other forecast output line should be a formula referencing an assumption cell elsewhere, so the assumption can be changed once and flows through. Assumptions that are single fixed figures (not rates) are fine to keep as flat dollar hardcodes in the assumptions block; just make sure the output row references them (black) rather than repeating the hardcode inline. - Date/period header row: apply one consistent fill color and font color (typically dark fill, white text) across every single column, historical and forecast alike. Check that no column was missed (a common failure mode when a new column is inserted or extended later). - Assumption-block shading (a pale background marking the "input area"): apply it ONLY to the forecast columns and ONLY to genuine assumption rows. Do not let it bleed into historical columns, and make sure it covers every assumption row consistently, including any new assumption rows added later. Re-verify this after any row insertion, since inserted rows do not automatically inherit neighboring shading. - Number formats: standard figures `#,##0.0_);(#,##0.0);0.0_);@_)`; EPS/beta/WACC `#,##0.00_);(#,##0.00);0.00_);@_)`; percentages `0.0%`; multiples `0.0"x"`. - Subtotals: bold with a top border. Grand totals: bold with a double-underline bottom border. - Column A holds section headings only; line-item labels start in column B. ## Structural content requirements - Show EBITDA and EBITDA margin directly on the income statement (immediately after EBIT), not only in a separate credit-stats section. If a separate stats section already computes EBITDA independently, have it reference the income-statement EBITDA row instead of recalculating, to avoid two sources of truth. - Give "other non-operating income/expense" its own forecast assumption (percent of revenue), not an inline hardcoded ratio baked into the output formula. Default the forecast assumption to zero unless there is clear, disclosed evidence the historical amount will recur. If zeroing it materially diverges the modeled EPS from consensus, say so explicitly and quantify the gap, since the default is a deliberate, conservative choice, not an oversight. - Give noncontrolling interest (NCI) on the income statement its own forecast assumption row too. Prefer a simple fixed dollar hardcode (blue, in the assumptions block) over a percent-of-revenue rate unless asked for a rate; the important thing is that the output row references the assumption with a plain formula (black), never repeats the number directly. - Do not build a residual "other non-current asset changes" or "other equity items" plug for the forecast years at all, and do not do a full historical reconciliation analysis for them either. For history, just hardcode the actual reported ending balances (see Balance sheet integrity above). For the forecast, let non-current assets roll forward as beginning + capex - D&A only, and equity roll forward as beginning + net income - dividends - buybacks only. - Remove any placeholder "other non-cash items" line from the cash flow statement; operating cash flow should be net income + D&A + change in net working capital only, unless a real non-cash item is identified and sourced. - Only show the interest-on-average-balances / circularity calculation block for the forecast years. Historical net interest expense is the actual reported figure and does not need to be derived; leave that section blank for historical columns. ## Process notes for inserting new rows into an existing formula-heavy sheet - Inserting rows with openpyxl (`ws.insert_rows`) does NOT automatically update formula text elsewhere in the sheet to point at the new row positions, and it can corrupt hyperlinks attached to cells that get physically relocated. Before inserting rows: capture every cell's hyperlink target/tooltip and strip the hyperlinks. After inserting, do a single regex-based pass over every formula in the sheet, remapping any bare (non-sheet-qualified) cell reference whose row number is at or above an insertion point by the cumulative number of rows inserted above it. Then reattach the saved hyperlinks at their new, correctly mapped coordinates. Verify afterward with a full recalculation that no `#REF!` or other formula errors were introduced. ## Delivery - Save the finished file under the outputs location so it is delivered automatically, or explicitly send it if it was produced elsewhere in the environment. - Maintain a Sources tab (or equivalent) documenting: which filing each historical figure came from, the consensus anchors used and their source, every judgment call and assumption that is not directly sourced (with the rationale), and a running note of any corrections made during review so the audit trail survives multiple rounds of feedback.