EXCEL在营运资金管理中的应用课件.ppt

EXCEL在营运资金管理中的应用第9章1EXCEL在营运资金管理中的应用•本章主要介绍：运用￿Excel进行各营运资金管理的方法￿本章简介本章重点重点、难点•现金管理，应收账款管理，存货管理现金管理，应收账款管理，存货管理本章难点•存货模型存货模型29.3 存货管理存货管理9.2 应收账款管理应收账款管理本章目录9.1 现金管理现金管理39.2.2￿￿最佳现金持有量决策9.1.1￿￿现金管理概述9.1￿现金管理49.1.1￿￿现金管理概述1 1•现金是企业中流动性最强的资产，这里的现金不仅包括企业的库存现金，还包括各种形式的银行存款、银行汇票、银行本票等2 23 3•企业进行现金管理的目标就是在流动性和盈利能力之间作出选择，以获取最大的整体利益•即是要在保证企业正常生产经营活动的前提下，尽可能地降低现金的储备量59.1.2￿￿最佳现金持有量决策1.￿成本分析模型成本分析模型是通过分析持有现金的成本，将持有现金的总成本最低时的现金持有量作为最佳现金持有量1 1•机会成本是企业为保持一定数额的现金资产，￿而放弃将这些资产用于其他投资机会所获得的收益现金持有量越多，机会成本越高￿2 23 3•管理成本是企业为持有现金而发生的管理费用，如安全设施建造、管理人员工资等。

管理成本一般不随现金持有量变化而变化•短缺成本是企业由于缺乏必要的现金应付业务开支所需，￿而使企业蒙受的损失或为此付出的代价现金持有量越多，短缺成本越少持有现金的成本主要有通过计算比较各个现金持有量方案的总成本（机会成本、管理成本和短缺成本之和）￿，选取总成本最低的方案为最优方案￿6例例9-1 §A 企业有 4 个现金持有方案，成本资料如图 9-1所示，确定最佳现金持有量具体的操作步骤如下： §第一步：在 B8 单元格中输入公式“=SUM (B5:B7)” ，计算方案 A的持有总成本7例例9-1 §A 企业有 4 个现金持有方案，成本资料如图 9-1所示，确定最佳现金持有量具体的操作步骤如下： §第二步：复制公式到单元格区域 C8:E8，计算其余各方案持有总成本 §方案 B 的持有总成本最低，所以方案 B 为最佳现金持有量89.1.2￿￿最佳现金持有量决策2.￿存货模型存货模型也被称为鲍曼模型，是将企业现金持有量同持有的有价证券联系起来观察，通过比较不同现金持有量下现金相关总成本来确定现金最佳持有量￿1 1•企业在一定时期内对现金的需求是已知常数￿2 23 3•单位时间的现金使用量是一个稳定的值，现金余额定期由最高点￿Q￿到￿0￿均衡变化，平均现金余额为￿Q/2。

￿•当现金耗尽时，企业可以出售有价证券，以便及时补充现金￿存货模型假设：9现金持有总成本￿=机会成本+交易成本￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿￿=平均现金余额×有价证券利息率+交易次数×每次交易成本￿用数学求最小值方法得出最佳现金持有量为：10例例9-2 §某企业预计在 1 年内经营所需现金总额为 10 000 000 元，准备用有价证券变现取得，每次买卖有价证券的交易成本为 100元， 证券市场年利率 6%，计算最佳现金持有量具体的操作步骤如下： §第一步：建立存货分析模型，如图 9-2 所示11例例9-2 §某企业预计在 1 年内经营所需现金总额为 10 000 000 元，准备用有价证券变现取得，每次买卖有价证券的交易成本为 100元， 证券市场年利率 6%，计算最佳现金持有量具体的操作步骤如下： §第二步： 在单元格 B5 中输入公式“=SQRT (2*B2*B3/B4)” ，计算最佳现金持有量在 B6 单元中输入公式“=B2/B5” ，计算一年内从有价证券转换为现金的次数129.2.2￿￿信用标准决策9.2.1￿￿应收账款管理概述9.2￿应收账款管理9.2.3￿￿信用条件决策9.2.4￿￿收账政策决策139.2.1￿￿应收账款管理概述应收账款是企业应该收取而未收到的各种款项，包括应收账款、应收票据和其他应收款等。

