利润敏感性分析模型Excel模板

[原创]E x c e l模型之十七 -敏感性分析 (入选推荐日志，加10币)飓风图 蛛网图和敏感性分析图表 这个例子模型说明如何利用Risk Simulator： 1、运行一个预仿真敏感性分析(飓风图和蜘蛛图) 2、运行一个后仿真敏感性分析(敏感性分析图) 模型背景 文件名称：飓风图蛛网图和敏感性分析图表（线性）.xls 这个示例描述了一个简单的现金流的模型，演示了如何在仿真之前和仿真之后进行敏感性分析。

飓风图和　蛛网图是静态的分析工具用来哪些变量会对结果影响最大。

即，每个先验变量扰动一定量，分析关键的结果　以决定哪些输入变量的是关键的成功因素，并且影响最大。

相反，敏感性图是动态的，即在仿真过程中，　所有的先验变量在仿真之后同时扰动（自相关，交叉相关，以及交互作用的影响结果都考虑在了敏感性图中）。

因此，在仿真之前使用飓风图进行静态分析，在仿真后使用敏感性分析。

创建飓风图和敏感性图 运行模型，简单地： 1、回到DCF 模型中然后选择 NPV作为结果（单元格 G6）。

2、选择仿真|工具| 飓风图分析（或者点击飓风图的图标）。

3、勾选通过软件的自动智能命名生成的先验变量名称，然后点击确定。

结果解析 生成的报告说明了敏感性表格（关键变量的初始值以及先前变量的扰动值），有最大影响（对结果的区间）　的变量被列在第一位。

飓风图说明了这一分析过程，蜘蛛图是同一个分析过程,但是它还包括了非线性的　影响部分。

也就是说，如果输入的变量对于输出结果非线性的的影响，蜘蛛图将是曲线的。

可参见飓风图　和敏感性分析图表（非线性）中关于Black-Scholes模型的分析。

创建一个敏感性分析图 运行这个模型，只要： 1 、建立一个新的仿真文档(仿真 l 新建仿真) 2 、在DCF模型工作簿中设定输入变量假设和输出预测 3 、运行仿真（仿真l 运行仿真) 4 、选择仿真l 工具 l 敏感性分析 结果解析 注意：如果相关性选项关闭，敏感性分析图和飓风图的结果相似。

《管理信息系统》课程
实验一、用Excel作利润估计模型一个公司需要对它的产品进行利润估计。

预计该产品的生命期为5年，总产量为50000个。

其他情况如下：
（1）产品单价：第1年为5.5元，考虑到通货膨胀率，以后每年估计以5%的速率上涨。

（2）销售成本：第1年是当年销售额的65%，以后按2%的速率递减。

（3）各年度中销售量占总产量的百分比为：10%，22%，32%，22%，14%。

（4）其他费用第1年为2000元，第2年后为上年度费用的5%递增。

（5）毛利为销售额和销售成本之差。

（6）推销费用按销售额的16%计算。

问题：
1．请用Excel做出该模型，输出下表，计算公司的总利润是多少？每年利润及其占总利润的百分比是多少？每年销售额占总额的百分比是多少？
2．如果第1年价格定为每件产品售价5元或6元，对公司分别会产生何种影响？
3．讨论对该模型和预测系统可能的改进。

要求：将以上实验过程、内容及问题分析，填写实验报告。

Excel在利润敏感性分析与预测模型中的应用【摘要】利润是企业的综合性经济指标，它反映企业一个时期的经营成果。

在利润的形成过程中，会存在许多不确定因素对利润产生影响，各因素对利润的影响程度和敏感程度不同。

本文阐述了各因素对利润的敏感系数的计算及如何运用敏感系数进行利润预测，在此基础上设计出利润敏感性分析与预测模型，可以方便地进行敏感性分析和利润预测。

【关键词】敏感系数；敏感分析；利润预测敏感性分析是在投资项目评价和企业其他经营决策中常用的一种不确定性分析方法。

它是在确定性分析的基础上，重复分析不确定因素（如销售量、价格、成本、投资等）变化时，将对项目经济效益评价指标（如利润、投资收益率、投资回收期等）的影响程度。

