
使用Excel计算企业盈亏平衡点与修正利润预测——从历史数据回归到经营情景
本文讨论如何用Excel建立保本点测算模型。财务人员若直接用上月费用除以收入,容易把保本线算偏,因为其中混有固定与变动支出。文章建议利用历史收入与成本费用数据拟合两者关系,据此计算保本收入和目标利润。模型以不含税收入为解释变量,回归得到的截距和斜率分别作为固定成本和变动成本费用率,但需财务人员判断数据可比性、费用变化及经营能力限制。文章还强调统一收入与成本期间、区分含税与损益金额、先识别费用性态,并以某售后服务站四个月数据演示建模过程。
“这个月收入做到多少才能保本?”财务人员经常需要回答这个问题。用上月总费用除以上月总收入,得到的只是一个已经发生的费用率;其中既有随业务增加的变动支出,也有即使没有业务仍要承担的固定支出,直接拿来预测下月盈亏,容易把保本线算偏。
Excel可以利用历史收入与成本费用,拟合两者的关系,再据此计算保本收入和目标利润。不过,函数只能完成计算,数据是否可比、费用为何变化、预测是否超出原有经营能力,仍要由财务人员进行主观判断。本文以某售后服务站四个月的真实数据为例,说明模型的建立、检查与修正方法。
第一章 先确定保本点的类型
财政部《管理会计应用指引第401号——本量利分析》将盈亏平衡分析纳入本量利分析,并分别给出单一产品和产品组合的计算方法。[1]
本文采用收入金额作为解释变量,估计一定经营范围内的成本费用,而不是直接预测会计报表中的净利润。
为保持前后一致,设X为当期不含增值税的营业收入,Y为与该收入对应的经营成本费用,P为分析口径利润。Y包括营业成本、税金及附加以及纳入分析范围的销售、管理等费用;融资利息、所得税和不随正常经营重复发生的损益另行测算。于是,P等于X减去Y。这一结果采用的是管理测算口径,并非报表营业利润或净利润。
其中,a为回归直线的截距,单位为元/月;b为收入每增加1元时,拟合成本费用的平均增量。在价格、业务组合和成本关系相对稳定,并经合同、明细账核对后,可将a、b分别作为模型中的固定成本费用和变动成本费用率使用,1-b相应为模型边际贡献率。截距不等于真实口径的固定支出,尤其不能据此认定企业零收入时必然发生相同金额的成本费用。[2]
用于测算时,公式中的Y表示拟合成本费用,P表示模型利润;第三章表1中的实际利润,则按实际收入减实际成本费用计算。若将X改为工时、维修台次或销量,b的单位也应相应改变。
把成本费用公式代入利润关系式,展开括号并合并同类项,可依次得到:
保本就是收入恰好覆盖本模型纳入的全部成本费用,即P=0。将上式移项,在a为正、b小于1且成本关系适用的条件下,逐步求出保本收入:
若要求实现目标利润T,仍从同一个利润公式出发,把等号左端的0换成T,再移项求收入:
这里的“固定”只在一定期间和业务范围内成立。房租可能在租约到期后上调,人员可能在订单达到一定规模后增加。若模型算出的保本收入已经超过现有产能,就要先增加相应的人力、场地或设备成本,再重新测算,不能只把收入数值继续放大。
本文的P未扣除融资利息和所得税,与息税前利润(EBIT)的分析思路一致,但还将不随正常经营重复发生的损益另行测算,因此P属于特定分析范围下的经营利润测算值,不能未经调整直接等同于企业整体的EBIT。P=0表示所选经营口径保本;企业仍可能因承担利息费用而出现税前亏损。
第二章 数据口径比函数选择更重要
(一)收入与成本必须属于同一期间
收入应从已确认的营业收入出发,并与业务台账核对。报价、开票额、收款额和内部统计的“总产值”各有用途,但不能未经核对就当成同一个指标。对执行企业会计准则的企业,收入确认以履约义务和客户取得商品控制权等条件为依据;预收款并不当然构成当期收入。[3]
本例将“总产值”视为与当期营业收入一致的业务金额;实际应用前,应先核对并调整两者的口径差异。
成本也要按相同期间整理。采购材料尚未领用,不能把付款额全部作为当月耗费;本月已经发生的工资、外包费和水电费,不能等到下月付款才纳入。年度奖金、季度服务费等应按适用会计政策和受益期间处理,不能为了让曲线平滑而任意摊平。各月调整都要保留从账面金额到模型金额的对照。
制造企业还要检查产量与销量、存货结转是否匹配。直接拿报表营业成本对收入回归,可能混入固定制造费用在库存与已售产品之间的分配变化。存货波动明显时,应单独整理变动成本与固定成本,并将管理测算利润与财务报表利润衔接,不能把生产支出、销售成本和期间费用混为一谈。
(二)把含税金额与损益金额分开
根据《中华人民共和国增值税法》的规定,增值税为价外税,应税交易的销售额不包括增值税税额。[4]
用含税收款额与不含税成本费用做回归,会高估收入对利润的贡献。收入端应先区分应税、免税等业务;同一金额中含有不同适用税率或征收率的,应分别按实际适用规则处理,不能统一除以一个比例。
成本端也不能一律按“不含税采购额”取数。准予抵扣的进项税额通常不构成相应成本;不得抵扣并按规定计入资产或成本费用的部分,则应随相关资产耗用或费用确认进入模型。[5]
模型应取归属于当期损益的成本费用,而不是简单取发票金额或银行付款。税金及附加等已经影响经营损益的项目应按选定口径计入,避免遗漏或重复。
(三)先识别费用性态再决定是否整体回归
成本性态反映成本与业务量之间的关系。财政部《管理会计应用指引第303号——变动成本法》区分固定成本、变动成本和混合成本,并列示回归分析、账户分析和合同确认等分解方法。[6]
实务中,可以先按合同和明细账识别大额项目,再对剩余的混合成本回归,通常比把全部费用一次性拟合更容易解释。
项目 | 通常的变化方式 | 建模时需要核对的内容 |
|---|---|---|
场地租金与基础人员薪酬 | 一定范围内相对固定 | 租约变化、增员安排及社保等附随成本 |
配件耗用与计件外包费 | 随业务量变化 | 按收入、台次还是工时变化,是否存在返利 |
底薪加绩效与基础费加用量费 | 固定与变动部分并存 | 合同约定、提成基数、阶梯及封顶条件 |
扩店增员与新增班次 | 达到条件后分段增加 | 触发条件、投用时间和新增固定支出 |
偶发赔偿与停业损失 | 不一定重复发生 | 核查事实,单列预计影响并保留调整记录 |
样本期需覆盖企业正常经营周期,并包含高低不同的业务量。月度数据应准备24至36个月,再按业务变化决定取舍;这是一种实务起点,不是法定要求,也不是样本合格线。样本是否充分,应看其能否反映经营周期和费用变化,不能只按月份数量判断。若中途更换经营模式或大幅调整绩效制度,较长的旧数据也可能降低预测价值。下文采用四个月数据展示计算过程,实际预测仍须扩大样本并验证。
第三章 建立可复算的Excel模型
(一)第一步 录入数据并保留原始金额
本例采用某售后服务站四个月的数据,金额单位均为元,收入与成本费用按同一分析口径计算。
表1某售后服务站月度数据
月份 | 收入X | 成本费用Y | 分析口径利润 |
|---|---|---|---|
1月 | 610,826.32 | 597,654.35 | 13,171.97 |
2月 | 467,433.09 | 552,654.35 | -85,221.26 |
3月 | 827,639.23 | 762,186.06 | 65,453.17 |
4月 | 547,817.38 | 569,304.88 | -21,487.50 |
在Excel中,将A1至D1依次设为“月份、收入、成本费用、利润”,将四个月数据录入第2至第5行。D2输入=B2-C2,并向下填充。E至I列依次设为“拟合成本费用、拟合利润、成本预测差额、成本偏差率、绝对误差”。J列填写参数名称,K列存放参数和结果。
金额必须是数值,不能是带有“元”字样的文本。公式引用的X、Y区域应一一对应,不能分别排序。导入数据时,要检查数字格式、重复记录和空白单元格。
本例计算前可先核对以下结果:
检查内容 | Excel公式 | 本例应满足的条件 |
|---|---|---|
收入是否均为数值 | =COUNT(B2:B5) | 等于4 |
成本费用是否均为数值 | =COUNT(C2:C5) | 等于4 |
收入是否具有变化 | =VAR.P(B2:B5) | 大于0 |
数值计数一致,并不能证明记录配对正确,还须按月份核对收入与成本是否来自同一期间。不要把缺失费用补成零后直接参与回归。
(二)第二步 用函数求出固定部分和变动比例
表2参数区域设置及Excel公式
单元格 | 内容 | 输入公式 |
|---|---|---|
K2 | 固定成本费用a | =INTERCEPT($C$2:$C$5,$B$2:$B$5) |
K3 | 变动成本费用率b | =SLOPE($C$2:$C$5,$B$2:$B$5) |
K4 | 模型边际贡献率 | =1-K3 |
K5 | 模型保本收入 | =IF(AND(K2>=0,K3>=0,K3<1),K2/(1-K3),NA()) |
K6 | 收入与成本费用相关系数r | =CORREL($C$2:$C$5,$B$2:$B$5) |
K7 | 决定系数R² | =RSQ($C$2:$C$5,$B$2:$B$5) |
表2中,INTERCEPT返回直线的截距a,SLOPE返回斜率b,两者均先填因变量Y的区域,再填自变量X的区域。本例C2:C5是成本费用Y,B2:B5是收入X。按K2、K3的公式计算,得到a=245,815.351184783,b=0.610721951133。K6、K7用于检验拟合关系,其结果在本章第四节说明。
(三)第三步 将成本公式代入利润公式并求保本收入
将估计参数代入利润公式,并令P=0,逐步求得保本收入。以下系数保留较多小数,便于复算。
由此得到模型保本收入631,464.71元,约63.15万元/月。b表示在这四个月数据拟合出的关系中,每增加1元收入,成本费用平均增加约0.611元,剩余约0.389元用于覆盖固定成本费用并形成利润。这里的边际贡献率不是会计毛利率;历史数据反映的近似拟合关系,不能保证未来每增加1元收入,成本都按相同幅度变化。
若b等于或大于1,在a为正时,该线性模型不存在靠持续增加收入实现保本的正向解;若截距为负或斜率为负,应先检查样本、价格变化、返利及成本归集。不能把不合理的参数继续用于下达经营目标,也不能在没有依据时强制截距为零。
表2的K5公式将负截距、负斜率及斜率不小于1的情况标记为#N/A,避免直接输出经营目标。这是本教学模型的使用限制,不意味着所有出现负参数的经济关系都不成立;查明原因后,应另行判断适用的成本关系。
(四)第四步 将公式带回每个月检查计算偏差
例如,1月收入为610,826.32元。代入成本费用式,得到拟合成本费用618,860.39元;再用收入减去这一金额,得到拟合利润-8,034.07元。下面将同一操作扩展至四个月,与实际成本及实际利润对照。
表3回算区域公式从第2行向下填充至第5行
单元格 | 含义 | 输入公式 |
|---|---|---|
E2 | 拟合成本费用 | =$K$2+$K$3*B2 |
F2 | 拟合利润 | =B2-E2 |
G2 | 成本预测差额 | =E2-C2 |
H2 | 成本偏差率 | =IF(C2=0,NA(),G2/C2) |
I2 | 绝对误差 | =ABS(G2) |
本文把成本预测差额定义为“拟合成本费用减实际成本费用”。正数表示模型高估成本,负数表示模型低估成本;偏差率以实际成本费用为分母。H列设置为百分比格式。若实际成本为零,公式返回#N/A,提示另行检查,避免把不可计算的比例显示为零。
表4案例回算结果金额单位为元
月份 | 拟合成本费用 | 拟合利润 | 成本预测差额 | 成本偏差率 |
|---|---|---|---|---|
1月 | 618,860.39 | -8,034.07 | 21,206.04 | 3.55% |
2月 | 531,287.00 | -63,853.91 | -21,367.35 | -3.87% |
3月 | 751,272.80 | 76,366.43 | -10,913.26 | -1.43% |
4月 | 580,379.45 | -32,562.07 | 11,074.57 | 1.95% |
表4中,四个月成本偏差率的绝对值均不到4%。单看这些比例,无法认定哪一个月份存在明显异常,也不能直接推断固定成本或变动成本率发生变化。按差额绝对值比较,1月和2月最大,都在2.1万元左右。具体原因需钻取到工资、配件耗用、结算及费用归属等明细进行核查。
更值得关注的是1月:实际利润为13,171.97元,模型回算却为亏损8,034.07元。费用偏差仅3.55%,已使盈亏方向相反。这说明靠成本偏差率评价模型不够;企业接近保本线时,几万元费用差异就可能改变盈亏。对管理决策而言,少赚2万元与从盈利变成亏损,意义并不相同。

