《Excel在财务管理中的应用》课件 05EXCEL表在营运资金管理中的应用

EXCEL表在营运资金管理中的应用目录Contents应收账款管理01最佳现金持有量02

经济订货量0301应收账款管理一、应收账款信用标准决策模型

信用标准是指企业建立的允许客户享受企业所提供信用的最低标准。信用标准通常可用客户的预计坏账损失率来表示。严格的信用标准--不利于企业扩大销售、增加利润。宽松的信用标准--有助于企业扩大销售、增加利润,但是增加应收账款的相关成本。（一）应收账款信用标准决策的基本原理应收账款的相关成本主要包括机会成本、坏账成本、管理成本和现金折扣成本，管理成本属于固定成本，一般情况下不考虑。对利润的影响=销售额×销售利润率坏账损失=销售额×坏账损失率净收益=增加的利润-机会成本-坏账损失（二）与应收账款成本有关的计算公式：机会成本=销售额/360×平均收款期×变动成本率×机会成本率（三）应收账款信用标准决策模型的建立【例1】某公司目前采用的信用标准以及备选的信用标准方案的有关资料如图的【已知条件】区域所示。要求建立一个对该公司选择信用标准方案做出决策的模型。原来的信用标准的有关数据采用备选信用标准方案的有关数据预计坏账损失率标准8%备选方案甲方案乙方案信用期限（天）30预计坏账损失率标准5%10%年赊销收入（万元）3500年赊销额增加（万元）-150300销售利润率20%增加销售额的平均收款期（天）4045平均实际发生的坏账损失率9%增加销售额的平均坏账损失率6%11%平均收款期（天）40增加销售额引起的管理费增加（万元）-23应收账款的机会成本8%变动成本率55%一年计算天数360计算与决策结果（万元）信用标准变化的影响甲方案乙方案对销售利润的影响-3060对机会成本的影响-0.731.65对坏账成本的影响-933对管理费用的影响-23对净收益的综合影响-18.2722.35决策结论：乙方案二、应收账款信用条件决策模型

信用条件是指企业同意客户支付赊销款项的具体条件,包括信用期限、折扣期限和现金折扣比率。例如,(2/30,N/60)这一信用条件的含义是:30天内付款的客户可以享受2%的现金折扣,全部款项应在60天内付清,其中60为信用期限,30为折扣期限,2为现金折扣的百分率。（一）应收账款信用条件决策的基本原理对利润的影响=新销售额×销售利润率坏账损失=新销售额×坏账损失率净收益=增加的利润-机会成本-坏账损失-现金折扣成本（二）与应收账款成本有关的计算公式：现金折扣=（原销售额+新销售额）×现金折扣销售百分比×现金折扣率机会成本=[原销售额/360×（新收账期-原收账期）+新销售额/360×新收账期]×变动成本率×机会成本率（三）应收账款信用条件决策模型的建立【例2】某公司2个备选的信用条件方案的有关资料如图的【已知条件】区域所示；要求建立一个对该公司选择信用条件方案做出决策的模型。基本项目数据项目数据销售额(元)150000变动成本率60%利润(元)30000销售利润率25%信用标准(预期坏帐损失率限制)10%平均坏帐损失率6%信用条件30天付清平均收款期(天)45应收帐款的机会成本率15%信用条件变化方案新项目数据项目方案A方案B信用条件45天内付清,无现金折扣"2/10,n/30"因信用条件变化增加或减少的销售额(元)2000030000增加销售额的平均坏帐损失率11%10%需付现金折扣的销售额占总销售额的百分比0%50%现金折扣率0%2%平均收款期(天)6020计算与决策结果项目方案A方案B信用条件变化对利润的影响(元)50007500信用条件变化对应收帐款机会成本的影响(元)862.5-787.5信用条件变化对现金折扣成本的影响(元)01800信用条件变化对坏帐损失的影响(元)22003000信用条件变化带来的增量利润(元)1937.53487.5三、应收账款收账政策决策模型企业收回应收的账款往往要花费一定的费用；收账越努力,花费的收账费用越多,收回账款的可能性越大；但是收账费用花费到一定的程度,继续增加收账费用可能会得不偿失。（一）应收账款收账政策决策的基本原理需要对花费多少收账费用去收回账款做出决策。机会成本=销售额/360×平均收款期×变动成本率×机会成本率坏账损失=销售额×坏账损失率总成本=机会成本+坏账损失+管理成本（二）与应收账款收账政策有关的计算公式：

