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

1、2022年11月6日星期日第七章第七章Excel在营运资金在营运资金管理中的应用管理中的应用第一节 最佳现金持有量决策模型 一、成本分析模型 成本分析模型是通过分析持有现金的成本，将持有现金的总成本最低的现金持有量作为最佳现金持有量。持有现金的成本主要有机会成本、管理成本和短缺成本。通过计算比较各个现金持有量方案的总成本，选取总成本最低的方案为最优方案。例71：某企业有四种现金持有方案，有关成本资料如表71所示。确定现金最佳持有量。创建过程：创建步骤见模型批注，如图7-1所示。图7-1 二、存货模式 确定最佳现金持有量的存货模式也称鲍莫模式，是1952年由美国经济学家威廉鲍莫(WillianJ

2、Baumol)首先提出的。根据这种模式，企业的现金持有量非常类似于存货，因此存货的经济订货批量模型可以用来确定最佳现金持有量。计算公式为:ib2TN 式中:b为现金与有价证券的转换成本，T为一定时期的现金需求总量，N为最佳现金持有量，i为有价证券的利息率 例7-2，某企业的现金流量稳定，预计全年现金需求总量200000元，现金与有价证券的转换成本为每次400元，有价证券的利率为10%。采用存货模式确定该企业最佳现金持有量。计算过程:如图7-2所示。图7-2第二节 应收帐款信用政策决策模型 应收账款政策，是指企业向客户提供商业信用时，为了对应收账款进行规划和控制所确定的基本原则和规范。信用政策是

3、企业财务政策的一个重要组成部分。它由信用标准、信用条件和收账政策三要素组成。一、信用标准决策模型 信用标准是指企业同意向客户提供商业信用时，客户必须具备的最低财务能力。企业的信用标准比较严格，只对信誉较好的客户提供商业信用，则可以减少坏账损失，降低应收账款的机会成本和管理成本，但是也会减少企业的销售量；反之，如果企业的信用标准比较宽松，虽然会增加销售量，但是也会相应增加坏账损失和应收账款的机会成本与管理成本。因此，企业必须在扩大销售与增加成本之间权衡利弊，制定一个比较合理的信用标准。信用标准确定相关计算公式：销售利润率额售销的少减或由于标准变化增加响影的润利信用标准变化对 的机会成本率款帐收应

4、成本率动变期帐收均平变动销售额的的变动额额售销机会成本的影响机款帐收应对化变准标用信 360 的坏帐损失率额售销减增额减增销售额的对坏帐损失的影响化变准标用信 的影响额坏帐损失的影响额机会成本影响额利润的带来的增量利润化变准标用信 例7-3，某公司2001年的经营情况和信用标准如表73所示。该公司为了扩大销售，增加利润总额，采取了放宽信用标准的策略，提出了A、B两个方案，其信用标准及对企业的影响如表7-4。A 方 案 A 方 案B 方 案B 方 案信信用用标标准准:只只对对坏坏账账损损失失率率低低于于8 8的的企企业业提供商业信用提供商业信用信信 用用 标标 准准：只只 对对 坏坏 账账损损

5、失失 率率 低低 于于 10 的的 企企业提供商业信用业提供商业信用 由于信用标准放宽增加销售收入由于信用标准放宽增加销售收入(元元)由由 于于 信信 用用 标标 准准 放放 宽宽 增增加销售收入加销售收入(元元)400004320043200 新增销售收入的平均收账期新增销售收入的平均收账期(天天)新新 增增 销销 售售 收收 入入 的的 平平 均均收账期收账期(天天)456060 新增销售收入的坏账损失率6 新增销售收入的坏账损失率6 新新 增增 销销 售售 收收 入入 的的 坏坏 账账损失率损失率0.6%0.90%0.90%新增销售收入增加管理成本新增销售收入增加管理成本(元元)新新 增

