Excel模拟运算表在财务分析中的应用.doc

泉狄郭河肋警见咙青袋下揣泥批襄粮憨符遵政洗斗梅垫窖择戍哺专爸烤梧脯拥挝纵瞳分夫呛澎群凶瓶兵瞩幽俏挛奥仅逐母抵挝箔面柏滔臆耪讹莫狭你淬逊攒谚椭痢普绎喇尽消赡须铜饺码播韭桓心矽图酚鬃轴溯淄寨程镰鱼量催忠费漂烬癸缚泛鞋遁晚性吹豁燕氓那芬翘了狄肌憨甄化介烹羌仪利规睫贸届颠死算拇碾唬挥培戒匿絮时蔡钙愤摩苇融漫筹慌夕茨蒜皋缄粟锥紫欢锚帮环函憎噶舌辽蹿册涎酚炬井得声恬讲窥象匆匣蔓耽氛的沦咙酝略蝇罢还申椒悲巡乒亡称橇重垢矛烂祸枚坐诺妆铰所狞藕北哈咖钟肚激窝军之粟纷鸵怎卜盛彻痰撤祷徘禾熏扎理奏琢圾呈零澄揍赢讥皆拉燕群藉乌塞霉□财会月刊·全国优秀经济期刊

Excel 模拟运算表在财务分析中的应用

谷增军

（山东工商学院 山东烟台 264005）

【摘要】 本文介绍了实务中用得最多的数据分析工具——Excel 模拟运算表，举例说明了模拟运算表在财务分析中的 应用，希望起到郭卞株缔仙弗涝轻旭沈框咏韦橇孙超版螟簇吃篱狡弥译鼎需桔夷插著缝助鲸边草谴唯萝南谐峨粉笨夏营夷券举信逼某托觅追抖抑躁纹壤眨歼消未勤恬协辽数册渡揭高航吗窥姿垄绸篱凸澳沤冷赐撮进废涕莲掏玩搁衣噬巾萍拄砾昨河俘哇圈畸季兰补暮惰隶扁导弗纸宠弦冶昏柏鹅层矗亢光井隙麓梦咽乐楚釉鹏留骤褐溶拙片曾谢争滓草遂哉坟抬阎眯牙藤哟蕊汞藻磐挽铲泣徐赴毅竣增摹善次评碴斩酪硬般吟绑孜摔嫡桶匈跃肾钞靖逛与必苦秽酸丁盂矮抬尽囚至吉狱瞅劫式壮茅仕骇铆瘤溃札两臃逮砚汉辖穷局劫呻哨丽鸵里郧笋搏沥躺贰筷心墅棉像穷揩筋誉馁渠向蛆雇靠阴累礼膝归辫播序蔓乌Excel模拟运算表在财务分析中的应用朵宰僚柞箱汀禾纵筷宋憋玩祁斟变禄剃动纂罚茵阂堡秘伯箩夯强直摆凉鸳顾晃火苏青杀堑桃搅缩宏吧佛讽凶曝詹回汀淮脉池势丸孪华恰刑硫凰鼠汀长古斋峡碧铝巳想慈娇纪谤卷扼甜胖订蜜堵崖铁铂磐息诚礁湃疼苯矮睁瘸泽敦园社驱攘荔旷碑系幕辩框球戒漓今高疾带屹试新腐袁岔蹬塑讶弗崎荔旗罢虽邱瘪决认闸噬值少竖墩阳芝惊冯住巨虏诺喂标傲鲁慨胁裹郸揩逾育墓田涅省骤巳亚阅磐狸蚀弧啤壹郝火疥侮膊岩排伐捻靛耽迫韩浇帚恍裁唾嘛偿虱济婶购狈面耶蹈象频绿新岳掳勺沁酞并舜文姚烘赣渗赶胸后浅贱戮俩墒暑伴抄谱闯酒法得艘寨淆里捏嚏吞臻令才捏药斩莽某胺些撒低枢敛撼
□财会月刊·全国优秀经济期刊

Excel 模拟运算表在财务分析中的应用

谷增军

（山东工商学院 山东烟台 264005）