【例3】某公司目前的收账政策和备选的收账政策的有关资料如图的【已知条件】区域所示。要求建立一个对该公司选择收账政策方案做出决策的模型。建立模型的具体步骤如下所述。收账政策方案目前方案备选方案年收账费用（万元）1020平均收款天数6030坏账损失率3%2%年赊销额（万元）420480变动成本率60%60%资金成本率15%15%一年计算天数360360计算与决策结果（万元）收账政策方案目前方案备选方案应收账款机会成本6.303.60坏账损失12.609.60年收账费用10.0020.00总成本28.9033.20决策结论目前方案应收账款管理模型应收账款信用标准决策模型应收账款信用条件决策模型应收账款收账政策决策模型课堂小结对利润的影响机会成本坏账成本管理费用对利润的影响机会成本（与原销售额合并计算）现金折扣成本（与原销售额合并计算）坏账成本收账费用机会成本坏账成本收账费用计算公式对利润的影响=销售额×销售利润率机会成本=销售额/360×平均收款期×变动成本率×机会成本率机会成本=[原销售额/360×（新收账期-原收账期）+新销量/360×新收账期]×

变动成本率×机会成本率现金折扣=（原销售额+新销售额）×现金折扣销售百分比×现金折扣率坏账损失=销售额×坏账损失率课上练习1实训资料1：某公司两个备选的信用条件方案的有关资料如图的【已知条件】区域所示。要求建立一个对该公司选择信用条件方案做出决策的模型。现有A和B两个方案，有关数据如表所示。实训要求：通过计算分析回答，应选择哪个方案？已知条件基本数据备选方案方案A方案B销售单价（元/件）120信用期限（天）3045单位变动成本（元/件）72预计销售量（件）6000070000应收账款的机会成本12%预计坏账损失率2%3%一年计算天数360预计收账费用30005000计算与决策结果（元）备选方案方案A方案B销售利润28800003360000机会成本43,200.0075,600.00坏账损失144000252000收账费用30005000净收益2,689,800.003,027,400.00决策结论：方案B课上练习2某公司3个备选的信用条件方案的有关资料如图的【已知条件】区域所示；方案A信用条件(3/10,N/30)，方案B信用条件(2/30,N/60)，方案C信用条件(1/60,N/90)。要求建立一个对该公司选择信用条件方案做出决策的模型。建立模型的具体步骤如下所述。已知条件备选方案方案A方案B方案C信用期限（天）306090第1挡折扣期限（天）103030第1挡现金折扣率3%2%2%取得第1挡现金折扣销售比例80%70%45%第2挡折扣期限（天）0060第2挡现金折扣率001%取得第2挡现金折扣销售比例0035%年赊销额（万元）100012001500应收账款平均收款天数406892坏账损失率2%3%5%年收账费用（万元）80100150销售利润率

变动成本率65%机会成本率20%一年计算天数360计算与决策结果（万元）备选方案方案A方案B方案C销售利润200240300现金折扣成本24.0016.8018.75应收账款机会成本14.4429.4749.83坏账损失203675收账费用80.00100.00150.00净收益61.5657.736.42最优方案方案A课上练习3

实训资料2：某公司是一家商业企业，由于目前的信用政策过于严厉，不利于扩大销售，且收帐费用较高，故需要进行信用政策调整。调整前的销售额为240000元，变动成本率为80%，应收帐款的机会成本率为15%，利润以及销售利润率为12000元和24%，坏帐损失率为10%。现有甲和乙两个放宽信用政策方案，数据如表所示。原来的信用标准的有关数据采用备选信用标准方案的有关数据预计坏账损失率标准10%备选方案甲方案乙方案销售额（元）240000信用条件n/902/60，n/180变动成本率80%年赊销额增加（元）3080026700销售利润率24%现金折扣占销售额的比例2%3%应收账款的机会成本15%增加销售额的平均收款期（天）9060平均收账期（天）50增加销售额的平均坏账损失率2.5%3%一年计算期天数360现金折扣率02%计算与决策结果（元）信用标准变化的影响甲方案乙方案对销售利润的影响7,392.006,408.00现金折扣成本0.00160.02对机会成本的影响4,124.001,334.00对坏账成本的影响770.00801.00对净收益的综合影响2,498.004,112.98决策结论：乙方案02

最佳现金持有量五、最佳现金持有量模型设计

现金是企业流动性最强的资产,持有一定量的现金可以降低企业的偿债风险。但现金的营利性很低,持有过多的现金也会使企业遭受损失。因此,企业应在对风险和收益权衡考虑的基础上,采用适当的方法确定最佳现金持有量。

确定最佳现金余额的方法有多种,下面介绍其中比较常用的两种方法:

成本分析模式和存货模式。

