Pricing Spreadsheet: Costs, Margin, and Markup in Excel
ESCOPO
This prompt turns real product data, costs, taxes, and commercial policies into a complete specification for building a robust pricing spreadsheet in Microsoft Excel. It delivers tab architecture, tables, fields, copyable formulas, validations, indicators, and consistency tests.
It is intended for financial analysts, controlling, sales, product managers, consultants, and entrepreneurs who need to set prices with traceability and confidence. The result considers variable cost, allocated fixed cost, taxes, commissions, discounts, target margin, markup, and minimum viable price.
In addition to the base model, the prompt guides the creation of scenarios and sensitivity analyses, making it possible to assess the impact of changes in costs, tax rates, margin, and discounts. The instructions are adapted to Excel in Brazilian Portuguese and prepared for direct implementation.
Conteudo
Prompt principal
Act as a senior specialist in financial modeling, controlling, and building corporate spreadsheets in Microsoft Excel. Your mission is to turn the business data and rules I provide into an auditable, scalable pricing solution ready for direct implementation in Excel. Do not respond with generic recommendations. Produce detailed operational instructions, with tab structure, headers, formulas, validation rules, controls, and tests. Prioritize formulas compatible with Microsoft 365 in Brazilian Portuguese; use function names in Portuguese and a semicolon as the argument separator. When there is a possible difference between Excel versions or locales, clearly note the alternative. Do not invent data: identify gaps, assume only what is strictly necessary, and mark every assumption as “A VALIDAR”. First, request or process the data below. If I have already provided some of it, organize what you received and ask only for what is missing: 1. Type of operation: manufacturing, retail, service, resale, import, or other. 2. Currency, unit of measure, reporting period, and estimated sales quantity by product/month. 3. Product/SKU master data: code, description, category, supplier, unit, and status. 4. Direct costs per SKU: raw material or purchase cost, inbound freight, insurance, packaging, direct labor, losses, import fees, and others. 5. Indirect/fixed costs: rent, administrative salaries, systems, energy, depreciation, marketing, and desired allocation basis (revenue, volume, hours, direct cost, or other). 6. Expenses and deductions on the sale: taxes and tax regime, commission, marketplace/card fee, royalties, subsidized freight, returns, delinquency, and other percentages. 7. Commercial policy: target margin, target markup if applicable, maximum discount, psychological/rounding price, competitor price, minimum price, and approval limits. 8. Required scenarios: base, pessimistic, optimistic, sales channel, region, customer, or volume band. 9. Technical requirements: Excel version, whether it will be used by multiple users, need for protection, Power Query, PivotTable, dashboard, VBA, and corporate visual standard. Then, follow this construction framework: A. Diagnose the financial logic adopted. Clearly differentiate markup from margin: markup as the relationship between price and cost; margin as profit relative to net or gross revenue, explicitly stating the chosen base. Explain how taxes, discounts, and commissions will be treated to prevent double counting. B. Propose the file architecture, preferably with the tabs: “00_Parametros”, “01_Cadastros”, “02_Custos”, “03_Rateio”, “04_Precificacao”, “05_Cenarios”, “06_Dashboard” and “99_Controles”. Adjust or remove tabs only with justification. C. For each tab, provide the objective, exact Excel table name, columns in creation order, data type, illustrative example that cannot be confused with real data, and input rules. Recommend using Excel Tables (Ctrl+T), structured references, and named ranges for critical parameters. D. In the “04_Precificacao” tab, present the full logic, including total direct cost, fixed cost allocation, unit total cost, percentages applied to sales, price by target margin, price by markup, minimum price, rounded suggested price, maximum allowed discount, and effective margin after discount. Provide each formula ready to paste, indicating destination cell/column and the dependency used. Use error handling such as SEERRO, and prevent division by zero. E. Model scenarios without overwriting the base. Explain editable parameters, dropdown selectors, PROCX/ÍNDICE+CORRESP when needed, and the formula for bringing in the selected scenario percentages. Include a sensitivity table that shows the effect of cost, margin, and discount on price and profitability. F. Define quality controls: alerts for price below minimum, margin below target, total deduction percentage equal to or greater than 100%, missing cost, duplicate SKU, allocation without a valid base, and negative price. Specify the alert formulas and the conditional formatting rules with colors without relying exclusively on color. G. If I request automation, provide a complete, commented VBA module for specific actions, such as updating calculations, generating a PDF report, locking formula cells, or exporting the price table. Tell me where to paste the code, how to save as .xlsm, which references are not needed, and security precautions. Do not include VBA if native formulas adequately solve the need. Deliver the final answer in exactly this order: 1) executive summary of the solution; 2) assumptions, gaps, and pending questions; 3) tab map; 4) field dictionary by tab; 5) ready-to-implement formulas; 6) step-by-step build in Excel; 7) scenarios and sensitivity analysis; 8) controls, validations, and protection; 9) dashboard specification, if applicable; 10) VBA, only if requested; 11) final acceptance checklist. Use technical, objective, and verifiable language. Preserve traceability between source data, assumptions, and final price. Always emphasize that tax and accounting validation must be performed by a qualified professional before final commercial use. My data/context for analysis: [PASTE HERE the data, business rules, summarized files, current spreadsheet structure, and desired results].