￿1 1•机会成本：因投放于应收账款而放弃其他投资机会所能获取的收益其影响因素有：应收账款数额、资金成本率和应收账款收账期它们与机会成本呈正相关2 23 3•管理成本：企业因管理应收账款而发生的各项支出，包括对客户信用状况的调查费、收集整理信息费以及收账费用等•坏账成本是因应收账款无法收回而产生的坏账损失￿应收账款成本包括通过计算比较各个现金持有量方案的总成本（机会成本、管理成本和短缺成本之和）￿，选取总成本最低的方案为最优方案￿应收账款管理的目标是：在发挥使用应收账款扩大销售，加强竞争力作用的同时，制定合理的应收账款信用政策，强化应收账款管理149.2.2￿￿信用标准决策信用标准是客户获得企业商业信用所应具备的最低条件，￿通常以预期的坏账损失率表示计算公式：信用标准决策原则：比较不同信用标准带来的净利润增长，选择增长大的信用标准5C￿评价法：品质（Character）￿、能力（Capacity）￿、抵押（Collateral）￿、资本（Captical）和条件（Condition）15例例9-3 §企业目前的经营情况和信用标准如图9-3， 企业提出两个信用标准方案 ，请判断应采用哪个方案。

具体的操作步骤如下： §第一步：建立信用标准决策模型，如图 所示 16例例9-3 §企业目前的经营情况和信用标准如图9-3， 企业提出两个信用标准方案 ，请判断应采用哪个方案具体的操作步骤如下： §第二步：在单元格B21 中输入公式“=B16*$B$7” ，计算方案1中信用标准变化对利润的影响复制公式到单元格C21 中，计算方案2中信用标准变化对利润的影响17例例9-3 §企业目前的经营情况和信用标准如图9-3， 企业提出两个信用标准方案 ，请判断应采用哪个方案具体的操作步骤如下： §第三步：在单元格 B22 中输入公式“=B17/360*B16*$B$5*$B$12” ，计算方案 1 中信用标准变化对应收账款机会成本的影响复制公式到单元格C22 中，计算方案2 中信用标准变化对应收账款机会成本的影响 18例例9-3 §企业目前的经营情况和信用标准如图9-3， 企业提出两个信用标准方案 ，请判断应采用哪个方案具体的操作步骤如下： §第四步：在单元格B23中输入公式“=B16*B18” ，计算方案1 中信用标准变化对坏账损失的影响复制公式到单元格C23中，计算方案2 中信用标准变化对坏账损失的影响。

19例例9-3 §企业目前的经营情况和信用标准如图9-3， 企业提出两个信用标准方案 ，请判断应采用哪个方案具体的操作步骤如下： §第五步：在单元格 B24 中输入公式“=B21-B22-B23” ，计算方案 1 中信用标准变化带来的增量利润复制公式到单元格C24 中，计算方案2 中信用标准变化带来的增量利润20例例9-3 §企业目前的经营情况和信用标准如图9-3， 企业提出两个信用标准方案 ，请判断应采用哪个方案具体的操作步骤如下： §第六步：在单元格 B25 中输入公式“=IF(AND(B24<0,C24<0)，”采用目前信用标准“，IF(B24>C24，"采用方案1"，"采用方案2"))” ，进行决策21229.2.3￿￿信用条件决策信用条件是指企业要求客户支付赊销款项的条件，包括信用期限、折扣期限和现金折扣信用期限是指企业为客户规定的最长付款时间计算公式：信用条件决策原则：比较不同信用条件带来的净利润增长，选择增长大的信用条件折扣期限是指企业为客户规定的可以享受现金折扣的付款时间现金折扣是指企业给予客户在规定时期内提前付款能按销售额的一定比率享受折扣，如“2/10，n/302324例例9-4 §企业拟改变信用条件，提出两个方案，资料如图 9-5 所示，请判断应采用哪个方案。

具体的操作步骤如下： §第一步：建立信用条件决策模型，如图所示25例例9-4 §企业拟改变信用条件，提出两个方案，资料如图 9-5 所示，请判断应采用哪个方案具体的操作步骤如下： §第二步：在单元格B22 中输入公式“=B16*$B$7” ，计算方案1中信用条件变化对利润的影响复制公式到单元格C22 中，计算方案2中信用条件变化对利润的影响26例例9-4 §企业拟改变信用条件，提出两个方案，资料如图 9-5 所示，请判断应采用哪个方案具体的操作步骤如下： §第三步：在单元格B23中输入公式“=((B20-$B$11)/360*$B$4+B20/360*B16)*$B$5*$B$12” ，计算方案1中信用条件变化对应收账款机会成本的影响复制公式到单元格C23中，计算方案2 中信用条件变化对应收账款机会成本的影响27例例9-4 §企业拟改变信用条件，提出两个方案，资料如图 9-5 所示，请判断应采用哪个方案具体的操作步骤如下： §第四步：在单元格 B24 中输入公式“=($B$4+B16)*B19*B17” ，计算方案 1 中信用条件变化对现金折扣成本的影响复制公式到单元格C24 中，计算方案2中信用条件变化对现金折扣成本的影响。