图1售后服务站收入线与拟合成本费用线
图中收入线与拟合成本费用线的交点,对应两者相等、模型利润为零的位置,并不保证每个月的实际盈亏都与模型一致。表1的最低月收入为467,433.09元,最高为827,639.23元,即样本收入范围约为46.74万至82.76万元。图示范围与此接近;超出样本范围的预测需要额外证据支持。
第四章 判断模型能否用于预测
(一)R²反映拟合程度不能代替业务判断
相关系数r和决定系数R²均由表1的四个月数据计算。收入区域为B2:B5,成本费用区域为C2:C5:K6输入=CORREL($C$2:$C$5,$B$2:$B$5),得到r≈0.979150527809;K7输入=RSQ($C$2:$C$5,$B$2:$B$5),得到R²≈0.958735756108。单元格按四位小数显示时,分别为0.9792和0.9587。
在包含截距的一元线性最小二乘回归中,R²等于相关系数的平方,也可用=K6^2复核。R²衡量模型对样本内成本费用波动的解释程度,不表示“预测准确率为95.87%”,也不能证明收入变化一定造成相同比例的成本变化。[7]
这里检查收入与成本费用的关系,是因为模型要估计收入变化对应的成本费用变化。因此,应结合两者的散点及业务联系,核查收入是否适合作为成本动因。若改为检查净利润与收入的相关性,会混入融资、税务等影响,也没有直接检验成本函数。即使使用分析口径利润,由于利润本身等于收入减成本,相关性仍不能替代成本动因核查。
如果企业内部采用“R²至少0.8”或“成本偏差不超过10%”等筛选条件,还需结合费用规模、利润空间和决策用途判断;这些数值不是适用于所有企业的统一标准,也不能单独决定是否更换模型。若主要成本是稳定租金,成本与收入的相关性本来就可能不高,这并不意味着企业不能进行保本分析;此时直接按合同识别固定成本可能更合适。
(二)同时看误差金额与新期间表现
第三章中一月利润的回算,说明较小的成本偏差率也可能改变盈亏方向,所以还应检查误差金额。表3的I2:I5存放各月差额的绝对值,在K8输入=AVERAGE(I2:I5),即将四个月的绝对误差相加后除以4,得到平均绝对误差MAE=16,140.31元。G2:G5存放带正负号的差额,在K9输入=SQRT(SUMSQ(G2:G5)/COUNT(G2:G5)),即先将差额平方、求平均,再开平方,得到样本内均方根误差RMSE=16,941.11元。RMSE会更突出较大的误差。这里分母取观测数4,用于描述回算误差;它不同于回归估计中按自由度计算的残差标准误。
误差指标还要结合样本数量判断。本例只有四个观测值,估计a、b两个参数后,残差自由度为4-2=2,可用于检验误差的信息很少。上述指标也来自同一批参与拟合的数据,不能充当独立预测验证。数据充分时,可按时间顺序,用较早月份建模、较晚月份检验,或逐月滚动预测,再比较误差。
以现有数据演示一次时间留出检验。只用1月至3月估计参数,再预测4月成本。在独立单元格输入:
=FORECAST.LINEAR(B5,$C$2:$C$4,$B$2:$B$4)
得到4月预测成本585,359.58元,比实际成本569,304.88元高16,054.70元,对应预测利润-37,542.20元。4月成本没有参与这次参数估计,因此这项差额与表4的样本内回算差额含义不同。不过,一次检验仍不足以判断模型是否可靠,实际应用须积累多个后续月份的预测记录。
回归预测是在给定收入条件下估计成本费用。即使已经完成上述检验,未来实际收入若与假设不同,利润仍需按实际收入重新衔接,不能把条件测算视为对未来收入和利润的共同保证。[8]
利润接近零或出现负数时,不能用“利润误差率”作为主要指标,分母过小会夸大比例,正负变化也使解释困难。可以同时报告利润误差金额、盈亏方向是否判断正确,以及误差是否会影响增员、报价或资金安排。
(三)异常月份应查明原因后处理
检查误差后,若发现相关性较低或个别月份偏差较大,应先核查原因,不应直接删去这些月份再重算。一个突出的数据点可能是录入错误,也可能真实反映停工、集中维修、返利结算或业务结构变化。录入和归属错误应更正;真实但不再重复的事项可在常态预测中单列调整,同时保留原始结果及调整依据;经常发生的旺淡季则应保留或分季建模。
建议分别检查按月份排列和按收入排列的成本预测差额。相邻月份的差额连续同向,应核查是否存在费用归属、季节性或制度变化;后续预测持续同向偏离,则应检查模型是否系统性高估或低估成本。对带截距的普通最小二乘回归,参与拟合样本的差额合计在数值误差范围内为零,不能据此认为未来预测不存在偏差。高收入区间偏差扩大时,还应核查加班、外包和阶梯成本等业务原因。[2]

