构建Ｅｘｃｅｌ进行本量利分析

构建Ｅｘｃｅｌ模板进展本量利分析【摘要】本量利分析在企业预测、决策、规划和控制中得到了广泛的应用,计算工作量相当大。本文利用exel提供的函数、引用、模拟运算表、图表制作等强大功能,通过分析模板的设计,自动得到本量利分析结果,进步了本量利分析的效率、数据计算的准确性和图表绘制的标准性。【关键词】本量利分析;exel;分析模板设计;模拟运算表本量利分析是本钱、业务量和利润三者依存关系分析的简称,它是在本钱习性分析的根底上,运用数学模型和图型,对本钱、利润、业务量与单价等因素之间的依存关系进展详细的分析,研究其变动的规律性。它在企业预测、决策、规划和控制中得到广泛的应用。本量利分析的理论和方法并不复杂,但计算工作量相当大。应用exel进展本量利分析将会使计算工作大大简化。本文拟对这一问题进展讨论。一、本量利分析的主要内容本量利分析的损益方程式为:利润=单价×销量-单位变动本钱×销量-固定本钱=p×q-b×q-f分析的主要内容包括:(一)预期利润分析在规划期间利润时,根据单价、单位变动本钱和固定本钱、销量关系,利用损益方程式计算出预期利润。(二)盈亏临界分析与平安边际分析盈亏临界分析主要研究利润为零的特殊经营状态的有关问题,是本量利分析的一项根本内容,亦称损益平衡分析或保本分析。它主要研究如何确定盈亏临界点、有关因素变动对盈亏临界点的影响等问题,主要计算盈亏临界点销售量、盈亏临界点销售额、盈亏临界点作业率。根据盈亏临界分析结果进一步进展平安边际率分析。平安边际,是指正常销售额超过盈亏临界点销售额的差额,它说明销售额下降多少企业仍不致亏损。平安边际=正常销售额(量)-盈亏临界点销售额(量)平安边际率是平安边际与正常销售额(或当年实际订货额)的比值。(三)敏感分析(1)分析有关参数发生多大变化使盈利转为亏损。(2)分析各参数变化对利润变化的影响程度。反映敏感程度的指标是敏感系数:敏感系数=利润变动百分比/参数变动百分比笔者认为,敏感系数也可以利用(1)的结果直接计算,因为从盈利到0表示利润下降了100%,因此:敏感系数=-100%/(1)下参数变动百分比(四)影响利润各因素变动分析主要研究利润不为零的一般经营状态的有关问题。包括:1.编制单因素变动利润敏感分析表与制作敏感性分析列示各因素变动百分率及相应的利润值,各因素变动百分比通常以±20%为范围。2.分析单项因素变动对利润的影响分析销量、单价、单位变动本钱、固定本钱诸因素中的一项变动对利润产生的影响。3.有关因素发生互相关联的变动对利润的影响由于外界因素变化或企业拟采取某项行动,有关因素发生互相关联的影响,企业需要测定其引起的利润变动,以便选择决策方案。(五)实现目的利润的有关条件分析1.采取单项措施以实现目的利润。2.采取综合措施以实现目的利润。二、案例资料甲企业目前消费的a产品单价为2.2元,单位变动本钱为1.5元,固定本钱为42000元,产销量为12万件,所得税率25%。要求采用本量利分析法分析:(1)a产品预期利润为多少?(2)a产品保本点为多少?平安性检验程度为什么等级?(3)a产品有关参数发生多大变化使盈利转为亏损、各参数变动对利润的敏感程度为多少?(4)编制a产品单因素变动利润敏感分析表与制作敏感性分析图。(5)甲企业假如采取以下单项措施,预期利润为多少?1)为进步市场竞争力产品价格下降到2元;2)采取措施将单位可变本钱降到1.4元;3)采取措施将固定本钱降到35000元;4)进步消费效率将产量进步到15万件。(6)甲企业经过屡次综合平衡采取以下综合措施,预期利润将为多少?1)产品价格下降到2.1元;2)单位可变本钱降到1.45元;3)固定本钱降到40000元;4)产量进步到14万件。(7)甲企业提出要求,a产品要实现60000元税后利润,不考虑其它参数变化各参数要到达什么程度?各参数发生关联变化时,各参数应到达什么程度?三、本量利分析exel分析模板设计思路第一,将分析模板设计成两大部分:条件区域与分析区域;第二,在条件区域输入各参数的现有程度、目的利润、将要采取的措施等;第三,在分析区域自动实现预期利润分析、盈亏临界分析与平安边际分析、敏感分析、影响利润各因素变动分析、实现目的利润的有关条件分析。模板设计完成后,分析者只需在条件区域输入数据就可以自动得到本量利分析的各项分析结果。四、本量利分析exel分析模板详细设计(一)设置条件区域(图1)(二)自动实现预期利润分析(见图2、详细公式见图3)(三)自动实现盈亏临界分析与平安边际分析(见图2、详细公式见图3)b20“平安性检验〞通过if函数进展自动判断,b20=if(b190.4,"很平安",if(b190.3,"平安",if(b190.2,"较平安",if(b190.1,"值得注意","危险")))(四)自动实现敏感分析(见图2、图3)敏感系数的计算不必按教科书所写先计算参加变化10%的利润,再计算利润变化百分比,然后相除得到,直接用“-1/临界时参数变化百分比〞。(五)自动实现影响利润各因素变动分析利用exel“数据〞下“模拟运算表〞可以快速制作单因素变动利润敏感分析表。第一,在a30设变化率为0值(a30=0),作为“模拟运算表〞引用行(变化率-20%到+20%)的根据。第二,在b32:b35输入根本公式,一定要将变化率a30设计在公式中,否那么无法运用“模拟运算表〞功能。第三,选中b31:k35,运用“模拟运算表〞得到各参数变化-20%到+20%下的利润结果。运算表中b32:b35为公式区域,“输入引用行单元格〞=a30,同时按trl+shirt+enter就完成了。详细过程见图4。根据敏感分析表的结果绘制敏感分析图(见图2)。分析单项因素变动对利润的影响与有关因素发生互相关联的变动对利润的影响,公式设计较为简单,略。(六)自动实现目的利润的有关条件分析1.采取单项措施以实现目的利润:可以利用敏感系数进展计算。先计算目的利润与原预期利润的变化率,如:价格应到达的程度=原价格×(1+利润变动率/敏感系数)。详细公式见图5。2.采取综合措施以实现目的利润:那么在条件区域不断调整参数程度,并在条件区域e9中设e9=k47(见图1),可以动态观察目的利润的变化,最终确定各项参数程度。借助笔者利用exel提供的函数、引用、模拟运算表、图表等强大功能