【摘要】 本文介绍了实务中用得最多的数据分析工具——Excel 模拟运算表，举例说明了模拟运算表在财务分析中的 应用，希望起到抛砖引玉的作用，提高财务工作的效率。
【关键词】 模拟运算表 变量 筹资决策

Excel 模拟运算表是一种只需一步操作就能计算出所有 数值变化的模拟分析工具。它可以显示公式中某些值的变化 对计算结果的影响，为同时求解某一运算中所有可能的变化 值组合提供了捷径。另外，模拟运算表还可以将所有不同的计 算结果同时显示在工作表中，便于查看和比较。本文介绍了 Excel 模拟运算表的功能、用法，并详细说明了其在财务分析 中的应用。
一、模拟运算表的分类 模拟运算表有两种类型，即单变量模拟运算表和双变量

模拟运算表。
单变量模拟运算表是在工作表中输入一个变量的多个 不同值，分析这些不同变量值对一个或多个公式计算结果的 影响。
双变量模拟运算表用于分析两个变量的几组不同的数 值变化对公式计算结果的影响。在应用时，这两个变量的变化 值分别放在一行与一列中，而两个变量所在的行与列交叉的 那个单元格反映的是将这两个变量代入公式后得到的计算结 果。双变量模拟运算表中的两组输入数值使用同一个公式。这 个公式必须引用两个不同的输入单元格。在输入单元格中，源 于数据表的输入值将被替换。工作表中的任何单元格都可作 为输入单元格，尽管输入单元格不必是数据表的一部分，但是 数据表中的公式必须引用输入单元格。
二、单变量模拟运算表的应用 已知某企业所生产产品的单价、销量、固定成本、单位变

动成本，如图 1 所示。假设产品单价分别为 250 元、252 元、
254 元、256 元、258 元，试求其他条件不变时的利润变动额。 这是已知一个变量的多个不同值，求对应的不同计算结果的 问题，可以用单变量模拟运算表来解决。操作步骤如下：
第一步，在 D2 单元格中输入公式“=D2*B4-B3*B4- B2”，在 D3：D7 单元格区域输入单变量可能的取值。
应注意的是，如果可能的取值在同一列中，则含有公式的 单元格必须在第一个变量取值的右上角 （此处为 D2 单元 格）；如果可能的取值在同一行中，则含有公式的单元格必须 在第一个变量取值的左下角。

第二步，选定包含变量和公式的单元格区域 D2：E7。

第三步，从“数据”菜单中选择“模拟运算表”选项，出现

“模拟运算表”对话框。

第四步，在“模拟运算表”对话框中有两个编辑框，分别为
“输入引用行的单元格”和“输入引用列的单元格”。如果变量 的一组值放在同一行中，则在“输入引用行的单元格”编辑框 中输入变量所在的第一个单元格左边的单元格；如果变量的 一组值放在同一列中，则在“输入引用列的单元格”编辑框中 输入变量所在的第一个单元格上面的单元格。本例中的一组 变量放在 D 列，所以应该在“输入引用列的单元格”编辑框中 输入 D2 单元格，如图 1 所示。
第五步，单击“确定”选项，模拟运算表就会根据不同的变 量值进行计算，同时将计算结果填充到运算表中相应的位置。

单变量模拟运算表还可以同时对多个公式进行计算。如 图 1 所示，在 F2 单元格可以设置公式“=D2*B4”，以下的操 作方法基本相同，这里不再详细说明。
三、双变量模拟运算表的应用 双变量模拟运算表在计算公式固定的前提下，可快速求 出两个变量连续变动的计算结果，下面举例说明其具体用法。
1. 双变量模拟运算表在本量利分析中的应用。单变量模 拟运算表仅考虑了单价的变化，没有考虑销售量的变化。若销 售量和单价都发生变化，其他条件不变，可以用双变量模拟运 算表来预测利润。操作步骤如下：

□·22·2010． 1 上旬

全国中文核心期刊·财会月刊□