图2售后服务站逐月成本预测差额
注:成本预测差额口径与表4一致。
第五章 用图表检查拟合关系并比较候选模型
前面提出的散点检查和候选模型比较,可以通过Excel图表完成。以下先演示操作,再回到售后服务站案例比较线性式与二次式。六张Excel截图采用(3,9)、(2,1)、(1,4)、(6,36)四组数据,与经营案例分开使用,数值不同。比较的目的,是检查不同函数如何改变拟合结果及其业务含义;是否采用新模型,还要看后续验证。
截图对应的桌面版Excel操作入口在其他版本、平台或语言环境中可能不同。操作时以实际界面为准,核心是保证横轴取X、纵轴取Y,并让拟合公式与选中的数据系列一致。
(一)第一步 选中两列数值插入XY散点图
把X、Y放在相邻两列,选中数据及列标题,依次进入“插入”“散点图”,选择只有数据点的XY散点图,如图3。不要把X、Y分别作为两个系列,也不要用按月份等距排列的分类折线图替代。售后服务站案例应选收入与成本费用两列,而不是选月份和利润。

图3插入XY散点图
(二)第二步 选中数据系列添加趋势线
选中图中的数据点,点击图表右侧“+”,勾选“趋势线”,进入“更多选项”,如图4。也可以右键点击数据系列,再选择添加趋势线。先用线性拟合建立基准,以便和后面的候选公式比较。