利润敏感性分析的主要作用之一是进行利润的预测和规划。

根据不确定性因素每次变动数目的多少，敏感性分析可以分为单因素敏感性分析和多因素敏感性分析。

本文拟分析当影响利润的一个或几个因素同时发生变动时，如何运用敏感系数进行利润预测，并建立Excel利润敏感性分析与预测模型。

一、单因素条件下敏感系数的计算一定时期内，企业可实现的营业利润是由产品的产销量、单价、单位变动成本及固定成本总额等因素决定的，这些因素中的任何一个发生变化，都会对利润产生不同程度的影响。

因此，常规条件下的敏感系数就是计量上述某一因素单独发生变化时对利润影响程度的指标，一般其计算公式为：某因素的敏感系数=营业利润变动率/该因素的变动率某因素的敏感系数越大，说明其变化对利润的影响程度越大，管理中应对其重点关注。

实务中，为事先了解各因素的变化对利润的影响情况，便于利润预测和管理，各因素的敏感系数必须根据基期的有关数据直接测算。

设基期营业利润为I，单价为p，单位变动成本为b，固定成本总额为a，产销量为Q，根据量本利分析法的计算公式，则有：I=Qp-Qb-a在计算常规条件下的敏感系数时，假设其他因素不变，计划期间只有价格发生变化，其变化率为k，则：利润增长额为：当影响利润的某一因素单独发生变化时，按常规的敏感系数可直接预测计划期的营业利润，计算公式为：预测期营业利润=基期利润×（1+某因素的敏感系数×该因素变化率）二、多个因素同时发生变化时敏感系数在利润预测中的应用现实经济生活中，计划期与基期相比，虽然影响利润的某个因素单独变动的情况时有发生，但更经常出现的是几个因素同时发生变化的情况。

e x c e l敏感性分析敏感性分析excel投资项目敏感性分析是用来衡量投资项目中某个因素的变动对该项目预期结果影响程度的一种方法。

通过敏感性分析，可以明确敏感的关键问题，避免绝对化偏差，防止决策失误，进而增强在关键环节或关键问题上的执行力。

在复杂的投资环境中，对投资项目净现值的影响是多方面的，各方面又是相互关联的，要实现预期目标，需要采取综合措施，多次测算，依靠手工完成，往往令人望而却步。

借助于Excel，可以实现自动化分析。

下面通过具体的实例来说明Excel在投资项目敏感性分析中的具体应用。

有关资料数据如表1所示。

一、投资项目敏感性分析涉及的计算公式营业现金流量=营业收入－付现成本－所得税=税后净利润＋折旧=(营业收入－营业成本)×(1－所得税税率)＋折旧=(营业收入－付现成本－折旧)×(1－所得税税率)＋折旧=(营业收入—付现成本)×(1-所得税税率)＋折旧×所得税税率投资项目净现值=营业现金流量现值－投资现值二、建立Excel分析模型第一步，在Excel工作表中建立如表1所示的投资项目敏感性分析格式。

第二步，定义计算公式：B9=PV($B$3，$B$4，－(($B$5-$B$6)*(1-$J}$7)+($B$8／$B$4)*$B$7))-$B$8；C12=BI2/100-0．5，用鼠标拖动C12单元格右下角的填充柄到C15单元格，利用Excel的自动填充技术，完成C13、C14、C15这三个单元格公式的定义；D12=B5*(1+C12)，用鼠标拖动D12单元格右下角的填充柄到D15单元格，完成D13、D14、D15这三个单元格公式的定义；E12=PV($B$3．$B$4．-(($D$12-$D$13)*(1-$D$14)+($D$15／$B$4)*$D$14))-$D$15，拖动E12单元格右下角的填充柄到E15单元格，完成E13、E14、E15这三个单元格公式的定义；F12=(E12-$B$9)／$B$9，用鼠标拖动F12单元格右下角的填充柄到F15单元格，完成F13、F14、F15这三个单元格公式的定义；G12=F12/C12，用鼠标拖动G12单元格右下角的填充柄到G15单元格，完成G13、G14、G15这三个单元格公式的定义。

