如何在 Excel 中制作盈亏平衡分析：完整指南
盈亏平衡分析可以确定总收入等于总成本时的销售水平，此时企业既不盈利也不亏损。要在 Excel 中进行分析，请输入您的固定成本、单价和单位变动成本。用固定成本除以价格与变动成本之间的差额，然后绘制收入与总成本的对比图表。
本指南涵盖了计算公式、所需的输入数据、三个 Excel 步骤以及一个实际案例。它还展示了如何使用单变量求解 (Goal Seek) 和数据表来测试价格或成本发生变化时的影响。
什么是盈亏平衡分析
美国小企业管理局（SBA）将盈亏平衡点定义为“总成本与总收入相等的点”。低于该点，每笔销售仍不足以弥补企业的成本。高于该点，每笔销售都会增加利润。
SBA 将盈亏平衡分析列为计算创业成本的原因之一，此外还有估算利润和获取贷款。贷款机构和投资者经常要求提供该分析，因为它展示了企业在停止亏损之前必须销售多少产品。
盈亏平衡分析回答了三个实际问题：我们需要销售多少件产品？这代表了多少收入？以及该结果对价格和成本变化的敏感度如何？
它还可以作为对新想法的快速测试。在您致力于新产品或新地点之前，先估算这三个输入数据。然后思考所需的销售量对于您所服务的市场是否现实。
盈亏平衡计算公式
SBA 提供了两个版本的盈亏平衡公式。
按单位计算：
盈亏平衡点（单位数）= 固定成本 /（单价 - 单位变动成本）
按销售额计算：
盈亏平衡点（销售额）= 固定成本 / 边际贡献率
SBA 将第二个术语解释为“产品价格与制造该产品成本之间的差额”。对于销售额公式，它将该边际计算为一个比率：价格减去变动成本，再除以价格。
这种区别在电子表格中非常重要。单位边际贡献是一个金额，例如 5 美元的产品上有 3 美元。边际贡献率是一个百分比，例如 60%。在单位数公式中使用金额数值，在销售额公式中使用比率。
SBA 还设定了一个界限：“此盈亏平衡分析是基于单一产品或服务的基础之上的。”后面的章节将介绍如何处理多种产品的情况。
开始前需要准备什么
盈亏平衡分析由三个输入数据驱动。正确进行成本拆分比 Excel 操作本身更重要。
| 输入数据 | 含义 | 示例 |
|---|---|---|
| 固定成本 | 无论销售量多少都保持不变的成本 | 房租、工资、保险、软件订阅 |
| 单位变动成本 | 随着每售出一件产品而增加的成本 | 原材料、包装、支付手续费、运费 |
| 单价 | 客户为单件产品支付的费用 | 标价，或折扣后的平均售价 |
为每个输入数据使用相同的时间周期。如果房租是按月计算的，那么结果就是月度盈亏平衡点。将年薪与月租混在一起计算得出的数字将毫无意义。
单位变动成本是人们最常凭空猜测的输入数据。相反，应该根据历史数据进行估算。用上季度的总变动成本除以同季度的销售数量。如果不同季度之间的结果波动很大，请使用多个周期的平均值。
某些成本是混合的。例如，包含基本费和使用费的电话套餐既有固定部分，也有变动部分。请将它们拆开，而不是猜测它们属于哪一类。
如何在 Excel 中进行盈亏平衡分析
以下三个步骤将构建一个实用的盈亏平衡模型和图表。它们使用的是同样适用于 Google Sheets 的标准公式。
步骤 1：设置输入数据
打开一张空白工作表，将单元格 A1 到 A3 分别命名为 Fixed costs、Price per unit 和 Variable cost per unit。在 B1 到 B3 中输入对应的值。
将输入数据保留在它们各自的单元格中，切勿直接输入到公式中。这样，当输入数据发生变化时，后续的每个计算都会自动更新，您只需编辑一个单元格即可测试不同的场景。
用浅色填充格式化输入单元格。这可以向打开文件的任何人示意哪些数字是可以修改的。
步骤 2：计算盈亏平衡点
在第 5 到 8 行中添加计算公式。
- 在 A5 中输入 Contribution margin per unit，在 B5 中输入
=B2-B3。 - 在 A6 中输入 Break-even units，在 B6 中输入
=ROUNDUP(B1/B5,0)。 - 在 A7 中输入 Contribution margin ratio，在 B7 中输入
=B5/B2。 - 在 A8 中输入 Break-even sales，在 B8 中输入
=B1/B7。
B6 中的 ROUNDUP 函数非常重要。您无法销售不足一件的产品，而向下舍入会导致您无法达到盈亏平衡。
检查 B8 是否大致等于 B6 乘以价格。如果不是，说明其中一个输入数据填错了单元格。
步骤 3：构建盈亏平衡图表
在计算结果下方构建一个小表格。在 D 列中，以均匀的步长列出从 0 开始递增的单位销量，例如 0、500 和 1,000。继续递增并超过您的盈亏平衡点。在 E 列中，使用 =D11*$B$2 计算收入。在 F 列中，使用 =$B$1+D11*$B$3 计算总成本。
选择 D 到 F 列并插入图表。带直线的散点图效果最好，因为它会将单位列视为真正的数值轴。
收入线从零开始并陡峭上升。总成本线从固定成本水平开始，上升速度较慢。它们的交点就是盈亏平衡点。在此处添加数据标签，以便读者无需自行估算。用浅色为交点右侧的区域着色，以显示利润区。
实际案例
一辆咖啡手推车的固定成本为每月 6,000 美元。它以每杯 $5.00 的价格销售咖啡，每杯咖啡在咖啡豆、牛奶、杯子和刷卡手续费方面的成本为 $2.00。
| 计算公式 | 结果 |
|---|---|
| 单位边际贡献 | $5.00 - $2.00 = $3.00 |
| 盈亏平衡单位数 | $6,000 / $3.00 = 2,000 杯 |
| 边际贡献率 | $3.00 / $5.00 = 60% |
| 盈亏平衡销售额 | $6,000 / 0.60 = $10,000 |
该手推车每月需要售出 2,000 杯咖啡，或实现 $10,000 的收入，才能弥补其成本。在此之后的每杯咖啡都会增加 $3.00 的利润。
目标利润也使用相同的逻辑。要实现每月 $3,000 的利润，请将其加入固定成本：$9,000 除以 $3.00 等于 3,000 杯。
安全边际展示了缓冲空间有多大。如果该手推车预计销售 2,600 杯，那么在开始亏损之前，它可以少卖 600 杯。这大约是预期销售额的 23%。
使用 Goal Seek 寻找盈亏平衡价格
有时问题会反过来。您知道自己的销量，并想知道达到盈亏平衡的价格是多少。
微软的支持页面对该使用场景进行了简单的界定。您知道自己希望从公式中获得什么结果，但不知道产生该结果的输入数据。Goal Seek 通过调整一个单元格来找到该输入数据。
添加一个利润单元格（例如 B9），输入 =B2*C1-B1-B3*C1，其中 C1 存放您的预期销量。然后按照微软的步骤操作。在“数据”选项卡上的“预测”组中，选择“模拟分析 (What-If Analysis)”，然后选择“单变量求解 (Goal Seek)”。通过更改单元格 B2，将单元格 B9 的值设置为 0。
Excel 会调整价格，直到利润正好为零。在每月 1,500 杯的情况下，咖啡手推车需要 $6.00 的价格才能达到盈亏平衡。
微软指出了一项限制：“Goal Seek 只能处理一个可变输入值。”为了同时求解多个输入数据，它指向了规划求解 (Solver) 加载项。
敏感性分析：如果价格或成本发生变化会怎样？
单一的盈亏平衡数字掩盖了其脆弱性。敏感性分析表展示了一系列不同输入数据下的结果。
以咖啡手推车为例，价格或成本的微小变化都会显著改变盈亏平衡点：
| 场景 | 边际贡献 | 盈亏平衡单位数 |
|---|---|---|
| 基准情况：$5.00 价格，$2.00 成本 | $3.00 | 2,000 |
| 价格上涨至 $5.50 | $3.50 | 1,715 |
| 变动成本上涨至 $2.50 | $2.50 | 2,400 |
| 固定成本上涨至 $7,500 | $3.00 | 2,500 |
Excel 中“模拟分析 (What-If Analysis)”下的“数据表 (Data Table)”功能可以自动构建这样的网格。在某一列中列出一系列价格，在某一行中列出一系列变动成本。然后将该表指向盈亏平衡单位数单元格。
比较相同幅度的变化，看看哪个输入数据的影响最大。在这个例子中，50 美分的变动如果发生在变动成本上，会使盈亏平衡点增加 400 杯；但如果体现在价格上，则只会减少 285 杯。这就是为什么变动成本往往是第一个值得谈判的成本。
多种产品的盈亏平衡分析
SBA 的公式假设的是单一产品。大多数企业销售多种产品，每种产品都有自己的边际。
通常的方法是计算加权平均边际贡献。估算每种产品贡献的销售份额，将每种产品的边际乘以其份额，然后相加。用固定成本除以该加权数值，即可得出整个产品组合的盈亏平衡单位数。
这里有一个简短的例子。产品 A 的边际为 $3，占单位销量的 60%。产品 B 的边际为 $5，占 40%。加权边际为 $3 乘以 0.6 加上 $5 乘以 0.4，即 $3.80。在固定成本为 $7,600 的情况下，盈亏平衡点为 2,000 件：其中产品 A 为 1,200 件，产品 B 为 800 件。
SBA 指出，如果每月的销售额有所波动，您可能还需要针对每种产品单独进行计算。它还提出了一个值得记住的警示：盈亏平衡点是用于规划和评估贷款可行性的估算值，不能取代详细的会计核算。
如果产品组合发生变化，盈亏平衡点也会随之改变。向低边际产品的转变会提高盈亏平衡点，即使总销售额保持稳定也是如此。
如何展示盈亏平衡分析
大多数读者需要三个数字和一个图表。首先展示盈亏平衡单位数、盈亏平衡销售额和安全边际。将图表直接放在下方，并标出交点。
然后展示敏感性分析表。它回答了每位贷款机构和管理人员接下来会问的问题：如果成本上升或销售未达预期会怎样？
保持输入数据可见。一份简短的固定成本、价格和单位变动成本清单可以让读者在几分钟内验证您的逻辑。SBA 指出，盈亏平衡点是商业计划书中的一项重要计算，因此请做好它会被仔细阅读的准备。
常见错误
- 混淆时间周期。 将月租与年薪混在一起会得出毫无意义的结果。请将所有数据转换为同一周期。
- 在客户支付较少时使用标价。 如果折扣很常见，请使用实际收到的平均价格。
- 遗漏微小的变动成本。 刷卡手续费、包装费和运费在每件产品上累加起来，会改变最终结果。
- 忽略产能限制。 固定成本往往会在达到特定销量时激增，例如需要增加第二班次或更大的空间时。请在每个阶段重新计算。
- 向下舍入。 带有小数的结果意味着您需要销售下一个完整的单位，而不是前一个。
利用 AI 更快地完成分析
一旦成本拆分明确，电子表格方法就非常有效。慢的部分通常在于整理数据的过程，因为成本数据往往分散在总账导出文件或一堆发票中。
AI 工作区可以完成这种整理工作。将导出的成本数据上传到 Powerdrill Bloom，并让它将每行分类为固定成本或变动成本，然后计算盈亏平衡单位数和销售额。您还可以在同一个请求中要求生成敏感性分析表和图表。
在信任结果之前，请先检查分类是否正确。混合成本（如公用事业费）通常需要您自己做出判断。
对于更广泛的规划蓝图，敏感性分析生成器和AI 财务建模页面涵盖了相关模型。我们的预算与实际对比报告指南展示了如何跟踪实际结果是高于还是低于计划。对于时间节点而非总量的把控，现金流量报告可以展示资金实际到账的时间。
如果您已经准备好了导出的成本数据，可以尝试使用 Powerdrill Bloom，并将其盈亏平衡分析结果与您自己的电子表格进行对比。
常见问题解答
什么是盈亏平衡分析？
盈亏平衡分析计算的是总收入等于总成本时的销售水平。此时，企业既不盈利也不亏损。它通常用于商业计划书和贷款申请中。
如何计算单位盈亏平衡点？
用固定成本除以单位边际贡献（即销售价格减去单位变动成本）。在固定成本为 $6,000 且单位边际为 $3 的情况下，盈亏平衡点为 2,000 件。
如何在 Excel 中进行盈亏平衡分析？
在不同的单元格中输入固定成本、价格和变动成本。将边际计算为价格减去变动成本，然后用固定成本除以该边际。绘制收入和总成本与单位销量的对比图表，并读取交点。
边际贡献与边际贡献率有什么区别？
单位边际贡献是一个金额：价格减去变动成本。边际贡献率是该金额除以价格，以百分比表示。在计算盈亏平衡单位数时使用金额数值，在计算盈亏平衡销售额时使用比率。
为什么盈亏平衡分析很重要？
它展示了企业在停止亏损之前必须销售多少产品。它还揭示了哪个输入数据（如价格、变动成本或房租）对该阈值的影响最大。SBA 将其列为计算创业成本的原因之一。
来源： 美国小企业管理局，计算您的创业成本 · 微软支持，使用单变量求解 (Goal Seek)。本实际案例中使用的数据仅用于说明。