第七章 Excel在营运资金管理中的应用

,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,第七章,Excel,在营运资金管理中的应用,计 算 机 财 务 管 理,1,第一节 最佳现金持有量决策模型,一、成本分析模型,成本分析模型是通过分析持有现金的成本，将持有现金的总成本最低的现金持有量作为最佳现金持有量。,持有现金的成本主要有机会成本、管理成本和短缺成本。,通过计算比较各个现金持有量方案的总成本，选取总成本最低的方案为最优方案。,例,7,1：某企业有四种现金持有方案，有关成本资料如表,7,1,所示。,2,确定现金最佳持有量。,创建过程,：,创建步骤见模型批注，如图,7-1,所示。,3,图,7-1,4,二、存货模式,确定最佳现金持有量的存货模式也称鲍莫模式，是1952年由美国经济学家威廉,鲍莫(,Willian,J,Baumol),首先提出的。根据这种模式，企业的现金持有量非常类似于存货，因此存货的经济订货批量模型可以用来确定最佳现金持有量。计算公式为:,式中:,b,为现金与有价证券的转换成本，,T,为一定时期的现金需求总量，,N,为最佳现金持有量，,i,为有价证券的利息率,5,例,7-2,，某企业的现金流量稳定，预计全年现金需求总量200000元，现金与有价证券的转换成本为每次400元，有价证券的利率为10%。采用存货模式确定该企业最佳现金持有量。,计算过程:,如图,7-2,所示。,图,7-2,6,第二节 应收帐款信用政策决策模型,应收账款政策，是指企业向客户提供商业信用时，为了对应收账款进行规划和控制所确定的基本原则和规范。信用政策是企业财务政策的一个重要组成部分。它由信用标准、信用条件和收账政策三要素组成。,一、信用标准决策模型,信用标准是指企业同意向客户提供商业信用时，客户必须具备的最低财务能力。,7,企业的信用标准比较严格，只对信誉较好的客户提供商业信用，则可以减少坏账损失，降低应收账款的机会成本和管理成本，但是也会减少企业的销售量；反之，如果企业的信用标准比较宽松，虽然会增加销售量，但是也会相应增加坏账损失和应收账款的机会成本与管理成本。因此，企业必须在扩大销售与增加成本之间权衡利弊，制定一个比较合理的信用标准。,8,信用标准确定相关计算公式：,9,例,7-3,，某公司2001年的经营情况和信用标准如表,7,3,所示。该公司为了扩大销售，增加利润总额，采取了放宽信用标准的策略，提出了,A、B,两个方案，其信用标准及对企业的影响如表,7-4,。,10,11,表,7-4,分析过程:,12,第一步，建立如图,7-3-1,、,2,所示的分析模型结构。,图,7-3,图,7-3-1,13,第二步，在单元格,C30,中输入公式,” =B19*$C$8/100”,；,图,7-3-2,14,第三步，在单元格,C31,中输入公式,” =(B19/360)*B21*$C$13/100”,；,第四步，在单元格,C32,中输入公式,”,=B19*B23,”,；,第五步，在单元格,C33,中输入公式,”,=B25,”,；,第六步，在单元格,C34,中输入公式,”,=C30-C31-C32-C33,”,，得到,A,方案的计算机结果。,第七步，选取单元格区域,C30:C34,，将其复制到单元格区域,D30:D34,中，得到方案,B,的有关计算结果。,第八步，在单元格,C35,中输入公式,” =IF(AND(C340,D340),IF(C34D34,采用方案,A,采用方案,B),IF(D340,采用方案,B,仍采用目前的信用标准,)”,15,二、信用条件决策模型,信用条件是指企业接受客户信用订单时所提出的付款要求，主要包括信用期限、折扣期限和现金折扣,。,采取现金折扣方式在有利于刺激销售、降低相关成本或损失的同时，也需付出一定的成本代价，即现金折扣造成的损失。如果加速收款增加的收益能够补偿现金折扣成本，企业就可以采取现金折扣或进一步改变当前的折扣方针；反之，现金折扣优惠便是不合理的。,主要计算公式如下：,16,17,信用条件变化带来的增量利润为第一项与后三项之差,18,例,7,4，某企业拟改变信用条件，现有两个可供选择的信用条件方案，有关资料表,7,5所示。企业应选择哪个方案。,表,7-5,19,分析过程:,第一步，建立如图,7-4,所示的分析模型结构。,续表,20,图,7-4,21,第二步，在单元格,C26,中输入公式,”,=C18*$C$8,”,；,第三步，在单元格,C27,中输入公式,”,=(C22-$C$12)/360*$C$5+C22/360*C18)*$C$6*$C$13,”,第四步，在单元格,C28,中输入公式,”,=(C5+C18)*C20*C21,”,；,第五步，在单元格,C29,中输入公式,”,=C18*C19,”,；,第六步，在单元格,C30,中输入公式,”,=C26-C27-C28-C29,”,，得到,A,方案的计算机结果。,22,第七步，选取单元格区域,C26:C30,，将其复制到单元格区域,D26:D30,中，得到方案,B,的有关计算结果。,第八步，在单元格,C31,中输入公式,”,=IF(AND(C300,D300),IF(C30D30,应采用方案A,应采用方案B),IF(C300,应采用方案A,IF(D300,应选择方案B,仍采用目前的信用条件),”,23,三、应收帐款信用政策的综合决策模型,信用政策中的每一项内容的变化都会影响到企业的利益，因此，需要这些因素综合起来来滤，以制定合适的信用政策。,主要计算公式：,1、利润政策变化对利润的影响,24,2、信用政策变化对应收帐款机会成本的影响,25,3、信用政策变化对现金折扣成本的影响,4、信用政策变化对坏帐损失的影响,26,5、信用政策变化对收帐管理成本的影响,例,7,5，某企业现有的信用政策以及要改变信用政策的两个可供选择的方案所表,7,6所示。决策,。,27,表,7,6,分析过程：,第一步：,建立如图,7-5,所示的分析模型结构。,28,图,7-5,29,第二步，在单元格,D17,中输入公式,”,=(D5-$C$5)*D6,”,；,第三步，在单元格,D18,中输入公式,”,=(D9-$C$9)/360*$C$5+D9/360*(D5-$C$5)*D13*D12,”,第四步，在单元格,D19,中输入公式,”,=D5*D8-$C$5*$C$8,”,；,第五步，在单元格,D20,中输入公式,”,=D5*D11*D10-$C$5*$C$10*$C$11,”,；,第六步，在单元格,D21,中输入公式,”,=D5*D7-$C$5*$C$7,”;,30,第七步，在单元格,D22,中输入公式,”,=D17-D18-D19-D20-D21,”,，计算,A,方案的增量利润。,第八步，,选取单元格区域,D17:D22,，将其复制到单元格区域,E17:E22,中，得到方案,B,的有关计算结果。,第九步，在单元格,C31,中输入公式,”,=IF(AND(D220,E220),IF(D22E22,“采用方案A”,“采用方案B”),IF(D220,“采用方案A”,IF(E220,“采用方案B”,“采用目前信用政策”),，,产生结论。,设计过程,31,第三节 存货的经济订货批量决策模型,存货决策涉及多方面的内容，包括决定进货项目、选择进货单位、决定进货时间和决定进货批量等，其中最常见的存货决策是确定经济订货批量。,企业购买和储存存货的有关成本包括：, 订货成本指取得订单的成本，如办公费、差旅费、邮资、电报电话费等支出。, 购置成本指存货本身的价值和运杂费。, 储存成本是指为保持存货而发生的成本。, 缺货成本是指由于存货供应中断而造成的损失。,32,一、基本的经济订货批量模型,经济订货量基本模型,33,例,7-6,，某企业全年需要材料3600千克，一次订货费用25元，材料单价20元/千克，单位材料的储存费用为2元，求该材料的经济订货批量、全年的订货次数和与储存存货相关的总成本。,设计过程：,第一步，建立如图,7-6,所示分析模型结构。,第二步，在单元格,C8,中输入公式,”,=SQRT(2*C3*C2/C5),计算经济批量；,第三步，在单元格,C9,中输入公式,”,=C2/C8,”,计算一年采购的次数；,34,图,7-6,35,第四步，在单元格,C10,中输入公式,”,=SQRT(2*C2*C3*C5),”,计算与经济批量相关的总成本。,设计过程, 存货陆续供应和耗用情况下的经济订货批量模型,36,例,7-7,如例,7,6中的企业所需要的材料不是瞬时到货,而是陆续供货,进货期内每日供货量30千克,每日耗用量为10千克。计算经济订货批量、全年的订货次数和与储存存货相关的总成本。,设计过程：,第一步，建立如图,7-7,所示分析模型结构。,37,图,7-7,38,第二步，,在单元格,E8,中输入公式,”,=SQRT(2*C3*C2/C5),，,计算经济批量；,第三步，在单元格,E9,中输入公式,”,=C2/E8,”,，计算一年采购的次数,；,第四步，在单元格,E10,中输入公式,”,=SQRT(2*C2*C3*C5*(1-E3/E2),”,计算与经济批量相关的总成本。,设计过程,39, 有数量折扣的经济订货批量决策模型,例,7,8,某企业每年需要某零件8500件.每次订货费用260元,存储费率为零件单价的10。供货商规定，凡一次购买1000件以下的价格为10元/件，1000件或以上但2000件以下的价格为9元件，2000件或以上但3000件以下的价格为8元件，3000件或以上的价格7元。问企业应如何订货。,设计过程：,第一步，建立如图,7-8,所示的分析模型结构。,40,图,7-8,41,第二步，在单元格,D9,、,E9,、,F9,、,G9,、,H9,、,I9,中分别输入以下公式：,单元格,D9,：“,=SQRT(2*$C$4*$C$5/(C9*$C$6),”，计算单价为,10,元是的经济批量，即采购批量,(G),在,1=G=B9,D9B10),”,有效,“,”,无效,“),”，判断计算的经济批量是否落在有效期间内，即,1=G=B12,有效,无效),”,43,第四步，在单元格,C14,种输入公式“,=INDEX(I9:I12,MATCH(MIN(H9:H12),H9:H12,0),” ，得出无需求量限制的经济订货批量。,第五步，在单元格,C15,种输入公式“,=IF(C4C14,C14,C4),” ，确定最有经济批量。,设计过程,44,第二节 最优决策分析工具 “规划求解”工具,一、规划求解工具的使用,谓规划就是指具有多种实用价值的有限资源，当其存在多种利用方案时，如何根据一定的经济目的，最有效获地谋划或选择最优方案。,规划求解模型就是将这种经济关系，归纳为一定的数学表达式。然后，求解出问题的最优解。规划求解主要用来解决极大值问题,(MAX)；,极小值问题,(MIN),和定值问题。,规划求解模型是由决策变量、目标函数和约束条件三部分组成。根据数据表达式的性质可分为线性规划和非线性规划两种。,45,决策变量：是需要经过模型求解计算来确定的决策因素。一个问题一般有一组决策变量，这些决策变量的一组确切值代表一个具体的规划方案。,目标函数：是反映经济目标的数学表达式。一般只能是具有同一度量单位的第一目标如利润、产值最大；或成本最低等。,约束条件：是实现经济目标的制约因素。如资金、劳力、资源等。包括客观约束条件和主管约束条件。,46,二、规划求解的设置过程,使用规划求解工具只需从工具菜单中选择标出规划求解对话框，见图,7-9,。,只有通过“自定义安装”方式将,Excel,完全安装，工具,菜单才有规划求解命令。,图,9,47, 目标单元格,目标单元格也称目标函数是模型中的一个你希望最大值、最小值的单元格，目标单元格应含有决策变量,(,可变单元格,),，即引用含有决策变量的单元格或名称,(,和目标函数对应,), 可变单元格,可变单元格也称决策变量是对目标单元格数值产生影响的单元格。可变单元格最多200个。,(,和决策变量对应,),可变单元格框中的输入项通常是某个单元格区域的引用位置，或使用逗号隔开的几个非相邻单元格的引用,48, 约束条件,约束条件是必须符合某些限制或目标值的单元格数值。约束条件可以使用在目标单元格和可变单元格上。,1. 约束条件中可以指定模型中任意单元格的上下界,2. 单元格引用位置,框中所引用到的单元格，通常含有于一个或多个可变单元格直接或间接相关的公式。,3. 使用“,int,”,运算符时，表示有约束条件约束约束的数值只能是整数。只有可变单元格才可以限制为整数。,49,例,7-9,：某企业在某月份生产甲、乙两种产品，其有关资料如表,7-7,所示，则企业应如何安排两种产品的产销组合，使企业获得最大销售利润？,表,7-7,50,利用规划求解工具解决该问题的步骤为：,第一步，建立优化模型，,决策变量：设,x,和,y,分别表示甲产品和乙产品的生产量。,目标函数：,MAX(,销售利润,)=(140-60)x+(180-100)y,约束条件：,6x+9y360,7x+4y240,18x+15y850,y30,x0, y0,且为整数,51,第二步,，单元格,C12,和,D12,为可变单元格，分别存放甲、乙产品的生产量。,第三步，单元格,C13,为目标单元格,(,销售利润,),，计算公式为“,=SUMPRODUCT(C5:D5-C6:D6,C12:D12),”。,第四步，在单元格,C14,中输入产品消耗工时合计计算公式“,=SUMPRODUCT(C7:D7,C12:D12),”。在单元格,C15,中输入产品消耗材料合计计算公式“,=SUMPRODUCT(C8:D8,C12:D12),”。在单元格,C16,中输入产品消耗能源合计计算公式“,=SUMPRODUCT(C9:D9,C12:D12),”。,52,第五步，单击,工具,菜单，选择规划求解项，则系统弹出,规划求解参数,对话框，如图,7-10,所示。 图,7-10,53,第六步，在,规划求解参数,对话框中，,设置目标单元格,中输入“,$C$13”,；,等于,选“最大值”；,可变单元格,中输入“,$C$12:$D$12”,；在,约束,中添加以下约束条件：“,$C$12:$D$12”,、 “,$C$12:$D$12=0”,、 “,$C$14=$F$4,”、 “,$C$15=$F$5,”、 “,$C$16=$F$6,”、 “,$D$12=$D$10,”。,添加约束条件的方法是：单击,添加,澳纽，系统会弹出,添加约束,对话框，如图,7-11,所示。,图,11,54,第七步，如有需要还可以设置有关的项目，即单击,选项,按钮，弹出,规划求解选项,对话框，如图,7-12,所示。,图,7-12,55,有关选项设置说明：,选项允许你控制求解过程的高级特性并加载或保存工作表上一个特殊问题的选择；可以为线性问题和非线性问题之间的差异定义参数。,最长运算时间限制求解过程花费的时间，其值必须是正整数，默认值为100秒；,精度控制规划求解找到答案的精度，在精度框中如入的数：,用来决定约束值是否满足目标或满足你所指定的上下限；,56,必须是一个在0和1（不包含0和1）之间的小数,默认值为0.000001,允许误差表示当一个正数约束条件被用于问题的任何代以元素时，规划求解所允许的误差百分比。当没有正数约束条件时，允许误差设置无效；,收敛度在此输入收敛度数值，当最近5此迭代后，目标单元格中数值的变化小于 “收敛度”编辑框中设置的数值时，“规划求解”停止运行。收敛度只适应于非线性规划问题，用小数表示；,采用线性模型当模型中的所有关系为线性时，可以选择该选项,57,显示迭代结果选择此项后，在每次迭代后中断规划求解，显示迭代结果,自动按比例缩放当输入（可变单元格）、输出（目标单元格）和限制在数量上有较大差别时，选择此选择框；,假定非负对于在“添加约束”对话框的“约束值”编辑框中没有设置下限的可变单元格，假定其下限为0；,估值该选项自动在每次一维搜索中用来获得基本数量的初始估计值的方法。正切函数：一维线性外推；二次方程：使用二维外推，适用于过度非线性问题,58,导数偏导数对目标函数和约束条件起作用。,前向差分：为默认值,中心差分：需要更多的计算工作表，使用它可提高规划求解结果的质量,搜索,决定在每次迭代时使用什么搜索算法来确定搜索方向,牛顿法：搜粟需要更多的内存，迭代次数少,共轭法：搜所需要的内存少，通常需要更多的迭代次数,59,估值、导数偏导数、搜索等选项中的数学方法、适用于大部分问题。,全部重新设置清除当全规划求解问题设置，并重新全部设置全部为默认项,开始规划求解，直到达到目标值为止,第八步，处理规划求解所得到的结果,当求解问题的过程结束时，出现如下对话框：图,7-13,。,图,7-13,60,敏感性分析报告包括的信息，展示了解决方案对问题所使用公式的变化的敏感程度。,运算结果报告： 运算结果的初值（原值）和终值（求解）；,可变单元格的初值和终值； 约束条件及相关信息。,极限结果报告： 下限极限； 上限极限； 目标式结果。当可变单元格位于上下界时，目标单元格的值。,第九步，单击,确定,按钮，生成结果见图,7-14,。,61,图,7-14,62,三、应用“规划求解”工具分析最优订货批量,例,7,10，应用“规划求解”工具分析最优订货批量模型。原始资料见表,7-7,。甲、乙、丙、丁材料的折扣起点分别为,400,、,450,、,500,、,500,。,表,7-7,63,求解过程,：,第一步，建立如图,7-14,所示的分析模型结构。,图,7-14,64,第二步，单元个区域,C15,、,D15,、,E15,、,F15,分别代表甲、乙、丙、丁四种材料的采购量。,第三步，选取单元个区域,C16:F16,，输入公式“,=C4:F4*(1-C9:F9),”，通过数组生成键计算材料的采购成本。,第四步，选取单元个区域,C17:F17,，输入公式“,=C15:F15*(1-C8:F8/C7:F7)/2*C6:F6,”，通过数组生成键计算材料的储存成本。,第五步，选取单元个区域,C18:F18,，输入公式“,=C4:F4/C15:F15*C5:F5,”，通过数组生成键计算材料的订货成本。,65,第六步，选取单元个区域,C19:F19,，输入公式“,=C16:F16+C17:F17+C18:F18,”，通过数组生成键计算各存货的总成本。,第七步，在单元格,C20,中输入公式“,=SUM(C19:F19),”，计算存货的总成本。,第八步，选取单元个区域,C21:F21,，输入公式“,=C4:F4/C15:F15,”，通过数组生成键计算最佳订货次数。,第九步，选取单元个区域,C22:F22,，输入公式“,=12/C21:F21,”，通过数组生成键计算订货周期。,66,第十步，选取单元个区域,C23:F23,，输入公式“,=C15:F15/2*C10:F10,”，通过数组生成键计算经济订货量占用资金。,第十一步，单击,工具,菜单，选择,规划求解,，按图,7-15,设置。,图,7-15,67,第十二步，单击求解，选择“运算结果报告” ，单击,确定,按钮，生成结果见图,7-16,。,图,7-16,68,