5.12 Excel应用实例之二——敏感分析[本节提要]本节主要通过投资分析等问题，介绍了Excel 2000的模拟运算表、方案和单变量求解的应用，着重说明了单变量模拟运算表和双变量模拟运算表的操作步骤，在模拟运算表的基础上进行敏感分析的方法，以及应用方案和单变量求解工具辅助决策的方法。

敏感分析也称作“What-If分析”，是在财务、会计、管理、统计等应用领域不可缺少的工具。

例如在财务分析中，许多指标的计算都要涉及到若干个参数。

像长期投资项目，其偿还额与利率、付款期数、每期付款额度等参数密切相关。

又如固定资产的折旧，与固定资产原值、估计残值、固定资产的生命周期、折旧计算的期次以及余额递减速率等密切相关。

而作为决策者往往需要定量地了解，当这些参数变动时对有关指标的影响。

这些分析可以利用Excel 2000的模拟运算表工具实现。

以下通过投资效益的分析说明有关工具的使用。

5.12.1模拟运算表所谓模拟运算表实际上是工作表中的一个单元格区域，它可以显示一个计算公式中某些参数值的变化对计算结果的影响。

由于它可以将所有不同的计算结果以列表方式同时显示出来，因而便于查看、比较和分析。

根据分析计算公式中的参数的个数，模拟运算表又分为单变量模拟运算表和双变量模拟运算表。

一、单变量模拟运算表单变量模拟运算主要用来分析当其它因素不变时，一个参数的变化对目标值的影响。

例如，要计算一笔贷款的分期偿还额，可以使用Excel 2000提供的财务函数之PMT。

而如果要分析不同的利率对贷款的偿还额产生的影响，则可以使用单变量模拟运算表。

假设某公司要贷款1000万元，年限为10年，目前的年利率为5%，分月偿还。

则利用PMT函数[ PMT(rate,nper,pv,fv,type) ]可以计算出每月的偿还额。

其具体操作步骤如下：(1) 在工作表中输入有关参数，如图5-12-1所示。

(2) 在B5单元格输入计算月偿还额的公式：“=PMT(B3/12,B4*12,B2)”在上述公式中，PMT函数有三个参数。

Excel敏感性分析报告1. 简介Excel是一款功能强大的电子表格软件，广泛应用于数据分析和管理。

敏感性分析是在Excel中用于评估数据模型对输入变量的敏感程度的工具。

本文档旨在介绍什么是敏感性分析，并提供一个在Excel中进行敏感性分析的简单示例。

2. 敏感性分析的定义敏感性分析是一种用于评估输入变量对输出变量的影响程度的分析方法。

在数据建模和预测中，我们常常需要了解输入变量的变动对输出结果的影响程度，以便做出相应的决策。

3. Excel中的敏感性分析工具Excel提供了多种敏感性分析工具，包括数据表、条件格式和数据透视表等。

这些工具能够帮助用户进行高效的敏感性分析，从而更好地理解数据模型和做出决策。

3.1 数据表数据表是Excel中用于组织和分析数据的一种工具。

通过数据表，用户可以快速计算和比较不同输入变量对输出变量的影响。

用户只需在数据表中输入不同的输入变量值，并观察输出变量的变化情况即可。

3.2 条件格式条件格式是Excel中一种用于根据特定条件自动格式化单元格的功能。

通过条件格式，用户可以根据输入变量的不同取值，自动对输出变量进行不同的颜色标记或其他格式设置。

这样可以直观地观察到输入变量对输出变量的影响。

3.3 数据透视表数据透视表是Excel中一种用于分析和总结大量数据的工具。

用户可以通过数据透视表对输入变量和输出变量进行汇总和对比，以便更好地理解它们之间的关系和敏感性。

4. 示例：利用Excel进行敏感性分析以下是一个简单的例子，展示了如何利用Excel进行敏感性分析。

假设我们有一个销售模型，其中包含两个输入变量：产品价格和广告费用。

我们想要了解这两个变量对销售额的影响程度。

首先，我们需要在Excel中建立一个数据表，列出不同的产品价格和广告费用组合，并计算对应的销售额。