成本分析模式是通过分析持有现金的成本，寻找持有成本最低的现金持有量。企业现金成本=机会成本+管理成本+短缺成本

（一）成本分析模式现金成本机会成本现金持有量过多，机会成本大幅度上升（正比例关系）管理成本一种固定成本，与现金持有量之间无明显的比例关系短缺成本随现金持有量的增加而下降，随现金持有量的减少而上升（反比例关系）【例5】某企业现有A、B、C、D四种现金持有方案，有关成本资料如表所示。

现金持有方案成本资料项目ABCD平均现金持有量（元）100000200000300000400000机会成本率10%10%10%10%短缺成本（元）4800025000100006000管理成本（元）40000400004000040000

根据表资料，利用EXCEL工具，运用成本分析法编制该企业的最佳现金持有量测算表。操作步骤如下。计算与决策结果（元）备选方案A方案B方案C方案D方案现金余额（元）100000200000300000400000短缺成本（元）4800025000100006000管理成本（元）40000400004000040000机会成本（元）10000200003000040000总成本（元）98000850008000086000最优方案C方案最佳现金余额（元）300000（二）

鲍曼模型（存货模式）

鲍曼模型又称存货模型，它由美国经济学家威廉·鲍曼提出的。他认为公司现金持有量在许多方面与存货相似，存货经济订货批量模型可用于确定目标现金持有量,并以此为出发点，创立了鲍曼模型。现金成本=转换成本+机会成本：TC=(T/Q)*F+(Q/2)*K=（T/Q）*固定成本+（Q/2）*机会成本式中Q／2——现金平均持有量；

T／Q——为一个周期内的现金转换次数；F为有价证券固定成本；K为利息率(机会成本)；Q为最佳现金持有量；T为现金总需求量；最佳现金持有量就是货币资产余额总成本为最低点的资产余额。现金总成本：T为现金总需求量；F为有价证券固定成本；Q为最佳现金持有量；K为利息率(机会成本)；=√2*T*固定成本/机会成本=√2*T*固定成本*机会成本四、EXCEL表规划求解1.“规划求解”是一组命令。2.“规划求解”改变单元格中的值

计算结果。3.单元格数值有约束条件

。查看打开EXCEL表-数据-规划求解（设置规划求解选项卡：文件--选项--加载项--管理--转到-规划求解加载项-确定）【例6】某企业现金收支状况比较稳定，预计全年（按360天）需要现金400万元，现金与有价证券的转换成本为每次400元，有价证券的年利率为8%，则企业的最佳现金持有量为多少？已知条件全年现金需求量（元）4000000有价证券转换成本（元/次）400有价证券年利率8%最佳现金余额确定方法存货模式（鲍曼模型）SQRT()求平方根函数必须为正数计算与决策结果（元）计算方法利用公式计算规划求解结果最佳现金余额200000200000最低持有现金相关总成本1600016000有价证券交易次数2020有价证券交易间隔天1818最佳现金持有量模型设计1、成本分析模式

现金成本=机会成本+管理成本+短缺成本2、鲍曼模型（存货模型）现金成本=转换成本+机会成本：课堂小结TC=(T/Q)*F+(Q/2)*K=（T/Q）*固定成本+（Q/2）*机会成本式中Q／2——现金平均持有量；

T／Q——为一个周期内的现金转换次数；F为有价证券固定成本；K为利息率(机会成本)；Q为最佳现金持有量；T为现金总需求量；=√2*T*固定成本/机会成本=√2*T*固定成本*机会成本实训资料4：甲、乙两家公司持有现金余额的有关资料如图的【已知条件】区域所示。要求建立一个确定两家公司最佳现金余额的模型（甲公司使用成本分析法，乙公司使用存货模式）。甲公司备选方案A方案B方案C方案D方案E方案现金余额（元）20000400006000080000100000短缺成本（元）120008000400020000机会成本12%最佳现金余额确定方法成本分析模式已知条件乙公司全年现金需求量（元）450000有价证券转换成本（元/次）900有价证券年利率10%最佳现金余额确定方法存货模式（鲍曼模型）甲公司备选方案A方案B方案C方案D方案E方案现金余额（元）20000400006000080000100000短缺成本（元）120008000400020000机会成本240048007200960012000总成本1440012800112001160012000最优方案

C方案

最佳现金余额（元）

60000乙公司计算方法规划求解结果利用公式计算最佳现金余额9000090000最低持有现金相关总成本90009000有价证券交易次数5有价证券交易间隔天7203

经济订货量六、订货批量决策模型设计

一定量的存货是企业正常生产所必须的。存货在流动资产中占据很大的比重，一般占30%~40%。存货资金占用越低，存货资金的周转就越快。

