利用Excel建立利润敏感分析模型

1、江西j ian gx i科sc ience学第 28卷第 1期2010年 2 月vo l. 28 no. 1feb. 2010文章编号 : 1001 - 3679 ( 2010 ) 01 - 0011 - 04利用exce l建立利润敏感分析模型杨桦(东北财经大学职业技术学院 ,辽宁 大连 116023)摘要 :利润是企业的综合性经济指标 ,它反映企业一个时期的经营成果 。在利润的形成过程中 ,会存在许多不确定因素对利润产生影响 ,各因素对利润的影响程度和敏感程度不同 。所谓利润敏感性分析是衡量单一变量 如产品售价或组合变量 (售价 、销量都变动 ) 变动对利润影响的一种分析方法 。利用 ex

2、cel 的分析工具 ,分 析了各因素变动对利润的影响 ,并在此基础上建立了利润敏感分析模型与动态图表 。关键词 :利润 ;敏感分析 ;模型中图分类号 : f275. 4文献标识码 : ato e sta b l ish the prof it sen s it iv ity ana ly s is m ode l by exce lyan g h ua( co llege of voca tiona l and techn ica l, dongbe i u n ive rsity of f inance and econom ic s, l iaon ing d a lian 116023

3、prc)a b stra c t: p rofit is a comp rehen sive econom ic ind ica to r of an en te rp rise, and it reflec ts the en te rp rises op e ra ting re su lts fo r a p e riod. d u ring the fo rm a tion of p rofit, the re a re m any unce rta in fac to rs tha t w ill have imp ac ts on p rofit. the effec t degr

4、ee and the sen sitivity of p rofit of each fac to r a re d iffe ren t. the sen sitivity ana lysis is a m e thod tha t d iscu sse s the influence of a single va riab le, such a s p rice, o r com b ined va riab le ( p rice, sa le s a re bo th sub jec t to change) on the p rofit.in th is p ap e r, the

5、influ2 ence of each va riab le fac to r on p rofit is d iscu ssed w ith excel ana lysis too ls. then the p rofit sen si2 tivity ana lysis mode l and dynam ic cha rts a re e stab lished on th is ba sis.key word s: p rofit, sen sitivity ana lysis, mode l小 。那些对利润影响大的因素称之为敏感因素 ,反之 ,则称为非敏感因素 。0 前言利润敏感性分析

6、法是指从定量分析的角度研 究有关因素发生某种变化对某一个或一组关键指 标影响程度的一种不确定分析技术 。其实质是通 过逐一改变相关变量数值的方法来解释关键指标 受这些因素变动影响大小的规律 。有些因素虽然 变化幅度较大 ,却只对利润产生微小的影响 。所 以对一个企业的管理者来说 ,不仅需要了解哪些 因素对利润增减有影响 ,而且需要了解影响利润 的若干因素中 , 哪些因素影响大 , 哪些因素影响1 利润敏感性分析指标的确定在运用利润敏感性对实际业务进行定量分析 时 ,常运用的方法可以包括质性的分析和量性的 分析两方面 。1. 1 质性的分析即与利润变化的方向分析 。如果因素 i对利 润的敏感度

7、s i 0, 则说明此因素的变化方向与利润的变化方向相同 , 或说增大此因素会使利润增收稿日期 : 2009 - 12 - 16;修订日期 : 2010 - 01 - 25作者简介 :杨 桦 ( 1963 - ) ,女 ,辽宁大连人 ,经济学硕士 ,从事信息管理方面的研究工作 。大 ; 反之 , 若 s i 1,即当某影响因素发生变化时 , 利润发生更大程度 的变化 , 该影响因素为敏感因素 ; ( 2 ) 敏感系数的 绝对值 1, 即利润变化的幅度小于影响因素变化 的幅度 , 该因素为非敏感因素 ; ( 3 ) 敏感系数的绝对值 = 1, 即影响因素变化合导致利润相同程度的 变化 , 该因素