然后，我们可以使用条件格式对销售额进行颜色标记，以便更直观地观察销售额的变化情况。

接下来，我们可以利用数据透视表对这些数据进行汇总和对比。

本量利关系敏感性分析的Excel应用珠海城市职业技术学院经济管理学院陈希本量利分析（Cost-Volume-ProfitAnalysis，CVP）是指在变动成本法基础上，以数学化的会计模型与图文来揭示固定成本、单位变动成本、销售单价、保本点和保利点等变量之间的内在规律性联系及企业的经营安全性，所提供的原理、方法在管理会计中有着广泛的用途，同时又是企业进行决策、计划和控制的重要工具。

在进行本量利分析时，主要变量有固定成本、单位变动成本、销售单价、目标利润、税后目标利润以及企业所得税率，供企业进行财务决策的主要指标包括保本点、保利点、保净利点和安全边际率等。

一、变动成本法下本-量-利关系（一）本量利基本关系式营业利润（P）=（单价－单位变动成本）×销量－固定成本=（p－b）x–a；边际贡献（Tcm）=销售收入-变动成本=（p–b）x；单位边际贡献（cm）=销售单价-单位变动成本=p–b；边际贡献率（cmR）=单位边际贡献/销售单价= cm/p。

（二）保本点保本点又称为盈亏临界点（Break-evenPoint），是指企业的经营规模（产销量）刚好使企业达到不盈不亏的状态，即营业利润为零时的销售量或销售额。

保本量=固定成本/（销售单价-单位变动成本）=a/（p-b），保本额=固定成本/边际贡献率=a/cmR。

（三）保利点保利点是指在单价和成本水平确定的情况下，为确保预先确定的目标利润（TP）能够实现而应达到的销售量或销售额。

保利量=（固定成本+目标利润）/（销售单价-单位变动成本）=（a+TP）/（p-b），保利额=（固定成本+目标利润）/边际贡献率=（a+TP）/cmR。

（四）保净利点保净利点是指在单价和成本水平确定的情况下，为确保预先确定的税后目标利润能够实现应达到的销售量或销售额。

)/单位边际贡献保净利量= (固定成本+目标利润1−所得税率)/单位边际贡献率保净利额= (固定成本+目标利润1−所得税率（五）经营安全分析安全边际量=计划（实际）产销数量-保本量安全边际率=安全边际量/计划（实际）产销量安全边际率越低，说明企业的计划产销量或实际产销量离保本量越近，随着企业经营状况的不确定性出现亏损的可能性越大；安全边际率越高，说明企业的计划产销量或实际产销量高出保本量越多，经营安全性越好，一般标准如表1二、利用Excel2010建立本-量-利模型（一）案例资料某企业只生产一种产品，单价5元，单位变动成本2.6元，2010年计划产销量60000件，目标利润240000元，固定成本108000元，企业所得税率25% ，求保本点、保利点、保净利点，并进行敏感性分析，以及对该企业的经营安全性做出判断。

E X C E L敏感性分析This model paper was revised by the Standardization Office on December 10, 2020敏感性分析excel投资项目敏感性分析是用来衡量投资项目中某个因素的变动对该项目预期结果影响程度的一种方法。

通过敏感性分析，可以明确敏感的关键问题，避免绝对化偏差，防止决策失误，进而增强在关键环节或关键问题上的执行力。

在复杂的投资环境中，对投资项目净现值的影响是多方面的，各方面又是相互关联的，要实现预期目标，需要采取综合措施，多次测算，依靠手工完成，往往令人望而却步。

借助于Excel，可以实现自动化分析。

下面通过具体的实例来说明Excel在投资项目敏感性分析中的具体应用。

有关资料数据如表1所示。

一、投资项目敏感性分析涉及的计算公式营业现金流量=营业收入－付现成本－所得税=税后净利润＋折旧=(营业收入－营业成本)×(1－所得税税率)＋折旧=(营业收入－付现成本－折旧)×(1－所得税税率)＋折旧=(营业收入—付现成本)×(1-所得税税率)＋折旧×所得税税率投资项目净现值=营业现金流量现值－投资现值二、建立Excel分析模型第一步，在Excel工作表中建立如表1所示的投资项目敏感性分析格式。

第二步，定义计算公式：B9=PV($B$3，$B$4，－(($B$5-$B$6)*(1-$J}$7)+($B$8／$B$4)*$B$7))-$B$8；C12=BI2/100-0．5，用鼠标拖动C12单元格右下角的填充柄到C15单元格，利用Excel的自动填充技术，完成C13、C14、C15这三个单元格公式的定义；D12=B5*(1+C12)，用鼠标拖动D12单元格右下角的填充柄到D15单元格，完成D13、D14、D15这三个单元格公式的定义；E12=PV($B$3．$B$4．-(($D$12-$D$13)*(1-$D$14)+($D$15／$B$4)*$D$14))-$D$15，拖动E12单元格右下角的填充柄到E15单元格，完成E13、E14、E15这三个单元格公式的定义；F12=(E12-$B$9)／$B$9，用鼠标拖动F12单元格右下角的填充柄到F15单元格，完成F13、F14、F15这三个单元格公式的定义；G12=F12/C12，用鼠标拖动G12单元格右下角的填充柄到G15单元格，完成G13、G14、G15这三个单元格公式的定义。

