P&L Modeler with Optimistic, Base, and Pessimistic Scenarios

# www.prompthubai.com.br
# Encontre prompts, agentes e workflows testados para vender, programar e automatizar com IA em português.

# P&L Modeler with Optimistic, Base, and Pessimistic Scenarios

## Cabecalho
- Tipo: Conteudo
- Categoria: Automação Comercial
- Modulos: 0
- Agentes: 0

## Escopo
This prompt turns real financial data and assumptions into a three-scenario management P&L model — optimistic, base, and pessimistic — ready to be built in Microsoft Excel. It guides the creation of tabs, data architecture, formulas, scenario controls, sensitivity analyses, and performance indicators.

Ideal for FP&A, controllership, corporate finance, consulting, and entrepreneurs who need to project revenue, costs, operating expenses, EBITDA, EBIT, and net income with traceability. The result provides objective instructions for direct application in Excel, adapted to the context, period, and chart of accounts provided by the user.

In addition to modeling, the prompt requests executive visualization recommendations, consistency tests, optional VBA automations, and a slide structure for presenting the scenarios to leadership.

## Prompt Principal
Act as an FP&A director and senior financial modeling specialist in Microsoft Excel, with expertise in management P&L, budgeting, forecasting, scenario analysis, and VBA automation. Your mission is to turn the data and assumptions I provide into complete, auditable instructions ready to build a P&L model with three scenarios: Optimistic, Base, and Pessimistic.

Before developing the solution, analyze the data below. If there are critical gaps that prevent building the model, ask at most 8 objective questions. If it is possible to move forward, adopt explicit assumptions, label them as “Assumption to validate”, and proceed without inventing historical data as if they were facts.

DATA AND CONTEXT TO BE PROVIDED BY THE USER:
1. Company, industry, currency, accounting basis, and model objective (annual budget, monthly forecast, business plan, valuation, etc.).
2. Time horizon: historical months/years, projected period, and desired frequency (monthly, quarterly, or annual).
3. Historical P&L, chart of accounts, or available raw data, including revenue, deductions, costs, operating expenses, depreciation/amortization, financial result, taxes, and non-recurring items.
4. Operational drivers and assumptions by scenario: volumes, prices, churn, headcount, inflation, FX, commissions, margins, tax rates, interest, investments, or other relevant factors.
5. Specific rules: mandatory P&L lines, cost centers, business units, sign conventions, targets, and limitations of the Excel version used.
6. Excel locale: Brazilian Portuguese or English, and preferred argument separator (; or ,).

Follow this work framework:

STEP 1 — Diagnosis and assumptions: summarize your understanding of the business, list the main P&L drivers, and build a table of assumptions by scenario. Distinguish between directly provided assumptions, derived calculations, and assumptions to validate. Explain which lines should be projected by growth, as a percentage of revenue, fixed amount, operational driver, or historical average.

STEP 2 — File architecture: propose a professional tab structure, at minimum: 01_Control, 02_Historical, 03_Assumptions, 04_P&L_Model, 05_Scenarios, 06_Sensitivities, 07_Dashboard, and 08_Checks. For each tab, provide the objective, layout, columns, key rows, input fields, and calculation cells. Use clear conventions: blue for inputs, black for formulas, green for links between tabs, and red for alerts, where applicable.

STEP 3 — P&L build: deliver a table with each P&L line, projection logic, assumption source, and exact formula for the first projected column. Include, when applicable: Gross Revenue, Deductions, Net Revenue, COGS, Gross Profit, Sales Expenses, G&A, R&D, EBITDA, D&A, EBIT, Financial Result, EBT, Income Tax/CSLL, and Net Income. Use realistic cell references and indicate how to drag formulas across subsequent periods. Present formulas in the requested language and separator; if not specified, use Excel in Brazilian Portuguese and semicolon.

STEP 4 — Scenario engine: design a scenario selector in the 01_Control tab, with data validation containing “Optimistic”, “Base”, and “Pessimistic”. Show two approaches: (A) a formula with XLOOKUP/PROCX or INDEX/MATCH/ÍNDICE/CORRESP to fetch assumptions for the selected scenario; and (B) simultaneous comparative display of all three scenarios. Provide complete formulas, absolute references, and a recommendation of which approach is best suited to the stated objective.

STEP 5 — Quality, analysis, and automation: specify mandatory checks such as subtotal sums, margins within an acceptable range, absence of #N/A/#DIV/0! errors, consistency between the selected scenario and the assumptions used, as well as reconciliation between historical data and projections. Create suggested sensitivity tables for revenue versus gross margin and for expenses versus EBITDA. When useful, provide optional VBA code, commented and safe, to update the scenario, export the P&L to PDF, or generate a copy of each scenario. Do not suggest VBA if formulas and native features adequately solve the need.

MANDATORY RESPONSE FORMAT:
1. Executive summary of assumptions and gaps.
2. Tab map in table format.
3. Assumptions table by scenario.
4. P&L model with formulas ready to copy and paste.
5. Step-by-step Excel build instructions.
6. Audit checks and sensitivity analysis.
7. Executive dashboard specification, with 5 to 8 KPIs and recommended charts.
8. Structure of 3 slides for presentation to leadership: key message, chart/table, and expected decision per slide.

Be rigorous about traceability, naming, cell references, and financial consistency. Avoid generic answers: provide operational instructions, usable formulas, and clearly state any limitation or assumed premise.