Excel在财务管理中的应用（第五版）第08章销售预测和利润管理

1、Excel在财务管理中的应用（第5版）08CHAPTER EIGHT第8章销售预测和利润管理【学习目标】通过本章的学习，学生应该掌握销售预测的方法，能够熟练地使用Excel工具进行销售预测、制定定价决策，利用数据透视表功能进行销售业绩分析；要求学生在掌握利润管理基本内容的基础上，能够熟练地运用Excel工具对利润进行目标分析、因素分析和杠杆分析，学会编制利润计划表和进行完成情况分析。销售预测8.1点击添加文本点击添加文本点击添加文本点击添加文本Excel的数据分析工具库(1)（2）（3） 移动平均法指数平滑法回归分析法点击添加文本点击添加文本点击添加文本点击添加文本8.1.1移动平均法【例8-

2、1】假设某企业2021年12个月的销售数据如图8-1所示。要求：利用移动平均法预测2022年1月的销售量。具体操作步骤如下：（1）从功能区【数据】的选项卡中选择【数据分析】命令。（2）打开【数据分析】对话框，选择【移动平均】选项，打开【移动平均】对话框，如图8-2所示。（3）在【输入区域】框中输入“$B$3：$B$14”，在【间隔】框中输入“3”表明是进行3期移动平均，在【输出区域】框中输入“$C$3”，最后选择【图表输出】选项。图8-1使用移动平均法预测销售量点击添加文本点击添加文本点击添加文本点击添加文本8.1.1移动平均法【例8-1】某企业2016年12个月的销售数据如图8-1所示。要求

3、：利用移动平均法预测2017年1月的销售量。 图8-2【移动平均】对话框点击添加文本点击添加文本点击添加文本点击添加文本8.1.1移动平均法【例8-1】（4）单击 【确定】按钮，则运算结果就显示在单元格区域C3：C14中，如图8-3所示，同时图表也显示出来。其中单元格C14中的预测数据“85.00”，即为2007年1月的销售预测值。在实际工作中，可以分别采用几个不同的期数进行销售预测分析，从中选择预测误差最小的期数作为移动期数。图8-3使用指数平滑法预测销售量点击添加文本点击添加文本点击添加文本点击添加文本8.1.1移动平均法【例8-2】假设某企业2021年12个月的销售数据如图8-3所示，假

4、定阻尼系数为0.8。要求利用指数平滑法预测2022年1月份的销售量。图8-3使用指数平滑法预测销售量点击添加文本点击添加文本点击添加文本点击添加文本8.1.1移动平均法【例8-2】具体操作步骤如下：（1）从功能区【工具】选项卡中选择【数据分析】命令。（2）打开【数据分析】对话框，选择【指数平滑】选项，打开【指数平滑】对话框。（3）在【输入区域】框中输入“$B$3：$B$14”，在【阻尼系数】框中输入“0.8”，在【输出区域】框中输入“$C$3”，最后选中【图表输出】选项，如图8-4所示。（4）单击【确定】按钮，则运算结果就显示在单元格区域C4：C14中，如图8-3所示，同时图表也显示出来。其中

5、单元格C14中的预测数据“70.11”即为2017年1月的销售量预测值。图8-4【指数平滑】对话框利润管理8.2点击添加文本点击添加文本点击添加文本点击添加文本 8.2.1利润管理基础分析 1）利润管理的基本内容 2）利润管理基本分析方法一是对影响获取利润的若干因素进行分析。二是进行目标利润分析。三是进行企业利润杠杆分析。假设分析是利润分析中常用的方法，这里介绍一种常用的假设工具【单变量求解】。 通过例8-3，我们来了解一下【单变量求解】的计算方法。点击添加文本点击添加文本点击添加文本点击添加文本 8.2.1利润管理基础分析【例8-3】某公司2022年的预计利润表见表8-1。现在，企业决策者希

6、望知道如果2022年利润分别增至35 000元、40 000元、42 000元、45 000元、50 000元，销售收入、成本、费用、税金各是多少？（以销售收入为可变量，并假设成本率、费用率、税率保持不变）表8-1某公司2022年利润表数据金额单位：元利润表项目2017年预计数营业收入126 000营业成本56 700毛利润69 300销售及管理费用20 000利息550利润总额48 750所得税16 000税后净利润32 750点击添加文本点击添加文本点击添加文本点击添加文本 8.2.1利润管理基础分析图8-5单变量求解 具体操作步骤如下：（1）新建工作表单变量求解表（图8-5）。（2）选择