图4进入趋势线设置
(三)第三步 显示线性公式和决定系数
在线性趋势线设置中勾选“显示公式”和“显示R平方值”,可得到图5的结果。对截图演示数据,回归直线约为Y=7.071428571429X-8.714285714286,R²约为0.9104。

图5线性拟合公式与R平方值
(四)第四步 增加系数显示精度再回代检查
点击公式标签,进入标签的数字格式设置,将类别改为“数字”,按计算需要增加小数位数,操作位置见图6。小数位数设为两位,复算时应根据所需精度增加。后面比较二次多项式时,系数还要与收入的平方相乘;即使二次项系数很小,少显示几位也可能明显改变预测结果。

图6设置公式标签的数字格式
图表公式主要用于查看关系。线性预测直接引用INTERCEPT和SLOPE结果单元格,或使用FORECAST.LINEAR,避免把图表标签上已舍入的系数作为唯一计算依据。FORECAST.LINEAR根据指定的收入和历史样本,返回对应的线性预测成本;第六节给出具体公式和预测区域的设置。
(五)第五步 切换为二次多项式比较拟合变化
若数据形态和业务分析支持进一步比较,可在“设置趋势线格式”中选择“多项式”,将阶数设为2,并保留公式和R²显示,如图7。图7所示面板已选中二次多项式;做线性基准时应选择同一面板中的“线性”。

