搭建销售商机跟进与预测表格
为小型销售团队设计商机台账、月度预测公式与逾期提醒,配套负责人视图及每周维护流程。
完整提示词
<context>
You build pipeline trackers for small sales teams and founders who are not ready for a CRM. A weighted forecast multiplies each open deal's value by a stage probability and sums by expected close month; it is meaningless for a single deal and only useful in aggregate. Stage probabilities are guesses until they are checked against the team's own win history, so they belong in a settings table, not inside formulas. Pipelines decay in predictable ways: close dates that are already in the past but the deal is still open, deals that have sat in one stage far longer than the usual cycle, and deals with no next action or a next action date that has passed. Stages should be defined by what the buyer has done (agreed a demo, received a proposal), not by the seller's hopes. Won and lost deals must stay in the table with a date and a reason, because they are the data for checking probabilities later.
</context>
<task>
Build the pipeline tracker in {{tool}}.
<sales_process>
{{sales_process}}
</sales_process>
<needs>
{{needs}}
</needs>
1. Layout: tabs for Deals (one row per deal), Settings (stages, probabilities, owners, stale-days limit, monthly targets), Forecast, and My Actions or per-owner views.
2. Deals table: columns with type and source: Deal ID, Company, Contact, Owner (dropdown), Stage (dropdown), Value, Expected Close, Probability (looked up from Settings by stage), Weighted Value, Next Action, Next Action Date, Last Contact, Stage Entered Date, Created, Closed Date, Lost Reason. Give the formulas for looked-up and computed columns.
3. Stage settings: the stage table with entry criteria from the user's process, a default probability for each (labelled as a starting assumption), Won at 100% and Lost at 0%.
4. Forecast: weighted and unweighted open pipeline by expected close month with SUMIFS between the month's first and last day (`EOMONTH(month, -1) + 1` to `EOMONTH(month, 0)`), excluding Won and Lost; won value by month from Closed Date; coverage of target (open pipeline divided by remaining target) with a note on what coverage the user should expect from their own win rate.
5. Flags and views: formulas for overdue next action, missing next action, close date in the past while open, and stale in stage (`TODAY() - [Stage Entered Date]` over the limit); conditional formatting for each; a filtered My Actions list per owner (FILTER in Excel 365 and Google Sheets, or a filter view).
6. Using it each week: a 15-minute routine: update stages and dates, clear flags, review the forecast, and once a quarter compare actual win rates by stage with the probabilities.
</task>
<constraints>
- Probabilities, targets and limits live in Settings, never in formulas.
- Use only the user's stages; do not invent a sales methodology.
- Use functions available in {{tool}}; note where Excel and Google Sheets differ.
- Keep contact data minimal and mention access control, since the sheet holds personal data about customers.
- If the team has more than a handful of users or needs email logging, say a CRM may now be cheaper than the time spent.
</constraints>
<output_format>
## Layout
## Deals table
Table: Column | Type | Formula or input.
## Stage settings
Table: Stage | Entry criteria | Probability.
## Forecast
Code blocks for each formula.
## Flags and views
## Using it each week
</output_format>可替换内容
- {{tool}}
- {{sales_process}}
- {{needs}}
- [Stage Entered Date]
分类与标签
办公效率、销售台账、商机跟进、电子表格、销售预测、待办提醒