7、功能区选项卡中的【数据】菜单条上的【模拟分析】，选择【单变量求解】命令，弹出对话框，如图8-6所示：【目标单元格】表示期望控制的目标，这里填写“B10”，指税后利润数；【目标值】表示期望控制的目标的数值，这里填写“35 000”；【可变单元格】表示可变动的参数，这里直接引用销售收入所对应的B3单元格。（3）单击【确定】按钮，弹出【单变量求解状态】对话框，显示计算结果。图8-6【单变量求解】对话框点击添加文本点击添加文本点击添加文本点击添加文本 8.2.2量本利分析 1）量本利分析概述030201（1）损益方程式利润=销售收入-总成本=销售收入-销售成本-期间费用=边际贡献-固定成本=销售量（销

8、售单价-单位变动成本）-固定成本盈亏平衡点销售量=（3）量本利图量本利图是以销售量为横轴、以成本或销售收入金额为纵轴建立一直角坐标系，在坐标系中标出成本与利润之间的关系。（2）贡献毛益方程式贡献毛益=产品的销售收入-变动成本单位贡献毛益=产品的单价-单位变动成本点击添加文本点击添加文本点击添加文本点击添加文本 8.2.2量本利分析图8-7单一产品的多因素变动分析模型 【例8-4】某公司生产和销售一种产品，其原定的目标利润为100 000元，在此目标下，销售单价为12.5元，销售数量为70 000件，固定成本为200 000元，单位变动成本为7.5元，据此设置基本数据区，具体如图8-8所示。2）

9、量本利分析模型（1）单一产品量本利分析模型设计步骤建立基本数据区。点击添加文本点击添加文本点击添加文本点击添加文本 8.2.2量本利分析第一步：激活【窗体】工具栏，即点击【开发工具】的【插入】按钮，并选择【表单控件】。第二步：为各个因素建立一个【滚动条】控件。2）量本利分析模型（1）单一产品量本利分析模型设计步骤建立多因素变动分析区。图8-8【设置控件格式】对话框点击添加文本点击添加文本点击添加文本点击添加文本 8.2.2量本利分析建立计算公式。第一，利润B8单元格的公式为：B5*（B4-B7）-B6第二，盈亏平衡点销售量=固定成本（单价-单位变动成本）考虑多因素变动影响，保本点D11单元格的

10、公式为：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.3.1年金函数PMT（）图8-9单一因素的改变对利润的影响【例8-5】某公司计划采取一项计算机管理程序，以提高工效，使单位变动成本降低5%，请预测其对利润的影响。【解析过程】只需在多因素变动分析模型中，拖动单位变

11、动成本的滚动条，如图8-9所示。 （2）多因素变动分析模型的使用 单一因素的改变对利润的影响。点击添加文本点击添加文本点击添加文本点击添加文本8.3.1年金函数PMT（）图8-10改变多因素对利润的影响【例8-6】某公司按照规定普调工资，使单位变动成本增加7%、固定成本增加2%，这将导致利润下降。为了抵消这种影响，该公司拟采取两种措施：一是将销售单价提高7%，因提高价格，使销售数量降低10%；另一种是降低销售单价4%，则增加销售数量18%。试分析该公司采用哪一种措施更有利。仍按照图8-8的模型进行多因素变动分析模型的方案比较。 （2）多因素变动分析模型的使用 多因素的改变对利润的影响。点击添加

12、文本点击添加文本点击添加文本点击添加文本8.3.1年金函数PMT（）图8-11量本利关系【例8-7】根据2016年6月份的数据，绘制出振华公司的盈亏平衡图。第一，绘制出振华公司的量本利关系表，如图8-11所示。第二，编制成本习性表。 （2）多因素变动分析模型的使用 盈亏平衡图。点击添加文本点击添加文本点击添加文本点击添加文本8.3.1年金函数PMT（）图8-12成本习性【例8-7】根据2016年6月份的数据，绘制出振华公司的盈亏平衡图。第一，绘制出振华公司的量本利关系表，如图8-11所示。第二，编制成本习性表。【例8-7】第三，绘制盈亏平衡图。 （2）多因素变动分析模型的使用 盈亏平衡图。点击

13、添加文本点击添加文本点击添加文本点击添加文本8.3.1年金函数PMT（）图8-13盈亏平衡图【例8-7】第三，绘制盈亏平衡图。 （2）多因素变动分析模型的使用 盈亏平衡图。点击添加文本点击添加文本点击添加文本点击添加文本8.2.3目标利润分析图8-14目标利润分析【例8-8】某公司生产和销售一种产品，其原定的目标利润为366 000元，在此目标下，销售单价为17元，销售数量为90 000件，固定成本为300 000元，单位变动成本为9.6元。现在假定该公司将目标利润定为380 000元。请分析从单个因素来看，影响目标利润的4个基本要素该怎样调整，资料如图8-14所示。 点击添加文本点击添加文本

