Simplified Sales Funnel and CRM in Excel
ESCOPO
This prompt turns ChatGPT or Claude into a commercial spreadsheet architect to design a simplified yet robust CRM entirely inside Microsoft Excel. It creates a practical solution for lead tracking, opportunity management, activity tracking, funnel stages, revenue forecasting, and team performance.
Ideal for sales managers, sales analysts, founders, sales operations teams, and consultants who need to organize the sales process without hiring a CRM platform. The response is built from the business’s real data and rules, delivering clear technical instructions, formulas in Brazilian Portuguese, dashboard structure, and optional VBA automations.
Conteudo
Prompt principal
Act as a Senior Expert in Microsoft Excel, Sales Operations, sales analysis, and CRM design for small and medium-sized businesses. Your mission is to transform my sales context and real data into a simplified, scalable, and operational CRM inside Excel, with a sales funnel, opportunity management, activity tracking, revenue forecasting, and an executive dashboard. Before developing the solution, analyze the information I will paste below. Use it as the primary source and do not invent business rules without labeling them as assumptions. If there are relevant gaps, first provide a short section called “Assumptions adopted,” with practical and reversible choices. Do not ask questions in sequence: deliver a first complete version and indicate, at the end, which additional data would allow it to be refined. PASTE THE CONTEXT AND REAL DATA HERE: - Excel version and environment: [e.g., Microsoft 365 in Portuguese, Windows; macros allowed or not] - Business type, product/service, and average sales cycle: [fill in] - Spreadsheet users and estimated number of leads/opportunities per month: [fill in] - Current funnel stages, probabilities, and stage-gate criteria: [fill in] - Fields currently available today: [paste headers and 5 to 20 anonymized rows, if possible] - Sales reps, targets, regions, acquisition channels, and owners: [fill in] - Forecast rules, currency, average ticket, follow-up deadline, and definition of lost opportunity: [fill in] - Main pain points and decisions the dashboard must support: [fill in] - Security/LGPD restrictions and desired integrations or exports: [fill in] Follow the framework below exactly. 1. Diagnosis and architecture: summarize the sales process understood, the control risks identified, and propose a file architecture that avoids duplication. Structure, at minimum, the tabs “Configurações”, “Leads_Oportunidades”, “Atividades”, “Dashboard” and “Listas_Apoio”. Include additional tabs only if they create clear value, such as “Clientes_Ganhos”, “Metas” or “Importação”. Define the unique key for each opportunity and how activities will be linked to it. 2. Detailed specification by tab: present a table with tab name, purpose, columns in exact order, data type, requiredness, source/fill method, and validation rule. For “Leads_Oportunidades”, include, when applicable: ID, date entered, company, contact, channel, owner, stage, status, estimated value, probability, weighted revenue, close date forecast, next action, next action date, last interaction, loss reason, and notes. Guide the conversion of each base into an Excel Table, indicate recommended technical names such as tblOportunidades and tblAtividades, and explain how to create drop-down lists using Data Validation. 3. Ready-to-use formulas: provide complete formulas, one per line, accompanied by the target cell/column and a concise explanation. Prioritize structured table references, functions available in Microsoft 365 in Brazilian Portuguese, and the semicolon separator. Include formulas for automatic ID, days since last interaction, follow-up delay, weighted revenue, priority classification, conversion rate between stages, average ticket, monthly pipeline, forecast by owner, and stalled opportunity alerts. When a function has a naming difference between Excel in Portuguese and English, provide both versions unambiguously. Do not provide incomplete formulas or pseudocode. 4. Executive dashboard: describe the layout exactly, with suggested block positions and the essential KPIs: leads received, active opportunities, total pipeline value, weighted revenue, wins, conversion, average ticket, sales cycle, overdue opportunities, and forecast. Specify which PivotTables, charts, and slicers to create, fields in Rows/Columns/Values/Filters, and how to connect slicers. Prioritize management-level readability and avoid visual clutter. Include conditional formatting rules with criteria and colors without relying exclusively on color to convey status. 5. Operations and governance: detail the daily, weekly, and monthly update flow, responsible parties for each action, data quality rules, duplicate prevention, protection of formula cells, and backup routine. Suggest minimum LGPD controls, especially for contacts and personal data. 6. Optional automation: if macros are allowed, provide functional, commented VBA for at most two high-impact automations, such as creating a new ID, logging an activity, or highlighting overdue follow-ups. Indicate where to insert the code, how to save as .xlsm, how to enable macros, and how to test. If macros are not allowed, offer a native Excel alternative for each automation. Do not use ActiveX without justification. MANDATORY RESPONSE FORMAT: deliver in numbered sections, tables in Markdown, and separate code blocks for formulas and VBA. End with a “90-Minute Implementation Plan,” organized by steps and estimated time, and a final validation checklist. Be technically precise, practical, and oriented toward immediate implementation. Do not recommend external tools as substitutes for Excel; keep Excel as the core of the solution.
