《excel会计与的财务管理——理论、方案暨模型第章excel在盈亏平衡分和经济订货量中应用》由会员分享，可在线阅读，更多相关《excel会计与的财务管理——理论、方案暨模型第章excel在盈亏平衡分和经济订货量中应用（41页珍藏版）》请在金锄头文库上搜索。
1、第8章 Excel在盈亏平衡分析和经济订货量中应用,本章主要内容： 8.1 Excel盈亏平衡分析模型 8.2 Excel经济订货量模型,本章目标：,盈亏平衡分析是财务管理中的基础性分析方法，它通过成本、销售量和利润三者关系的分析，找出三者之间联系的规律，从而有效地帮助企业管理者制定经营决策。经济订货量是目前大多数企业最常采用的货物订购方式。当企业按照经济订货量来订货时，可以确定企业一次订货（外购或自制）的数量，实现订货成本和储存成本之和最小化。本章主要介绍用Excel建立盈亏平衡分析模型及经济订货量模型的方法及其应用。,盈亏平衡分析（breakeven analysis）也称本量利分析或保本
2、分析，是根据产品的业务量（产量或销售量）、成本、利润之间的相互制约关系的综合分析来掌握盈亏变化的的规律，指导企业选择能够以最小的成本生产和销售出最多的产品，并使企业获得最大利润的经营方案。,8.1.1盈亏平衡分析的基本模式,1盈亏平衡分析的意义 盈亏平衡分析通过分析产品成本、销售量和销售利润这三个变量之间的关系，找出三者之间联系的规律，从而有效地制定经营决策，预测利润，控制成本，判断企业经营状况。 盈亏平衡分析主要用于确定公司达到盈亏平衡时的销售水平，即分析销售量高于或低于盈亏平衡点时的盈利和亏损状况，帮助企业进行决策。例如公司在做新产品经营决策时，可以通过盈亏平衡分析计算达到盈利所需要完成的
3、销售量。,8.1.1盈亏平衡分析的基本模式,2盈亏平衡分析的前提假设 进行盈亏平衡分析，是建立在一系列前提假设基础之上的，这些前提假设包括： （1）假设销售总收入函数和销售总成本函数均为线性函数； （2）假设单价、总固定成本、单位变动成本能准确地确定，且在相关范围内保持不变； （3）假设生产量与销售量相等，即产销平衡； （4）在多种产品条件下，假设已知销售组合，即品种结构不变； （5）目标利润一般不考虑投资收益和营业外收支。,8.1.1盈亏平衡分析的基本模式,3盈亏平衡分析的基本模式 营业利润=销售收入-销售成本 =销售收入-（固定成本十变动成本） =单价销售量-单位变动成本销售量-固定成本
4、=（单价-单位变动成本）销售量-固定成本,8.1.1盈亏平衡分析的基本模式,（1）图解法。在坐标轴中画销售收入线、总成本线，交叉点为保本销售量。 （2）使用Excel所提供的单变量求解工具。 （3）使用Excel所提供的规划求解分析工具。 （4）使用Excel所提供的模拟运算表工具生成销售量-利润（或销售收入-利润）对照表，利用查表加内插值的方法。,8.1.2 Excel中盈亏平衡分析求解方法和步骤,无论采用什么方法进行盈亏平衡分析，在Excel中建立盈亏平衡模型的步骤如下： （1）在Excel工作表中建立盈亏平衡分析模型的框架，并在相应的单元格中输入产品单价、单位变动成本、固定成本等参数的值
5、； （2）在给定销售量下，利用公式计算成本、销售收入和利润等的值； （3）在Excel单元格中计算产品的盈亏平衡点，进而确定作为当前销售量或计划销售量与盈亏平衡点销售量之差的“安全边际”。 （4）进一步探讨问题中的各种经营管理参数（如单价）的变化对盈亏平衡点的影响。可在模型中添加一个对参数（如单价）进行调节的“滚动条”控件，通过对滚动条的调节，观察参数的变化对盈亏平衡点的影响； （5）根据预定的目标利润值确定实现该利润值所应达到的产品销售量。,8.1.2 Excel中盈亏平衡分析求解方法和步骤,【例8-1】假设东方股份有限公司计划投资生产一种新产品M，其单位变动生产成本和销售费用共计120元，
6、全年全部固定成本为900,000元，估计销售价格为200元。假设所得税税率为25%，东方股份有限公司如果要获得税后利润为220,000元，那么销售量和销售额各为多少？如果M的销售价格可能在200到220之间变动（变动步长为2），要获得同样数额的税后利润，在不同的价格条件下，其保本点和保利点的销售量和销售额又各为多少？,8.1.3盈亏平衡分析模型举例,（1）保本分析 设保本销售量为x，在销售价格为200元时，保本销售量为： (200-120)x=900,000 x=11,250（件） 保本销售额为11,250200=2,250,000元 即当销售量达到11,250件、销售额达到2,250,000
7、元时，该企业才不亏本。 （2）保利分析 税后利润=税前利润(1所得税率) =(销售收入变动成本固定成本)(1所得税率) 220,000=(200x120x900,000)(125%) x=14,917（件） 保利销售额为14,917200=2,983,400元,8.1.3盈亏平衡分析模型举例,2盈亏平衡分析模型的建立,8.1.3盈亏平衡分析模型举例,3单因素变动盈亏平衡分析表和分析图的建立 （1）直接用公式计算的单因素变动平衡分析表模型,8.1.3盈亏平衡分析模型举例,（2）运用单变量模拟运算表来设计单因素变动盈亏平衡分析表模型,8.1.3盈亏平衡分析模型举例,（3）运用滚动条设计单因素变动平
8、衡分析表模型,8.1.3盈亏平衡分析模型举例,（4）单因素变动盈亏平衡分析图的建立,8.1.3盈亏平衡分析模型举例,4多因素变动盈亏平衡分析表和分析图的建立 （1）多因素变动盈亏平衡分析表的建立,8.1.3盈亏平衡分析模型举例,4多因素变动盈亏平衡分析表和分析图的建立 （2）多因素变动盈亏平衡分析图的建立,8.1.3盈亏平衡分析模型举例,8.2 Excel经济订货量模型,8.2.1库存成本 1库存成本的内容 （1）库存采购成本 （2）库存订货成本 （3）库存储存成本 2.库存的有效控制 对库存量影响的关键因素是订货数量，每次订货数量过多，会使库存上升，增加储存成本；而订货数量过少，又会使库存不
9、足，可能造成生产销售的中断，同时也会使订货次数增加导致订货成本上升，还会因临时采购增加相关成本费用。,8.2.2经济订货量,经济订货量(economic order quantity,EOQ)是采用数学方法计算出库存的每次订货量，这一订货量能够使在一定时期内，某一品种存货的库存总成本达到最低。,8.2.2经济订货量,1经济订货量的前提条件 （1）存货的年需要量在分析期内稳定不变； （2）从订货到货物到达所间隔的时间固定不变，而且每批货物一次全额到达； （3）存货的购买价格在分析期内稳定； （4）存货的耗用或者销售比较均衡； （5）每次订货成本、单位商品的储存成本在分析期内不变； （6）仓储条件
10、及所需现金不受限制； （7）所需存货市场供应充足，不会因买不到所需存货而影响其他方面； （8）没有缺货情况。,8.2.2经济订货量,1经济订货量的计算公式 设：T：年成本合计，即年订货成本和年存储成本之和； R：某存货的年需求量； C：单位存货的采购成本； S：一次采购费用； K：变动存储费用率； Q：一次采购量，则：,8.2.2经济订货量,8.2.2经济订货量,（2）存货陆续供应和耗用情况下的经济订货量计算公式,如果考虑存货不能一次到达、各批存货可能陆续入库，在这种情况下，其基本公式又是什么样子的呢？ 假设P表示每日到货量，d表示每日耗用量，则Q/P为每批存货全部送达所需要的天数，Q/p*d
11、为送货期内已耗用量。 由于存货在送达的同时也在耗用，所以每批存货送完时的库存量为Q-Q/P*d，平均库存量为Q*（1-d/p）/2，从而得出总成本为：,8.2.2经济订货量,（3）允许缺货条件下的经济订货量计算公式 在允许缺货的条件下，假设a表示单位缺货损失费用，则经济订购量Q和总成本T的计算公式为：,8.2.2经济订货量,2经济订货量模型举例,（1）基本的经济订货量模型举例 【例8-2】某公司全年采购某种原材料36 000公斤，一次采购成本为6 000元，存储费用率为10%，单位材料采购成本为30元，则采购批量和总成本计算如下：,8.2.2经济订货量,2经济订货量模型举例,（1）基本的经济订
12、货量模型举例,8.2.2经济订货量,2经济订货量模型举例,（1）基本的经济订货量模型举例,当其他因素都有变化时，可用盈亏平衡分析中的多变量分析模型设计滚动条进行控制选择。,8.2.2经济订货量,2经济订货量模型举例,（2）存货陆续供应和耗用情况下的经济订货量模型举例 在【例8-2】中，假设企业所需的原材料是陆续供应和耗用的，送货期内每日平均送货量为1000公斤，每日平均耗用量为100公斤，则可建立存货陆续供应和耗用情况下的经济订货量模型，如图8-32所示。在此模型中，E10单元格中的公式为“=SQRT(2*E4*E6/(1-E9/E8)*E5*E7)”，E11单元格中的公式为“=SQRT(2*
13、E4*E6*E5*E7*(1-E9/E8)”，E12单元格中的公式为“=(E11-34152.6)/34152.6”（34152.6为存货陆续供应和耗用情况下各个因素不发生变动时的总成本）。,8.2.2经济订货量,2经济订货量模型举例,8.2.2经济订货量,2经济订货量模型举例,（3）允许缺货条件下的经济订货量模型举例,8.2.2经济订货量,2经济订货量模型举例,（3）允许缺货条件下的经济订货量模型举例,在单元格E11中输入经济订购批量计算公式“=IF(B9=1,SQRT(2*E4*E6/(E5*E8),SQRT(2*E4*E6/(E5*E8)*(E7+E5*E8)/E7)”， 在单元格E12
14、中输入总成本计算公式“=IF(B9=1,SQRT(2*E4*E6*E5*E8),SQRT(2*E4*E6*E5*E8*(E7+E5*E8)/E7)”， 在单元格E13中输入总成本变化率计算公式“=IF(B9=1,(E12-36000)/36000,(E12-41026)/41046)”（41046为允许缺货条件下各个因素不发生变动时的总成本）。,8.2.2经济订货量,2经济订货量模型举例,（4）基本经济订货量图形制作举例,图8-36 修改后的基本经济订货量模型,8.2.2经济订货量,8.2.2经济订货量,图8-38 经济订货量图形,8.2.2经济订货量,8.2.2经济订货量,上机实验,1假设有
15、一家生产家电设备的大型企业，其中该公司的甲设备尤为畅销，1998年销售量为100000件，单位售价为750元/件，单位变动成本为300元/件，固定成本为25000000元。现假设单位变动成本在270元/件至330元/件间（增减幅度为10%）变动，其他因素不变。要求：请用滚动条设计单位变动成本变动时相对应的盈亏分界点，列出滚动条的设计步骤，并指出哪几个单元格需要设置公式，并写出相应的公式。,上机实验,2某企业生产需要耗用甲、乙、丙、丁四种材料，相关的数据下表所示：,要求： （1）建立模型，计算甲、乙、丙、丁四种材料的最优订货批量。 （2）确定最优订货次数（R/Q）和最佳订货周期(1年*Q/R)。
16、,上机实验,3某公司制造产品时需要某种零件，此零件如果自制，单位变动成本为4.8元，固定成本共计30000元；如果向外采购，单件买价为7元，但可以避免固定成本20000元，而且当采购批量达到18000件时单件买价可以降低到4元。本工作表中现已建立了一个针对自制与外购的总成本比较模型。 要求： （1）若当前需求量为22000时，计算出每种方案下的总成本。 （2）添加一个微调器，使得需求量从2000按步长1000变化到35000时，反映决策结论：“自制”、“外购”或“皆可”。 （3）计算出相对盈亏平衡量，并计算出相对盈亏平衡量时的总成本。（说明：相对盈亏平衡量要随着单位变动成本的变化而变化）。,上机实验,4某公司生产甲、乙两种产品，两种产品需求量为8000件。甲、乙两种产品固定成本分别等于25000元和42000元，单位边际贡献分别等于6元/件和12元/件。现假定甲产品产量占总生产量的比例为53%，试在本工作表中建立一个模型以实现以下要求: （1）计算出每种生产方案下的利润。 （2）计算出两种产品各自