14、点击添加文本点击添加文本8.2.3目标利润分析图8-15【单变量求解】对话框 【例8-8】1）提高销售单价操作分析步骤如下：选择目标单元格即利润所在的单元格B8；选择【数据】项下的【模拟分析】命令，打开【单变量求解】对话框；此时目标单元格编辑框中已有利润单元格地址B8，如果该地址有误，可重新输入正确的地址；在【目标值】编辑框中输入目标利润“380 000”元；在【可变单元格】编辑框中输入销售单价单元格地址“$B$4”，如图8-15所示；单击【确定】按钮，屏幕上出现【单变量求解状态】对话框，如图8-16所示；单击【确定】按钮，则在销售单价地址B4单元格中以计算结果“17.16”代替原值“17”，

15、即当目标利润为380 000元时，销售单价应该提高到17.16 元。 图8-16【单变量求解状态】对话框点击添加文本点击添加文本点击添加文本点击添加文本8.2.3目标利润分析 【例8-8】2）增加产销量操作分析步骤如下：选择目标单元格即利润所在的单元格B8；选择【数据】项下的【模拟分析】，打开【单变量求解】对话框；在【目标值】编辑框中输入目标利润“380 000”元；在【可变单元格】编辑框中输入销售数量单元格地址“$B$5”；单击【确定】按钮，屏幕上出现【单变量求解状态】对话框；单击【确定】按钮，则在销售数量地址B5单元格中以计算结果“91 891.89”代替原值“90 000”，即当目标利润

16、为380 000元时，销售数量应该提高到91 891.89元。3）降低固定成本 操作分析步骤如下：选择目标单元格即利润所在的单元格B8；选择【数据】项下的【模拟分析】，打开【单变量求解】对话框；在【目标值】编辑框中输入目标利润“380 000”元；在【可变单元格】编辑框中输入固定成本单元格地址“$B$6”；单击【确定】按钮，屏幕上出现【单变量求解状态】对话框；单击【确定】按钮，则在固定成本地址B6单元格中以计算结果“286 000”代替原值“300 000”，即当目标利润为380 000元时，固定成本应该降低到286 000元。点击添加文本点击添加文本点击添加文本点击添加文本8.2.3目标利润

17、分析图8-17【单变量求解】对话框 【例8-8】 4）降低单位变动成本操作分析步骤如下：选择目标单元格即利润所在的单元格B8；选择【数据】项下的【模拟分析】，打开【单变量求解】对话框，如图8-17所示；在【目标值】编辑框中输入目标利润“380 000”元；在【可变单元格】编辑框中输入单位变动成本单元格地址“$B$7”；单击【确定】按钮，屏幕上出现【单变量求解状态】对话框；单击【确定】按钮，则在单位变动成本地址B7单元格中以计算结果“9.44”代替原值“9.6”，即当目标利润为 380 000元时，单位变动成本应该降低到9.44元。 点击添加文本点击添加文本点击添加文本点击添加文本 8.2.4利

18、润综合杠杆分析 1）经营杠杆分析经营杠杆分析涉及企业固定运营成本水平及其变动程度分析，而企业经营杠杆能力，就是包括固定制造成本、固定销售费用和固定管理费用等固定性经营成本在内的经营程度杠杆。经营杠杆系数（DOL）就是指因固定性费用的存在所导致的息税前利润随产量变动而变动的程度。点击添加文本点击添加文本点击添加文本点击添加文本 8.2.4利润综合杠杆分析图8-18经营杠杆分析（原始资料）【例8-9】震源公司目前有两个投资方案A、B，其成本资料见表8-2。试根据以上资料，结合经营杠杆分析法，分析当销售量为多少时，两个投资方案在成本上没有任何区别，均达到损益平衡点。具体操作步骤如下：建立工作表经营杠

19、杆分析，如图8-18所示。输入销售量资料，可以用“填充柄”填。分别求出方案A、B的总成本。B10=$B$5+$C$5*A10；C10=$B$6+$C$6*A10（其余单元格的数值可以采用填充柄的方式填写）。求出A、B两方案总成本之间的差额，如图8-19所示。D10=B10-C10，即可得到销售量为720时的差额（其余单元格的数值可以采用“填充柄”的方式填写）。点击添加文本点击添加文本点击添加文本点击添加文本 8.2.4利润综合杠杆分析图8-19经营杠杆分析（成本比较）【例8-9】震源公司目前有两个投资方案A、B，其成本资料见表8-2。试根据以上资料，结合经营杠杆分析法，分析当销售量为多少时，两个投资方案在成本上没有任何区别，均达到损益平衡点。具体操作步骤如下：建立工作表经营杠杆分析，如图8-18所示。输入销售量资料，可以用“填充柄”填。分别求出方案A、B的总成本。B10=$B$5+$C$5*A10；C10=$B$