Excel在管理会计中的高级应用:非线性成本、数量和利润分析模型的设计与应用_变动成本论文

管理会计中Excel的高级应用——非线性本量利分析模型设计与应用,本文主要内容关键词为:管理会计论文,模型论文,高级论文,Excel论文,此文献不代表本站观点,内容供学术参考,文章仅供参考阅读下载。

       本量利分析模型是基于成本性态分析和变动成本法的利润规划方法。但是,基本的本量利分析模型都是以线性假设为基础的,即假设成本、销量和利润之间的关系呈理想的线性关系,而现实经济问题的复杂多变经常导致线性假设失效,使基本的本量利模型失去决策有效性。因此,现实经济的非线性特征在本量利分析中不容忽视。

       一、非线性本量利分析模型的基本形式

       在线性假设下,由于假设单价、单位变动成本不变,本量利分析模型的基本形式可简化为:利润=(单价-单位变动成本)×销售量-固定成本。然而,现实经济环境的复杂性经常导致产品价格、单位变动成本并非固定不变的常量,而呈现多种变化。

       根据微观经济理论,销售量和价格之间不可能完全独立,产品价格与销售量之间往往存在一定的负相关关系,那么销售收入与销售量之间就不是线性关系,而是抛物线关系。再如,企业为了实现薄利多销,往往采取销售价格阶梯折扣的情况,此时的销售收入线呈现为分段型。销售折扣和销售折让也经常使产品的实际价格发生变化,导致销售收入与销售量呈非线性关系。

       在企业生产经营过程中,随着产量的不断增大,单位变动成本和固定成本也会发生变化,主要表现为:随着产销量的不断增大,市场的不确定性将更大,原材料采购和储存的不确定性更大。原材料采购过程中,一方面,材料购买价格可能会随着采购量的增加而下降,从而降低原材料的单位成本;另一方面,由于订货次数的增加,原材料采购的订货成本会增加,进而提高原材料的单位成本。由于原材料运输量和处理量的增大,其正常损失和非正常损失也会相应增大,进而提高原材料的单位成本;随着生产量和销售量的增大,产品生产消耗的不确定性也会进一步提高,提高了原材料储存的不确定性,要求持有更高的保险储备量,从而增加原材料的单位储存成本。

       在非线性假设条件下,本量利分析模型为:利润=销售量×单价-销售量×单位变动成本-固定成本。其中,单价、单位变动成本、固定成本为可变因素。

       二、非线性本量利分析模型设计

       (一)模型概要

       1.主要变量包括变动成本、固定成本、销售量、单价、利润、边际贡献、销售折扣阈值、折扣率等,其中,单价、单位变动成本、固定成本均为可变因素。

       2.决策变量包括保本点、保利点、利润。

       3.决策方法包括盈亏平衡分析、图示法、最大利润法。

       4.关键技术包括非线性收入线的绘制方法、非线性成本线的绘制、阶梯式固定成本线的绘制方法、非线性盈亏平衡分析模型的设计与图示方法、模拟运算表的使用、微调器的使用、IF函数、INDEX函数、MAX函数、MATCH函数。

       (二)问题描述

       某公司生产A产品,该产品每件售价为100元。该产品当前的固定成本为20000元,当销售量达到2500件时,由于生产量加大,超过了当前的生产负荷,需要增加机器设备,固定成本将增加到40000元,当销售量增加到4500件时,固定成本将增加到60000元。单位变动成本随产量的变动而变动,其变动规律为:单位变动成本=20X+0.018X[2],X为销售量。另外,为了扩大销售,企业制定了薄利多销政策:销售量达到2000件时,增量销售的价格下调15%;销售量达到4000件时,增量销售的价格下调20%。

       现对该产品的生产和销售进行决策。

       (三)建模的技巧与步骤

       1.相关函数

       ①逻辑判断函数IF(判断语句,a,b),其中a表示判断语句为TRUE时的返回值,b表示判断语句FALSE时的返回值。

       ②最大值函数MAX()用于返回一组数值中的最大值。

       ③查询引用函数INDEX(A,b,c)用于返回查询数据组中的元素值,其中A表示所引用的某一单元格或某一区域的数据,b表示所引用数据的行号,c表示所引用数据的列标。例如,INDEX(A1∶B9,4,2)表达的函数意义是,引用A1到B9区域中第4行第9列的数据。

       ④位置搜寻函数MATCH(a,B,c)用于指定数值在指定数组区域中的位置,其中a表示要查找的数值,B表示要查询的数据区域,c表示查找方式,可省略或赋值为1、0、-1。当c为“省略或者1”时,表示查找小于或等于数据a的最大值在数组区域B中的位置,数组必须按升序排列;当c被赋值为“0”时,表示查找等于a的值所在的位置,数组可按任意排列;当c被赋值为“-1”时,表示查找大于或等于数值a的最小值所在的位置,数组必须按降序排列。例如,MATCH(50,B2∶B5,1)表示在区域(B2∶B5)中查找小于或等于50的最大值所在的位置;MATCH(50,B2∶B5,-1)表示在区域(B2∶B5)中查找大于或等于50的最小值所在的位置。

       MATCH函数经常与INDEX函数结合使用,用于在数组中查找出符合条件的数值并进行引用。MATCH函数用来确定符合条件的数值的所在位置,INDEX函数用来根据其位置对相应的数值进行引用。例如,若要快速查询某季度某型号产品的效率,只需在单元格D9中输入公式:“=INDEX(C3∶E5,MATCH(C9,B3∶BS,0),MATCH(B9,C2∶E2,0))”,并在查询区输入要查询的产品型号和季度,即可返回查询值(如图1)。

      

       图1 INDEX和MATCH函数应用示范

       2.建模步骤

       本文的计算模型见:http://wensubin.ys168.com之文件夹“《财务与会计》论文”之“非线性本量利分析模型的设计与应用.xlsx”。建议读者先下载该文件,边阅读边操作。

       第一步:构建模型界面。根据问题概述,模型界面应包括数据区、计算区、模拟试验区、结论区、绘图辅助区。

       第二步:在数据区域(B3∶C14)输入相关数据资料。其中,固定成本的公式为:C14=IF(C3<=2500,20000,IF(C3<=4500,40000,60000))。

       第三步:设计分析区。在分析区(E3∶F8)中设计计算销售收入、变动成本、固定成本、边际效应和利润的公式。相关公式如下:

       销售收入的公式为:F3=IF(C3<=B7,C3×C4,IF(C3<=B8,B7×C4+(C3-B7)×C4×C8,B7×C4+(B8-B7)×C4× C8+(C3-B8)×C4×C9))。

       变动成本的公式为:F4=25×C3+0.012×C3×C3。

       其他公式为:F5=C14;F6=F4+F5;F7=F3-F4;F8=F7-F5。

       第四步:进行模拟运算。计算不同的销售情况下,销售收入、总成本和利润。具体操作如下:

       ①设定模拟运算区,输入不同销售情况下的销量,保证获得一组不同水平的销量,本例将设定从0到5000件的54组销量数据,见模型文件的工作表中的数据区域(B19∶ B72)。

       ②将销售收入、总成本和利润链接到模拟运算区,即令 C18=F3,D18=F6,E18=F8。

       ③选定区域(B18∶E72),在Excel2010中打开菜单“数据”,单击“模拟分析”,选择“模拟运算表”,即可弹出“模拟运算表”图框,设定引用单元格的行与列,由于本例以“当前销售量”为自变量,在模拟运算表中位于列,因此,只需设置引用列的单元格,即$C$3,点击“确定”后模拟计算出相应数据(如图2)。

      

       图2 模拟运算表设计

       第五步:设计结论区。结论区设计在区域(E10∶F14)。结论区需要输入的公式如下:E10=INDEX(B19∶B72,MATCH($F$10,$E$19∶$E$72,0));F10=MAX(E19∶E72);E11=“最优销售量为:“&E10&”件”;E12=“最大利润额为:“&F10&”元”;E13=“当前销售量为:“&C3&”件”;E14=“当前利润为:“&F8&”元”。

       第六步:列出最佳销售量和当前销售量,绘制非线性的盈亏平衡分析图,根据个人喜好对图表进行修饰(如图3)。

      

       图3 非线性本量利分析图

       三、非线性本量利分析模型的应用意义

       Excel的计算分析和数据处理等中高级功能,可以使企业轻松实现本量利模型的动态决策功能。借助Excel来解决非线性本量利分析问题的应用意义有以下几点:

       第一,运用Excel来解决财务问题是现代财务人员须具备的技能之一。将Excel和财务办公软件相结合,可实现数据的筛选和分析,进行多种非线性的计算,减少不必要的计算和操作,提高财务人员的工作效率。

       第二,采用Excel而建立的本量利分析模型可使数据表达得更直观、简便。在Excel的非线性本量利分析模型中,不仅可运用函数和公式来确定单元格之间的关系实现数据连续跟踪,而且可借助相关的图形动态地观察变量之间的变动趋势和勾稽关系。同时,还可在图形中加入微调器,对相关变动进行动态调节,有利于进行敏感分析。

       总之,基于Excel的非线性本量利分析模型可将冗杂的数据简化、筛选和处理,帮助决策者就企业规模、生产结构和成本等方面问题做出科学决策。

标签:;  ;  ;  ;  ;  

Excel在管理会计中的高级应用:非线性成本、数量和利润分析模型的设计与应用_变动成本论文
下载Doc文档

猜你喜欢