模型建立.docx

模型建立.docx

《模型建立.docx》由会员分享，可在线阅读，更多相关《模型建立.docx（18页珍藏版）》请在冰豆网上搜索。

模型建立

本-量-利分析模型及经营预测模型的的创建

1.本-量-利分析模型的创建

本-量-利分析模型是利用Excel工作表的相关功能，根据本-量-利分析的基本原理和分析方法而创建的专门用于分析成本、销量、利润之间的相互关系。

本-量-利分析模型的建立是计算机技术在管理会计中应用的主要标志之一，对于提升管理会计信息质量，减轻管理会计人员的劳动强度，提高管理会计人员各种效率都具有重要意义。

由于主教材中已经对本-量-利方法作了介绍，因此，这里主要介绍在Excel工作表中创建盈亏临界点基本计算模型，实现税后目标利润模型的方法

本-量-利分析案例

假定江海电器有限公司生产销售某一种产品2010年的有关资料如下：

单位售价

单位变动成本

固定成本总额

目标税后利润

所得税税率

70

50

4800元

1200

25%

要求：

根据以上资料计算下列指标：

（P78-79）

1）单位贡献毛益。

2）保本量、保利量、保本额和保利额。

3）单位售价提高到72元的保本量、保本额、保利量和保利额。

4）单位变动成本下降到48元的保本量、保本额、保利量和保利额。

5）固定成本总额上升到5000元的保本量、保本额、保利量和保利额。

6）目标税后利润提高到1300元的保本量、保本额、保利量和保利额。

7）所得税税率提高到40%的保本量、保本额、保利量和保利额。

8）单位售价提高到72元，单位变动成本下降到48元，固定成本上升到5000元，目标税后利润提高到1300元，所得税税率提高到40%五个因素同时变动后的保本量、保本额、保利量和保利额。

我们发现，依靠手工保本量，保本额，保利量和保利额进行计算时，工作量是相当繁重的。

特别是当表中的某一个因素发生变化时，就需要对保本和保利销售量及销售额进行修正，这是就必须重新计算。

现在让我们来创建本-量-利分析模型，并体验Excel工作表是如何解决上述问题的。

2．2．2创建盈亏临界点基本计算模型的步骤和方法

在Excel工作表中创建高盈亏临界点基本计算模型的步骤如下

第一步

启动Excel工作表，将案例资料以及需要计算的有关指标录入Excel表。

如图所示

第二步

输入相关的公式和函数创建盈亏临界点基本计算模型

在Excel工作表中创建盈亏临界点基本计算模型主要使用公式和函数。

在Excel工作表中输入公式和函数，计算盈亏临界点基本计算模型的相关指标。

计算单位贡献毛益

用鼠标选中单元格A10，然后在单元格中输入相关公式”=A3-B3”,如图所示，按下enter键，计算出此时的单位贡献毛益为20元。

计算保本量

建立保本量计算模型：

保本量=固定成本/（单价-单位变动成本）

选中单元格B10，然后再单元格中输入公式”=C3/（A3-B3）”,如图所示，再按enter键，得出结果，保本量为240件。

注：

考虑到，保本销售量不能为小数，所以需要对保本量模型进行修正。

即使之变为，当计算出的保本量为小数时，自动取整再加1，例如：

当单位售价为72时，计算出的保本量为218.2.件，模型直接进行取整加1，即为219件。

操作如下：

选中单元格C10，然后在菜单栏的“插入”命令中选择“函数”命令。

弹出“插入函数”对话框，在“或选择类别（C）”中选择“常用函数”类，在“选择函数（N）”中选择“IF”（相关系数）函数名。

然后按下“确定”按钮，弹出“IF”函数的“函数参数”编辑框。

在Logical-test对话框中输入条件”B10-TRUNC（B10）>0”（表示如果B10数值，也就是计算出的保本量减去保本量取整后的数值大于0），再在Value-if-true对话框中输入”TRUNC（B10）+1”（表示保本量取整加1），最后在Value-if-false对话框中输入”B10”。

（整条语句表示的意思是，若计算出的保本量为小数，则修正的保本来量为保本量取整加1，若为整数，则修正的保本量仍为未修正的数值，而且计算出的保本量肯定大于或等于计算出的实际保本量取整）。