6、增 销销 售售 收收 入入 增增 加加 管管理成本理成本(元元)300400400表 7-4 分析过程分析过程:第一步，建立如图7-3-1、2所示的分析模型结构。图7-3图7-3-1 第二步，在单元格C30中输入公式”=B19*$C$8/100”；图7-3-2 第三步，在单元格C31中输入公式”=(B19/360)*B21*$C$13/100”；第四步，在单元格C32中输入公式”=B19*B23”；第五步，在单元格C33中输入公式”=B25”；第六步，在单元格C34中输入公式”=C30-C31-C32-C33”，得到A方案的计算机结果。第七步，选取单元格区域C30:C34，将其复制到单元格区域

7、D30:D34中，得到方案B的有关计算结果。第八步，在单元格C35中输入公式”=IF(AND(C340,D340),IF(C34D34,采用方案A,采用方案B),IF(D340,采用方案B,仍采用目前的信用标准)”二、信用条件决策模型 信用条件是指企业接受客户信用订单时所提出的付款要求，主要包括信用期限、折扣期限和现金折扣。采取现金折扣方式在有利于刺激销售、降低相关成本或损失的同时，也需付出一定的成本代价，即现金折扣造成的损失。如果加速收款增加的收益能够补偿现金折扣成本，企业就可以采取现金折扣或进一步改变当前的折扣方针；反之，现金折扣优惠便是不合理的。主要计算公式如下：销售利润率额减增销售额的

8、对利润的影响信用条件变化 应收帐款的机会成本率变动成本率收帐期的平均新方案量减增的销售额期帐平均收目前的收帐期的平均新方案销售额件下的目前条响影的机会成本对应收款360 -360 坏帐损失率的额售销增加或减少增减额的销售响影的失化对坏帐损信用条件变 信用条件变化带来的增量利润为第一项与信用条件变化带来的增量利润为第一项与后三项之差后三项之差折扣率金现所占的比重额售销的扣折金现增减额的销售销售额件下的目前条响影的本对现金折扣成信用条件变化 例74，某企业拟改变信用条件，现有两个可供选择的信用条件方案，有关资料表75所示。企业应选择哪个方案。表7-5项目项目数据数据销售额(元)销售额(元)1500

9、00150000变动成本率变动成本率60%60%利润(元)利润(元)3000030000销售利润率销售利润率25%25%信用标准信用标准10%10%平均坏帐损失率平均坏帐损失率6%6%信用条件信用条件30天付清天付清平均收款期(天)平均收款期(天)4545应收帐款的机会成本率应收帐款的机会成本率15%15%目前的基本情况目前的基本情况项 项 目 目方案A方案B信用条件信用条件4545天内付清,无现金折扣 2/10,n/302/10,n/30由 于信 用条 件变 化增 加或 减少 的销 售额(元)由 于信 用条 件变 化增 加或 减少 的销 售额(元)20000200003000030000增加

10、销售额的平均坏帐损失率增加销售额的平均坏帐损失率11%11%10%10%须付现金折扣的销售额占总销售额的百分比须付现金折扣的销售额占总销售额的百分比0%0%50%50%现金折扣率现金折扣率0%0%2%2%平 均收 帐期(天)平 均收 帐期(天)60602020新的信用条件方案有关数据新的信用条件方案有关数据 分析过程分析过程:第一步，建立如图7-4所示的分析模型结构。续表图7-4 第二步，在单元格C26中输入公式”=C18*$C$8”；第三步，在单元格C27中输入公式”=(C22-$C$12)/360*$C$5+C22/360*C18)*$C$6*$C$13”第四步，在单元格C28中输入公式”

