在excel中如何进行敏感性分析

[在EXCEL中如何编写程序]基本思路基础实现方法同上篇文章《直接通过odbc读、写excel表格文件》相同，都是通过odbc来把excel表格文件当成数据库文件来进行读、写等操作，所以在excel表格文件中写入的行...+阅读

在excel中如何进行敏感性分析

一、投资项目敏感性分析涉及的计算公式营业现金流量=营业收入-付现成本-所得税 =税后净利润+折旧 =（营业收入-营业成本）*（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这三个单元格公式的定义。

第三步，定义单元格格式：C12:C15、F12:F15区域为“百分比”格式，并且保留两位小数。其余数字区域为“常规”格式，其中G12:G15区域里单元格数据保留两位小数，D12:E15区域里单元格数据保留到整数。第四步，设计微调按钮。

（1）如果窗体工具按钮没有在工具栏中出现，则依次单击“视图”、“工具栏”、“窗体”，以调出窗体工具按钮；

（2）单击“窗体”工具栏中的“微调项”按扭，当鼠标光标变为十字状时，在B12单元格中画一个矩形框，这时会出现一个微调按钮形状；

（3）用鼠标右键单击画好的微调按钮，在打开的快捷菜单中选择“设置控件格式”命令，再在打开的对话框中选择“控制”选项，进入控件格式参数的设置状态；

（4）设营业收入的波动幅度在50%~50%之间，此时需要将微调项的参数设置为：最小值为0，最大值为100，步长为l，单元格链接到$B$12; (5)用复制的办法，分别在B13、B14、B15这三个单元格中画出-微调项按钮；

（6）假设付现成本的波动幅度在-50%~50%之间，那么B13单元格中的微调按钮的参数应设置为：最小值为0，最大值为100，单元格链接到$B$13; (7)假设所得税税率的变化幅度在50%~0之间，那么B14单元格中微调按钮的参数需设置为：最小值为0，最大值为50，步长为1，单元格链接到$B$14; (8)假设投资额减增幅度在-50%~150%之间，那么B15单元格的微调按扭的参数需设置为：最小值为0，最大值为200，步长为1，单元格链接到$B$15。

实际变动百分比的计算在C12、C13、C14、C15单元格中，B12、B13、B14、B15单元格只是起到调整和传递数据作用。设置微调按钮控件格式时均要选中“三维阴影”选项。

三、投资项目敏感性分析模型的使用

（1）分析因素变化对投资项目的影响结果。通过因素变动调整按钮，观察各因素发生单一变化或者组合变化时对净现值的影响。比如，如果企业能利用所得税的税收优惠政策，可以很方便地观察到，当税负减少到原有一半时净现值发生变化后的结果。

（2）计算各因素的敏感系数。只改变其中的一个因素值，可以测算出某个因素变化对净现值影响的敏感系数（敏感程度）。敏感系数说明，在进行决策时以及执行决策过程中要慎重对待敏感性强的影响因素。敏感系数为正数的，表明它与净现值为同向增减；敏感系数为负数的，表明它与净现值为反向增减。敏感系数绝对值的大小则说明敏感程度的强弱。

通过微调按钮，可以方便得出分析结论，上例中四个因素的敏感性由强到弱的顺序是：年营业收入、年付现成本、初始投资额、所得税税率。

（3）进行因素变动的极限分析。观察某个因素在多大范围内变动才不会影响原定投资项目决策的有效性。具体方法是，保持其他因素不变，调整某个因素的数值，直到投资项目的净现值小于0，则此时的方案不能被接受。

由于客观外部环境不断变化，如投资增减、成本升降、价格涨跌等，而且主观上也会存在偏差，如项目提前或拖后完成，项目投产后生产能力估计、产品销售估计有较大出入等，因此有必要对决策方案能否经受住这些变动进行分析。因此，根据投资项目敏感性分析的基本应用方法，可以对投资方案选择进行决策分析。遇到不利变化时不敏感，比较稳定，而遇到有利变化却能倍增经济效益，这种方案应是选取的最优方案。

如何用EXCEL画工可里的敏感性分析图

可以，但要求对Excel有比较深的了解，用Excel来进行敏感性分析属于Excel比较高级的应用了。主要是要用到模拟运算。下面是本人几年前在Excel Home论坛上发表的：[分享]运用模拟运算表进行财务评价中的敏感性分析工程建设的可研报告评价中敏感性分析是进行项目风险分析的重要方法，通过对投资、成本、产量及售价变化对收62616964757a686964616fe58685e5aeb931333330353563益率的影响程度，来分析项目投资风险程度。在可研报告中，同时提供敏感性分析以表格和趋势图。对于已按基准资料计算出的收益率，如何得到投资、成本、产量及售价变化分别发生变化时，收益率的不同值呢？当然我们可以对一个个参数分别改变其数值来求得不同的收益率，并分别记下各数据得到敏感性分析表，进而生成敏感性分析图。