如图所示：

再按确定，得出结果为219件。

计算保本额

建立保本额计算模型：

保本额=固定成本/（单位贡献毛益/单价）=保本量（修正保本量）*销售单价

首先将单价仍改为原来的70元，用鼠标选中单元格D10，然后在单元格中输入公式”==C10*A3”,如图所示，单击“Enter”键，得出保本额为16800元。

实现税后目标利润的模型建立

计算保利量

实现目标利润的销售量（保利量）=（税前目标利润+固定成本）/单位贡献毛益=（税后目标利润/（1-所得税税率）+固定成本）/单位贡献毛益

用鼠标选中单元格D10，然后在单元格中输入公式”=（D3/（1-E3）+C3）/A10”，如图所示，按下Enter键，所得结果为320件

注：

保利量同样存在保本量相同的问题，可能为小数，所以需要修正的保利量进行准确修改。

操作同修正保本量的操作：

选中单元格F10，然后在菜单栏的“插入”命令中选择“函数”命令。

弹出“插入函数”对话框，在“或选择类别（C）”中选择“常用函数”类，在“选择函数（N）”中选择“IF”（相关系数）函数名。

然后按下“确定”按钮，弹出“IF”函数的“函数参数”编辑框。

在Logical-test对话框中输入条件”E10-TRUNC（E10）>0”（表示如果E10数值，也就是计算出的实际保本量减去保本量取整后的数值大于0），再在Value-if-true对话框中输入”TRUNC（E10）+1”（表示保本量取整加1），最后在Value-if-false对话框中输入”E10”。

（整条语句表示的意思是，若计算出的保本量为小数，则修正的保本来量为保本量取整加1，若为整数，则修正的保本量仍为未修正的数值，而且计算出的保本量肯定大于或等于计算出的实际保本量取整）。

如图所示：

再单击确定按钮，得出结果为320件。

计算保利额

实现目标利润的销售额（保利额）=（税前目标利润+固定成本）/单位贡献毛益率=（税后目标利润/（1-所得税税率）+固定成本）/（单位贡献毛益/单价）=保利量（修正）*销售单价

用鼠标选中单元格E10，然后在单元格中输入公式”==F10*A3”，如图所示，按下Enter键，所得结果为22400元。

第三步

调试和运行模型，计算出相应的结果。

对创建完成的本-量-利分析模型中的公式和函数进行检查和调试，确认无误后运行模型，就可以分别计算出在各个条件变动的情况下计算出相应的保本量，保本额，保利量和保利额，根据所建的本-量-利分析模型，计算出来保本量，保本额，保利量和保利额分别是240件16800元，320件，22400元。

至此，在Excel工作表中本-量-利分析模型就完成了。

现在我们可以利用本-量-利分析模型来计算当单价，单位变动成本，固定成本等变动时的保本量，保本额，保利量，保利额。

1．当单位售价提高到72元的保本量、保本额、保利量和保利额。

当销售单价变为72元时，只需将模型中70元改为72元，这时，相应的保本量，修正保本量保本额，保利量，修正保利量，保利额变为218.2件，219件，15768元，290.9件，291件，20952元，如图：

（注：

保本额和保利额分别根据修正的保本量和保本额计算得出）

2.单位变动成本下降到48元的保本量、保本额、保利量和保利额。

当单位变动成本下降到48元时，只需将模型中50元改为48元，这时，相应的保本量，修正保本量保本额，保利量，修正保利量，保利额变为218.2件，219件，15330元，290.9件，291件，203702元，如图：

（注：

保本额和保利额分别根据修正的保本量和保本额计算得出）

3.固定成本总额上升到5000元的保本量、保本额、保利量和保利额。

当固定成本上升到5000元时，只需将模型中4800元改为5000元，这时，相应的保本量，修正保本量保本额，保利量，修正保利量，保利额变为250件，250件，17500元，330件，330件，,23100元，如图：

（注：

保本额和保利额分别根据修正的保本量和保本额计算得出）

6）目标税后利润提高到1300元的保本量、保本额、保利量和保利额。

当目标税后利润上升到1300元时，只需将模型中1200元改为1300元，这时，相应的保本量，修正保本量保本额，保利量，修正保利量，保利额变为240件，240件，16800元，326.7件，327件，,22890元，如图：

