Annual Departmental Corporate Budget in Excel
ESCOPO
Ideal for FP&A professionals, controllers, finance teams, administrative managers, and consultants, this material provides practical instructions to build each tab, import data, apply formulas in PT-BR Excel, set up validations, pivot tables, indicators, and optional VBA automations.
Conteudo
Prompt principal
Act as a senior expert in FP&A, controllership, and advanced modeling in Microsoft Excel. Your mission is to design a fully auditable, scalable annual corporate budget workbook by department, ready for use in a Brazilian corporate environment. Do not deliver generic recommendations: produce technical, actionable instructions so I can build the file directly in Excel. Before developing the solution, use the data I paste below. If there are critical gaps, list objective questions in a maximum of one initial section called "Pendências de dados"; then, assume reasonable premises, clearly identifying them, and deliver an implementable version anyway. DADOS E CONTEXTO A FORNECER PELO USUÁRIO 1. Company/industry, currency, budget year, and fiscal calendar (January-December or other). 2. Departments, cost centers, responsible managers, and any hierarchy (executive area, area, department). 3. Budget lines or chart of accounts: revenue, payroll, benefits, third-party services, operating expenses, CAPEX, taxes, and other applicable categories. 4. Monthly historical actuals, prior budget, and existing forecasts, if available. 5. Assumptions: inflation/adjustments, headcount, average salary, hires/terminations, seasonality, correction indexes, exchange rate, growth, revenue targets, and approval levels. 6. Data source (ERP, CSV, payroll system, manual input), Excel version, and whether macros/VBA are allowed. 7. Business rules: locks, allocations, zero-based or incremental budgeting, review frequency, department limits, and priority indicators. [PASTE HERE THE REAL DATA, TABLES, AVAILABLE COLUMNS, AND COMPANY RULES] Follow the framework below strictly. ETAPA 1 — DIAGNÓSTICO E PREMISSAS Summarize the model objective, the scope covered, the received assumptions, and the assumed assumptions. Identify quality risks such as inconsistent chart of accounts, missing relationship keys, duplicates, missing months, or mixing CAPEX and OPEX. Also define the conventions: currency, rounding, financial sign, date format, file naming, and version control. ETAPA 2 — ARQUITETURA DO ARQUIVO Design the ideal sheet structure in order of creation. As a standard, evaluate the sheets: 00_Instruções, 01_Parâmetros, 02_Cadastros, 03_Histórico_Realizado, 04_Premissas, 05_Orçamento_Detalhado, 06_Rateios, 07_Consolidado, 08_Real_vs_Orçado, 09_Dashboard, and 10_Log_Aprovações. For each sheet, state the objective, users, fields/columns, data type, source, editable fields, and protected fields. Determine the integration keys, for example Year+Month+Cost Center+Account+Version, and recommend using Excel Tables with technical names, such as tbOrcamento and tbRealizado. ETAPA 3 — CONSTRUÇÃO OPERACIONAL NO EXCEL Deliver sequential setup instructions, including headers, formatting, freeze panes, slicers, data validation, named ranges, cell protection, and fill rules. Specify drop-down lists for department, cost center, account, version, and approval status. Explain how to structure the monthly budget with columns or a normalized table, choosing the most suitable alternative for the context and justifying it technically. ETAPA 4 — FÓRMULAS PRONTAS PARA COLAR Provide complete formulas ready to use in Brazilian Portuguese Excel, using semicolons as separators. For each formula, provide: destination sheet/cell or column, purpose, formula, and adaptation condition. Include, when applicable, SOMASES, SOMARPRODUTO, SEERRO, PROCX or ÍNDICE/CORRESP as an alternative, FILTRO, ÚNICO, LET, and date functions. Create calculations for monthly budget, annual budget, actuals, variance in value, percentage variance with division-by-zero handling, cumulative actuals, cumulative budget, forecast, budget balance, percent consumed, allocation, and overrun alert. Never invent incompatible references: use structured table references whenever possible. ETAPA 5 — CONSOLIDAÇÃO, CONTROLES E DASHBOARD Define the Consolidated view by department, category, account, month, and manager. Propose a Real x Budget x Forecast analysis matrix, with variances and management comments. Describe KPIs, targets, traffic-light ranges, and conditional formatting rules. Structure an executive dashboard with KPI cards, monthly trend chart, department ranking, expense composition, and key variance view. State the source of each visual, recommended filters, and the executive message the panel should answer. ETAPA 6 — AUTOMAÇÃO E GOVERNANÇA If macros are allowed, provide complete VBA code, commented and ready to paste, with exact installation and execution instructions. Prioritize automations such as refreshing queries/pivot tables, validating required fields, recording submission date and user, exporting the dashboard to PDF, and generating an overrun alert. If VBA is not allowed, present an alternative with Power Query, validations, and formulas. Include backup rules, permissions, approval trail, and a monthly close checklist. FORMATO OBRIGATÓRIO DA RESPOSTA Organize the delivery into: 1) executive summary; 2) pending items and assumptions; 3) sheet map in table format; 4) numbered construction roadmap; 5) field dictionary; 6) ready-to-use formulas; 7) dashboard; 8) automation; 9) quality controls; 10) implementation checklist. Be specific, consistent, and pragmatic. Do not use formulas in English, do not propose features that do not exist in Excel, and do not omit model maintenance instructions.
