EXCEL建模（共3篇）
EXCEL建模 篇1
存货是企业一项很重要的资产,在企业的流动资产中占有重要的地位。在企业的生产经营中,对发出存货成本的计算是否准确,会直接影响有关成本、当前损益和有关税金的计算,也直接影响各期期末存货价值的确定。按现行制度规定,企业存货按照实际成本核算的,应当采用先进先出法、加权平均法(包括移动加权平均法)或个别计价法确定发出存货的实际成本,而先进先出法和移动加权平均法在核算存货成本时计算繁锁且工作量大,给实务工作者带来很多不便。
本文结合实例探讨如何利用Excel的函数等计算功能设计存货成本自动计算模型,以提高存货成本计算的准确性和时效性。
例:某企业2014年6月1日A材料期初结存150公斤, 单价10元。2014年6月,A材料收发情况如下:6月5日,购入100公斤,每公斤进价12元;6月10日,发出200公斤;6月15日,购入200公斤,每公斤进价14元 ;6月20日,发出100公斤;6月25日,购入100公斤,每公斤进价15元 ;6月30日,发出100公斤。分别采用先进先出法、加权平均法和移动加权平均法对期末存货进行计价并计算本期耗用的成本。
下面根据实例,分步骤建立存货成本自动计算模型。
一、设计模型基本界面
按图1所示,在Excel工作表的1 ~ 30行建立“模型基本界面”,由于篇幅的关系,图示中的模型隐藏了部分行。 单元格E4和F4供用户输入上期结存存货的数量和单价; 从第5行开始的E列和F列供用户输入每次入库存货的数量和单价,H列供用户输入每次发出存货的数量;模型的左上角设计了一个“清零”的宏按钮,模型的右上角设计了一个选择存货计价方式的“组合框”控件,其中设置了 “先进先出法、加权平均法、移动平均法”三种方法供用户选择。
二、设计宏按钮和组合框
1. 设计并指定“清零”宏按钮。如图1所示,在模型的左上角制作一个“清零”按钮并指定宏,用于计算完后对指定单元格进行清零,以方便下次处理时输入数据。其宏代码如下:
2. 设计组合框。在模型的右上角制作一个组合框,指定其数据源区域为$N$2:$N$4,单元格链接到$N$1,在N2、N3、N4单元格中分别输入存货计价方法的名称,其中,N2为“先进先出法”、N3为“加权平均法”、N4为“移动加权平均法”,以方便用户根据需要选择存货计价方法。
三、定义模型相应单元格的计算公式
如图1所示,将单元格A1 ~ M1合并成一个单元格, 并定义公式:“=INDEX(N2:N4,N1,1)&"存货成本计算模型"”,是模型标题。在工作表中的K4、L4、M4单元格中设置计算公式,分别为:“=E4”、“=F4”、“=K4*L4”,用于将手工录入的月初结存存货的数据从模型的“入库”栏调入到“结存”栏。
在工作表中第5行的相应单元格中分别定义计算公式,各单元格的计算公式如表1所示:
将G5单元格中的公式复制到G6~G29单元格,同样的方法,将I5、J5、K5、L5、M5单元格中的公式分别复制到I6~I29、J6~J29、K6~K29、L6~L29和M6~M29单元格中。
在工作表中第30行的相应单元格中分别定义计算公式,各单元格的计算公式如表2所示:
上述公式全部设置完毕后,一个存货成本自动计算模型就建好了。将要进行数据处理的期初结转存货和本期购入存货的数量、单价以及发出存货数量等有关原始数据分别输入模型中的相应单元格,在右上角的“组合框”中选择存货计价方式后,模型会自动计算出本期发出和期末结存存货成本,计算结果见图2、图3。
四、模型说明
该模型的设计只考虑到了30行以内的情形,如果企业存货收发业务量较大,用户只需在30行的上方插入行并对相应单元格的公式进行复制即可。模型建立好之后,用户只需在模型的相关列输入数据,选择相应的存货计价方法,模型就会自动计算出相应存货计价方法下的本期存货发出成本和期末结存成本。当用户进行下期的计算时,只需单击“清零”按钮,模型相关单元格数据会自动清空,回到图1所示的情形,供用户进行处理。
参考文献
财政部会计司编写组.企业会计准则讲解[M].北京:人民出版社,2007.
EXCEL建模 篇2
一、“规划求解”的加载
使用Excel中的“规划求解”功能与大家熟悉的Excel菜单项不同, 在Excel 的菜单中不能直接找到。“规划求解”是Microsoft Office Excel的一个加载项 (加载项是为 Microsoft Office 提供自定义命令或自定义功能的补充程序) 程序。要在 Excel 中使用规划求解, 必需要先进行加载。 以Excel2007为例说明其加载过程。 (1) 单击“Office 按钮”, 然后单击“Excel 选项”。 (2) 单击“加载项”, 然后在“管理”框中, 选择“Excel 加载宏”, 单击“转到”按钮。 (3) 在“可用加载宏”框中, 选中“规划求解加载项”复选框, 然后单击“确定”。 (提示:如果“规划求解加载项”未在“可用加载宏”中列出, 请单击“浏览”找到该加载宏。如果出现一条消息, 指出您的计算机上当前未安装规划求解加载宏, 请单击“是”进行安装。) (4) 加载规划求解加载宏后, “规划求解”命令将出现在“数据”选项卡的“分析”组中。这时就可以使用“规划求解”对线行规划问题进行求解了。
二、“规划求解”设计的指导原则
使用Excel的“规划求解”命令, 对于一些已知数据的输入要有一定的组织性和逻辑性, 不能随意摆放, 否则不便于引用, 对于使用者也不容易读懂。因此, 数据在电子表格中的输入要遵循一定的原则。
(一) 尊重人们的阅览习惯组织数据, 增强模型的可鉴别性。人们的阅览习惯是从左向右、从上向下的, 这个事实应当得到重视并反映到电子表格的设计中。所以要按照线性规划模型的结构从上到下摆放数据, 将数据排列好以后, 决策变量、约束条件与目标函数的逻辑位置自然容易设定。一些对数据的说明信息都应放在数据的前面, 数据以便于使用者看得懂、学得会。
(二) 体现相关数据的逻辑关系, 增强模型的整体性和合理性。具有逻辑关系的内容, 如约束条件左侧的表达式与右侧常数项的值, 所安排的位置要相互近一些, 且最好在同一行或同一列, 以方便相互引用。
(三) 充分利用电子表格公式复制的功能, 增强模型的可修改性。公式可以被复制的模型在一个区域内完成一系列运算不容易出错且有助于理解。一旦使用者理解了区域中的第一个公式, 就理解了这个区域中的所有公式及计算方法。公式可以被复制的模型也存在便于修改的特点, 公式引用一旦错误, 只需要修改一个单元格的公式即可。
(四) 使用注释、颜色、边框和阴影等区分已知数据与可变参数, 增强模型的可读性。使用这些特殊效果可以让使用者很容易就辨别出已知与所求, 快速找到最优解。对于一些使用公式的单元格要加上相应的注释便于使用者理解。
三、“规划求解”的求解过程
为简便易懂起见, 使用如下两个变量的例子加以说明。
例:某浴缸公司制造和销售两种型号的浴缸:AS和HL。该公司从一家本地的供应商那里购买浴缸外壳, 在外壳上安装水泵和水管。在下一个生产周期中, 仅有200台可用的水泵。从生产角度看两种型号浴缸之间的主要差别是所需水管和工人的数量。一个AS浴缸需要9个工时和12英尺水管, 一个HL浴缸需要6个工时和16英尺水管。预计在下一个生产周期中该公司有1, 556个生产工时和2, 880英尺水管可以使用。售出一个AS浴缸可得到利润350元, 售出一个HL浴缸可得利润300元。该公司产品销售势头很好, 供不应求。问题是, 如果该公司想在下一个生产周期中获取最大利润, 应该分别生产多少AS浴缸和HL浴缸?
设生产AS浴缸的数量为X1, 生产HL浴缸的数量为X2, 资源有三种就应该有三个约束条件:故线性规划模型为:
MAXZ:350X1+300X2
St. 1 X1+ 1X2≤200
9 X1+ 6X2≤1, 566
12 X1+ 16X2≤2, 880
X1 , X2≥0
(一) 决策变量的表示。
单元格B3、C3代表模型中的决策变量X1、X2的最优解, 这些单元格是该线性规划问题所求信息, 被加上阴影和外框, 以直观地将它们与模型中的数据元素区别开来。因为生产多少AS和HL的浴缸, 需要计算机去反复试算求解, 在“规划求解”中称为“可变单元格”, 所以在单元格B3、C3中我们录入的数据就是初始值0、0;在B2、C2中输入所设的决策变量“X1”和“X2”用以提示。在A2、A3和中输入对后面数据相应含义的说明信息。
(二) 目标函数的表示。
分别在单元格B4、C4中录入AS和HL的单位利润即目标函数的系数350和300, 在E4单元格中存放最优值, 它的值需要计算机根据B3和C3当前的值分别乘以B4和C4的单位利润反复试算, 在“规划求解”中称为“设置目标单元格”, 所以E4中输入表示出求目标函数值的公式:=B3*B4+C3*C4, 它也是该线性规划问题的所求信息, 也加上阴影和外框, 与数据元素区别开来。在A4、D4中输入对后面数据相应含义的说明信息。
(三) 约束的表示。
分别在单元格B6:C8中录入AS和HL所需的水泵数量、工作小时数、水管的长度即所有约束条件的系数, 分别在E6到E8中录入所有约束条件的常数项在A6中输入提示信息。再用Excel公式把约束左侧表示出来并将此公式输入到单元格中以备引用, 在单元格D6中输入的公式=B6*B$3+C6*C$3, 然后将该公式复制到D7和D8单元格中, 也加上阴影和外框, 与数据元素区别开来。
(四) 对决策变量限制的表示。
对于X1 , X2≥0可看做约束且像其它约束一样建立, 但规划求解对决策变量可指定简单上限和下限。因此, 没必要在电子表格中采取特别的工作来建立这些限制。
(五) 求解。
在Excel2007的功能区单击“数据”选项卡, 在后面的“分析”区选择“规划求解”, 会弹出一个如图1所示的“规划求解参数”对话框。
1.设置目标单元格。
代表模型中目标函数的单元格即E4, 该单元格必须包含公式, 如果通过折叠按钮和鼠标选择, 自动就变成绝对引用$E$4。
2.等于。
在此指定目标函数的优化方向, 即希望目标单元格为最大值、最小值或某一特定数值。如果需要指定数值, 在右侧编辑框中键入该值。
3.可变单元格 (决策变量) 。
在此指定电子表格中表示决策变量的单元格。求解时其中的数值不断调整, 直到满足约束条件并且“设置目标单元格”框中指定的单元格达到目标值。可变单元格必须直接或间接地与目标单元格相关联。即B3:C3, 同样也可为$B$3:$C$3。
4.推测。
单击此按钮, 自动推测“设置目标单元格”框中的公式所引用的所有非公式单元格, 并在“可变单元格”框中定位这些单元格的引用。
5.约束。
在此列出了所有约束左侧用公式表示的单元格 (和用于这些表达式的任何上限和下限) 。单击“添加”按钮:显示“添加约束”对话框。输入$D$6:$D$8≤$E$6:$E$8。定义约束的非负条件时可再单击“添加”按钮, 添加第二个约束条件:$b$3:$C$3≥0。
6.选项。
在“选项”对话框中, 选定“假定非负”的复选框。没有指明明显的下限的变量以0为下限。同时可以更改“迭代次数”、“精度”、“允许误差”和“收敛度”等。在其中还可加载或保存规划求解模型, 并对求解过程的其它高级属性进行控制。
上述步骤设计完后, 单击“求解”按钮, 其结果如图2所示:
(六) 解的说明。
在加阴影和边框的单元格中显示最优解X1是122, X2是78, 最优值是66100, 第一个约束水泵和第二个约束生产工时全部用完, 第三个约束水管是松弛的, 有剩余。还可以选择生成“运算结果报告”、“敏感性报告”和“极限值报告”, 每份报告都形成一个单独的工作表并可保存到该工作簿中, 已备随时查用。
四、多变量的求解实例
线性规划模型为:
MAXZ:2X1+X2 +X3
St. 4X1+ 2X2+ 2X3≥4
2X1+ 4X2≤20
4X1+ 8X2+2X3≤16
X1, X2, X3≥0
该线性规划问题不但有三个变量, 而且在用单纯型法求解时, 化成标准形式后找不到一个初始基本可行解, 这就需要加入人工变量, 求解比较麻烦。使用Excel的“线性规划”进行求解则比较简单方便。具体求解过程与结果如图3所示。
摘要：线性规划问题的求解历来都是繁琐和困难的, 伴随着计算机技术的发展, 许多求解软件包应运而生, 但需要购买和加载。自从微软公司推出Office系列办公软件后, 使用其中的Excel就可以简单、方便的对线性规划问题求解。但Of-fice系列教材对此描述的十分有限, 基本看不懂、学不会。本文针对Excel规划求解的使用方法进行了深入、详细的介绍, 有着非常重要的现实指导意义。
关键词：Excel,线性规划,求解方法
参考文献
[1].暴奉贤, 陈宏立.经济预测与决策方法[M].广州:暨南大学出版社, 2005, 1
[2].韩伯棠.管理运筹学[J].北京:高等教育出版社, 2007, 2
[3].[美]Cliff.T.Ragsdale;杜学孔, 崔鑫生译.电子表格建模与决策分析[M].北京:电子工业出版社, 2006, 2
EXCEL建模 篇3
所谓财务建模, 是用数学术语或者计算机语言建立起来的表达财务问题各种变量之间关系的学科。财务建模是一门理论性很强的学科, 具有坚实的理论基础和理论依据。其理论基础包括数学、经济学、金融学、统计学、会计学、计算机程序设计等。财务建模不仅包括财务问题的数学建模, 也包括计算机建模。所谓数学建模就是把一个称为原型的实际问题进行数学上的抽象, 在做出了一系列的合理假设以后, 将原型用一个或者一组数学方程来表示。所谓计算机建模是将一个复杂的财务问题用计算机模拟, 从而了解和掌握它的内在规律, 预测它的未来发展。
二、财务管理建模常用的工具与其各自特点
财务建模的工具对于财务建模问题的研究至关重要。过去财务建模大多通过微软办公软EXCEL来完成, 在解决一般的小型财务建模问题上, EXCEL是完全可以胜任的。现在用MATLAB应用软件包建模使财务建模更加容易。MATLAB是一个功能完备、易学易用的软件。MATLAB的主要特点是:计算能力强, 绘图能力强, 编程能力强。MATLAB的使用扩充了财务建模研究的内容, 并为财务建模提供很好的计算机支持。用MATLAB作为工具不仅可以提高财务建模的效率, 而且可以以非常直观的方式将自己的模型表现出来, 更可以创造出适合于特定企业和特定情况的模型系统。
尽管总体而论, 在财务管理建模中MATLAB相对比EXCEL具有更强大的功能, 但在实际操作中, 却各有优劣, 因而如果能够将二者的优势互补, 联合运用来解决实际中一个相对较为复杂的问题, 显然不失为明智之举。
三、MATLAB结合EXCEL在财务建模的一个综合运用
本文以固定资产折旧的建模为例, 讨论了将EXCEL和MATLAB结合起来求解财务计算问题的方法。事实证明, 这种方法极大地扩展了EXCEL的计算能力, 并使复杂的财务计算问题得到了很好的简化。
1、建立MATLAB到EXCEL的接口
Matlab以“Excel Link”的方式提供了到Excel的接口。要使用该功能, 在安装MATLAB时必须在选择组件的窗口中勾选“Excel Link”。如果电脑上已经安装Matlab但没有安装Excel Link, 可以重新运行MATLAB的安装程序, 在选择组件的窗口中单独勾选“Excel Link”。安装成功后, MATLAB的根目录下将出现toolboxexlink这个子目录。要在EXCEL中加载Excel Link, 可在其菜单项中选择“工具-->加载宏”, 然后点击“浏览”按钮, 选择exlink目录下的excllink.xla文件。然后在“可用加载宏”列表中勾上“Excel Link 2.3 for use with MATLAB”
2、在MATLAB中实现固定资产折旧计算
固定资产折旧模型要求在计算时有4个输入参数:固定资产原值、预计净残值、预计使用年限和折旧方法;4个输出参数:年份、年折旧率、年折旧额和累计折旧。可以看到, 所有的输出是A列到D列之间从第7行开始的一个动态矩阵, 而生成和操作矩阵是MATLAB的专长, 所以这个问题特别适合于使用MATLAB求解。Matlab的金融工具箱提供了大部分常见财务问题的计算公式, 其中“现金流分析和计算”类别中就直接提供了利用各种方法计算固定资产折旧的命令, 所以这里只需要简单地把这些命令组合起来即可, 完全不需要“重新发明轮子”。整个程序保存在一个名为mydepfcn.m的文件中。程序如下:
function m=mydepfcn (cost, salvage, life, method)
%MYDEPFCN通用固定资产折旧计算入口.
%M=MYDEPFCN (COST, SALVAGE, LIFE, METHOD) 根
据输入的固定资产原值、预计净残值、预计使用年限和折
旧方法, 计算每一年的年折旧率、年折旧额和累计折旧。
在以上程序中, 输出的年份、年折旧率、年折旧额和累计折旧分别由列向量y、r、d和da表示。需要注意的是, MATLAB没有提供计算年折旧率的命令, 所以r是根据不同折旧方法中年折旧率的定义分别计算出来的。当然, 这在Matlab中是非常简单而直观的, 不需要使用任何循.depgendb是一个通用的资产折旧计算命令, 其并没有考虑双倍余额递减法中最后两年的年折旧率相等这一特点, 在程序中需要对此进行修正。调用此函数返回的是一个n×4的矩阵, n等于输入参数life的值。
3、连接EXCEL和MATLAB
在Excel中调用MATLAB的函数是非常简单的。Excel Link提供了一个称为matlabsub的公式, 可以直接调用MATLAB中的任何命令, 并指定该命令的输出显示在哪些单元格上。matlabsub的第一个参数是MATLAB命令的名字, 第二个参数是命令输出的位置, 其余的参数是该MATLAB命令本身的输入参数。需要说明的是, 由于MATLAB命令的返回值一般是一个矩阵, 所以matlabsub的第二个参数指出的是Excel Link用返回的矩阵填充EXCEL工作表时矩阵左上角在工作表中的位置。
四、结束语
本文利用上诉介绍的方法, 将MATLAB与EXCEL联系起来, 结合二者的优势联合去解决现实相对复杂的问题, 事实证明, 这种方法成功地使复杂的财务计算问题得到了很好的简化。
参考文献
[1]、中国注册会计师协会.会计[M].北京:中国财政经济出版社, 2011:94~95.
[2]、段新生.MATLAB财务建模与分析[M].北京:中国金融出版社, 2009.