共享成本分摊与对账方案
根据成本动因和支持部门关系,设计表格分摊、核对公式及接收方贡献结果,辅助判断成本去向。
完整提示词
<context>
You allocate shared costs in spreadsheets. An allocation is only as fair as its driver: rent by floor area occupied, IT by users or devices, HR by headcount, finance by transactions or invoices, management time by estimated share; revenue is the driver of last resort, because it loads costs onto whatever sells most rather than whatever uses most. When support departments serve each other (IT supports HR and HR supports IT), the direct method ignores that, the step-down method allocates one support department at a time in a stated order, and the reciprocal method solves the mutual services exactly with simultaneous equations (MMULT and MINVERSE in a spreadsheet). Rounding must leave the total unchanged, so the last receiver or the largest takes the remainder. The most common misuse is dropping a product or client because it shows a loss after allocation, when most allocated fixed costs would stay if it went; decisions need contribution before allocation shown alongside.
</context>
<task>
Build the allocation.
<cost_pools>
{{cost_pools}}
</cost_pools>
<receivers>
{{receivers}}
</receivers>
<purpose>
{{purpose}}
</purpose>
1. Driver choice: for each cost pool, recommend a driver from the data available, one line on why, and a fallback if the data is missing. Point out any pool where the user's preferred driver would distort the result.
2. Method: direct, step-down (with the order: the department serving the most others first) or reciprocal, chosen for the purpose and the support-department structure, with one line on the trade-off.
3. Layout: tabs for Pools (cost by pool), Drivers (driver quantity per receiver per pool), Shares (driver share = quantity / total for that pool), Allocation (pool x share), and Results.
4. Allocation formulas: share `=quantity / SUMIFS(driver total for the pool)` or a column total, allocated amount `=ROUND(pool * share, 2)` with the remainder assigned so totals match; for step-down, the running pools after each step; for reciprocal, the equations and the MMULT/MINVERSE setup.
5. Reconciliation: each pool's allocated total equals the pool; total allocated equals total pools; no receiver with a driver of zero receives cost; shares sum to 100% per pool.
6. Results and how to use them: a table per receiver: direct revenue and costs, contribution before allocation, allocated costs by pool, result after allocation; a note on which allocated costs would go away if the receiver stopped and which would not.
7. Assumptions to confirm: driver data quality, period mismatches, and choices the user should agree with whoever receives the recharge.
</task>
<constraints>
- Every driver and pool is an input on its own tab; no numbers inside formulas.
- Do not present an allocated loss as a reason to cut a product, client or department without showing contribution before allocation.
- If the purpose is a statutory, tax or funder requirement (transfer pricing, grant full cost rules), say the method may be prescribed and must be checked against those rules.
- Use functions available in the user's tool.
- Test with the user's figures for two pools and show that totals reconcile.
</constraints>
<output_format>
## Driver choice
Table: Pool | Driver | Why | Fallback.
## Method
## Layout
## Allocation formulas
Code blocks with cell references.
## Reconciliation
## Results and how to use them
Table: Receiver | Contribution | Allocated | After allocation.
## Assumptions to confirm
</output_format>可替换内容
- {{cost_pools}}
- {{receivers}}
- {{purpose}}
分类与标签
财务理财、成本分摊、财务核算、电子表格、部门 recharge