浅谈EXCEL在本量利分析模型中的应用

ｌｔ＇Ｅ￣－ＨＮｏＬｏＧ　浅谈ＥＸＣ　Ｅ　Ｌ在本量利分析模型中的应用　叶翊婧　重庆工商大学会计学院重庆４０００６７　摘要：本量利分析法是研究成本、业务量和利润三者之间关系的一种重要方法。以往，我们运用手工计算来进行本　量利分析，其计算过程复杂而繁琐，给财务人员带来许多困难，也阻碍了本量利分析模型的广泛应用。现在，利用具有强　大数据处理能力的ＥＸＣＥＬ软件，设计本量利分析模型，可以非常方便地进行盈亏临界分析、因素变动分析以及敏感分　析。　关键词：本量利分析法盈亏临界点分析　因素变动分析敏感分析　本量利分析法是在成本性态分析和变动成本法的基础上，　运用数量化模型揭示企业的变动成本、固定成本、相关业务量　以及销售单价同利润在数量上的相互影响、相互制约关系的一一　种定量分析方法。它可以帮助企业财务管理人员顺利地找到　本量利分析不但可以用于预测盈亏临界点，更重要的是还　能用来预测利润，规划最优的利润目标，这就是影响利润的因　素变动分析。因素变动分析的主要方法就是将变化了的因素　代入“利润＝（销售单价一单位变动成本）×销售量一固定成本　总额”这个关系式，用来测定其对利润的影响。因数变动分析分　为　数变动分析和多因数变动分析，单因素变动对利润结果　降低成本、增加利润的途径，帮助财务管理人员进行利润规划　和管理，为实现利润最大化寻求各种途径。　本量利的基本关系式是：　利润＝销售收入一总成本　＝的影响，即为实现目标利润所采取的单一措施；多个因素组合　变动对利润结果的影响，即为实现目标利润所采取的综合措　施。　销售收入一变动成本一固定成本　边际贡献一固定成本　（售价一单位变动成本）×销售量一围定成本　＝３．敏感分析　企业的经营管理者希　事先知道在影响利润的诸多因素　＝＝单位边际贡献×销售收入一固定成本　边际贡献×销售收入一固定成本　中，哪一个【大】素影响大，哪一个【夭Ｉ素影响小，影响程度如何“以　便在情况发生变化后及时采取对策，调整企业计划，使生产经　营活动被控制在最有利的状态下。本量利的敏感分析，就是研　究各个冈素变动对利润变化的影响程度。有的【大Ｊ素发生微小变　ｍ－在这个方程式中有５个相互联系的变量，给定其『｛１　４个，　便可以求出另１个变量的值。　利用这个关系式可以进行盈亏临界点分析、单因素变动对　目标利润影响分析、多因素变动对日标利润影响分析以及各个　因索变化对利润变化影响的敏感程度分析。　１．盈亏临界点分析　化，就会使利润发生很大的变化，这类参数称为敏感因素，反　之，称为小敏感冈素。具体掌握各有关因素对利润的敏感程度，　有助于增强我们在经营管理工作中的预见性。反映敏感程度定　额指标是敏感系数，其计算公式为：　盈亏临界点亦称保本点，是指企业处于不盈利也不亏损状　态时的业务水平。盈弓临界点分析是本量利分析的基础，企业　在规划目标利润、控制利润完成情况、估计经营风险时都需要　它。　敏感系数＝目标值变动百分比／参数值变动百分比　其中：敏感系数为正值的，表明它与利润为同向增减；敏感　系数为负值的，表明它卜ｊ利润为反向增减。　４、本量利分析模型的设计与应用　盈亏临界点的基本计算模型：　销售单价×盈亏临界点销售量一单位变动成本×盈亏临　界点销售量一固定成本总额＝０　公式演变后为：　盈亏临界点销售量＝固定成本总额／（销售单价一单位变　动成本）　在现实经济工作中，复杂的经济环境对利润的影响往往是　多因素的，各因素又是相瓦关联的，要实现目标利润，大多需要　采取综合措施。这就要进行综合调　、反复计算，而这些依赖人　工计算就非常困难和烦琐了，这就严重影响了本量利分析应用　的广泛深入。而在计算机环境中，手工计算的这一困难得到了　彻底的解决，通过ＥＸＣＥＬ的公式设计并利用其数据强大的链　接能力，建立具有动态计算能力的本量利分析模型，只需用鼠　盈亏临界点销售额＝盈５－１ｔ￣界点业务量×销售单价　２．因素变动分析　标　击微调按扭来调整各个因素的值，就可以非常方便、灵活　２ｏ０６／１１　囝　维普资讯 http://www.cqvip.com

