搭建服务项目报价计算表
把成本、加价和税费整理成报价工作簿,生成客户报价页,并提示税务假设需确认。
完整提示词
<context>
You help small trade and service businesses price jobs. Owners lose money on quotes in predictable ways: confusing markup with margin (a 25% markup is only a 20% margin), pricing labour at the wage instead of the loaded cost, forgetting travel, waste and overheads, and quoting from memory so similar jobs get different prices. You build one calculator that does the arithmetic the same way every time and produces a clean quote the customer sees, separate from the internal costing they should not see.
</context>
<task>
Build a job quote calculator for a {{business_type}} business.
<cost_items>
{{cost_items}}
</cost_items>
<markup_rules>
{{markup_rules}}
</markup_rules>
1. List assumptions and questions. If the markup rules are missing, propose a structure with clearly labelled placeholder percentages and ask the owner to set them; do not present invented percentages as industry standard. Ask about the sales tax rate and which items it applies to rather than assuming.
2. Rates sheet (named cells or a table): material price list with unit, unit cost and waste factor (for example extra for cutting tiles or timber); labour roles with loaded hourly cost (wage plus employer taxes, insurance, paid leave and non-billable time) and charge-out rate; overhead recovery per billable hour (yearly overheads divided by yearly billable hours, with both inputs visible); travel cost per trip or per distance; markup by cost type; minimum charge; tax rate; deposit percentage; quote validity in days.
3. Quote builder sheet, one row per line item: type (material, labour, equipment, subcontract, other), item picked from a dropdown, quantity, unit cost looked up from Rates, waste-adjusted quantity, cost, markup, sell price. Then subtotals by type, contingency if used, minimum charge check, discount, tax, total, deposit due, and the quote expiry date.
4. Formulas, with the distinction made explicit: sell price from markup is `cost * (1 + markup)`; sell price from a target margin is `cost / (1 - margin)`. Show both, and show the resulting margin of the whole job as `(price before tax - total cost) / price before tax`.
5. Work one realistic example job through the calculator with the user's items, showing each line and the totals.
6. Customer quote sheet: the business details placeholder, customer, job description, scope in plain words, priced sections (not internal costs or markups), exclusions, tax, total, deposit, validity, payment terms placeholder.
7. Margin guardrails: a warning cell when the job margin falls below a floor the owner sets, and when labour hours look low for the job type relative to a rule of thumb the owner enters.
</task>
<constraints>
- Every rate and percentage lives on the Rates sheet; no numbers typed into formulas.
- Use only functions in both Excel and Google Sheets unless one app is named; comma separators, and note once that some locales use semicolons.
- Tax rules vary by country and by item. State the tax assumption and tell the owner to confirm it with their accountant or tax authority; do not give tax advice.
- Keep internal cost and markup off the customer quote sheet, and say how to export or print only that sheet.
- Round the customer-facing total the way the owner chooses (to the cent, or to a whole amount), and round only the final price, not intermediate lines.
</constraints>
<output_format>
## Assumptions and questions
Bullets, with placeholders marked.
## Workbook layout
Rates, Quote builder and Customer quote sheets with their columns and named cells.
## Formulas
Table: Purpose | Cell or column | Formula | Notes.
## Worked example
The example job as a table: Line | Qty | Unit cost | Cost | Markup | Sell. Then subtotals, tax, total, margin.
## Customer quote
The layout of the customer-facing page.
## Checks before sending
A short checklist: scope matches the site visit, exclusions listed, margin above floor, expiry date set, tax correct.
</output_format>可替换内容
- {{business_type}}
- {{cost_items}}
- {{markup_rules}}
分类与标签
办公效率、报价工作簿、成本核算、客户报价、利润率预警