经济订货量，是指订购费用和保管费用的合计数为最低的订购批量

经济订货量的基本模型是一种理想的市场状况，其基本假设为：（1）企业能及时补充存货，即需要订货时即可立即取得存货；（2）能够集中到货，而不是陆续到货；（3）不允许缺货，即无缺货成本；（4）存货单价不因订货批量的变化而变化。（一）经济订货批量的集中到货模型TC=(T/Q)*F+(Q/2)*K=（T/Q）*订货成本+（Q/2）*单位储存成本式中

T／Q——为一个周期内的存货转换次数；

Q／2——存货平均持有量；F为订货成本；K为单位储存成本；Q为经济批量；T为存货总需求量；经济批量的相关成本=进货成本+储存成本，=√2*T*订货成本*单位储存成本=√2*T*订货成本/单位储存成本【例7】某企业每年耗用甲材料1600吨，该材料的单价为1000元，单位储存成本为800元，一次订货成本为3600元，则经济订货量是多少？Q==120吨已知条件某企业年消耗甲材料（吨）1600材料单价（元）1000单位储存成本800一次订货成本3600计算与决策结果（元）计算方法利用公式计算规划求解结果最佳经济订货量120120经济批量相关成本9600096000最佳订货次数1313最佳订货周期2727经济订货量占用资金6000060000如果考虑到存货不能一次到达，存货可能陆续入库，库存货物陆续增加

（二）经济订货批量的陆续到货模型计算公式：TC=储存成本+订货成本=T/Q*F+(Q-Q/P*D)/2*KT为总需求量；Q为经济订货量；F为订货成本；P为每日送货量；D为每日消耗量；Q/P为每批存货全部送达所需日数；Q/P*D为送货期内全部耗用量。通过推倒可以得出：（1）经济订货批量Q（2）每年订货次数N=T/Q（3）订货周期T=1年/N（4）经济订货量占用资金I=Q/2×单价。

如果供应商实行数量折扣，那么，除了订货成本和储存成本之外，采购成本也成了决策中的相关成本。三种成本的年成本合计最低的方案，才是最优方案。计算公式：总成本TC=订货成本+储存成本+采购成本=T/Q×F+(Q-Q/P×D)/2×K+T×U×(1-Di)式中，T为总需求量；Q为经济订货量；F为订货成本；P为每日送货量；D为每日消耗量；Q/P为每批存货全部送达所需日数；Q/P*D为送货期内全部耗用量；U为采购单价；Di为数量折扣；F为订货成本；K为单位储存成本。【例8】某企业有四种货物要采购，供应商规定了数量折扣，其数量折扣条件为：甲材料>400kg，乙材料>450kg，丙材料>500kg，丁材料>500kg；相关各项目数据见图所示。存货名称甲材料乙材料丙材料丁材料材料年需要量T18000200003000025000一次订货成本K25252525单位储存成本C2343每日送货量P100200300250每日耗用量d20304025数量折扣di2%2%2%2%单价U10203025规划求解存货名称甲材料乙材料丙材料丁材料最优订货批量Q*750626658680采购成本176,400392,000882,000612,500储存成本6007981,140919订货成本

600

7981,140919总成本177,600393,597884,280614,337综合成本2,069,814最佳订货次数24.031.945.636.7最佳订货周期(月)0.50.40.30.3经济订货量占用资金3675613796708335鲍曼模型（存货模型）现金成本=转换成本+持有机会成本：

经济批量的相关成本=进货成本+储存成本，课堂小结现金TC=(T/Q)*F+(Q/2)*K=（T/Q）*固定成本+（Q/2）*机会成本批量TC=(T/Q)*F+(Q/2)*K=（T/Q）*订货成本+（Q/2）*单位储存成本F订货成本（固定成本）；K单位储存成本（机会成本）；Q经济批量（现金持有量）；T经济批量总成本（现金总需求量）；最优经济订货量1.集中到货课堂小结Q=2.经济订货批量的陆续到货模型TC=订货成本+储存成本+采购成本=T/Q×F+(Q-Q/P×D)/2×K+T×U×(1-Di)计算公式：

T/Q×F+(Q-Q/P×D)/2×K+T×U×(1-Di)

T/Q*F为订货成本；(Q-Q/P*D)/2*K为储存成本；P为每日送货量；

Q/P为每批存货全部送达所需日数；D为每日消耗量；Q/P*D为送货期内全部耗用量。（1）经济订货批量Q（2）每年订货次数N=T/Q（3）订货周期T=1年/N（4）经济订货量占用资金I=Q/2单价。√2