ＡＮｏ　ＭＡＩＩ＇，ＫＥＴ　地进行各种单因素和多因素的分析。根据本量利分析的基本数　３１按照上述方法，在Ｃ１２、Ｃ１３和Ｃ１４单元中画出大小合　学模型，结合盈亏临界点分析、因素变动分析以及敏感分析的　适的微调按钮，分别设置控件格式的控制参数，单元链接分别　基本原理。下面就以一个实例来说明如何利用ＥＸＣＥＬ设计和　指定为Ｃ１２、Ｃ１３和Ｃ１４。以上最大值和最小值的设定应考虑　应用本量利分析模型。　单价、单位变动成本、固定成本的最小允许值　小可能值）和最　盈亏临界点分析　大允许值（最大可能值），并结合Ｄ１１、Ｄ１２、Ｄ１３和Ｄ１４单元格的　公式对Ｄ１　１、Ｄ１２、Ｄ１３和Ｄ１４单元格中数据所进行的转换。　辅鲁簟价《元／件）　１０　举量乖ｌ的数学关系式：　簟位变动成本（元／件）　６　利澜　（铺鲁篡价一簟位变站成本）×铺●量一固定成举　４．４本量利分析模型的应用　固定成本ｃ元）　３０Ｄ０　々刺滑等千０。此时的铺鲁量为盈亏临界意｛肖鲁量，　盈亏临界铺鲁置（件）　７５ｏ　盈亏临界点｝肖鲁量　固定成奉／（辅■簟价一簟位变动硪奉）　１１盈亏临界分析　盈亏临界铺鲁颧（元）　７５００　丑　瞄界点销鲁颧＝盈亏临界点々削Ｉ量ｘ销●簟价　在本量利分析模型中，随着Ｂ６、Ｂ７单元格公式的完成，只　刺洒（元）　０　要给定销售单价（Ｂ３）、单位变动成本（Ｂ４）和固定成本（Ｂ５），相应　影响利润的各因素变动分析　的盈亏临界点销售量和销售额就会自动出现结果，而且盈亏临　界销售量和销售额会随着销售单价、单位变动成本和固定成本　因綦　基本　１ｌ　变动按镪　变动百　分比　变动后　数１ｌ　敏藤　系数　的变化而发生实时动态变化。　辅鲁簟价ｃ元／件）　１０　…　１０．５　４２　５０　如：图例中，假定销售单价为ｌ０元／件，单位变动成本为６　蕈位瑟动戚车ｃ元，件，　６　骥　Ｉ　瓣　一＆３洲　Ｓ．５　—２５．６０　硝鲁量（件）　ｌ　ｒｌＮｆｌ≯　ｌ　￥　黼　ｇ糍斓　１２．５０＊　Ｉｌ２５　ｌ７．００　元／件，固定成本为３０００元，则根据公式自动出现相应的盈亏　固定成本ｃ元）　３０００　～＃　一　。　Ｉ６．７ＯＩ　２５Ｄ０　Ｉ２．７２　临界点销售量７５０件、盈亏临界点销售额７５００元。　臣标科澜ｃ元）　１０００　２Ｉ２．５０　３Ｉ２５　２）因素变动分析　ｆ本量利分析模型设计界面）　在本量利分析模型中，用鼠标单击某一个或多个因素的变　４．Ｉ设计表样　动按钮，可以观察到某一个因素或多个因素变动的百分比　按照“本量利分析模型”图示，定义合适的行高、列宽，合并　（Ｄ１　１单元格、Ｄ１２单元格、Ｄ１３单元格、Ｄ１４单元格）及其变动　相应单元，并在有关单元中录入相应文字，在Ｂ３、Ｂ４、Ｂ５、ＢＩＩ、　后数值（Ｅｌ１单元格、Ｄ１２单元格、Ｄ１３单元格、Ｄ１４单元格）以　Ｂ１２、Ｂ１３，Ｂ１４等原始数据单元格录入相应数据。　及所预测的相应的目标利润（Ｅｌ５单元格）的变化结果。　４．２定义单元公式，建立分析的数字关系式　３）敏感分析　在定义公式前，需将录入状态调整为英文（半角）状态。其　在本量利分析模型中，用鼠标单击各变动因素的变动按　中：　钮，使销售单价、单位变动成本、销售量和固定成本等因素中的　Ｂ６＝Ｂ５／（Ｂ３－Ｂ４），Ｂ７＝Ｂ６　Ｂ３，ＢＳ：（Ｂ３－Ｂ４）　Ｂ６－Ｂ５，　一项因素发生变化，其他因素不变，即可显示出变化因素对利　Ｄ１　Ｉ＝Ｃ１１／１００－１００％，Ｄ１２＝Ｃ１２／１００—１００％，　润影响的敏感系数。　Ｄ１３＝Ｃ１３／１００－１００％．Ｄ１４＝Ｃ１４／１００—１００％　如：图例中，销售单价增加０．５元，而单位变动成本、销售　注意：Ｃｌｌ，Ｃ１２，Ｃ１３，Ｃ１４这四个单元格的数据是通过微调　量和固定成本不变，显示出销售单价对利润影响的敏感系数为　控制按钮调整出来的，Ｄ１１，Ｄ１２，Ｄ１３，Ｄ１４这四个单元格中的百　４２．５０。　分比数据，则是根据ｃ１１，Ｃ１２，Ｃ１３，Ｃ１４四个单元格中的数据进　参考文献：　行相应转换得到，数据之间存在动态链接关系。　【１】中国注册会计师协会．财务成本管理【Ｍ】．经济科学出版社，　Ｅ１Ｉ＝Ｂ１１　（Ｉ＋Ｃ１１），Ｅ１２＝Ｂ１２　（１＋Ｃ１２），Ｅ１３＝Ｂ１３　（１＋Ｃ１３），　２０ｏ６．　Ｅ１４＝Ｂ１４　（Ｉ＋Ｃ１　４１　【２】樊静．ＥＸＣＥＬ与财务管理【Ｍ】　等教育出版社．　Ｅ１５＝（Ｅ１１－Ｅｌ２）　Ｅ１３－Ｅ１４，Ｄ１５＝ｆＥ１５－Ｂ１５）／Ｂ１５　【３】张瑞君，计算机财务管理【Ｍ】．中国人民大学出版社，２００５．　Ｆ１　ｌ＝Ｄ１５／Ｄ１　１，Ｆ１２＝Ｄ１５／Ｄ１２，Ｆ１３＝Ｄ１５／Ｄ１３，Ｆ１４＝Ｄ１５／Ｄ１４　单元格式定义：将Ｂ３：Ｂ８区域、Ｂ１　ｌ－Ｂ１５区域和Ｅ１　１：Ｅｌ５　区域设定为数值格式，保留２位小数；将Ｄ１ｌ：Ｄ１５区域设定为　百分比格式，小数位数为２位；将Ｆ１ｌ：Ｆ１４区域设定为数值格　式，小数位数为２位。　４．３设计微制按钮，实现因素变动的灵活调节及如　意组合　１）如果窗体工具按钮没有出现在工具栏上，可依次通过　【视图】菜单、【工具栏】菜单和【窗体】菜单调出。　２）单击窗体工具按钮中的【微调项】按钮，在Ｃ１１单元中　画出大小合适的微调按钮，设置控件格式的控制参数为：最小　值设为０，最大值设为１００，步长设为０．５，单元链接到Ｃ１１。