Excel在财务管理 中的应用 （第5版）08CHAPTER EIGHT第8章销售预测和利润管理【学习目标】通过本章的学习，学生应该掌握销
售预测的方法，能够熟练地使用Excel工具进行销售预测、制定定价决策，利用数据透视表功能进行销售业绩分析；要求学生在掌握利润管理基
本内容的基础上，能够熟练地运用Excel工具对利润进行目标分析、因素分析和杠杆分析，学会编制利润计划表和进行完成情况分析。销售预测
8.1点击添加文本点击添加文本点击添加文本点击添加文本点击添加文本点击添加文本点击添加文本点击添加文本【例8-1】假设某企业202
1年12个月的销售数据如图8-1所示。要求：利用移动平均法预测2022年1月的销售量。具体操作步骤如下：（1）从功能区【数据】的选
项卡中选择【数据分析】命令。（2）打开【数据分析】对话框，选择【移动平均】选项，打开【移动平均】对话框，如图8-2所示。（3）在【
输入区域】框中输入“$B$3：$B$14”，在【间隔】框中输入“3”表明是进行3期移动平均，在【输出区域】框中输入“$C$3”，最
后选择【图表输出】选项。图8-1 使用移动平均法预测销售量点击添加文本点击添加文本点击添加文本点击添加文本【例8-1】某企业201
6年12个月的销售数据如图8-1所示。要求：利用移动平均法预测2017年1月的销售量。 图8-2 【移动平均】对话框点击添加文本点
击添加文本点击添加文本点击添加文本【例8-1】（4）单击 【确定】按钮，则运算结果就显示在单元格区域C3：C14中，如图8-3所示
，同时图表也显示出来。其中单元格C14中的预测数据“85.00”，即为2007年1月的销售预测值。在实际工作中，可以分别采用几个不
同的期数进行销售预测分析，从中选择预测误差最小的期数作为移动期数。图8-3 使用指数平滑法预测销售量点击添加文本点击添加文本点击添
加文本点击添加文本【例8-2】假设某企业2021年12个月的销售数据如图8-3所示，假定阻尼系数为0.8。要求利用指数平滑法预测2
022年1月份的销售量。图8-3 使用指数平滑法预测销售量点击添加文本点击添加文本点击添加文本点击添加文本【例8-2】具体操作步骤
如下：（1）从功能区【工具】选项卡中选择【数据分析】命令。（2）打开【数据分析】对话框，选择【指数平滑】选项，打开【指数平滑】对话
框。（3）在【输入区域】框中输入“$B$3：$B$14”，在【阻尼系数】框中输入“0.8”，在【输出区域】框中输入“$C$3”，最
后选中【图表输出】选项，如图8-4所示。（4）单击【确定】按钮，则运算结果就显示在单元格区域C4：C14中，如图8-3所示，同时图
表也显示出来。其中单元格C14中的预测数据“70.11”即为2017年1月的销售量预测值。图8-4 【指数平滑】对话框利润管理8.
2点击添加文本点击添加文本点击添加文本点击添加文本假设分析是利润分析中常用的方法，这里介绍一种常用的假设工具——【单变量求解】。
通过例8-3，我们来了解一下【单变量求解】的计算方法。点击添加文本点击添加文本点击添加文本点击添加文本【例8-3】某公司2022年
的预计利润表见表8-1。现在，企业决策者希望知道如果2022年利润分别增至35 000元、40 000元、42 000元、45 0
00元、50 000元，销售收入、成本、费用、税金各是多少？（以销售收入为可变量，并假设成本率、费用率、税率保持不变）表8-1 某
公司2022年利润表数据 金额单位：元点击添加文本点击添加文本点击添加文本点击添加文本图8-5 单变量求解 具体操作步骤如下：（1
）新建工作表——单变量求解表（图8-5）。（2）选择功能区选项卡中的【数据】菜单条上的【模拟分析】，选择【单变量求解】命令，弹出对
话框，如图8-6所示：【目标单元格】表示期望控制的目标，这里填写“B10”，指税后利润数；【目标值】表示期望控制的目标的数值，这里
填写“35 000”；【可变单元格】表示可变动的参数，这里直接引用销售收入所对应的B3单元格。（3）单击【确定】按钮，弹出【单变量
求解状态】对话框，显示计算结果。图8-6 【单变量求解】对话框点击添加文本点击添加文本点击添加文本点击添加文本1）量本利分析概述点
击添加文本点击添加文本点击添加文本点击添加文本图8-7 单一产品的多因素变动分析模型 【例8-4】某公司生产和销售一种产品，其原定
的目标利润为100 000元，在此目标下，销售单价为12.5元，销售数量为70 000件，固定成本为200 000元，单位变动成本
为7.5元，据此设置基本数据区，具体如图8-8所示。2）量本利分析模型（1）单一产品量本利分析模型设计步骤①建立基本数据区。点击添
加文本点击添加文本点击添加文本点击添加文本第一步：激活【窗体】工具栏，即点击【开发工具】的【插入】按钮，并选择【表单控件】。第二步
：为各个因素建立一个【滚动条】控件。2）量本利分析模型（1）单一产品量本利分析模型设计步骤②建立多因素变动分析区。图8-8 【设置
控件格式】对话框点击添加文本点击添加文本点击添加文本点击添加文本③建立计算公式。第一，利润B8单元格的公式为：B5（B4-B7）
-B6第二，盈亏平衡点销售量=固定成本÷（单价-单位变动成本）考虑多因素变动影响，保本点D11单元格的公式为：B6（1+C6）/
（B4（1+C4）-B7（1+C7））第三，考虑多因素变动影响，预计利润B11单元格的公式为：（B4（1+C4）-B7（1
+C7））B5（1+C5）-B6（1+C6）第四，利润增减额C11单元格的公式为：“B11-B8”。2）量本利分析模型（1）
单一产品量本利分析模型设计步骤②建立多因素变动分析区。点击添加文本点击添加文本点击添加文本点击添加文本图8-9 单一因素的改变对利
润的影响【例8-5】某公司计划采取一项计算机管理程序，以提高工效，使单位变动成本降低5%，请预测其对利润的影响。【解析过程】只需在
多因素变动分析模型中，拖动单位变动成本的滚动条，如图8-9所示。 （2）多因素变动分析模型的使用 ①单一因素的改变对利润的影响。点
击添加文本点击添加文本点击添加文本点击添加文本图8-10 改变多因素对利润的影响【例8-6】某公司按照规定普调工资，使单位变动成本
增加7%、固定成本增加2%，这将导致利润下降。为了抵消这种影响，该公司拟采取两种措施：一是将销售单价提高7%，因提高价格，使销售数
量降低10%；另一种是降低销售单价4%，则增加销售数量18%。试分析该公司采用哪一种措施更有利。仍按照图8-8的模型进行多因素变动
分析模型的方案比较。 （2）多因素变动分析模型的使用 ②多因素的改变对利润的影响。点击添加文本点击添加文本点击添加文本点击添加文本
图8-11 量本利关系【例8-7】根据2016年6月份的数据，绘制出振华公司的盈亏平衡图。第一，绘制出振华公司的量本利关系表，如图
8-11所示。第二，编制成本习性表。 （2）多因素变动分析模型的使用 ③盈亏平衡图。点击添加文本点击添加文本点击添加文本点击添加
文本图8-12 成本习性【例8-7】根据2016年6月份的数据，绘制出振华公司的盈亏平衡图。第一，绘制出振华公司的量本利关系表，如
图8-11所示。第二，编制成本习性表。【例8-7】第三，绘制盈亏平衡图。 （2）多因素变动分析模型的使用 ③盈亏平衡图。点击添加
文本点击添加文本点击添加文本点击添加文本图8-13 盈亏平衡图【例8-7】第三，绘制盈亏平衡图。 （2）多因素变动分析模型的使用
③盈亏平衡图。点击添加文本点击添加文本点击添加文本点击添加文本图8-14 目标利润分析【例8-8】某公司生产和销售一种产品，其原
定的目标利润为366 000元，在此目标下，销售单价为17元，销售数量为90 000件，固定成本为300 000元，单位变动成本为
9.6元。现在假定该公司将目标利润定为380 000元。请分析从单个因素来看，影响目标利润的4个基本要素该怎样调整，资料如图8-1
4所示。 点击添加文本点击添加文本点击添加文本点击添加文本图8-15 【单变量求解】对话框 【例8-8】1）提高销售单价操作分析步
骤如下：①选择目标单元格即利润所在的单元格B8；②选择【数据】项下的【模拟分析】命令，打开【单变量求解】对话框；③此时目标单元格编
辑框中已有利润单元格地址B8，如果该地址有误，可重新输入正确的地址；④在【目标值】编辑框中输入目标利润“380 000”元；⑤在【
可变单元格】编辑框中输入销售单价单元格地址“$B$4”，如图8-15所示；⑥单击【确定】按钮，屏幕上出现【单变量求解状态】对话框，
如图8-16所示；⑦单击【确定】按钮，则在销售单价地址B4单元格中以计算结果“17.16”代替原值“17”，即当目标利润为380
000元时，销售单价应该提高到17.16 元。 图8-16 【单变量求解状态】对话框点击添加文本点击添加文本点击添加文本点击添加文
本 【例8-8】2）增加产销量操作分析步骤如下：①选择目标单元格即利润所在的单元格B8；②选择【数据】项下的【模拟分析】，打开【单
变量求解】对话框；③在【目标值】编辑框中输入目标利润“380 000”元；④在【可变单元格】编辑框中输入销售数量单元格地址“$B$
5”；⑤单击【确定】按钮，屏幕上出现【单变量求解状态】对话框；⑥单击【确定】按钮，则在销售数量地址B5单元格中以计算结果“91 8
91.89”代替原值“90 000”，即当目标利润为380 000元时，销售数量应该提高到91 891.89元。3）降低固定成本
操作分析步骤如下：①选择目标单元格即利润所在的单元格B8；②选择【数据】项下的【模拟分析】，打开【单变量求解】对话框；③在【目标值
】编辑框中输入目标利润“380 000”元；④在【可变单元格】编辑框中输入固定成本单元格地址“$B$6”；⑤单击【确定】按钮，屏幕
上出现【单变量求解状态】对话框；⑥单击【确定】按钮，则在固定成本地址B6单元格中以计算结果“286 000”代替原值“300 00
0”，即当目标利润为380 000元时，固定成本应该降低到286 000元。点击添加文本点击添加文本点击添加文本点击添加文本图8-
17 【单变量求解】对话框 【例8-8】 4）降低单位变动成本操作分析步骤如下：①选择目标单元格即利润所在的单元格B8；②选择【
数据】项下的【模拟分析】，打开【单变量求解】对话框，如图8-17所示；③在【目标值】编辑框中输入目标利润“380 000”元；④在
【可变单元格】编辑框中输入单位变动成本单元格地址“$B$7”；⑤单击【确定】按钮，屏幕上出现【单变量求解状态】对话框；⑥单击【确定
】按钮，则在单位变动成本地址B7单元格中以计算结果“9.44”代替原值“9.6”，即当目标利润为 380 000元时，单位变动成本
应该降低到9.44元。 点击添加文本点击添加文本点击添加文本点击添加文本点击添加文本点击添加文本点击添加文本点击添加文本图8-18
经营杠杆分析（原始资料）【例8-9】震源公司目前有两个投资方案A、B，其成本资料见表8-2。试根据以上资料，结合经营杠杆分析法，
分析当销售量为多少时，两个投资方案在成本上没有任何区别，均达到损益平衡点。具体操作步骤如下：①建立工作表——经营杠杆分析，如图8-
18所示。②输入销售量资料，可以用“填充柄”填。③分别求出方案A、B的总成本。B10=$B$5+$C$5A10；C10=$B$6
+$C$6A10（其余单元格的数值可以采用填充柄的方式填写）。④求出A、B两方案总成本之间的差额，如图8-19所示。D10=B1
0-C10，即可得到销售量为720时的差额（其余单元格的数值可以采用“填充柄”的方式填写）。点击添加文本点击添加文本点击添加文本点
击添加文本图8-19 经营杠杆分析（成本比较）【例8-9】震源公司目前有两个投资方案A、B，其成本资料见表8-2。试根据以上资料，
结合经营杠杆分析法，分析当销售量为多少时，两个投资方案在成本上没有任何区别，均达到损益平衡点。具体操作步骤如下：①建立工作表——经
营杠杆分析，如图8-18所示。②输入销售量资料，可以用“填充柄”填。③分别求出方案A、B的总成本。B10=$B$5+$C$5A1
0；C10=$B$6+$C$6A10（其余单元格的数值可以采用填充柄的方式填写）。④求出A、B两方案总成本之间的差额，如图8-1
9所示。D10=B10-C10，即可得到销售量为720时的差额（其余单元格的数值可以采用“填充柄”的方式填写）。点击添加文本点击添加文本点击添加文本点击添加文本点击添加文本点击添加文本点击添加文本点击添加文本图8-20 财务杠杆分析【例8-10】震源公司财务杠杆分析如图8-20所示。点击添加文本点击添加文本点击添加文本点击添加文本点击添加文本点击添加文本点击添加文本点击添加文本图8-21 总杠杆分析根据【例8-9】、【例8-10】的数据，可求出总杠杆系数DTL的大小，如图8-21所示。主要概念和观念主要概念量本利分析 目标利润 财务杠杆主要观念单变量求解主要用于利润管理中的基础分析的原因 在进行量本利分析时利用Excel 2016考察各个要素变动对利润的影响的方法 end |
|
