Capturing Templates from Existing Spreadsheets

Guide for extracting skill templates from existing Excel financial models.

---

Overview

When you have a well-built Excel model, you can capture its structure and logic to create a reusable skill template. This process involves:

1. Analyzing the spreadsheet structure
2. Documenting the layout
3. Extracting formulas
4. Recording formatting
5. Creating the skill file

---

Step 1: Analyze the Spreadsheet

Identify Structure

Open the workbook and document:

Example Analysis

Workbook: LBO_Model.xlsx

Sheets:
1. Assumptions (inputs)
2. Sources & Uses (transaction structure)
3. Operating Model (projections)
4. Debt Schedule (debt paydown)
5. Returns (IRR/MOIC)

Cross-References:
- Sources & Uses → Assumptions (entry multiple, EBITDA)
- Debt Schedule → Assumptions (interest rates)
- Returns → Debt Schedule (ending debt)

---

Step 2: Document the Layout

Create ASCII Diagram

Convert each sheet to ASCII format:

Sheet: Assumptions (B2:E25)

Row 2:  LBO ASSUMPTIONS
Row 3:  (blank)
Row 4:  TRANSACTION
Row 5:  Entry EV/EBITDA Multiple       10.0x      ← Input
Row 6:  LTM EBITDA ($M)                $100.0     ← Input
Row 7:  Purchase Price (EV)            $1,000.0   ← =C5*C6
Row 8:  Transaction Fees (% EV)        2.0%       ← Input

Tips for ASCII Layouts

1. Start at Row 2, Column B - Leave margins
2. Show Row Numbers - For reference in formulas
3. Mark Inputs vs. Formulas - Use arrows (←) with notes
4. Indicate Blank Rows - Use (blank) explicitly
5. Include Sample Values - Makes layout clearer

---

Step 3: Extract Formulas

Document Key Formulas

For each calculated cell, record:

Formula Categories to Capture

1. Links - Cross-sheet references
2. Drivers - Growth rates, margins
3. Calculations - Sums, products
4. Plugs - Balancing figures
5. Outputs - Final results (IRR, share price)

Recording Format

### Purchase Price

=Entry_Multiple * LTM_EBITDA Excel: =C5*C6

### Interest Expense (BOP-based)

=Beginning_Balance * Interest_Rate Excel: =D5*Assumptions!$C$15

---

Step 4: Record Formatting

Document Styles

Inspect each element and record:

Capture Number Formats

Select each cell type and check Format Cells > Number:

Note Conditional Formatting

Document any conditional rules:

### Conditional Formatting

| Range | Condition | Format |
|-------|-----------|--------|
| C25 | =0 | Green fill |
| C25 | <>0 | Red fill |
| IRR cells | >20% | Green text |

---

Step 5: Identify Dependencies

Map Cross-Sheet References

Create a dependency diagram:

Assumptions
    ↓ (Entry Multiple, EBITDA, Rates)
Sources & Uses
    ↓ (Equity Investment)
Operating Model
    ↓ (EBITDA, FCF)
Debt Schedule
    ↓ (Ending Debt)
Returns

Document Link Patterns

## Cross-Sheet References

| Target Sheet | Source Cell | Source Sheet | Purpose |
|--------------|-------------|--------------|---------|
| Sources & Uses | =Assumptions!C5 | Assumptions | Entry multiple |
| Debt Schedule | =Assumptions!C15 | Assumptions | Interest rate |
| Returns | ='Debt Schedule'!K17 | Debt Schedule | Exit debt |

---

Step 6: Create the Skill File

Assemble Components

1. Write frontmatter with name and description
2. Create Overview explaining model purpose
3. Add Critical Errors based on common mistakes
4. Document Architecture using ASCII diagrams
5. List Formulas with Excel syntax
6. Include Formatting table
7. Add Validation checklist
8. Define Triggers

Template Assembly Checklist

[ ] YAML frontmatter complete
[ ] Overview section written
[ ] All sheets documented as ASCII
[ ] Cross-sheet dependencies mapped
[ ] Key formulas extracted with Excel syntax
[ ] Formatting table populated
[ ] Validation checks comprehensive
[ ] Common triggers defined
[ ] Tested with sample prompts

---

Extraction Tools & Tips

Excel Tips

View Formulas

• `Ctrl + `` to toggle formula view
• Or: Formulas > Show Formulas

Trace Dependencies

• Formulas > Trace Precedents (arrows show sources)
• Formulas > Trace Dependents (arrows show destinations)

Copy Formats

• Format Painter to inspect applied styles
• Right-click > Format Cells for detailed settings

Quick Extraction Method

1. Screenshot each sheet for reference
2. Export to text (Save As > .txt) for structure
3. Use formula auditing to trace links
4. Create template section by section

---

Example: Capturing a DCF Template

Original Spreadsheet Analysis

Sheet: DCF Model
Range: B2:K45

Structure:
- Header: B2-K2 (Years)
- FCF Section: B4-K15
- Valuation: B17-E30
- Sensitivity: G17-L30

Captured ASCII

### DCF Model Sheet

Row 2: x FY20A FY21A FY22A FY23A FY24A FY25E FY26E Row 4: Revenue $3,500 $4,500 $6,000 ... Row 5: % growth - 28.6% 33.3% ... Row 7: EBIT $1,320 $2,125 $2,888 ... ... Row 24: FCF $1,432 $2,020 $7,367 ... Row 26: Discount Factor 0.896 0.803 Row 27: PV of FCF $6,298 $6,647

Captured Formulas

## Key Formulas

### Revenue Growth

=(C4-B4)/B4

### Discount Factor

=1/(1+WACC)^(COLUMN()-6) Reference: =1/(1+$C$5)^(G1)

---

Validation After Capture

Before finalizing the skill, verify:

1. Recreate the model using only the skill instructions
2. Compare outputs to the original
3. Test edge cases (negative values, zeros)
4. Verify formatting matches
5. Confirm all links work across sheets

---

Common Capture Mistakes