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

第七章Excel在营运资金

管理中的应用

计算机财务管理2020/10/281第一节最佳现金持有量决策模型

一、成本分析模型

成本分析模型是通过分析持有现金的成本，将持有现金的总成本最低的现金持有量作为最佳现金持有量。持有现金的成本主要有机会成本、管理成本和短缺成本。通过计算比较各个现金持有量方案的总成本，选取总成本最低的方案为最优方案。例7－1：某企业有四种现金持有方案，有关成本资料如表7—1所示。2020/10/282精品资料确定现金最佳持有量。

创建过程：

创建步骤见模型批注，如图7-1所示。2020/10/284图7-12020/10/285二、存货模式确定最佳现金持有量的存货模式也称鲍莫模式，是1952年由美国经济学家威廉·鲍莫(Willian•J•Baumol)首先提出的。根据这种模式，企业的现金持有量非常类似于存货，因此存货的经济订货批量模型可以用来确定最佳现金持有量。计算公式为:

式中:b为现金与有价证券的转换成本，T为一定时期的现金需求总量，N为最佳现金持有量，i为有价证券的利息率2020/10/286例7-2，某企业的现金流量稳定，预计全年现金需求总量200000元，现金与有价证券的转换成本为每次400元，有价证券的利率为10%。采用存货模式确定该企业最佳现金持有量。

计算过程:如图7-2所示。图7-22020/10/287第二节应收帐款信用政策决策模型应收账款政策，是指企业向客户提供商业信用时，为了对应收账款进行规划和控制所确定的基本原则和规范。信用政策是企业财务政策的一个重要组成部分。它由信用标准、信用条件和收账政策三要素组成。一、信用标准决策模型信用标准是指企业同意向客户提供商业信用时，客户必须具备的最低财务能力。2020/10/288企业的信用标准比较严格，只对信誉较好的客户提供商业信用，则可以减少坏账损失，降低应收账款的机会成本和管理成本，但是也会减少企业的销售量；反之，如果企业的信用标准比较宽松，虽然会增加销售量，但是也会相应增加坏账损失和应收账款的机会成本与管理成本。因此，企业必须在扩大销售与增加成本之间权衡利弊，制定一个比较合理的信用标准。2020/10/289信用标准确定相关计算公式：2020/10/2810例7-3，某公司2001年的经营情况和信用标准如表7—3所示。该公司为了扩大销售，增加利润总额，采取了放宽信用标准的策略，提出了A、B两个方案，其信用标准及对企业的影响如表7-4。2020/10/28112020/10/2812表7-4分析过程:2020/10/2813第一步，建立如图7-3-1、2所示的分析模型结构。图7-3图7-3-12020/10/2814第二步，在单元格C30中输入公式”=B19*$C$8/100”；图7-3-22020/10/2815

第三步，在单元格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(C34>0,D34>0),IF(C34>D34,"采用方案A","采用方案B"),IF(D34>0,"采用方案B","仍采用目前的信用标准"))”2020/10/2816二、信用条件决策模型信用条件是指企业接受客户信用订单时所提出的付款要求，主要包括信用期限、折扣期限和现金折扣。采取现金折扣方式在有利于刺激销售、降低相关成本或损失的同时，也需付出一定的成本代价，即现金折扣造成的损失。如果加速收款增加的收益能够补偿现金折扣成本，企业就可以采取现金折扣或进一步改变当前的折扣方针；反之，现金折扣优惠便是不合理的。主要计算公式如下：

2020/10/28172020/10/2818信用条件变化带来的增量利润为第一项与后三项之差2020/10/2819例7－4，某企业拟改变信用条件，现有两个可供选择的信用条件方案，有关资料表7－5所示。企业应选择哪个方案。表7-52020/10/2820

