本量利Excel敏感分析方法（单选按钮控制）

1.敏感分析

敏感分析是在求得某个数学模型的最优解后，研究该模型中某个或若干个参数允许变化到何种范围，仍能使原最优解保持不变；或当参数变化超出允许范围，原最优解已不能保持最优性时，提供一种简便的计算方法，重新求得最优解。在进行利润敏感分析时，为了简化计算，假设利润只受销售单价、单位变动成本、销售量和固定成本总额的影响，并且假设各因素均独立变动，即其中一个因素变动不会引起其他因素的变动。反映利润敏感性的指标是敏感系数，它说明各有关因素变动对利润的影响程度。敏感系数计算公式如下：

某参数敏感系数＝利润变动百分比÷该参数变动百分比

2.Excel数据验证（有效性）

Excel中为了保证录入数据的准确性，可通过设置“数据验证”（Excel 2013—Excel 2019）或“数据有效性”（Excel 2010及其以前版本）的方法，在录入前提示输入信息、录入出错时提示错误信息，以便准确理解所要录入数据的经济含义，或在出错时及时发现更正。

3.除余MOD函数

在Excel数学与三角函数中，MOD函数是一个求余函数，即是两个数值表达式作除法运算后的余数。函数公式为：

＝MOD(number，divisor)

式中，number为被除数；divisor为除数；其算法为MOD（n，d）＝n-d∗Int（n/d），并与除数的±号相同。

例如：＝MOD（23，2）的计算结果为“1”；　＝MOD（25.16，4）的计算结果为“1.16”；＝MOD（13，0）的计算结果为“＃DIV/0！”。

再如：由于算法为MOD（n，d）＝n-d∗Int（n/d），并与除数的±号相同。所以，＝MOD（-18，5）计算结果为“2”，＝MOD（-246，5）的计算结果为“4”，＝MOD（-247.07，-5）计算结果为“-2.07”，　＝MOD（29.07，-5）的计算结果为“-0.93”，　＝MOD（29.07，5）计算结果为“4.07”。

某产品销售单价20元，预计销量3万台，单位变动成本12元，固定成本总额9万元。要求计算这4个因素独立变动1%时各自的敏感系数，确定它们对利润、保本点的影响情况。

Excel中设计敏感分析模型如图6-21所示。在销售单价不符合规定时将警告并提示修改；通过单选按钮确定其业务数据后，自动计算各因素的敏感系数。

图6-21　本量利敏感分析与单选按钮

(https://www.xing528.com)

（1）在“Excel财务会计”工作簿中新建“本量利敏感”工作表，在该表录入A列、B列、第2行的文字（不录入C3至G9单元区域），合并单元区域。

（2）设置数据验证（有效性），以便在录入前、录入出错时提示，方法如下。

a.选定C3单元格，在Excel 2003及其以前版本中选择“数据/有效性”菜单命令，在Excel 2007、Excel 2010中选择“数据/数据工具/数据有效性”命令，在Excel 2013—Excel 2019中选择“数据/数据工具/数据验证”命令，进入“数所验证（有效性）”对话框，该对话框有设置、输入法模式等4个卡片。

b.在“设置”卡片中选择允许为“整数”，数据为“介于”，键入最小值“15”、最大值“25”，如图6-22（a）所示。在“输入信息”卡片的标题框中键入“销售单价”，输入信息框中键入“值为15～25元整数”。在“出错警告”卡片的样式中选择“停止”，在标题中键入“出错了！”，在错误信息框中键入“您录入的值在15～25元之外，或不是整数！”，如图6-22（b）所示。单击“确定”按钮。

图6-22　数据验证的设置（a）与出错警告（b）卡片

（3）在C3至C6中录入已知数据。当选定C3单元格时，将显示“输入信息”卡片设置的内容；如果键入C3单元格的值有错，则弹出警告对话框，直到修改正确为止，如图6-23（a）所示。

（4）插入单选按钮与代码取值，方法如下。

a.单击窗体工具（Excel 2003及其以前版本）或表单控件（Excel 2007—Excel 2019）上的单选按钮，在D3单元格中拖动一个适当大小的单选按钮控件，再将其复制粘贴到D4、D5、D6单元格。

b.设置控件格式。右击D3单元格中的单选按钮，进入“设置控件格式”对话框，如图6-23（b）所示；在单元格链接中键入“＄D＄3”，勾选“三维阴影”选项，单击“确定”按钮。

图6-23　输入出错警告（a）与单选按钮格式（b）设置

c.代码取值。以上单元格链接的设置对本工作表所有单选按钮生效，即所有单选按钮的代码值将显示于D3单元格中，并按其创建的先后分别显示为1，2，3，…。由于是单变量敏感分析（每一因素均独立变动），所以其变动率公式为：E3＝IF（D3＝1，1%，0）、E4＝IF（D3＝2，1%，0）、E5＝IF（D3＝3，1%，0）、E6＝IF（D3＝4，1%，0）。

d.隐藏设计代码。将D3单元格的字体颜色设置为白色而隐藏。

（5）自变量变动后状况。F3＝C3∗（1＋E3）、F4＝C4∗（1＋E4）、F5＝C5∗（1＋E5）、F6＝C6∗（1＋E6）。

（6）因变量相关公式。目标利润各单元格公式为：C7＝（C3-C5）∗C4-C6、E7＝（F7-C7）/C7、F7＝（F3-F5）∗F4-F6；保本销量各单元公式为：C8＝C6/（C3-C5）、E8＝（F8-C8）/C8、F8＝F6/（F3-F5）；保本点销售额各单元格公式为：C9＝C3∗C8、E9＝（F9-C9）/C9、F9＝F3∗F8。

（7）键入敏感系数公式。在G3单元格键入敏感系公式“＝ABS（IF（E3＝0，0，＄E＄7/E3））”。这是因为：自变量的变动率为0时作分母将出现计算错误，所以用条件IF函数将错误值转换为0；又因为成本上升时敏感系数为负数，所以用ABS函数取绝对值。

（8）将0值显示为“-”。选定G3至G6单元区域，右击选择并进入“设置单元格格式”对话框；选定“数字”卡片的“自定义”项，在右部选择或录入千位分隔，保留两位小数，负数用小括号，0值显示为“-”的格式“＿-＃，＃＃0.00＿-；（＃，＃＃0.00）；＿-"-"＿-”。