（注：

保本额和保利额分别根据修正的保本量和保本额计算得出）

7）所得税税率提高到40%的保本量、保本额、保利量和保利额。

当目所得税税率提高到40%时，只需将模型中25%元改为40%，这时，相应的保本量，修正保本量保本额，保利量，修正保利量，保利额变为240件，240件，16800元，340件，340件，,23800元，如图：

（注：

保本额和保利额分别根据修正的保本量和保本额计算得出）

8）单位售价提高到72元，单位变动成本下降到48元，固定成本上升到5000元，目标税后利润提高到1300元，所得税税率提高到40%五个因素同时变动后的保本量、保本额、保利量和保利额。

当单位售价提高到72元，单位变动成本下降到48元，固定成本上升到5000元，目标税后利润提高到1300元，所得税税率提高到40%五个因素同时变动时，只需将相应的因素作相应的变化，这时，相应的保本量，修正保本量保本额，保利量，修正保利量，保利额变为208.3件，209件，15048元，298.6件，299件，,21528元，如图：

（注：

保本额和保利额分别根据修正的保本量和保本额计算得出）

2.制造费用预测模型的创建

制造费用预测模型是利用Excel工作表的相关功能，根据相关原理和方法而创建的专门用于预测制造费用的计算分析程序。

由于在企业的生产过程中不可避免会产生相关的制造费用。

而且制造费用是产品成本的重要组成部分。

所以制造费用预测模型的建立对于提升管理会计信息质量，减轻管理会计人员的劳动强度，提高管理会计人员各种效率都具有重要意义。

因此，这里主要介绍在Excel工作表中创建多元回归分析法对制造费用进行预测。

制造费用预测案例

假定某公司对过去的成本资料进行了分析，发现直接人工小时和机器小时是制造费用的影响因素。

其2005-2009年的有关资料如下所示。

项目

2005年

2006年

2007年

2008年

2009年

制造费用/万元

3200

2501

2700

3135

2964

直接人工小时

26000

15000

18000

21000

20000

机器小时

50000

35000

40000

45000

40000

要求:

根据以上资料，用多元回归法预测2010年的制造费用，并写出成本方程。

预计2010年的直接人工小时为26000小时，机器小时为51000小时，制造费用为多少？

制造费用在生产制造企业中是经常发生的，作为产品成本的重要组成部分，制造费用的预测对企业的生产决策以及成本控制都有很大的作用。

这就要求工作人能够尽量准确的预测制造费用，对工作人员的要求很高，而且工作量极大。

但是通过excel建立制造费用预测模型可以使问题大大简化，极大的减轻了相关人员的工作负担。

对制造费用进行预测可以采用多种方法，现在仅介绍采用多元回归分析法建立制造费用预测模型进行预测。

2．2．2创建制造费用预测模型的步骤和方法

1）首先建立多元回归模型

Y=a+bX1+cX2（Y为制造费用，X1为直接人工，X2为机器工时）

2）建立以下三元一次方程组，解出a,b,c的值

ΣYi=na+bΣX1i+cΣX2i

ΣX1iYi=aΣX1i+bΣ（X1i*X1i）+cΣX1iX2i

ΣX2iYi=aΣX2i+bΣX1iX2i+cΣ（X2i*X2i）

在Excel工作表中创建制造费用预测模型的步骤如下

第一步

启动Excel工作表，将案例资料以及需要计算的有关指标录入Excel表。

如图所示

第二步

输入相关的公式和函数创建制造费用预测模型

在Excel工作表中创建制造费用模型主要使用公式和函数。

在Excel工作表中输入公式和函数，计算制造费用模型的相关指标。

1）首先计算X1i平方与X2i的平方，选中单元格E11，然后在菜单栏的“插入”命令中选择“函数”命令。

弹出“插入函数”对话框，在“或选择类别（C）”中选择“数学与三角函数”类，在“选择函数（N）”中选择“POWER”（平方函数）函数名。

然后按下“确定”按钮，弹出“POWER”函数的“函数参数”编辑框。

在Number对话框中输入C11，再在Power对话框中输入2，如图所示，单击确定，得出所求值。