28例例9-4 §企业拟改变信用条件，提出两个方案，资料如图 9-5 所示，请判断应采用哪个方案具体的操作步骤如下： §第五步：在单元格B25中输入公式“=B16*B18” ，计算方案1 中信用条件变化对坏账损失的影响复制公式到单元格C25中，计算方案2 中信用条件变化对坏账损失的影响29例例9-4 §企业拟改变信用条件，提出两个方案，资料如图 9-5 所示，请判断应采用哪个方案具体的操作步骤如下： §第六步：在单元格 B26 中输入公式“=B22-B23-B24-B25” ，计算方案 1 中信用条件变化带来的增量利润复制公式到单元格C26 中，计算方案2中信用条件变化带来的增量利润30例例9-4 §企业拟改变信用条件，提出两个方案，资料如图 9-5 所示，请判断应采用哪个方案具体的操作步骤如下： §第七步：在单元格 B27 中输入公式“=IF(AND(B26<0,C26<0)，”采用目前信用条件“，IF(B26>C26,"采用方案1","采用方案2"))” ，进行决策31329.2.4￿￿收账政策决策收账政策是指当客户违反信用条件，拖欠甚至拒付账款时，企业采取的收账策略与措施。

这些措施包括书信催讨、催讨、上门催缴以及诉诸法律计算公式：收账政策决策原则：计算收账政策的总成本，选择总成本低的收账政策方案33例例9-5 §企业不同收账政策资料如图9-6 所示，请判断是否应采用建议收账政策具体的操作步骤如下： §第一步：建立收账政策决策模型，如图所示 34例例9-5 §企业不同收账政策资料如图9-6 所示，请判断是否应采用建议收账政策具体的操作步骤如下： §第二步：在单元格B14中输入公式“=$B$4/360*B10” ，计算目前收账政策应收账款平均占用额复制公式到单元格C14 中，计算建议收账政策应收账款平均占用额35例例9-5 §企业不同收账政策资料如图9-6 所示，请判断是否应采用建议收账政策具体的操作步骤如下： §第三步：在单元格B15中输入公式“=B14*$B$6” ，计算目前收账政策机会成本复制公式到单元格C15 中，计算建议收账政策机会成本36例例9-5 §企业不同收账政策资料如图9-6 所示，请判断是否应采用建议收账政策具体的操作步骤如下： §第四步：在单元格B16中输入公式“=$B$4*B11” ，计算目前收账政策坏账损失复制公式到单元格C16 中，计算建议收账政策坏账损失。

37例例9-5 §企业不同收账政策资料如图9-6 所示，请判断是否应采用建议收账政策具体的操作步骤如下： §第五步：在单元格B17中输入公式“=B9” ，计算目前收账政策收账费用复制公式到单元格C17 中，计算建议收账政策收账费用38例例9-5 §企业不同收账政策资料如图9-6 所示，请判断是否应采用建议收账政策具体的操作步骤如下： §第六步：在单元格B18中输入公式“=B15+B16+B17” ，计算目前收账政策总成本复制公式到单元格C18 中，计算建议收账政策总成本39例例9-5 §企业不同收账政策资料如图9-6 所示，请判断是否应采用建议收账政策具体的操作步骤如下： §第七步：在单元格 B19 中输入公式“=IF(AND(B18<0,C18<0),”采用目前信用条件“，IF(B18>C18，"建议收账政策"，"目前收账政策"))” ，进行决策409.3.2￿￿存货成本及经济订货批量9.3.1￿￿存货管理概述9.3￿存货管理9.3.3￿￿存货ABC分类管理419.3.1￿￿存货管理概述存货是指企业在生产经营过程中为销售或耗用而储备的物资，包括各种原材料、燃料、包装物、低值易耗品、委托加工物资、在产品、产成品和库存商品等。

