基于Excel的企业经营预测模型设计
开篇：润墨网以专业的文秘视角，为您筛选了一篇基于Excel的企业经营预测模型设计范文，如需获取更多写作素材，在线客服老师一对一协助。欢迎您的阅读与分享！
摘要：在会计电算化软件的发展进程中，财务会计的信息处理问题已基本得到了解决，但管理会计中的许多信息处理问题还急需解决。从企业经营预测的角度，分别设计了单一产品保本点预测分析模型和多种产品综合保本点预测分析模型，以其实现借助excel这一IT分析工具来解决管理会计中的信息处理的问题，更好地帮助企业的管理者进行预测与决策。
关键词：Excel；预测；模型；企业经营
中图分类号：F224.0文献标志码：A文章编号：1673-291X（2011）21-0103-03
一、设计背景
预测分析是在企业经营预测过程中，根据过去和现在预计未来以及根据已知推测未知数据的各种科学的专门分析方法。预测分析的内容包括有保本点预测分析、利润预测分析、销售预测分析以及成本预测分析等。本文仅对保本点预测分析进行模型设计。
“保本点”（break-even point）是指企业在这一点上正好处于不盈不亏的状态，即“所得”等于“所费”，或“销售收入”等于“销售成本”。它在管理会计中是一项很重要的管理信息，是企业获得利润的基础，任何一个企业为了预测利润，必须首先预测“保本点”。保本点通常有两种表现形式：以实物量的表现，称为“保本销售量”；或以货币金额来表现，称为“保本销售额”。其分析方法有量本利分析法和贡献毛益分析法。
二、相关函数及分析工具简介
（一）OFFSET函数
OFFSET函数是常用的查找函数之一。它以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。
函数语法：
OFFSET（reference，rows，cols，height，width）
Reference作为偏移量参照系的引用区域。Reference 必须为对单元格或相连单元格区域的引用；否则，函数 OFFSET 返回错误值 #VALUE!。
Rows相对于偏移量参照系的左上角单元格，上（下）偏移的行数。如果使用 5 作为参数 Rows，则说明目标引用区域的左上角单元格比 reference 低 5 行。行数可为正数（代表在起始引用的下方）或负数（代表在起始引用的上方）。
Cols相对于偏移量参照系的左上角单元格，左（右）偏移的列数。如果使用 5 作为参数 Cols，则说明目标引用区域的左上角的单元格比 reference 靠右 5 列。列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。
Height高度，即所要返回的引用区域的行数。Height 必须为正数。
Width宽度，即所要返回的引用区域的列数。Width 必须为正数。
（二）IF函数
IF函数是较为常用的逻辑函数之一，它执行真假值判断，根据逻辑计算的真假值，返回不同结果。可以使用函数IF对数值和公式进行条件检测。
函数语法：
IF（logical_test，value_if_true，value_if_false）
Logical_test表示计算结果为TRUE或FALSE的任意值或表达式。
Value_if_truelogical_test为TRUE 时返回的值。
Value_if_falselogical_test为FALSE时返回的值。
如果函数IF的参数包含数组，则在执行IF语句时，数组中的每一个元素都将计算。
（三）模拟运算表
Excel模拟运算表工具是一种只需一步操作就能计算出所有变化的模拟分析工具。它可以显示公式中某些值的变化对计算结果的影响，为同时求解某一运算中所有可能的变化值组合提供了捷径。并且，模拟运算表还可以将所有不同的计算结果同时显示在工作表中，便于查看和比较。
三、分析模型设计
（一）单一产品保本点预测分析模型设计
1.量本利分析法
（1）新建表。打开Excel工作簿，新建一工作表，命名为“量本利分析”，根据相关资料建立“量本利分析模型表”（如图1所示）。
（2）计算保本点销售量与销售额。选中A9单元格在编辑栏中输入“=D3/（B3-C3）”；即可求得保本点的销售量为500盒；选中B9单元格在编辑栏中输入“=A9*B3”；即可求得保本点的销售额为52 500元（如图2所示）。
（3）设置动态模型。假设单价在50元~300元之间变动、单位变动成本在500元~1 000元之间变动、固定成本在8 000元~15 000元之间变动，保本点销售量与销售额如何变化。
在菜单栏的空白处右击选择[窗体]，在打开的[窗体]工具条中，添加一个[微调控件]，右击[微调控件]选择[设置控件格式]在弹出的[设置控件格式]对话框将单价范围设置在50元~300元之间。同理可进行单位变动成本和固定成本的设置。结果（如图3所示），单击三个不同的滚动条，保本点销售量和销售额也随之发生变动。
2.贡献毛益分析法（以上题为例）
（1）新建表。在打开的Excel工作簿，新建一工作表，命名为“贡献毛益分析”，根据相关资料建立“贡献毛益分析模型表”（如图4所示）。
（2）计算保本点销售量与销售额。选中A9单元格在编辑栏中输入“=B6/B5”；即可求得保本点的销售量为500盒；选中B9单元格在编辑栏中输入“B6/（B5/B3）”；即可求得保本点的销售额为52 500元（如图5所示）。
（3）设置动态模型。同前述。将[微调控件]改成[滚动条]，结果（如图6所示），单价为109元、单位变动成本为88元、固定成本为10 011元时的保本点销售量与销售额。
3.模拟运算
选中D9单元格在编辑栏中输入“=B3*A9”，计算销售收入；选中E9单元格在编辑栏中输入“=A9*B4+B6”，计算成本总额；选中F9单元格在编辑栏中输入“= D9 -E9”，计算利润总额。
选中C9：F15单元格区域单击[数据][模拟运算表]在弹出的[模拟运算表]对话框中输入如图7所示的参数按[回车]键确认即可，这样即可计算出不同销售量所对应的销售收入、成本总额以及利润总额。
从模拟运算表中我们可以清楚地看出，销售量500时的利润总额等于0。
1.新建表
打开Excel工作簿，新建一工作表，命名为“多种产品保本分析”，根据相关资料建立“多种产品保本点预测模型”（如图8所示）。
2.计算相关指标
单位贡献毛益：选中B6单元格在编辑栏中输入“=B4-B5”，复制公式至D6单元格即可。
贡献毛益率：选中B7单元格在编辑栏中输入“=B6/B4”，复制公式至D7单元格即可。
销售收入总额：选中B8单元格在编辑栏中输入“=B4*B3”，复制公式至D8单元格即可。
销售比重：选中B9单元格在编辑栏中输入“=B8/$E$8”，复制公式至D9单元格即可。
注意这里的E8单元格要使用绝对引用。
加权贡献毛益率：选中B10单元格在编辑栏中输入“=B4-B5”，复制公式至D10单元格即可。
综合保本销售额：选中B11单元格在编辑栏中输入“=19992/E10”即可。
A、B、C产品的保本销售额：选中B12单元格在编辑栏中输入“=$B$11*B$9”，复制公式至D12单元格，即可分别求出B、C产品的保本销售额。
3.设置动态模型
动态模型的设计方法同单一产品相同。
4.制作动态图表
制作一个动态图表，反映A、B、C三种产品保本点销售额与综合保本销售额之间的关系。
选中A21单元格在编辑栏中输入“=IF($B$20=1，OFFSET（A13，0，1），IF（C20=2，OFFSET（A13，0，2），OFFSET（A13，0，3）））”，即可。
该公式的含义是，假设B20单元格值为1时，返回B13单元格的值，如果B20单元格值2时，返回C13单元格的值，如果B20单元格值不为1或2时，返回D13单元格的值。
选中B21单元格在编辑栏中输入“=IF（$B$20=1，OFFSET（A12，0，1），IF（B20=2，OFFSET（A12，0，2），OFFSET（A12，0，3）））”即可。公式含义同前。
选中A11：B11、 A21：B21单元格区域单击[工具栏]上的[图表向导]按钮，建立一饼形图。
打开窗体控件，添加三个单选按钮，分别命名为“A产品”、“B产品”、“C产品”；右击[A产品]单元按钮选择[设置控件格式]命令在打开的[设置控件格式]对话框中操作，其他两个案钮同理。
四、小结
总之，建立保本预测模型与动态保本图不仅仅能计算出在固定条件下的保本点的销售量与销售额，用来控制企业的利润，而且能把本量利三者之间的依存关系描绘得更为直观的同时，反映不同单价、单位变动成本以及固定成本条件下的保本点销售量与销售额的变化情况，便于经营者进行预决策分析。
参考文献：
[1]Excel HOME.Excel应用大全[M].北京:中国邮电出版社，2008:1-3.[责任编辑 陈丹丹]