Family Cash Flow with Automatic Projection in Excel
ESCOPO
This prompt turns real financial data into a complete technical project to create or improve a personal or family cash flow spreadsheet in Microsoft Excel. It guides the AI to deliver a robust tab structure, normalized tables, categorization rules, ready-to-paste formulas, automatic projections, and financial health indicators.
It is intended for financial professionals, consultants, planners, advanced Excel users, and families who want to control income, expenses, debts, investments, and short- to medium-term goals. The result is adapted to the user’s stated reality, without assuming missing data exists.
In addition to the operational structure, the prompt requires data validation, conditional formatting, an executive dashboard, scenarios, and optional VBA automations. The instructions are delivered in an implementable way, with cell and structured table references compatible with Excel in Brazilian Portuguese.
Conteudo
Prompt principal
Act as a senior specialist in personal financial modeling, budgeting, and spreadsheet engineering in Microsoft Excel. Based on my real data, design a professional personal or family cash flow spreadsheet, with actual versus planned control, automatic balance projection, scenarios, and a management dashboard. Your response must be practical and directly implementable in Excel, without generic explanations. First, request or use the information below. If I do not provide any item, explicitly state the adopted assumption, highlight it as [PREMISSA], and keep the model parameterizable: (1) Excel version and formula language; (2) currency, start date, projection horizon, and update frequency; (3) family members, bank accounts, cards, cash/wallet, and investments that should make up the cash balance; (4) initial balance per account and available credit limit; (5) recurring, variable, and extraordinary income, with expected dates; (6) fixed, variable, annual, installment-based, and occasional expenses, including amount, due date, owner, category, cost center, and payment method; (7) loans, financing, and debts; (8) reserve, payoff, travel, or purchase goals; (9) desired categories and subcategories; (10) deficit tolerance and alert rules. Then, produce the project following this framework exactly: 1. DIAGNOSIS AND ASSUMPTIONS: present a summary of the data received, identified gaps, assumptions used, and data quality risks. Clearly distinguish cash flow (inflows and outflows by payment/receipt timing) from budget (planned). 2. SHEET ARCHITECTURE: define the recommended structure, with purpose, fields, associated Excel table, and relationships between sheets. Include at minimum: Configuracoes, Cadastros, Lancamentos, Recorrencias, Parcelamentos, Contas_Saldos, Orcamento, Projecao, Metas, Dashboard, and Instrucoes. Use technical table names without spaces, for example tbLancamentos and tbCategorias. Indicate which columns are manually filled, calculated, or populated by drop-down lists. 3. DETAILED BUILD: for each sheet, provide the suggested section positions, column headers, data type, numeric/date format, input rule, and data validations. Structure Lancamentos as a single transactional base, with ID, expected date, actual date, competence, type, nature, status, account, category, subcategory, description, expected amount, actual amount, recurrence, current installment, total installments, and notes. Define unambiguous rules for income, expenses, transfers between accounts, card payments, reversals, and future entries, avoiding double counting. 4. READY-TO-PASTE FORMULAS: provide complete formulas in Excel PT-BR, using semicolon as the separator and structured references whenever possible. For each formula, indicate the destination cell/column, its purpose, and how to replicate it. Include formulas for accumulated balance by account and consolidated balance, monthly competence, month/year, effective value according to status, budget versus actual, daily and monthly projected balance, overdue identification, negative balance alerts, income commitment, savings rate, and goal progress. Prioritize functions compatible with Microsoft 365; when using modern functions such as FILTRO, ÚNICO, LET, or SEQUÊNCIA, provide an alternative compatible with older versions. 5. AUTOMATIC PROJECTION: explain how to generate future events from recurring items and installments without duplicating records. Recommend a preferred approach: Power Query, dynamic formulas, or VBA, justifying it according to my Excel version. Model Base, Conservative, and Optimistic scenarios, with editable parameters for income variation, expenses, and inflation. The projection must show at least the next 12 months and highlight the first month with a possible deficit. 6. DASHBOARD AND ALERTS: describe suitable KPIs, summary tables, and charts: current balance, projected balance, income, expenses, monthly result, spending by category, fixed versus variable expenses, reserve evolution, and upcoming due dates. Indicate data sources, filters/slicers, and conditional formatting rules with colors and objective thresholds. Do not recommend decorative charts. 7. OPTIONAL AUTOMATION: if VBA is appropriate, provide complete, commented, and safe VBA code to generate recurring items/installments, update projections, or register an entry. State exactly where to insert the code, the module name, how to run it, required permissions, and how to create a backup. If VBA is not necessary, explain why and prioritize a native solution. Deliver the response in this order: A) assumptions and decisions; B) sheet map in table format; C) build instructions by sheet; D) formulas in code blocks; E) projection and scenarios; F) dashboard; G) optional VBA/Power Query; H) testing and validation checklist. Be precise with sheet names, table names, columns, and references. Do not invent financial values or treat financial advice as an investment recommendation; focus on control, visibility, and model quality.