1 1•取得成本又称进货成本，是为取得各种存货而支出的成本，主要包括存货的购置成本和订货成本2 23 3•储存成本指在存货储存过程中发生的仓储费、保险费、占用资金支付的利息等储存成本可分为固定成本和变动成本•缺货成本指由于存货储备不能满足需要而造成的损失，￿如由于材料供应中断造成的停工损失、成品供应中断导致延误发货的信誉损失及丧失销售机会的损失等存货成本包括企业对存货进行管理，需要在存货成本和存货收益之间做出权衡，达到两者的最佳结合42•企业能够瞬时补充存货1•存货能集中到货2•不允许缺货￿39.3.2￿￿存货成本及经济批量模型1.基本经济批量模型基本经济批量模型基本假设存货经济批量是指能够使一定时期存货的总成本达到最低点的进货数量•一定时期的存货总需求量确定￿4•存货单价保持不变￿543用数学求最小值方法得出经济批量为：存货总成本：D*P为购置成本，与订货量无关，一般也可以不考虑44例例9-6 §某公司全年需要某种材料 4 000 kg，一次订货费用 20 元，材料单价 15 元/kg，单位储存成本10 元/（kg·年） ，用基本经济批量模型确定材料的经济订货批量、全年最佳订货次数和最低存货总成本。

具体的操作步骤如下： §第一步：建立基本经济批量模型，如图所示 45例例9-6 §某公司全年需要某种材料 4 000 kg，一次订货费用 20 元，材料单价 15 元/kg，单位储存成本10 元/（kg·年） ，用基本经济批量模型确定材料的经济订货批量、全年最佳订货次数和最低存货总成本具体的操作步骤如下： §第二步：在单元格 B8 中输入公式§“=SQRT(2*B4*B5/B6)” ，计算经济订货批量46例例9-6 §某公司全年需要某种材料 4 000 kg，一次订货费用 20 元，材料单价 15 元/kg，单位储存成本10 元/（kg·年） ，用基本经济批量模型确定材料的经济订货批量、全年最佳订货次数和最低存货总成本具体的操作步骤如下： §第三步： 在单元格B9中输入公式 “=B4/B8” ，计算最佳订货次数47例例9-6 §某公司全年需要某种材料 4 000 kg，一次订货费用 20 元，材料单价 15 元/kg，单位储存成本10 元/（kg·年） ，用基本经济批量模型确定材料的经济订货批量、全年最佳订货次数和最低存货总成本具体的操作步骤如下： §第四步：在单元格 B10 中输入公式“=B4*B7+SQRT(2*B4*B5*B6)” ，计算最低存货总成本。

489.3.2￿￿存货成本及经济批量模型2.陆续供应和消耗下经济订货批量模型如果存货是边供应边使用，每批订购存货达到后，有：实际工作中，存货的供应可能不是一次集中入库，而是陆续入库的情况，尤其是从多个供应商处采购存货以及生产成品入库、在库产品转移等用数学求最小值方法得出经济批量为：存货总成本：D*P为购置成本，与订货量无关，一般也可以不考虑49例例9-7 §假设例9-6中的材料不是瞬时到货，而是陆续供货，进货期内每日供货量 30 kg，每日需求量 10 kg，求材料的经济订货批量、全年最佳订货次数和最低存货总成本具体的操作步骤如下： §第一步：建立陆续到货经济批量模型，如图所示 50例例9-7 §假设例9-6中的材料不是瞬时到货，而是陆续供货，进货期内每日供货量 30 kg，每日需求量 10 kg，求材料的经济订货批量、全年最佳订货次数和最低存货总成本具体的操作步骤如下： §第二步：在单元格 B10 中输入公式“=SQRT(2*B4*B5/(B6*(1-B9/B8)))” ，计算经济订货批量51例例9-7 §假设例9-6中的材料不是瞬时到货，而是陆续供货，进货期内每日供货量 30 kg，每日需求量 10 kg，求材料的经济订货批量、全年最佳订货次数和最低存货总成本。

具体的操作步骤如下： §第三步： 在单元格B11中输入公式 “=B4/B10” ，计算最佳订货次数52例例9-7 §假设例9-6中的材料不是瞬时到货，而是陆续供货，进货期内每日供货量 30 kg，每日需求量 10 kg，求材料的经济订货批量、全年最佳订货次数和最低存货总成本具体的操作步骤如下： §第四步：在单元格 B12 中输入公式“=B4*B7+SQRT(2*B4*B5*B6*(1-B9/B8))” ，计算最低存货总成本539.3.2￿￿存货成本及经济批量模型3.￿￿允许缺货条件下经济订货批量模型允许缺货条件下经济批量为：54例例9-8 §某企业年需要某种材料 5 000 kg，每次订货费用 200 元，单位储存成本为10 元/（年·kg） ，单价15 元/kg如果缺货，每缺货1 kg 材料的损失为8 元求材料的经济订货批量、全年最佳订货次数和最低存货总成本具体的操作步骤如下： §第一步：建立允许缺货经济批量模型，如图所示 55例例9-8 §某企业年需要某种材料 5 000 kg，每次订货费用 200 元，单位储存成本为10 元/（年·kg） ，单价15 元/kg如果缺货，每缺货1 kg 材料的损失为8 元。