但这样不但繁琐，工作量大，且失去动态性。当计算中的其它参数变化时就得重新一个个计算。而运用Excel的模拟运算能很好地解决这一问题，本人在实际工作中成功地运用模拟运算来作可研的敏感性分析，当敏感性分析发现投资有较大风险时，及时与业主协商，调整生产规模或投资计划。调整时敏感性分析图能及时地表现出来。收到了很好的效果。老朽在本论坛得到了不少朋友的帮助，受益匪浅。为感谢本论坛，特将老朽这一数年来的心血之作贡献出来，希望能引致抛砖引玉的作用。尚存在的一个小问题：四个临界点只能手工调节，使模拟表中相应的收益率与基准收益率相同时来得到。曾试过单变量求解和规划求解，都不能得到正确的结果。好在只四个数据的计算，调整的工作量不大。

希望高手能找出更好的解决办法加以完善。可参看下面链接中的141楼。...

如何用Excel做决策支持系统的敏感度分析

以一具体实例来阐明Excel在经济数学模型中的应用。原料配比问题表一原料药物甲乙丙丁 A 1 1 1 1 B 5 4 6 5 C 2 1 1 2 某药厂生产A、B、C三种药物，可供选择的原料有甲、乙、丙、丁四种，成本分别是每公斤5元、6元、7元、8元。每公斤不同原料所能提供的各种药物如表一所示。药厂要求每天生产A药品恰好100克、B药品至少530克、C药品不超过e79fa5e98193e58685e5aeb931333332623962160克。要求选配各种原料的数量，即满足生产的需要，又使总成本最少。求解方法：

（1）建立简单的数学模型。根据题意，设X

1、X

2、X

3、X4分别表示甲、乙、丙、丁原料的用量，易得到如下线性规划：目标函数： Min Z=5X1+6X2+7X3+8X4 约束条件： X1+X2+X3+X4=100 5X1+4X2+5X3+6X4≥530 2X1+X2+X3+2X4≤160 X1≥0,X2≥0,X3≥0 (2)将该线性规划问题的数学模型按表二样式输入Excel中，在表二中，有关单元格所含公式如下：单元格公式 C5 =D3*D5+E3*E5+F3*F5+G3*G5 C6 =D3*D6+E3*E6+F3*F6+G3*G6 C7 =D3*D7+E3*E7+F3*F7+G3*G7 C8 =D2*D3+E2*E3+F2*F3+G2*G3 (3)选择“工具”菜单中“加载宏”选项，在安装提示下装入“规划求解”（注意要插入安装盘）。也可以把安装盘中“Pfiles\Office\Library”下的Solver文件夹及其目录下的Solver.xla、Solvr32.dll复制到Office安装目录“Office\Library”下，然后加载即可。

（4）在“工具”菜单中选择“规划求解”，然后在弹出的“规划求解参数”对话框中通过点击C8单元格使“目标单元格”出现$C$8的绝对引址，并根据本题题意在其后的小框框内选择“最小值”。在“可变单元格”中通过从表格中选择D3:G3区域，使之在文本框内出现$D$3:$G$3。在“约束条件”处按“增加”，然后在出现的“增加约束”对话框中的“单元格引用位置”处通过点击C5单元格使之出现$C$5，在后面的框框内选“=”，“约束值”编辑为$B$5。类似地，第二、三、四个约束条件分别编辑为“$C$6≥$B$6”,“$C$7≤$B$7”,“$D$3:$G$3≥0”. 按“确定”退出。

（5）按“求解”按钮，在弹出的“规划求解结果”对话框内可根据需要生成运算结果、敏感性分析和限制范围的报告，然后按“确定”对模型进行求解。

（6）如发现数字解为小数，可按需要该为用整数表示，方法如下： ① 按住Ctrl键，分别选定需改为用整数表示的单元格D

3、E

3、F

3、G

3、C8。 ② 选取“格式”、“单元格… …”、“数字”、“科学计数”。 ③ 在“小数位数”中选定“0”格式。按“确定”退出。

（7）根据以上步骤，可得到本模型的计算结果如表三所示。从表三可以看出，当甲30公斤、丙40公斤、丁30>公斤而乙为0时，成本达到最小，最小成本为670元。表二 A B C D E F G 1 甲乙丙丁 2 数量 5 6 7 8 3 单价 1 1 1 1 4 约束条件最适结果 5 a 100 1 1 1 1 6 b 530 5 4 5 6 7 c 160 2 1 1 2 8 总成本表三 A B C D E F G 甲乙丙丁 2 数量 5 6 7 8 3 单价 1 1 1 1 4 约束条件最适结果 5 a 100 100 1 1 1 1 6 b 530 530 5 4 5 6 7 c 160 160 2 1 1 2 8 总成本 670 用Excel的规划求解工具线性规划问题，简单易行，很容易掌握。其规律及技巧可归纳为：在实际的求解过程中，只需确定目标函数单元格及“可变单元格”区域位置两处单元格位置，然后正确地输入约束条件和确定所求的目标是最大还是最小即可求得正确结果。利用Excel提供的规划求解法可以解运筹学中的许多问题，譬如线性规划、指派问题、运输问题、机器分配问题、人事安排… …等，只要是对生产、制造、投资、财务、工程等求最大利润、最小成本等问题，就基本上可以用规划求解法快速得到答案。