分析过程:第一步，建立如图7-4所示的分析模型结构。续表2020/10/2821图7-42020/10/2822第二步，在单元格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方案的计算机结果。2020/10/2823第七步，选取单元格区域C26:C30，将其复制到单元格区域D26:D30中，得到方案B的有关计算结果。第八步，在单元格C31中输入公式”=IF(AND(C30>0,D30>0),IF(C30>D30,"应采用方案A","应采用方案B"),IF(C30>0,"应采用方案A",IF(D30>0,"应选择方案B","仍采用目前的信用条件")))”2020/10/2824

三、应收帐款信用政策的综合决策模型信用政策中的每一项内容的变化都会影响到企业的利益，因此，需要这些因素综合起来来滤，以制定合适的信用政策。主要计算公式：1、利润政策变化对利润的影响2020/10/28252、信用政策变化对应收帐款机会成本的影响2020/10/28263、信用政策变化对现金折扣成本的影响4、信用政策变化对坏帐损失的影响2020/10/2827

5、信用政策变化对收帐管理成本的影响

例7－5，某企业现有的信用政策以及要改变信用政策的两个可供选择的方案所表7－6所示。决策。2020/10/2828表7－6

分析过程：第一步：建立如图7-5所示的分析模型结构。2020/10/2829图7-52020/10/2830第二步，在单元格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”;2020/10/2831第七步，在单元格D22中输入公式”=D17-D18-D19-D20-D21”，计算A方案的增量利润。第八步，选取单元格区域D17:D22，将其复制到单元格区域E17:E22中，得到方案B的有关计算结果。第九步，在单元格C31中输入公式”=IF(AND(D22>0,E22>0),IF(D22>E22,“采用方案A”,“采用方案B”),IF(D22>0,“采用方案A”,IF(E22>0,“采用方案B”,“采用目前信用政策”)))，产生结论。设计过程2020/10/2832第三节存货的经济订货批量决策模型

存货决策涉及多方面的内容，包括决定进货项目、选择进货单位、决定进货时间和决定进货批量等，其中最常见的存货决策是确定经济订货批量。企业购买和储存存货的有关成本包括：

⑴订货成本指取得订单的成本，如办公费、差旅费、邮资、电报电话费等支出。⑵购置成本指存货本身的价值和运杂费。⑶储存成本是指为保持存货而发生的成本。⑷缺货成本是指由于存货供应中断而造成的损失。2020/10/2833一、基本的经济订货批量模型㈠经济订货量基本模型2020/10/2834

例7-6，某企业全年需要材料3600千克，一次订货费用25元，材料单价20元/千克，单位材料的储存费用为2元，求该材料的经济订货批量、全年的订货次数和与储存存货相关的总成本。设计过程：第一步，建立如图7-6所示分析模型结构。第二步，在单元格C8中输入公式”=SQRT(2*C3*C2/C5)计算经济批量；第三步，在单元格C9中输入公式”=C2/C8”计算一年采购的次数；2020/10/2835图7-62020/10/2836

第四步，在单元格C10中输入公式”=SQRT(2*C2*C3*C5)”计算与经济批量相关的总成本。设计过程㈡存货陆续供应和耗用情况下的经济订货批量模型2020/10/2837

例7-7,如例7－6中的企业所需要的材料不是瞬时到货,而是陆续供货,进货期内每日供货量30千克,每日耗用量为10千克。计算经济订货批量、全年的订货次数和与储存存货相关的总成本。

设计过程：第一步，建立如图7-7所示分析模型结构。2020/10/2838图7-72020/10/2839第二步，在单元格E8中输入公式”

=SQRT(2*C3*C2/C5)，计算经济批量；第三步，在单元格E9中输入公式”=C2/E8”，计算一年采购的次数；第四步，在单元格E10中输入公式”=SQRT(2*C2*C3*C5*(1-E3/E2))”,计算与经济批量相关的总成本。

设计过程2020/10/2840

㈢有数量折扣的经济订货批量决策模型