8、亦为非敏感因素 。2 利用实例建立模型在企业只生产单品种产品 , 且产销平衡的条 件下 , 本量利之间的基本关系为 :利润 =销售量 (单价 - 单位变动成本 ) -固定成本 。在进行利润敏感性分析时 , 假定条件如下 :利 润只受销售量 、产品单价 、单位变动成本和固定成本的影响 ; 上述各因素的变动均不会影响其他因 素的变动 。2. 1 多因素综合变动对利润的影响2. 1. 1 新建工作簿与工作表 打开 exce l,新建一工作簿 ,命名为 敏感分析. xls ,再新建一工作 表 ,取名为 敏感分析模型 ,建立 敏感分析模型 表 ,如图 1所示 。图 1 敏感分析模型2. 1. 2 指标计

9、算打开【窗体 】控件 单击【滚动条 】按钮 在 e3: e6单元格分别添加【滚动条 】控件 右击 e3 单元格【滚动条 】选择【设置控件格式 】在弹出的【设置控件格式 】对话框中作如图 2 的设置 。其它控件同理 , 分别链接到 f3、f4、f5、f6单元格 。图 2 参数设置本例 中 各 因 素 的 变 动 范 围 为 - 25 % +25 % ,而滚动条控制按钮的值的变化范围为 0 50 ,为了使滚动条控制按钮的变化表示为百分数 的变化 ,这里将控制按钮的值除以 100 后再减去25 % ,则每次单击滚动条两端的箭头 ,单元格中的变动百分比就变化 1 % ,而当滚动条在中间位置 ,百分比恰

10、好为零 ; 当单击滚动框与滚动条两端之间时 ,每单击一次 ,变动百分比就增 (或减 ) 10 % 。选取 c3: c6 单元格区域 在编辑栏中以数组方式输入 = b 3: b 6 3 ( 1 + d 3: d6 ) ,即可计算 出变动后的数值 ; 单击 d 3 单元格 在编辑栏中输入 = f3 /100 - 25 % 复 制 公 式 至 d 6 单 元格 ,即可设置出变动的幅度 ,如图 3所示 。2. 1. 3 因素分析单击 b 11 单元格 在编辑栏 中输入 = b 6 3 (b 3 - b 4 ) - b 5 复 制公 式至c11 单 元 格 ; 单 击 d11 单 元 格 在 编 辑 栏

11、 中 输 入 = c11 - b 11 ;单击 e11单元格 在编辑栏中时 ,利润增加 3 % 。因此 ,产品单价对利润的影响最大 , 敏感性最强 , 其次是单位变动成本和销售 量 ,而固定成本对利润的影响最小 。2. 2. 2 建立单因素动态图 单击 b 19 单元格 在编辑栏中输入公式 = if (a 18 = 1 , f14 , if (a 18= 2 , f15 , if (a 18 = 3 , f16 , f17 ) ) ) ,即当 a18 = 1 时 ,让其值等于单价变动的幅度 ; 当 a 18 = 2 时 , 让其值等于单位变动成本变动的幅度 ;当 a18 = 3 时 ,让其值等

12、于固定成本变动的幅度 ; 当 a 18 不 等于 1 或 2 或 3 时 ,让其值等于销售量变动的幅度 。选中 b 18: b 19 单元格区域 利用图表向导 建立变动幅度的直方图 ;利用【窗体 】控件添加一 个【组合框 】右击【组合框 】选择【设置控件格 式 】在弹出的【设置控件格式 】对话框中作如图6的设置 。建立的动态图表如图 7所示 。图 3 变动后的数值计算输入 = d 11 /b 11 ,这样就计算出了多因素综合 变动对利润的综合影响 ,如图 4所示 。图 4 多因素分析当上述 4个因素同时朝着使利润增加的方向 变动 5 %时 ,则利润的增加幅度为 44. 60 % 。2. 2 单

13、因素变动对利润的影响2. 2. 1 编辑指标计算公式 选取 b 14: b 17 单元 格区域 在编辑栏中以数组公式输入 = b 11 ; 选取 c14: c17 单元格区域 在编辑栏中以数组公式输入 = d3: d 6 ,复制原始数据 。单击 d 14单元格 在编辑栏中输入 = b 6 3( c3 - b 4 ) - b 5 ; 单击 d15 单元格 在编辑栏 中输入 = b 6 3 ( b 3 2c4 ) 2b 5 ; 单击 d16 单元 格在编辑 栏中 输 入 = b 6 3 (b 3 2b 4 ) 2c5 ; 单 击d17单元格 在编辑栏中输入 = c6 3 (b 3 2b 4 ) 2