怎么用excel2007求敏感性分析和盈亏平衡分析求助啊坐等非常感谢

我只有03的步骤，作为参考吧！盈亏平衡分析例2：假定生产产品A时，年固定成本为50000元，年产销量，单位售价，单位变动成本同上。试分析生产该产品的盈亏平衡情况。盈亏平衡分析 1. 制工作表2：盈亏平衡分析的公式：销售收益 = 产销量 * 单位售价总成本 = 固定成本 + 产销量 * 单位变动成本利润 = 销售收益 – 总成本产销量盈亏平衡点 = 固定成本 / （单位售价 – 单位变动成本）在B2: C6单元格：输入已知的条件：B2:C2格用乙机器生产产品A B3格固定成本、B4格年产销量、B5格单位售价、B6格单位变动成本、C3:C6格输入已知数据：C3 50000 C49000 C510 C6 6 在B8:C10单元格输入： B8格销售收益、B9格总成本、B10格利润在 C8输入 =C4*C5 C9输入 =C3+C4*C6 C10输入 =C8-C9 选定B2:C10 套框同上在B12:C15输入：B12格盈亏平衡时产销量、B13格销售收益、B14 格总成本、B15格利润在 C12输入 =C3 /(C5-C6) C13输入 =C12*C5 C14输入 =C3+C12*C6 C15输入 =C13-C14 选定B12:C15 套框同上作一维模拟运算表，分析在不同产销量的情况下，销售收益、总成本、利润的变化。

在E2:H16输入：E2格年产销量、F2格销售收益、G2格总成本、H2格利润。在 E4格输入 8000 工具栏选编辑 \ 填充 \ 序列 \ 选列、等差序列、步长值 1000 、终止值20000 确定 F3格输入 =C8 、G3格输入 =C9 、H3格输入 =C10 选定E3:H16 工具栏选择数据 \ 模拟运算表 \ 输入引入列单元格 $C$4 \ 确定会自动填充数据反映和利润随产销量的变化情况选定 E2:H16 套框同上数字换位：选定数字区域工具栏选格式 \ 单元格 \ 数字 \ 数值 \ 小数点后0位数、使用千分位分隔符、确定 2. 制图表2：用模拟运算表中销售收益、总成本数据作XY散点图。选工作表2、选定 E2:G16 工具栏选图表向导 \ 标准类型 \ XY 散点图 \ 无数据平滑线散点图 \ 下一步 \ 列下一步 \ 选标题图表标题输入盈亏平衡分析数值x轴a输入产销量数值y轴v输入金额 \ 下一步 \ 选新工作表完成在图表上调整XY轴数据，右击X轴 \ 坐标轴格式 \ 选刻度最小值 8000、最大值20000、主要刻度2000、次要刻度200、数值Y轴、交叉于0、确定右击Y轴 \ 坐标轴格式 \ 选刻度最小值0、最大值250000、主要刻度50000、次要刻度10000、数值X轴、交叉于0、工作表2作经过盈亏平衡点的直线数据：I3格输入经过盈亏平衡的直线数据：在J4:K6画盈亏平衡线的X数据Y数据在J4输入公式 = $C$12然后将J4复制到J5:J6 在K4输入Y轴的最小值0 在K6输入Y轴的最大值250000 在K5输入销售收益和总成本交叉时的Y值， = C13或=C14 选定J4:K6，套框同上。

选定J4:K6 单击工具栏复制 \ 选图表2 工具栏选编辑 \ 选择性粘贴选新系列、列、首列为分类X值、确定图中加上一条经过盈亏平衡点的垂直线选垂线右击选数据系列格式 \ 图案 \线形自动 \数据标记无样式前景颜色无、背景颜色无、大小 6磅 \ 数据标志显示值确定鼠标箭头指向图表背景墙显示图表区域右击 \ 数据源 \ 选定系列 1 在名称栏输入销售收益、选定系列 2 在名称栏输入总成本、选定系列3 在名称栏输入盈亏平衡线确定。 3. 设置滚动条：在工具栏选择绘图 \ 自选图形 \ 方框拖入合适位置 \ 视图 \ 工具栏 \ 窗体 \ 滚动条 \ 拖到方框中 \再选绘图 \ 自选图形 \ 方框两个至滚动条上方 \右击大框 \ 添加文字输入单位售价 = 选表2 D5格输入 100 回图表2 选定小方框在编辑栏上输入 = 并单击表2 选定 D5 回车滚动条参数设置右击滚动条 \ 设置控件格式 \ 控制 \ 当前值100、最小值85、最大值120、步长5、页步长10、单元格链接 sheet2!$D$5 确定 \ 选表2 C5单元格改为 = D5/10 回车返回图表2