利用Ｅｘｃｅｌ构建投资项目内部收益率敏感性分析模型[摘要] 文章介绍了利用Excel构建投资项目内部收益率敏感性分析模型的步骤和方法,以及如何利用该模型进行投资项目内部收益率的敏感性分析｡[关键词] Excel;投资项目;内部收益率;敏感性分析在进行固定资产投资决策时,如果是在贴现率和未来现金流量确定的条件下,利用Excel的IRR函数(内部收益率函数)即可直接求出投资项目的内部收益率,并可据此判断投资项目的可行性｡而实际上,固定资产投资项目涉及的时间较长,对未来收益和成本很难准确预测,投资活动中充满了不确定性或风险,项目投产后所带来的未来现金流量只是对未来可能发生结果的一种估计和预测,而不是未来实际发生的结果｡因此,在投资决策中,应充分考虑到风险因素｡敏感性分析是固定资产投资决策中常用的一种重要的分析方法,用来衡量当投资方案中某个因素发生了变动时,对该方案预期结果的影响程度｡本文将介绍利用Excel构建投资项目内部收益率敏感性分析模型的步骤和方法,以及如何利用该模型进行投资项目内部收益率的敏感性分析｡一､投资项目内部收益率敏感性分析模型构建的投资项目内部收益率敏感性分析模型,如图1所示｡图1 投资项目内部收益率敏感性分析模型二､构建投资项目内部收益率敏感性分析模型的步骤对投资项目内部收益率的敏感性进行分析,可以利用Excel构建如图1所示的敏感性分析模型,分别进行多因素变动和单因素变动对内部收益率的影响分析｡但需要注意的是,当要分析单因素变动对内部收益率的影响时,内部收益率的计算是一件很麻烦的事,因为当投资项目寿命期内各年的净现金流量不相等时,不能使用RATE函数来计算内部收益率,不过可以通过自定义内部收益率函数来解决这个问题｡1. 自定义内部收益率函数的计算原理及步骤(1)首先假定一个内部收益率的初始值,并以此内部收益率作为贴现率i,计算项目的净现值NPV;(2)根据计算出的净现值数据,利用下面的公式计算第1次迭代后的内部收益率IRR:式中I为初始投资现值｡若相邻两次计算的内部收益率相差不大,或计算出的净现值接近于零,则停止计算,就得到了内部收益率的近似值,否则重复上述迭代步骤｡2. 定义“内部收益率”自定义函数定义一个名为“内部收益率”的自定义函数,其语法为:内部收益率(初始投资,期末残值,寿命期,年付现成本,年销售量,产品价格,单位变动成本,所得税税率)｡自定义函数可以通过一小段程序对其参数及参数之间的关系进行描述,这种程序又称过程代码｡“内部收益率”自定义函数的建立方法和步骤如下:单击[工具]菜单,选择[宏]项,在[宏]项的子菜单中选择[Visual Basic编辑器],打开Visual Basic编辑器窗口,再单击Visual Basic编辑器窗口的[插入]菜单,选择[模块]项,则显示模块1的窗口｡在模块1窗口中,单击[插入]菜单,选择[过程]项,则系统弹出[添加过程]对话框,如图2所示｡在[添加过程]对话框中,[名称]栏中输入“内部收益率”,[类型]选“函数”,单击[确定],出现编辑过程页面｡在该页面中,将Public Function内部收益率和End Function修改为如下的过程代码:Public Function 内部收益率(初始投资,期末残值,寿命期,年付现成本,年销售量,产品价格,单位变动成本,所得税税率)净现金流量=(年销售量*(产品价格-单位变动成本)/10 000-年付现成本)*(1-所得税税率)+(初始投资-期末残值)/寿命期*所得税税率x1= 0.110jxz=净因素变动对内部收益率综合影响分析表格在单元格B14中输入预计内部收益率的计算公式为“=内部收益率(B4,B5,B6,B7,B8,B9,B10,B11)”(步骤为:单击工具栏的[粘贴函数]按钮,选择“用户定义”,选中“内部收益率”函数,出现该函数对话框,输入相应的内容即可);单元格D14中的计算公式为“=内部收益率(C4,C5,C6,C7,C8,C9,C10,B11)”,在单元格F14中输入公式“=D14-B14”｡这样,就得到了多因素变动对内部收益率的综合影响结果｡4. 设计单因素变动影响分析表格如图1所示,在单元格B17:B23中输入公式“=D4:D10”(数组公式输入),在单元格C17:C23中分别输入各个因素单独变动时的内部收益率计算函数如下:单元格C17:“=内部收益率(C4,B5,B6,B7,B8,B9,B10,B11)”单元格C18:“=内部收益率(B4,C5,B6,B7,B8,B9,B10,B11)”单元格C19:“=内部收益率(B4,B5,C6,B7,B8,B9,B10,B11)”单元格C20:“=内部收益率(B4,B5,B6,C7,B8,B9,B10,B11)”单元格C21:“=内部收益率(B4,B5,B6,B7,C8,B9,B10,B11)”单元格C22:“=内部收益率(B4,B5,B6,B7,B8,C9,B10,B11)”单元格C23:“=内部收益率(B4,B5,B6,B7,B8,B9,C10,B11)”在单元格D17:D23中输入公式“=(C17:C23-B14)/B14”(数组公式输入)｡这样,一个投资项目内部收益率的敏感性分析模型就建立起来了｡单击各个影响因素滚动条的箭头,改变其变动幅度,就可以很方便地了解各个因素对投资项目内部收益率的单独影响程度以及综合影响程度｡三､投资项目内部收益率敏感性分析模型的应用在这个模型中,通过单击滚动栏两端的箭头或用鼠标拖曳滑块,即可改变各种因素的变动率,并分析其对投资项目内部收益率的影响程度｡如果某因素在较小范围内发生了变动就会影响原定方案的经济效果,即表明该因素的敏感性强;如果某因素在较大范围内变动时才会影响原定方案的经济效果,即表明该因素的敏感性弱｡在长期投资决策中,敏感性分析通常用来研究有关投资方案的现金净流量或固定资产寿命发生变动时,对该方案的净现值和内部收益率的影响程度｡同时,它也可以用来研究有关投资项目的内部收益率变动时,对该方案的现金净流量或使用年限的影响程度｡敏感性分析有助于企业领导了解在执行决策方案时应注意的问题,从而可以预先考虑措施与对策,避免决策上的失误｡主要参考文献[1] 韩良智等. Excel在财务管理与分析中的应用[M]. 北京:中国水利水电出版社,2004.[2] 韩良智. Excel在投资理财中的应用[M]. 北京:电子工业出版社,2005.[3] 钟爱军.用Excel进行利润的敏感性分析[J]. 中国管理信息化,2006,(2):64.[4] 杨鉴淞. 基于Excel的盈亏平衡分析在投资项目不确定性分析中应用[J]. 中国管理信息化,2006,(4):9.。