14、b 5 ,这样就分别计算出了销售量 、产品单价 、单 位变动成本和固 定成 本 单独 变动 时 对利 润的 影 响 ;单击 e14 单元格 在编辑栏中输入 = d14 2b 14 复 制 公式 至 e17 单 元格 ; 选 取 f14: f17单元 格 区 域 在 编 辑 栏 中 以 数 组 公 式 输 入 = e14: e17 /b 14: b 17 , 将 全 部 因 素 的 变 动 数 值 计算完成 ,如图 5所示 。2. 2. 3 建立销售收入变动与利润变动动态图( 1 )建立销售收入变动与利润变动表 , 如图 8 所 示 。图 5 单因素分析这样利润敏感性分析模型就建立起来了 。单

15、击各个影响因素滚动条的箭头 ,改变其变动幅度 , 就可以很方便地了解各个因素变动对利润的单独 影响程度以及综合影响程度 。例如 ,当产品销量 单独增加 5 %时 ,利润增加 8 % ; 当产品单价单独 增加 5 %时 ,利润增加 20 % ;当单位变动成本减少5 %时 ,利润增加 12 % ; 当固定成本单独减少 5 %图 8 销售收入与利润变动表其中 d 21 单元格的计算公式为“ = c21 2b 6 3b 4 2b 5 ”; d 22 单元格的计算公式为“ = c22 2c6 3c4 2c5 ”。( 2 )建立模拟运算表 。复制 c24: d 24 单元格区域的数值至 g20: h20

16、,在 f21: f26 单元格区域 填入必要的销售收入数值 ,选取 f20: h26 单元格 区域 单击【数据 】菜单 选择【模拟运算表 】 在弹出的【模拟运算表 】对话框“输入引用列的单元格 ”中输入“c21 ”,这样就建立起了不同销售收 入所对应的销售收入的变动幅度与利润的变动幅 度 。如图 9所示 。综合利用各有关因素之间的相互联系 ,采取综合措施 ,是成功的关铰 。3. 1 慎重实施价格决策单价为绝对敏感因素 ,单价的变化会引起利 润以几倍于单价变化的速度发生变化 。所以在经济决策中 ,对降价必须给予格外的关注 。价格下 调带来的利润损失 ,若不能通过扩大销售量或降 低单位成本予以更大

17、程度的补偿 ,则企业的整体 利润肯定会下降 ,目标利润难以实现 。同时 ,价格 上涨的同时 ,应尽可能地抑制销量的大幅缩减和成本的大幅上升 。否则目标利润同样难以实现 。3. 2 降低单位成本单位变动成本同样属于利润的敏感因素 ,所 以降低单位成本对实现企业目标利润具有重要意 义 。特别是从长期看 ,成本的高低将是企业能否生存和发展的关键 。企业降低单位成本的主要措 施有 : ( 1 )源头控制 , 降低材料的采购成本 , 其主 要方式有招标采购或网上采购等 ; ( 2 ) 生产过程图 9 模拟运算表( 3 )建立动态图 。根据模拟运算表 ,利用【图 表向导 】,建立 x、y散点图 ;利用窗体

18、控件添加一个微调控件 , 链接到 c21 单元 格 , 该 动态 图显 示了随着销售收入绝对值的变动 ,销售收入与利润 百分比的变动 ,如图 10所示 。控制 ,降低生产成本 ,对生产过程中的材料浪费 、不必要的机器停工损失都必须通过严格管理加以控制 ; ( 3 )实行批量生产 ,实现规模效益 ; ( 4 )必要 时 ,建立责任成本控制制度 ,将成本控制的责任落 实到具体的部门或个人 。3. 3 扩大销售量销量的增加会导致企业利润的大幅度增加 。但这里有一个前提 ,就是单价的降低幅度不能太大 ,否则 ,由于价格的敏感程度大于销量的敏感程 度 ,销量的增加反而会减少企业的利润 。 2000 年 度中国彩电行业第一次出现全行业亏损 ,就是价 格战的恶果 ,而不是销售出了问题 。一般而言 ,企 业扩大