图7二次多项式及公式显示选项
切换后得到图8。对截图的演示数据,二次式约为Y=1.497237569061X²-3.837016574586X+5.295580110497,R²约为0.9857,取两位小数后就是图中的1.50X²-3.84X+5.30和0.99。

图8二次多项式拟合结果
(六)第六步 回到售后案例比较线性式与二次候选式
截图演示了趋势线的设置方法,下面用表1的售后服务站数据比较线性式与二次式。为便于阅读,将收入和成本费用同时换算为万元,设U=X÷10,000,V=Y÷10,000。线性式的常数项a也除以10,000,斜率b保持不变,换算后为:
二次成本式在一次项之外增加U²项,可写成下式。d、e、f分别是常数项、一次项系数和二次项系数,均需用表1的数据重新估计;它们是二次曲线的数学参数,不能把d、e直接当成前面线性式中的固定成本和变动成本率。
在Excel中取得二次式系数,可另建“二次测算”工作表。A2:A5录入表1收入除以10,000的数值,B2:B5录入成本费用除以10,000的数值;C2输入=A2,D2输入=A2^2,向下填充。用LINEST将C、D两列共同作为解释变量,得到的系数按最高次项在前返回。表5中,LINEST的TRUE表示同时估计截距,FALSE表示只返回系数;外层INDEX从结果的第1行按列提取f、e、d。
表5二次候选式的系数与回算公式独立工作表
单元格 | 内容 | 输入公式 |
|---|---|---|
H2 | 二次项系数f | =INDEX(LINEST($B$2:$B$5,$C$2:$D$5,TRUE,FALSE),1,1) |
H3 | 一次项系数e | =INDEX(LINEST($B$2:$B$5,$C$2:$D$5,TRUE,FALSE),1,2) |
H4 | 常数项d | =INDEX(LINEST($B$2:$B$5,$C$2:$D$5,TRUE,FALSE),1,3) |
E2 | 二次式拟合成本万元 | =$H$4+$H$3*A2+$H$2*A2^2 |
F2 | 二次式拟合利润万元 | =A2-E2 |
G2 | 成本预测差额元 | =(E2-B2)*10000 |
E2:G2向下填充至第5行,再与表4采用同一单位和差额口径比较。支持动态数组的Excel可直接计算;部分较早版本的LINEST相关公式需按数组公式方式确认,即使用Ctrl+Shift+Enter,而非仅按回车键。
用表5的公式求得d、e、f,再将H4、H3、H2中的完整系数代入二次成本式,得到:
二次模型的拟合程度和误差也要复算。在“二次测算”工作表中,B2:B5为实际成本费用,E2:E5为拟合成本费用,两者单位均为万元。R²按“1-误差平方和÷实际成本离差平方和”计算:在H9输入=1-SUMXMY2(B2:B5,E2:E5)/DEVSQ(B2:B5),得到0.999882532732,约为0.999883。其中,SUMXMY2计算两列数据之差的平方和,DEVSQ计算实际成本相对其平均值的离差平方和。
G2:G5为以元计量的成本预测差额。在I2输入=ABS(G2),向下填充至I5,再在H10输入=AVERAGE(I2:I5),得到二次模型的平均绝对误差750.63元,低于第四节线性模型的16,140.31元。
拟合误差缩小,并不足以证明预测能力提高。二次式用四个点估计d、e、f三个参数,残差自由度仅为4-3=1,已经接近用曲线模拟样本。还需要业务解释和新期间验证,才能决定是否采用。