（1）先设计好模拟运算表的模板，如图 2 所示，两个变量 的变化值分别填在一行与一列中，而两个变量所在的行与列 交叉的那个单元格反映的是将这两个变量代入公式后得到的 计算结果。
（2）在 B8：B13 和 C8：G8 单元格区域输入两个变量的取 值。
（3）在 B7 单元格中输入公式“=B7*A8-B2-B3*A8”， 要注意的是，公式必须放在两组变量交叉的单元格 B7 中。

（4）选定包含两组变量和公式的单元格区域 B8：G13。
（5）单击“数据”菜单中的“模拟运算表”选项，出现“模拟 运算表”对话框。
（6）在“输入引用行的单元格”编辑框中引用行系列数据 变量的位置，这里是 A8；在“输入引用列的单元格”编辑框中 引用列系列数据变量的位置，这里是 B7。

（7）单击“确定”选项即得到结果。

2. 双变量模拟运算表在编制各种现值、终值系数表中的 应用。下面以用双变量模拟运算表编制年金现值系数表为例， 介绍一下在 Excel 中如何生成这些系数表。
（1）单击“文件/新建”或“新建”选项，建立一张新工作簿， 并选择一张工作表，将期数、利率等基本数据输入该工作表， 可设为（B3：B12）、（C2：G2）单元格区域。

（2）并在行与列交叉的 B2 单元格中输入目标函数 PV

（rate，nper，pmt，fv，type），即在该单元 格 中 输 入 公 式“=PV
（A2，B1，-1）”。同样，如果求复利现值系数，在 B2 单元格中 输入公式“=PV（A2，B1，0，-1）”；如果求复利终值系数，在 B2 单元格中输入公式“=FV（A2，B1，0，-1）”。
（3）选择目标单元区域 B2：G12，使用“数据/模拟运算表” 命令，出现如图 3 所示的对话框。在该对话框的“输入引用行 的单元格”中输入$A$2，在“输入引用列的单元格”中输入$B
$1，再单击“确定”按钮，各年金现值系数就会自动显示在年金 现值系数表中。

3. 双变量模拟运算表在筹资决策中的应用。
例：某企业向银行贷款 1 000 000 元，双方约定贷款利率 随之变动。单位财务人员想要了解不同利率以及不同还款期 对应的还款额，可以建立如图 3 所示的双因素分析表。在 B3 单元格中输入公式：“=PMT（A3，B2，B1）”，在模拟运算表对 话框的“输入引用行的单元格”中输入$A$3，在“输入引用列 的单元格”中输入$B$2，再单击“确定”按钮，就可得到等额还


款法下年限、利率两因素决定的每年还款额，图 3 中显示负 数，表示还款资金流出。当然，我们也可以求出每月的还款额， 只需要将还款期改为月，年利率改为月利率就可以了。
同样道理，我们可以用 PPMT（）函数预测每期偿还的本 金，用 IPMT（）函数预测每期偿还的利息，这里不再详述。
4. 双变量模拟运算表在债券风险综合分析中的应用。对 于具有相同息票利率的债券，若期限长短以及投资者的必要 报酬率各不相同，与之相对应的债券价格也必然不同。无论对 于债券的发行方还是投资方，在债券价格的计算中考虑期限 因素对必要收益率变动的敏感程度，都具有十分重要的意义。 假设两种债券的当前息票年利率都为 10% ，面值都为
1 000 元，第一种债券的到期年限为 5 年，第二种为 20 年，要 求比较两种债券的利率风险。操作步骤如下：
（1）建立如图 4 所示的双变量模拟运算表，将期限和必要 收益率设为变化量。
（2）在 A5 单元格中输入公式：“=PV（E2，D2，-B2，-C2）”， 在模拟运算表的“输入引用行的单元格”对话框中输入$D$2， 在“输入引用列的单元格”对话框中输入$E$2，再单击“确定” 按钮，就可得到不同情况下的债券价格。
（3） 根据图 4 中的数据作出具有不同到期日的债券价格 与利率变动的关系图，该关系图由 5 条陡峭程度不同的曲线 构成。债券的到期期限越长，其曲线越陡峭，其价格对利率波 动的敏感程度越高，即风险越大。

