Prompt Engineering Financial Model

1. Define Purpose & Scope

• Purpose: What decisions or stakeholders will this model serve? (e.g., fundraising, internal budgeting, strategic planning)

• Scope: Which business units, product lines, geographies, or time horizons does it cover? (e.g., 3-5 years, monthly or quarterly granularity)

2. Gather Key Inputs & Assumptions

• Market size & growth: TAM, SAM, SOM estimates

• Customer acquisition: CAC, channels, conversion rates

• Pricing & Revenue: Pricing tiers, subscription models, upsell rates

• Customer behavior: Churn rates, retention, lifetime value (LTV)

• Costs: Fixed & variable costs, COGS, SG&A, R&D, sales team expenses

• Capital expenditures (CAPEX): Infrastructure, equipment, software licenses

• Funding assumptions: Equity rounds, debt, convertible notes, interest rates

• Tax & depreciation: Applicable rates, schedules

3. Revenue Modeling

• Break down revenue by streams: subscriptions, services, upsells, one-time fees

• Model customer cohorts and growth: new customers, churn, expansion

• Forecast Monthly Recurring Revenue (MRR) and Annual Recurring Revenue (ARR) if relevant

• Include seasonality or market effects if applicable

4. Expense Modeling

• COGS: Direct costs linked to revenue (hosting, support)

• Operating expenses: Marketing, sales, payroll, general & admin

• Model hiring plans, salary growth, and commissions

• Include variable costs scaling with revenue or customers

5. Capital Expenditure & Depreciation

• Forecast capital investments by category and timing

• Model depreciation and amortization schedules

6. Funding & Capital Structure

• Include equity raises, convertible notes, debt schedules

• Model dilution and ownership (cap table) changes

• Interest payments and repayment schedules for debt

7. Financial Statements Integration

• Construct Profit & Loss (P&L) statement

• Build Balance Sheet projections

• Prepare Cash Flow forecasts (operating, investing, financing)

8. Key Performance Indicators (KPIs) & Metrics

• Calculate relevant KPIs such as:

  • CAC, LTV, CAC:LTV ratio

  • Churn rate, retention rate

  • Gross margin, contribution margin

  • SaaS Quick Ratio (if SaaS)

  • Burn rate, runway

• Dashboard with visualizations and summary tables

9. Scenario Analysis & Sensitivity Testing

• Create scenarios: Base case, optimistic, pessimistic

• Test key variables’ impact on financial outcomes (e.g., price changes, churn, CAC)

• Identify break-even points and critical thresholds

10. Documentation & Validation

• Document all assumptions and data sources clearly

• Validate model logic and calculations with stakeholders

• Include version control and update procedures

11. Presentation & Reporting

• Prepare summary dashboards and charts

• Create an executive summary with key insights

• Prepare detailed backup sheets for auditors or investors

Optional: Automation & Integration

• Link to live data sources or CRM/ERP systems for real-time updates

• Set up alerts for KPI thresholds or budget overruns

---

Prompt ENGINEERING

1. Assumptions & Drivers

"Create an Assumptions & Drivers tab for a financial model of a {{business_type}} business called {{business_name}}. Include key inputs such as:

• Market growth rate: {{market_growth_rate}}% per year

• Customer acquisition cost (CAC): ${{cac}} per customer

• Monthly churn rate: {{churn_rate}}%

• Average subscription price: ${{avg_price}} per month

• Salary growth rate: {{salary_growth_rate}}% annually

• Tax rate: {{tax_rate}}%

• Initial number of customers: {{initial_customers}}
  Organize inputs clearly with labels, and format cells for easy updating."

2. Revenue Model

"Build a Revenue Model tab for {{business_name}}, a {{business_type}} company. Model revenue based on:

• Monthly new customers: starting at {{monthly_new_customers}} and growing by {{customer_growth_rate}}% monthly

• Average revenue per user (ARPU): ${{arpu}} per month

• Churn rate: {{churn_rate}}% monthly
  Include cohort-based monthly recurring revenue (MRR) projections for 36 months, and break down revenue by subscription tiers if applicable."

3. Cost of Goods Sold (COGS)

"Create a COGS tab for {{business_name}} reflecting direct costs proportional to revenue. Include:

• Hosting fees: ${{hosting_fee_per_user}} per user per month

• Payment processing fees: {{payment_fee_percentage}}% of revenue

• Customer support costs: ${{support_cost_per_user}} per user per month
  Calculate monthly total COGS based on projected customers and revenue."

4. Operating Expenses (OPEX)

"Design an Operating Expenses tab for {{business_name}}. Include monthly expenses such as:

• Marketing budget starting at ${{marketing_budget}} with growth rate of {{marketing_growth_rate}}% monthly

• Sales team salaries: ${{avg_sales_salary}} per salesperson, starting with {{number_of_salespeople}} sales reps

• General & Administrative expenses: ${{g_and_a_expenses}} per month

• Research & Development expenses: ${{rnd_expenses}} per month
  Include hiring plans and salary escalations over time."

5. Capital Expenditures (CAPEX)

"Build a CAPEX tab to project capital spending for {{business_name}}. Include planned purchases such as:

• Equipment: ${{equipment_cost}} in month {{equipment_purchase_month}}

• Software licenses: ${{software_license_cost}} annually starting month {{software_license_start_month}}
  Model depreciation over {{depreciation_period}} months using straight-line method."

6. Funding & Capital Structure

"Create a Funding & Capital Structure tab for {{business_name}}. Include:

• Pre-seed funding amount: ${{preseed_amount}} raised in month {{preseed_month}}

• Seed round: ${{seed_amount}} in month {{seed_month}} with valuation ${{seed_valuation}}

• Convertible notes: ${{convertible_note_amount}} at {{interest_rate}}% interest

• Equity dilution calculations
  Build a cap table showing ownership percentages before and after each funding round."

7. Financial Statements

"Construct an integrated Financial Statements tab for {{business_name}} showing:

• Profit & Loss statement with revenue, COGS, OPEX, EBITDA, taxes, and net income monthly over 36 months

• Balance Sheet including assets, liabilities, and equity based on CAPEX and funding

• Cash Flow Statement covering operating, investing, and financing activities
  Ensure all statements link dynamically to other tabs."

8. KPIs & Dashboards

"Design a KPIs & Dashboard tab for {{business_name}} summarizing key metrics such as:

• Customer Acquisition Cost (CAC)

• Lifetime Value (LTV)

• Monthly Churn Rate

• Gross Margin %

• SaaS Quick Ratio

• Burn Rate and Cash Runway
  Include charts to visualize monthly MRR growth, churn trends, and cash position."

9. Scenario Analysis

"Create a Scenario Analysis tab for {{business_name}} with three cases: Base, Optimistic, and Pessimistic. For each scenario, vary key assumptions:

• Growth rate ({{growth_rate_base}}, {{growth_rate_optimistic}}, {{growth_rate_pessimistic}})

• Churn rate ({{churn_rate_base}}, {{churn_rate_optimistic}}, {{churn_rate_pessimistic}})

• CAC ({{cac_base}}, {{cac_optimistic}}, {{cac_pessimistic}})
  Show the impact on revenue, expenses, and cash flow over 36 months."

10. Supporting Schedules / Backups

"Build a Supporting Schedules tab for {{business_name}} containing detailed calculations backing the main model, such as:

• Salary escalation schedules by role

• Depreciation and amortization tables

• Tax calculations based on {{tax_rate}}%

• Detailed breakdown of marketing and sales commissions"