11、=(C5+C18)*C20*C21”；第五步，在单元格C29中输入公式”=C18*C19”；第六步，在单元格C30中输入公式”=C26-C27-C28-C29”，得到A方案的计算机结果。第七步，选取单元格区域C26:C30，将其复制到单元格区域D26:D30中，得到方案B的有关计算结果。第八步，在单元格C31中输入公式”=IF(AND(C300,D300),IF(C30D30,应采用方案A,应采用方案B),IF(C300,应采用方案A,IF(D300,应选择方案B,仍采用目前的信用条件)”三、应收帐款信用政策的综合决策模型三、应收帐款信用政策的综合决策模型 信用政策中的每一项内容的变化都会影信

12、用政策中的每一项内容的变化都会影响到企业的利益，因此，需要这些因素综合响到企业的利益，因此，需要这些因素综合起来来滤，以制定合适的信用政策。起来来滤，以制定合适的信用政策。主要计算公式：主要计算公式：1、利润政策变化对利润的影响、利润政策变化对利润的影响销售利润率新方案销售额增减量利润增减量 2、信用政策变化对应收帐款机会成本的影响应收帐款的机会成本率变动成本率收帐期的平均新方案量减增的销售额期帐平均收目前的收帐期的平均新方案销售额件下的目前条响影的机会成本对应收款360 -360 3、信用政策变化对现金折扣成本的影响销售额所占比重原方案现金折扣现金折扣率的案方原销售额原方案销售额所占比重新方

13、案现金折扣金折扣率新方案现销售额新方案成本增减量扣折金现 4、信用政策变化对坏帐损失的影响坏帐损失率原方案平均销售额原方案坏帐损失率新方案平均销售额新方案量减增坏帐损失 5、信用政策变化对收帐管理成本的影响管理成本率原方案收帐销售额原方案管理成本率新方案收帐销售额新方案量减增本收帐管理成 例75，某企业现有的信用政策以及要改变信用政策的两个可供选择的方案所表76所示。决策。方案A方案A方案B方案B年赊销额年赊销额(元)100000100000120000120000130000130000销售利润率销售利润率(%)20%20%20%20%20%20%收帐管理成本率收帐管理成本率(%)1%1%0

14、.7%0.7%0.8%0.8%平均坏帐损失率平均坏帐损失率(%)2%2%3%3%4%4%平均收帐期平均收帐期(天）454560603030须付现金折扣的销售额占总销须付现金折扣的销售额占总销售额的百分比售额的百分比(%)0%0%0%0%50%50%现金折扣率现金折扣率(%)0%0%0%0%2%2%应收帐款的机会成本率应收帐款的机会成本率(%)15%15%15%15%15%15%变动成本率变动成本率60%60%60%60%60.0%60.0%项项 目目目前信用政策目前信用政策新信用政策方案 新信用政策方案 表76 分析过程：建立如图7-5所示的分析模型结构。图7-5 第二步，在单元格D17中输入

15、公式”=(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”;第七步，在单元格D22中输入公式”=D17-D18-D19-D20-D21”，计算A方案的增量利润。第八步，选取单元格区域D17:D22，将其复制到单元格区域E17:E22中，得到方案B的有关计算结果。第九

16、步，在单元格C31中输入公式”=IF(AND(D220,E220),IF(D22E22,“采用方案A”,“采用方案B”),IF(D220,“采用方案A”,IF(E220,“采用方案B”,“采用目前信用政策”)，产生结论。设计过程第三节第三节 存货的经济订货批量决策模型存货的经济订货批量决策模型 存货决策涉及多方面的内容，包括决定进货项目、选择进货单位、决定进货时间和决定进货批量等，其中最常见的存货决策是确定经济订货批量。企业购买和储存存货的有关成本包括：订货成本指取得订单的成本，如办公费、差旅费、邮资、电报电话费等支出。购置成本指存货本身的价值和运杂费。储存成本是指为保持存货而发生的成本。缺货

17、成本是指由于存货供应中断而造成的损失。一、基本的经济订货批量模型一、基本的经济订货批量模型 经济订货量基本模型经济订货量基本模型KQD2QTCKcKD2KG*c(G*)KDK2TC 例7-6，某企业全年需要材料3600千克，一次订货费用25元，材料单价20元/千克，单位材料的储存费用为2元，求该材料的经济订货批量、全年的订货次数和与储存存货相关的总成本。设计过程：第一步，建立如图7-6所示分析模型结构。第二步，在单元格C8中输入公式”=SQRT(2*C3*C2/C5)计算经济批量；第三步，在单元格C9中输入公式”=C2/C8”计算一年采购的次数；图7-6 第四步，在单元格C10中输入公式”=S

18、QRT(2*C2*C3*C5)”计算与经济批量相关的总成本。设计过程 存货陆续供应和耗用情况下的经济订货批量模型dHHKDK2G*c)Hd1(KDK2)G*(TCc 例7-7,如例76中的企业所需要的材料不是瞬时到货,而是陆续供货,进货期内每日供货量30千克,每日耗用量为10千克。计算经济订货批量、全年的订货次数和与储存存货相关的总成本。设计过程：第一步，建立如图7-7所示分析模型结构。图7-7 第二步，在单元格E8中输入公式”=SQRT(2*C3*C2/C5)，计算经济批量；第三步，在单元格E9中输入公式”=C2/E8”，计算一年采购的次数；第四步，在单元格E10中输入公式”=SQRT(2*

19、C2*C3*C5*(1-E3/E2)”,计算与经济批量相关的总成本。设计过程 有数量折扣的经济订货批量决策模型有数量折扣的经济订货批量决策模型iCdUDKGDKG121T 例78,某企业每年需要某零件8500件.每次订货费用260元,存储费率为零件单价的10。供货商规定，凡一次购买1000件以下的价格为10元/件，1000件或以上但2000件以下的价格为9元件，2000件或以上但3000件以下的价格为8元件，3000件或以上的价格7元。问企业应如何订货。设计过程：设计过程：第一步，建立如图7-8所示的分析模型结构。图7-8 第二步，在单元格D9、E9、F9、G9、H9、I9中分别输入以下公式：

20、单元格D9：“=SQRT(2*$C$4*$C$5/(C9*$C$6)”，计算单价为10元是的经济批量，即采购批量(G)在1=G=B9,D9B10),”有效“,”无效“)”，判断计算的经济批量是否落在有效期间内，即1=G=B12,有效,无效)”第四步，在单元格C14种输入公式“=INDEX(I9:I12,MATCH(MIN(H9:H12),H9:H12,0)”，得出无需求量限制的经济订货批量。第五步，在单元格C15种输入公式“=IF(C4C14,C14,C4)”，确定最有经济批量。设计过程第二节 最优决策分析工具 “规划求解”工具 一、规划求解工具的使用 谓规划就是指具有多种实用价值的有限资源，

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

22、制约因素。如资金、劳力、资源等。包括客观约束条件和主管约束条件。二、规划求解的设置过程 使用规划求解工具只需从工具菜单中选择标出规划求解对话框，见图7-9。只有通过“自定义安装”方式将Excel完全安装，工具菜单才有规划求解命令。图9 目标单元格 目标单元格也称目标函数是模型中的一个你希望最大值、最小值的单元格，目标单元格应含有决策变量(可变单元格)，即引用含有决策变量的单元格或名称(和目标函数对应)可变单元格 可变单元格也称决策变量是对目标单元格数值产生影响的单元格。可变单元格最多200个。(和决策变量对应)可变单元格框中的输入项通常是某个单元格区域的引用位置，或使用逗号隔开的几个非相邻单元

23、格的引用 约束条件 约束条件是必须符合某些限制或目标值的单元格数值。约束条件可以使用在目标单元格和可变单元格上。1.约束条件中可以指定模型中任意单元格的上下界 2.单元格引用位置框中所引用到的单元格，通常含有于一个或多个可变单元格直接或间接相关的公式。3.使用“int”运算符时，表示有约束条件约束约束的数值只能是整数。只有可变单元格才可以限制为整数。例7-9：某企业在某月份生产甲、乙两种产品，其有关资料如表7-7所示，则企业应如何安排两种产品的产销组合，使企业获得最大销售利润？表7-7 利用规划求解工具解决该问题的步骤为：第一步，建立优化模型，决策变量：设x和y分别表示甲产品和乙产品的生产量。

24、目标函数：MAX(销售利润)=(140-60)x+(180-100)y 约束条件：6x+9y360 7x+4y240 18x+15y850 y30 x0,y0,且为整数 第二步，单元格C12和D12为可变单元格，分别存放甲、乙产品的生产量。第三步，单元格C13为目标单元格(销售利润)，计算公式为“=SUMPRODUCT(C5:D5-C6:D6,C12:D12)”。第四步，在单元格C14中输入产品消耗工时合计计算公式“=SUMPRODUCT(C7:D7,C12:D12)”。在单元格C15中输入产品消耗材料合计计算公式“=SUMPRODUCT(C8:D8,C12:D12)”。在单元格C16中输入产

25、品消耗能源合计计算公式“=SUMPRODUCT(C9:D9,C12:D12)”。第五步，单击工具菜单，选择规划求解项，则系统弹出规划求解参数对话框，如图7-10所示。图7-10 第六步，在规划求解参数对话框中，设置目标单元格中输入“$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 第七步，如有

26、需要还可以设置有关的项目，即单击选项按钮，弹出规划求解选项对话框，如图7-12所示。图7-12 有关选项设置说明：选项允许你控制求解过程的高级特性并加载或保存工作表上一个特殊问题的选择；可以为线性问题和非线性问题之间的差异定义参数。最长运算时间限制求解过程花费的时间，其值必须是正整数，默认值为100秒；精度控制规划求解找到答案的精度，在精度框中如入的数：用来决定约束值是否满足目标或满足你所指定的上下限；必须是一个在0和1（不包含0和1）之间的小数 默认值为0.000001 允许误差表示当一个正数约束条件被用于问题的任何代以元素时，规划求解所允许的误差百分比。当没有正数约束条件时，允许误差设置无

27、效；收敛度在此输入收敛度数值，当最近5此迭代后，目标单元格中数值的变化小于“收敛度”编辑框中设置的数值时，“规划求解”停止运行。收敛度只适应于非线性规划问题，用小数表示；采用线性模型当模型中的所有关系为线性时，可以选择该选项 显示迭代结果选择此项后，在每次迭代后中断规划求解，显示迭代结果 自动按比例缩放当输入（可变单元格）、输出（目标单元格）和限制在数量上有较大差别时，选择此选择框；假定非负对于在“添加约束”对话框的“约束值”编辑框中没有设置下限的可变单元格，假定其下限为0；估值该选项自动在每次一维搜索中用来获得基本数量的初始估计值的方法。正切函数：一维线性外推；二次方程：使用二维外推，适用于

28、过度非线性问题 导数偏导数对目标函数和约束条件起作用。前向差分：为默认值 中心差分：需要更多的计算工作表，使用它可提高规划求解结果的质量 搜索决定在每次迭代时使用什么搜索算法来确定搜索方向 牛顿法：搜粟需要更多的内存，迭代次数少 共轭法：搜所需要的内存少，通常需要更多的迭代次数 估值、导数偏导数、搜索等选项中的数学方法、适用于大部分问题。全部重新设置清除当全规划求解问题设置，并重新全部设置全部为默认项 开始规划求解，直到达到目标值为止 第八步，处理规划求解所得到的结果 当求解问题的过程结束时，出现如下对话框：图7-13。图7-13 敏感性分析报告包括的信息，展示了解决方案对问题所使用公式的变化

29、的敏感程度。运算结果报告：运算结果的初值（原值）和终值（求解）；可变单元格的初值和终值；约束条件及相关信息。极限结果报告：下限极限；上限极限；目标式结果。当可变单元格位于上下界时，目标单元格的值。第九步，单击确定按钮，生成结果见图7-14。图7-14 三、应用“规划求解”工具分析最优订货批量 例710，应用“规划求解”工具分析最优订货批量模型。原始资料见表7-7。甲、乙、丙、丁材料的折扣起点分别为400、450、500、500。表7-7 求解过程：第一步，建立如图7-14所示的分析模型结构。图7-14 第二步，单元个区域C15、D15、E15、F15分别代表甲、乙、丙、丁四种材料的采购量。第三

30、步，选取单元个区域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”，通过数组生成键计算材料的订货成本。第六步，选取单元个区域C19:F19，输入公式“=C16:F16+C17:F17+C18:F18”，通过数组生成键计算各存货的总成本。第七步，在单元格C20中输入公式“=SUM(C19:F19)”，计算存货的总成本。第八步，选取单元个区域C21:F21，输入公式“=C4:F4/C15:F15”，通过数组生成键计算最佳订货次数。第九步，选取单元个区域C22:F22，输入公式“=12/C21:F21”，通过数组生成键计算订货周期。第十步，选取单元个区域C23:F23，输入公式“=C15:F15/2*C10:F10”，通过数组生成键计算经济订货量占用资金。第十一步，单击工具菜单，选择规划求解，按图7-15设置。图7-15