Excel Sales Report with MoM and YoY Executive Dashboard
ESCOPO
This prompt directs ChatGPT or Claude to act as a senior expert in Excel, commercial BI, and financial analysis to design a professional sales report with month-over-month (MoM) and year-over-year (YoY) comparisons. It starts from the company’s real data and delivers actionable instructions, with no generic answers.
The result covers workbook architecture, data modeling, formulas in Brazilian Portuguese, PivotTables, slicers, executive indicators, quality rules, and charts. It also includes VBA automation when it makes sense and a concise slide outline for presenting to leadership.
It is intended for sales analysts, controllership, sales managers, planning teams, and consultants who need to standardize recurring analyses, identify trends, seasonality, regional performance, products, and channels.
Conteudo
Prompt principal
Act as a senior specialist in Microsoft Excel, sales analysis, controllership, and executive dashboard development. Your mission is to turn the data and context I provide into a month-over-month (MoM) and year-over-year (YoY) comparative sales report, with practical instructions that can be executed directly in Excel. Before proposing the solution, analyze the information below. If any essential item is missing, ask at most 8 objective questions and wait for my response; if the data is sufficient, proceed without asking for confirmation. DATA AND CONTEXT TO PROVIDE: 1. Excel version and formula language (e.g., Microsoft 365 PT-BR, Excel 2021, English). 2. Report objective, target audience, update frequency, and most recent closing date. 3. Sample of the raw data: paste the headers and 10 to 30 representative rows, or describe the source/file structure. 4. Available fields and their meanings. Especially provide sales date, gross amount, discounts, returns, net amount, quantity, target, customer, product, category, salesperson, channel, region/branch, status, and currency. 5. Business rules: official revenue definition, treatment of cancellations/returns, targets, sales with no history, incomplete months, fiscal year versus calendar year, and comparison criteria. 6. Dimensions that need to be analyzed and priority filters (e.g., region, channel, category, and salesperson). 7. Constraints: whether Power Query, Power Pivot, PivotTable, macros/VBA, external connections, and sheet protection are allowed or not. 8. If available, corporate layout or visual identity, targets, and examples of indicators already used by leadership. EXECUTE THE WORK USING THE FOLLOWING FRAMEWORK: A. Diagnosis and assumptions: validate the base grain, identify duplicates, invalid dates, null values, risk of double counting, and period gaps. Explicitly state the assumptions adopted when a rule is not provided. B. File architecture: propose a sheet structure with names, purpose, owners, and dependencies. As a default, evaluate the sheets 00_Parametros, 01_Base_Bruta, 02_Calendario, 03_Base_Tratada, 04_Calculos, 05_Pivots, 06_Dashboard and 07_Apresentacao. Adapt when necessary and explain the end-to-end update flow. C. Preparation and modeling: instruct how to turn the base into an Excel Table, name it, and create helper columns. Include Date, Year, Month Number, Month, Year-Month sorting, Quarter, Net Revenue, comparable period, and necessary keys. When Power Query is appropriate, describe the menu steps and transformations, without inventing nonexistent fields. D. Metrics: define formula, calculation rule, interpretation, and exceptions for Current Revenue, Previous Month Revenue, MoM Variation in R$, MoM Variation %, Same Month Last Year Revenue, YoY Variation in R$, YoY Variation %, Quantity, Average Ticket, Target, Target Attainment, Variance versus Target, and percentage share. Handle division by zero, absence of history, and negative numbers safely. E. Excel implementation: provide ready-to-copy formulas, using structured references and named ranges when that increases robustness. Use functions in PT-BR and semicolon separator if I inform Excel in Portuguese; otherwise, use the indicated language. For each formula, state exactly which sheet, cell/column, and context it should be applied in. Prioritize SUMIFS, COUNTIFS, IFERROR, INDEX/MATCH or XLOOKUP, EDATE, EOMONTH, TEXT, and LET when compatible with my version. F. Visual analysis: specify PivotTables, fields in rows/columns/values/filters, slicers, and timeline. Design the dashboard in blocks: top KPIs, monthly trend, YoY comparison, performance by dimension, ranking, and alerts. For each chart, indicate type, source, axis, title, legend, suggested colors, and expected management insight. Avoid redundant charts, 3D, and visual clutter. G. Automation and governance: if macros are allowed, deliver a complete, commented, and safe VBA script to refresh queries/PivotTables, record the refresh date, and display a completion message. If VBA is not allowed, provide the equivalent manual procedure. Include quality checks and a monthly refresh checklist. H. Executive presentation: create a structure of 5 to 7 slides for PowerPoint, with title, key message, metrics/charts to insert, and the management question answered by each slide. Highlight growth, decline, contribution by dimension, target, risks, and actionable recommendations. MANDATORY RESPONSE FORMAT: 1. Diagnosis and assumptions; 2. Sheet structure in a table; 3. Step-by-step preparation; 4. KPI dictionary; 5. Ready formulas in code blocks; 6. PivotTable and dashboard setup; 7. VBA or manual alternative; 8. Validation and update checklist; 9. Slide outline; 10. Insights to investigate. Be specific, auditable, and pragmatic. Do not invent results, values, or columns that I have not provided. Clearly distinguish mandatory, optional, and Excel-version-dependent instructions. Always prioritize a scalable model that is easy to update and understandable to non-technical managers.
