构建Excel模板进行本量利分析

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

【摘要】本量利分析在企业预测、决策、规划和控制中得到了广泛的应用,计算工作量相当大。本文利用Excel 提供的函数、引用、模拟运算表、图表制作等强大功能,通过分析模板的设计,自动得到本量利分析结果,提高了本量利分析的效率、数据计算的准确性和图表绘制的规范性。

【关键词】本量利分析; Excel; 分析模板设计; 模拟运算表

本量利分析是成本、业务量和利润三者依存关系分析的简称,它是在成本习性分析的基础上,运用数学模型和图型,对成本、利润、业务量与单价等因素之间的依存关系进行具体的分析,研究其变动的规律性。它在企业预测、决策、规划和控制中得到广泛的应用。本量利分析的理论和方法并不复杂,但计算工作量相当大。应用Excel进行本量利分析将会使计算工作大大简化。本文拟对这一问题进行探讨。

一、本量利分析的主要内容

本量利分析的损益方程式为:

利润=单价×销量-单位变动成本×销量-固定成本

M=P×Q-b×Q-F

分析的主要内容包括:

(一)预期利润分析

在规划期间利润时,根据单价、单位变动成本和固定成本、销量关系,利用损益方程式计算出预期利润。

(二)盈亏临界分析与安全边际分析

盈亏临界分析主要研究利润为零的特殊经营状态的有关问题,是本量利分析的一项基本内容,亦称损益平衡分析或保本分析。它主要研究如何确定盈亏临界点、有关因素变动对盈亏临界点的影响等问题,主要计算盈亏临界点销售量、盈亏临界点销售额、盈亏临界点作业率。

根据盈亏临界分析结果进一步进行安全边际率分析。

安全边际,是指正常销售额超过盈亏临界点销售额的差额,它表明销售额下降多少企业仍不致亏损。

安全边际=正常销售额(量)-盈亏临界点销售额(量)

安全边际率是安全边际与正常销售额(或当年实际订货额)的比值。

(三)敏感分析

(1)分析有关参数发生多大变化使盈利转为亏损。

(2)分析各参数变化对利润变化的影响程度。

反映敏感程度的指标是敏感系数:

敏感系数=利润变动百分比/参数变动百分比

笔者认为,敏感系数也可以利用(1)的结果直接计算,因为从盈利M到0表示利润下降了100%,因此:

敏感系数=-100%/(1)下参数变动百分比

(四)影响利润各因素变动分析

主要研究利润不为零的一般经营状态的有关问题。包括:

1.编制单因素变动利润敏感分析表与制作敏感性分析

列示各因素变动百分率及相应的利润值,各因素变动百分比通常以±20%为范围。

2.分析单项因素变动对利润的影响

分析销量、单价、单位变动成本、固定成本诸因素中的一项变动对利润产生的影响。

3.有关因素发生相互关联的变动对利润的影响

由于外界因素变化或企业拟采取某项行动,有关因素发生相互关联的影响,企业需要测定其引起的利润变动,以便选择决策方案。

(五)实现目标利润的有关条件分析

1.采取单项措施以实现目标利润。

2.采取综合措施以实现目标利润。

二、案例资料

甲企业目前生产的A产品单价为2.2元,单位变动成本为1.5元,固定成本为42 000元,产销量为12万件,所得税率25%。要求采用本量利分析法分析:

(1)A产品预期利润为多少?

(2)A产品保本点为多少?安全性检验水平为什么等级?

(3)A产品有关参数发生多大变化使盈利转为亏损、各参数变动对利润的敏感程度为多少?

(4)编制A产品单因素变动利润敏感分析表与制作敏感性分析图。

(5)甲企业如果采取以下单项措施,预期利润为多少?1)为提高市场竞争力产品价格下降到2元;2)采取措施将单位可变成本降到1.4元;3)采取措施将固定成本降到35 000元;4)提高生产效率将产量提高到15万件。

(6)甲企业经过多次综合平衡采取以下综合措施,预期利润将为多少?1)产品价格下降到2.1元;2)单位可变成本降到1.45元;3)固定成本降到40 000元;4)产量提高到14万件。

(7)甲企业提出要求,A产品要实现60 000元税后利润,不考虑其它参数变化各参数要达到什么水平?各参数发生关联变化时,各参数应达到什么水平?

三、本量利分析Excel分析模板设计思路

第一,将分析模板设计成两大部分:条件区域与分析区域;

第二,在条件区域输入各参数的现有水平、目标利润、将要采取的措施等;

第三,在分析区域自动实现预期利润分析、盈亏临界分析与安全边际分析、敏感分析、影响利润各因素变动分析、实现目标利润的有关条件分析。

模板设计完成后,分析者只需在条件区域输入数据就可以自动得到本量利分析的各项分析结果。

四、本量利分析Excel分析模板具体设计

(一)设置条件区域(图1)

(二)自动实现预期利润分析(见图2、具体公式见图3)

(三)自动实现盈亏临界分析与安全边际分析(见图2、具体公式见图3)

B20“安全性检验”通过if函数进行自动判断,B20=IF(B19>0.4,”很安全”,IF(B19>0.3,”安全”,IF(B19>0.2,”较安全”,IF(B19>0.1,”值得注意”,”危险”)))

(四)自动实现敏感分析(见图2、图3)

敏感系数的计算不必按教科书所写先计算参加变化10%的利润,再计算利润变化百分比,然后相除得到,直接用“-1/临界时参数变化百分比”。

(五)自动实现影响利润各因素变动分析

利用Excel“数据”下“模拟运算表”可以快速制作单因素变动利润敏感分析表。

第一,在A30设变化率为0值(A30

=0),作为“模拟运算表”引用行(变化率

-20%到+20%)的依据。

第二,在B32:B35输入基本公式,一定要将变化率A30设计在公式中,否则无法运用“模拟运算表”功能。

第三,选中B31:K35,运用“模拟运算表”得到各参数变化-20%到+20%下的利润结果。运算表中B32:B35为公式区域,“输入引用行单元格”=A30,同时按