例7－8,某企业每年需要某零件8500件.每次订货费用260元,存储费率为零件单价的10％。供货商规定，凡一次购买1000件以下的价格为10元/件，1000件或以上但2000件以下的价格为9元／件，2000件或以上但3000件以下的价格为8元／件，3000件或以上的价格7元。问企业应如何订货。

设计过程：

第一步，建立如图7-8所示的分析模型结构。2020/10/2841图7-82020/10/2842

第二步，在单元格D9、E9、F9、G9、H9、I9中分别输入以下公式：

单元格D9：“=SQRT(2*$C$4*$C$5/(C9*$C$6))”，计算单价为10元是的经济批量，即采购批量(G)在1<=G<1000时对应的经济批量；单元格E9：“=IF(AND(D9>=B9,D9<B10),”有效“,”无效“)”，判断计算的经济批量是否落在有效期间内，即1<=G<1000；单元格F9：“=IF(E9="有效",C9*$C$4+$C$4*$C$5/D9+$C$6*C9*D9/2,"无效")”2020/10/2843单元格G9：“=C9*$C$4+$C$4*$C$5/B9+$C$6*C9*B9/2”，计算按折扣起点批量计算的费用。单元格H9:“=IF(E9=”有效“,MIN(F9:G9),G9”,取最小费用。单元格I9：“=IF(H9=G9,B9,D9)”，判断最小费用对应的经济订货批量或折扣起点批量第二步，将单元格D9:I9复制到D10:I12中，分别得到不同折扣点对应的计算指标。

第三步，将单元格E12中的公式改为：“=IF(D12>=B12,"有效","无效")”2020/10/2844

第四步，在单元格C14种输入公式“=INDEX(I9:I12,MATCH(MIN(H9:H12),H9:H12,0))”，得出无需求量限制的经济订货批量。

第五步，在单元格C15种输入公式“=IF(C4>C14,C14,C4)”，确定最有经济批量。

设计过程2020/10/2845第二节最优决策分析工具

——“规划求解”工具

一、规划求解工具的使用谓规划就是指具有多种实用价值的有限资源，当其存在多种利用方案时，如何根据一定的经济目的，最有效获地谋划或选择最优方案。规划求解模型就是将这种经济关系，归纳为一定的数学表达式。然后，求解出问题的最优解。规划求解主要用来解决①极大值问题(MAX)；②极小值问题(MIN)和定值问题。规划求解模型是由决策变量、目标函数和约束条件三部分组成。根据数据表达式的性质可分为线性规划和非线性规划两种。2020/10/2846决策变量：是需要经过模型求解计算来确定的决策因素。一个问题一般有一组决策变量，这些决策变量的一组确切值代表一个具体的规划方案。目标函数：是反映经济目标的数学表达式。一般只能是具有同一度量单位的第一目标如利润、产值最大；或成本最低等。约束条件：是实现经济目标的制约因素。如资金、劳力、资源等。包括客观约束条件和主管约束条件。

2020/10/2847二、规划求解的设置过程

使用规划求解工具只需从［工具］菜单中选择标出规划求解对话框，见图7-9。只有通过“自定义安装”方式将Excel完全安装，［工具］菜单才有［规划求解］命令。图92020/10/2848㈠目标单元格目标单元格也称目标函数是模型中的一个你希望最大值、最小值的单元格，目标单元格应含有决策变量(可变单元格)，即引用含有决策变量的单元格或名称(和目标函数对应)㈡可变单元格可变单元格也称决策变量是对目标单元格数值产生影响的单元格。可变单元格最多200个。(和决策变量对应)［可变单元格］框中的输入项通常是某个单元格区域的引用位置，或使用逗号隔开的几个非相邻单元格的引用2020/10/2849㈢约束条件约束条件是必须符合某些限制或目标值的单元格数值。约束条件可以使用在目标单元格和可变单元格上。1.约束条件中可以指定模型中任意单元格的上下界2.[单元格引用位置]框中所引用到的单元格，通常含有于一个或多个可变单元格直接或间接相关的公式。3.使用“int”运算符时，表示有约束条件约束约束的数值只能是整数。只有可变单元格才可以限制为整数。2020/10/2850例7-9：某企业在某月份生产甲、乙两种产品，其有关资料如表7-7所示，则企业应如何安排两种产品的产销组合，使企业获得最大销售利润？表7-72020/10/2851

利用规划求解工具解决该问题的步骤为：第一步，建立优化模型，决策变量：设x和y分别表示甲产品和乙产品的生产量。目标函数：MAX(销售利润)=(140-60)×x+(180-100)×y

约束条件：6x+9y≤3607x+4y≤240

18x+15y≤850y≤30

x≥0,y≥0,且为整数2020/10/2852

第二步，单元格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)”。