图9售后服务站线性式与二次候选式比较
(七)第七步 按新公式重新求保本点并筛选可用解
二次成本式建立后,利润仍然等于收入减成本。先代入,再展开,最后把利润为零的条件整理成二次方程。由于这里使用万元,计算出的利润和收入也都为万元。
求解这一方程,可用二次方程求根公式,也可在“二次测算”工作表中使用单变量求解。另设H6为待求收入,单位为万元;H7输入=H4+H3*H6+H2*H6^2计算成本,H8输入=H6-H7计算利润。先在H6输入60,再进入“数据”“模拟分析”“单变量求解”,设置H8的目标值为0,可变单元格为H6;求解后将H6改为100,再运行一次。两次采用不同初始值,以查找曲线与收入线的不同交点。每次求解后都应检查H8,确认利润接近零。
不同初始值不能保证找到全部交点,还可用求根公式核对。在“二次测算”工作表中增加:
单元格 | 内容 | 输入公式 |
|---|---|---|
H11 | 判别式 | =(H3-1)^2-4*H2*H4 |
H12 | 较小交点 | =IF(OR(H2=0,H11<0),NA(),(1-H3-SQRT(H11))/(2*H2)) |
H13 | 较大交点 | =IF(OR(H2=0,H11<0),NA(),(1-H3+SQRT(H11))/(2*H2)) |
上述公式按本例H2为正的二次式设置,结果单位为万元。H2为零时应改按一次方程处理;判别式小于零时没有实数交点,等于零时两个结果重合。求得交点后,仍应筛除负收入以及缺少业务依据的解。
按上述步骤并使用未舍入系数,两次求解得到的收入分别约为58.4652万元和104.1094万元。与表1约46.74万至82.76万元的样本收入范围比较,前一个交点位于范围内,后一个超过样本上限。后一个解不能直接用于经营决策;前一个也只是候选模型的计算结果,不能因为比线性式的63.15万元低,就选它作为新的保本目标。
二次式还隐含利润先升后降的关系。由P=(1-e)U-d-fU²,可求得利润转折处的收入U=(1-e)÷(2f),代入本例系数约为81.29万元;在Excel中可用=(1-H3)/(2*H2)复算。超过这一收入后,模型中的成本增量大于收入增量,利润反而下降。该转折已经落入样本收入范围,必须核查是否存在相应的加班、外包或产能约束,才能判断曲线是否具有业务依据。
模型应按偏差原因修正:费用随业务门槛增加的,可采用分段函数;收入结构变化的,应分业务建模;绩效制度变化的,应重算相应成本。修正结果仍须经后续数据验证。三次多项式有四个待估参数,四个横轴互不相同的点可被其完全拟合;此时R²达到1,也不代表能够预测第五个月。
第六章 将模型用于收入目标和经营方案
(一)算出目标收入也要算出利润余量
经营目标测算先采用第三章建立的线性模型,a、b分别引用表2的K2、K3。假设预计月收入为700,000元,代入P=(1-b)X-a,得到模型利润26,679.28元;若要求月利润达到50,000元,则按X=(a+T)÷(1-b)计算,所需收入约为759,907.61元。两项计算都假定价格、业务组合和成本关系保持不变。
在前述参数区,K10输入700000,K11输入=$K$2+$K$3*K10,K12输入=K10-K11,即可分别得到预计收入、预计成本费用和预计利润。K11也可改用=FORECAST.LINEAR(K10,$C$2:$C$5,$B$2:$B$5)计算预计成本费用。K13输入目标利润50000,K14输入=IF(ISNUMBER($K$5),($K$2+K13)/$K$4,NA()),即得到目标收入。实际建模时,目标利润必须与P采用同一口径。
预计收入700,000元减去模型保本收入631,464.71元,差额为68,535.29元;再用该差额除以预计收入,得到9.79%。按财政部《管理会计应用指引第403号——边际分析》的计算口径,前者是安全边际额,后者是安全边际率;在成本关系不变的前提下,分别反映收入回落至保本线的金额和比例。[1]
在K15输入=(K10-$K$5)/K10,并设为百分比格式,即可复算安全边际率。
安全边际仍是模型结果,需要与误差一起判断。本例预计利润约2.67万元,而第四节算得的样本内平均绝对误差已约1.61万元,不能据此作出确定的盈利判断。MAE描述样本回算误差,也不能直接加减成某个置信水平的预测区间。
收入变化还要区分数量与价格。以70万元收入情景为例,若业务量不变、全部业务统一降价5%,并假定成本费用不随此次降价改变,则收入降为665,000元,成本费用仍为原模型在70万元收入下估计的673,320.72元,利润转为-8,320.72元。如果直接把665,000元代入原回归式,却会得到盈利13,054.55元。差异在于,原模型同时调低了成本。涉及降价、折扣或价格谈判时,应分别测算业务量、单价与相关成本,不能只替换收入数值。
(二)把已知经营变化直接放进情景
已经签订的涨租协议、批准的增员计划和明确的绩效调整,比从四个月样本中推断变化更有依据。可把新增固定支出记为Δa,把变动成本费用率的变化记为Δb,得到调整后的预测关系。这里Δb用小数表示,增加3个百分点应填0.03。
Δa、Δb均应相对于基准模型计算,只纳入基准尚未反映的变化。例如,月租金由30,000元提高至32,000元,若基准模型已经包含原租金,则Δa应为2,000元。如果样本后几个月已经执行新租金或新绩效制度,应先统一测算口径,或分开估计新旧期间,避免重复调整。
各情景的保本收入按(a+Δa)÷[1-(b+Δb)]计算,预计利润按上式计算。表6固定预计月收入为700,000元,分别假设固定支出增加20,000元、变动成本费用率提高3个百分点,以及两项变化同时发生;基准情景的Δa、Δb均为0。各项假设只调整相应成本参数。
表6收入均为70万元时的示例情景金额单位为元
情景 | 固定成本费用 | 变动成本费用率 | 保本收入 | 预计利润 |
|---|---|---|---|---|
基准 | 245,815.35 | 61.0722% | 631,464.71 | 26,679.28 |
固定支出增加2万元 | 265,815.35 | 61.0722% | 682,841.87 | 6,679.28 |
变动率增加3个百分点 | 245,815.35 | 64.0722% | 684,192.51 | 5,679.28 |
上述变化同时发生 | 265,815.35 | 64.0722% | 739,859.70 | -14,320.72 |
表中调整均为演示假设,不是企业已经发生的事实。经营方案应写清收入预期及成本变化,不能只给一个“保本收入”数字。
表6把变动费用率变化直接计入Δb,实际应用前仍要核对费用的计提基础。若费用按业务量或毛利提成,而不是按收入提成,应按真实计提基础建立公式;若存在分档比例和最低保障,应分档计算,不能把旧比例直接加到b上。对绩效制度调整前后的数据,可在保留账面金额的同时,按可核验的新制度作可比口径测算,避免把制度差异误认为业务量影响。
(三)多业务企业需要关注收入结构
除了租金、用工和计提制度的变化,收入结构变化也会影响综合变动成本费用率。售后服务站可能同时经营维修工时、配件销售和外包服务,不同业务的变动成本费用率通常不同。若收入结构稳定,可以使用综合模型;结构变化较大时,应先分别估计各业务边际贡献,再汇总覆盖共同固定成本。不能把各板块分摊后的固定成本分别计入后,又在公司层面重复扣除。
以工时、配件两项业务为例,假设边际贡献率分别为60%、20%,共同及专属固定成本合计24万元。两项业务收入占比各为50%时,综合边际贡献率=60%×50%+20%×50%=40%,保本收入=24÷40%=60万元;若工时占比降至30%、配件占比升至70%,综合边际贡献率=60%×30%+20%×70%=32%,保本收入=24÷32%=75万元。上述参数均为情景假设。
另建“业务组合”工作表,将各业务预计收入放在B2:B4、各业务边际贡献率放在C2:C4,使用=SUMPRODUCT(B2:B4,C2:C4)计算边际贡献总额,再扣除共同及各业务专属的固定成本。上面的两项业务示例只需两行数据,公式区域相应改为B2:B3和C2:C3。保本测算的收入权重必须与目标情景一致,不能机械沿用历史权重。
第七章 保本不等于现金够用,预测也不等于预算
上述模型衡量的是经营损益。资金能否周转,还需另编现金收支预测。折旧会减少利润,但当期通常没有相同金额的付款;赊销已经确认收入,却可能尚未收款;备货、偿还借款本金、支付设备款和缴纳增值税等,均可能占用现金。仅将折旧从固定成本中扣除,仍不足以判断资金能否周转。
若经营目标改为税后利润,需要从第一节确定的分析口径利润出发,衔接融资损益、其他损益及所得税测算。所得税不能一概按分析口径利润乘以某个比例,实际还可能涉及纳税调整、亏损弥补、优惠及递延所得税等事项。
模型投入使用后,每月结账时可保留上次预测版本,补入最新实际数据,比较原预测与实际差异,再决定是否更新参数。更新前先判断偏差来自收入规模、业务组合、成本价格还是一次性事项。滚动预测反映最新判断,已批准预算是否调整则应按企业原有授权程序处理。
本文公式中的$B$2:$B$5、$C$2:$C$5只覆盖四个月样本。追加月份后,应同步更新参数、拟合值和误差指标的引用区域。持续使用时,可将历史数据转换为Excel表,例如命名为“历史样本”,再使用表列引用:
=INTERCEPT(历史样本[成本费用],历史样本[收入])
=SLOPE(历史样本[成本费用],历史样本[收入])
追加实际记录后,表列引用会随表扩展。准备检验后续月份时,应另存并冻结原参数,避免新实际数据进入模型后,把原预测覆盖成重新拟合的结果。
最终提交经营者的测算结果,应同时写明分析对象和期间、收入与费用口径、样本区间、预计收入、保本收入、目标利润所需收入,以及主要变化假设和可接受误差。参数还应与租金合同、工资计划和采购安排相互校验。
结语
盈亏平衡点不是企业永久不变的门槛。价格、业务组合、人员配置和采购条件一变,门槛也会移动。Excel的作用,是厘清这些关系,让经营者看见收入增长究竟能留下多少利润。财务人员需要执行的,是数据口径一致、参数能够解释、预测经过验证,并在经营条件变化后及时重算。
版权声明
本文原创文字、自制图表等著作财产权由财误通鉴网站(https://www.caiwu.icu)运营者依法享有。除法律另有规定外,转载、改编等使用须取得许可。第三方作品的权利归原权利人所有。
免责声明
本文依据截至2026年11月10日可核实的公开资料撰写,仅供Excel应用与经营测算方法学习参考,不构成针对具体企业的预算、投资或财税意见。实际应用须核对数据口径、经营条件及模型适用范围,必要时咨询专业人士,并承担自身决策与操作的相应责任。
参考文献
[1] 财政部. 管理会计应用指引第400号——营运管理及第401号至第403号应用指引: 财会〔2017〕24号附件4[EB/OL]. (2017-09-29)[2026-11-06]. https://kjs.mof.gov.cn/zhengcefabu/201710/P020171019290297338815.pdf.
[2] 国家统计局. 参数估计的最小二乘方法[EB/OL]. (2023-03-28)[2026-11-10]. https://www.stats.gov.cn/zs/tjll/xgfx/202303/t20230328_1938148.html.
[3] 财政部. 企业会计准则第14号——收入: 财会〔2017〕22号[EB/OL]. (2017-07-05)[2026-11-06]. https://kjs.mof.gov.cn/zt/kjzzss/kuaijizhunzeshishi/201709/t20170907_2694006.htm.
[4] 全国人民代表大会常务委员会. 中华人民共和国增值税法[EB/OL]. (2024-12-25)[2026-11-06]. https://m.mof.gov.cn/zcfb/202412/t20241225_3950494.htm.
[5] 财政部. 关于印发《增值税会计处理规定》的通知: 财会〔2016〕22号[EB/OL]. (2016-12-03)[2026-11-06]. https://www.mof.gov.cn/gp/xxgkml/hjs/201612/t20161214_2512803.htm.
[6] 财政部. 管理会计应用指引第300号——成本管理及第301号至第304号应用指引: 财会〔2017〕24号附件3[EB/OL]. (2017-09-29)[2026-11-06]. https://kjs.mof.gov.cn/zhengcefabu/201710/P020171026318594747473.pdf.
[7] 国家统计局. 判定系数[EB/OL]. (2023-04-23)[2026-11-10]. https://www.stats.gov.cn/zs/tjll/xgfx/202304/t20230423_1938956.html.
[8] 国家统计局. 模型的预测[EB/OL]. (2023-08-24)[2026-11-10]. https://www.stats.gov.cn/zs/tjll/xgfx/202308/t20230824_1942295.html.