再选中E11，并拖动右下角的填充柄，就可以得到E11—E15中的数值，同理X2i的平方仿照前述步骤，最后得出结果，如图所示：

2）计算X1iX2i,X1iYi,X2iYi的值，以X1iX2i为例，步骤如下：

用鼠标选中单元格G11，然后在菜单栏的“插入”命令中选择”函数”命令。

弹出“插入函数”对话框，在“或选择类别（C）”中选择“数学与三角函数”类，在“选择函数（N）”中选择“PRODUCT”函数名。

按下“确定”按钮，弹出“PRODUCT”函数的“函数参数”编辑框。

单击参数“Number1”编辑框右边的红色箭头“折叠编辑框按钮”，折叠粘贴函数编辑框，然后用鼠标选择单元格C11和D11，如图所示，单击确定，计算出结果。

再选中G11，并拖动右下角的填充柄，就可以得到G11—G15中的数值，同理X1iYi,X2iYi的值仿照前述步骤，最后得出结果，如图所示：

3）各列进行求和，计算出ΣYi，ΣX1i，ΣX2i，ΣX1iYi，Σ（X1i*X1i），ΣX1iX2i，ΣX2iYi的值，以ΣYi为例，其步骤如下:

选中单元格B16，然后在菜单栏的“插入”命令中选择”函数”命令。

弹出“插入函数”对话框，在“或选择类别（C）”中选择“数学与三角函数”类，在“选择函数（N）”中选择“SUM”函数名。

按下“确定”按钮，弹出“SUM”函数的“函数参数”编辑框。

单击参数“Number1”编辑框右边的红色箭头“折叠编辑框按钮”，折叠粘贴函数编辑框，然后用鼠标选择单元格B11和B15，如图所示，单击确定，计算出结果。

ΣX1i，ΣX2i，ΣX1iYi，Σ（X1i*X1i），ΣX1iX2i，ΣX2iYi的值均同以上步骤，这里不再赘述，最后结果如图：

将表中数据代入方程组得：

{14500=5a+100000b+c210000

294430000=100000a+2066000000b+4290000000c

615170000=210000a+4290000000b+8950000000c}

三元一次方程组求解模型的建立

1）由于excel2007中并没有宏的使用，所以首先要进行宏的加载，其步骤如下：

第一步：

点击Excel左上角徽标选择”Excel选项”再到”加载项”,然后点击”管理”中的”转到”就会出现加载项列表，勾选规划求解加载项即可。

第二步点击Excel左上角徽标，选择”Excel选项”,再到”信任中心”中的”信任中心设置-----宏设置”选择“禁止无数字签署的所有宏”即可成功添加加载宏。

2）将相关数据输入excel表中，其结果如下：

3）设置未知数及方程组

选中单元格F23，在单元格中输入“=5*F20+100000*F21+210000*F22”，选中F24单元格，并输入“=100000*F20+2066000000*F21+4290000000*F22”，最后在F25中输入“=210000*F20+4290000000*F21+8950000000*F22”，其结果如图所示：

选择“数据”菜单栏中的规划求解，打开规划求解参数对话框，在“设置目标单元格”中选择F23，选择“值为”，并填入14500，在“可变单元格”中选择

F20：

F22。

单击“添加”按钮，

单击“添加”按钮，在“添加约束”中的“单元格引用位置”中点击F24，选择“=”“约束值”中填入294430000，单击添加，在“添加约束”中的“单元格引用位置”中点击F25，选择“=”“约束值”中填入615170000，如图所示，

再单击确定，回到“规划求解参数”界面，再单价“求解”选项，即可得到a,b,c的值，为1296.17，0.043,0.018，如图所示

调试和运行模型，计算出相应的结果。

对创建完成的制造费用预测模型中的公式和函数进行检查和调试，确认无误后运行模型，就可以分别计算出在各个条件变动的情况下制造费用的金额。

根据所建的制造费用预测模型，计算出的a,b,c分别为1296.17，0.043,0.018。

则多元回归分析法得出的制造费用预测模型为：

Y=1296.17+0.043X1+0.018X2现在我们可以制造费用预测模型来预测制造费用。

预计2010年的直接人工小时为26000小时，机器小时为51000小时，则制造费用为：

Y=1296.17+0.043*26000+0.018*51000

=3317.25