2020/10/2853第五步，单击[工具]菜单，选择规划求解项，则系统弹出[规划求解参数]对话框，如图7-10所示。图7-102020/10/2854

第六步，在[规划求解参数]对话框中，[设置目标单元格]中输入“$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所示。图112020/10/2855第七步，如有需要还可以设置有关的项目，即单击[选项]按钮，弹出[规划求解选项]对话框，如图7-12所示。图7-122020/10/2856有关［选项］设置说明：

⑴［选项］允许你控制求解过程的高级特性并加载或保存工作表上一个特殊问题的选择；可以为线性问题和非线性问题之间的差异定义参数。

⑵［最长运算时间］限制求解过程花费的时间，其值必须是正整数，默认值为100秒；

⑶［精度］控制规划求解找到答案的精度，在［精度］框中如入的数：

①用来决定约束值是否满足目标或满足你所指定的上下限；2020/10/2857

②必须是一个在0和1（不包含0和1）之间的小数

③默认值为0.000001

⑷［允许误差］表示当一个正数约束条件被用于问题的任何代以元素时，规划求解所允许的误差百分比。当没有正数约束条件时，［允许误差］设置无效；⑸［收敛度］在此输入收敛度数值，当最近5此迭代后，目标单元格中数值的变化小于“收敛度”编辑框中设置的数值时，“规划求解”停止运行。收敛度只适应于非线性规划问题，用小数表示；

⑹［采用线性模型］当模型中的所有关系为线性时，可以选择该选项2020/10/2858

⑺［显示迭代结果］选择此项后，在每次迭代后中断规划求解，显示迭代结果

⑻［自动按比例缩放］当输入（可变单元格）、输出（目标单元格）和限制在数量上有较大差别时，选择此选择框；

⑼［假定非负］对于在“添加约束”对话框的“约束值”编辑框中没有设置下限的可变单元格，假定其下限为0；

⑽［估值］该选项自动在每次一维搜索中用来获得基本数量的初始估计值的方法。正切函数：一维线性外推；二次方程：使用二维外推，适用于过度非线性问题2020/10/2859

⑾［导数／偏导数］对目标函数和约束条件起作用。前向差分：为默认值中心差分：需要更多的计算工作表，使用它可提高规划求解结果的质量

⑿［搜索］决定在每次迭代时使用什么搜索算法来确定搜索方向牛顿法：搜粟需要更多的内存，迭代次数少共轭法：搜所需要的内存少，通常需要更多的迭代次数2020/10/2860［估值］、［导数／偏导数］、［搜索］等选项中的数学方法、适用于大部分问题。⒀［全部重新设置］清除当全规划求解问题设置，并重新全部设置全部为默认项

⒁开始规划求解，直到达到目标值为止第八步，处理规划求解所得到的结果当求解问题的过程结束时，出现如下对话框：图7-13。图7-132020/10/2861

敏感性分析报告包括的信息，展示了解决方案对问题所使用公式的变化的敏感程度。运算结果报告：⑴运算结果的初值（原值）和终值（求解）；⑵可变单元格的初值和终值；⑶约束条件及相关信息。极限结果报告：⑴下限极限；⑵上限极限；⑶目标式结果。当可变单元格位