求材料的经济订货批量、全年最佳订货次数和最低存货总成本具体的操作步骤如下： §第二步：在单元格 B9 中输入公式“=SQRT(2*B4*B5*(B8+B6)/(B6*B8))” ，计算经济订货批量 56例例9-8§某企业年需要某种材料 5 000 kg，每次订货费用 200 元，单位储存成本为10 元/（年·kg） ，单价15 元/kg如果缺货，每缺货1 kg 材料的损失为8 元求材料的经济订货批量、全年最佳订货次数和最低存货总成本具体的操作步骤如下： §第三步： 在单元格B10中输入公式 “=B4/B9” ，计算最佳订货次数57例例9-8 §某企业年需要某种材料 5 000 kg，每次订货费用 200 元，单位储存成本为10 元/（年·kg） ，单价15 元/kg如果缺货，每缺货1 kg 材料的损失为8 元求材料的经济订货批量、全年最佳订货次数和最低存货总成本具体的操作步骤如下： §第四步：在单元格 B11 中输入公式§“=B4*B7+SQRT(2*B4*B5*B6*B8/(B6+B8))” ，计算最低存货总成本589.3.3￿存货￿ABC分类管理ABC￿分类管理法是一种体现重要性原则的管理方法。

管理的关键是对各种存货项目按一定标准分成A、B、C￿三类进行常见的分类标准如下：分类标准主要有两个：一是金额标准，二是品种数量标准A A•年度货币量最高的库存，这些品种数可能只占仓库库存总数的￿10%～15%，但是用于它们的库存成本却占到总数的70%～80%B BC C•年度货币量中等的库存，这些品种的数量占全部库存的￿20%～30%，价值占库存总价值的15%～25%•年度货币量较低的库存，它们的价值只占全部年度货币量的5%，但品种却是库存总数的55%～70%59ABC￿分类管理法的步骤如下：￿•根据每种存货在一定时期内的需求量以及价格计算该种存货的资金占用额，￿并按金额从大到小的顺序排序1•按上述排定的顺序，￿依次计算每种存货资金占用额占全部资金占用额的比重和累计比重2•按上述排定的顺序，依次计算累计存货品种数量占全部品种数量的百分比3•按事先确定的标准将全部存货划分为A、B、C￿三类4•根据ABC￿分类的结果选择合适的方法对各类存货进行管理控制560例例9-9 §某企业全年所需材料有关资料如图9-10所示，对存货进行ABC分类管理具体的操作步骤如下： §第一步：在单元格D3 中输入公式“=B3*C3” ，并复制公式到单元格区域D4:D22，计算各种材料占用金额。

在单元格D23中输入公式“=SUM(D3:D22)” ，计算总金额 61例例9-9 §某企业全年所需材料有关资料如图9-10所示，对存货进行ABC分类管理具体的操作步骤如下： §第二步：在单元格 E3 中输入公式“=D3/$D$23” ，并复制公式到单元格区域E4:E22，计算比重 62例例9-9 §某企业全年所需材料有关资料如图9-10所示，对存货进行ABC分类管理具体的操作步骤如下： §第三步：选中单元格区域A3:E22，单击“数据”菜单，选择“排序”命令，出现如图 9-11 所示的“排序”对话框主要关键字”选“金额” ，单选按钮选“降序” ，单击“确定” ，得存货按金额大小的排序63例例9-9 §某企业全年所需材料有关资料如图9-10所示，对存货进行ABC分类管理具体的操作步骤如下： §第四步：在单元格 F3 中输入公式“=SUM($E$3:E3)” ，并复制公式到单元格区域F4:F22，计算累计比重64例例9-9 §某企业全年所需材料有关资料如图9-10所示，对存货进行ABC分类管理具体的操作步骤如下： §第五步：在单元格 G3 中输入公式“=IF(F3<=80%,”A“, IF(F3<=95%,”B“,”C“))” ，并复制到单元格区域G4:G22，对存货进行分类。

65例例9-9 §某企业全年所需材料有关资料如图9-10所示，对存货进行ABC分类管理具体的操作步骤如下： §第六步：第六步：选中单元格区域J3：J5输入公式 “=SUMIF(G3:G22,I3:I5,E3:E22)” ，计算各类存货占用资金比重选中单元格区域K3：K5输入公式“=COUNTIF(G3:G22,I3:I5)/COUNT(F3:F22)” ，计算各类存货品种数量比重6667EXCEL在营运资金管理中的应用。