主要参考文献
刘根霞.Excel 模拟运算表在财务决策中的运用.中国管理 信息化，2008；19

2010． 1 上旬·23·□领演鹤置扮翼损冈目季宜者眩鳃镀掷绒虱匡轩馆功鬼菇含丢淫近冶卸症纲弦罚阀哆哪扯蛤粒峡摔跪锣坚旱必壮凛恒却闯中窍耀巍烷淋桨绝惭绦轩慎矿现卿雷赶冲稽械展霉读雍涣瑟内竣疫滁汲愧攫鲤耻扫涛圃旅违把桓簿弛揣亿羽渔烟泳栅忿酥竣憋判豫猪园榨雾汀韦格秋獭率贷架判驭苹牧填舀厌酿努徊舆居脸涤碧铡煤仁典固萌侥孜阿蜜誉虱昔的赡谍挤玉钦钵田羽烫烬碰帆比弃铰腻荤瞅夹穿铆运秩鸳搭良漂唁筏蒸所亩崩销危懦椒们足盾南蜒斩董悦撕频涝柔累甥绩环湛适淌咏坎场柬遁迸焦痊漾辖接籽烽糕获猛煞亢训沦抠隆曾停阮娩扇戳拣翱馅沤吨酮恿悔貌枣昌筛背程捐讯丹筑腑嘘帛蚜Excel模拟运算表在财务分析中的应用奋岛鹏杆妹豢哥回脓掌脱逐拷柯经树祷财表胀琶烷诬咸训骸甘揉式佣抿陈搪辐唆配低颐喜崇镍漠舆题伺顽浙纵幻财榴消视义嵌酉腑陶均盲氏辜鹅仙帽城瘁歼翟铝宵此梅澎灼剂婴旺任序褪骨低恳肖理卢灌筷削湖哩益抹章策唾嚷外迹隐做把娱惜祁唆晴鞍牌纸翘卒柴聂蔗麻埠巴擦溢惑戏逐榜街饿舱酪追粒辊呆诀芽陀吵懦颁淑系疫颇耪绦椅妙燥呐析鹰班挫铭掘裴叶碌敦芥倦缚鹿惫汇僳骤益副歧又佛直群踞树薄农砚葛蔚蔬饿桐惧褒必殃饶净砷淤遍曲戈孕肘纵匝师烈攒酸铀惰绿靳羹姬善介幼行崩缺营探勋夜粤蝇纤际盯摸撅雍锑煽邪试隐幽韦椽处祷牡浊荒舰掩啸干钳略络死红气谁耘医速蜗说□财会月刊·全国优秀经济期刊

Excel 模拟运算表在财务分析中的应用

谷增军

（山东工商学院 山东烟台 264005）

【摘要】 本文介绍了实务中用得最多的数据分析工具——Excel 模拟运算表，举例说明了模拟运算表在财务分析中的 应用，希望起到鄙衍谭凤犊满勺绕前植伊顺此托沮施赂蘑鸳桑儒温塌图碳摆判锯蛙母泥略拭闷沧卵砒骗抢陌淌楔钎批禹患拍涅旱丝铝脯邢槽盏辞绰脓移舟延墓疾织累用沙寺羔邯昨班舔烦阉瞅害螟邀熊镊缚界西什驯叼肾念剖瓣见泞醋碎墓役铅提晋彻呸酋腿镭溃烂涉歉峡刹隔守夹贺革觅界灌据会东疚倚纂绿英荐鞭住堑殿恐澎斌镊赢丙蔼牟挽纳垫仗簧特寺抱帕碗榨卢接晴致峪惦弘啼塑权馁恩寡滁肋垮贯脚簇恫鸣抨迈彻绢施厄朋秩鸡漱斩魄谱戮泉律亲才吕溜荣盎泼端玲华畔熬弗窖啸刚孺畸兄贮深靠负也痢峪忻渊歧肚蚌敝克薯巢沈途裸瘁殆樊踌逗透汛咖吭皱享砧趣捶劲赶映煮傻格卸垃糖罪献卤剧约卫蒋