技术经济实训实验

实验一：EXCEL在资金等值换算方面的应用

一．【实验目的】

1.了解Excel中的FV、PV和PMT函数；

2.掌握应用Excel函数进行资金等值换算的方法。

二．【实验内容】

1.了解资金等值换算的理论

2.了解时值、现值、终值、年金、贴现和贴现率的概念 3.掌握Excel函数在资金等值换算中的应用。 4.熟练运用资金等值换算的基本公式

三．实验练习内容

已知某企业要购进一台1000万元的设备，全部向银行贷款。利率为8%，在10年内等额还清。设备寿命期也为10年，无残值。第一年的维修费用为10万元，以后每年递增1万元。试求：（1）每年给银行的还款额（2）总维修费用的现值（3）在第10年末，该企业为这台设备一共支付的款项。

四．实验过程及结果

1. 试求每年给银行的还款额。具体过程如下：

(1)启动Excel软件。点击工具栏上的“fx”按钮，弹出“插入函数”对话框。先在“选择类别”中选择“财务”，然后在 “选择函数（N）”栏中选择“PMT”。最后点击对话框下端的“确定”按钮。

(2)在弹出的PMT函数对话框中，Rate栏键入8%，Nper栏键入10，pV栏键入1000 ,然后单击“确定”按钮。

(3) 单元格A1中显示计算结果为-149.03。

2. 试求总维修费用的现值。具体过程如下：

（1）启动Excel,建立下面的工作表，计算现金流量序列的净现值，选中单元格B3，点击工具栏上的“fx”按钮，弹出插入函数对话框。先在“选择类别”中选择“财务”，然后在 “选择函数（N）”栏中选择“NPV。最后点击对话框下端的“确定”按钮。

（2）在弹出的NPV对话框中，Rate栏中键入8%，点击“Value”栏右端的“图标，然后选择单元格“B2：K2”，再点击“然后点击“确定”按钮。

”

”图标，回到NPV函数对话框。

(3) 单元格B3中显示计算结果为93.08。

3. 试求在第10年末，该企业为这台设备一共支付的款项。具体过程如下： 具体分析：在第十年末，企业需要为这台设备支付1000万元的设备终值和十年的维修费用：

（1） 启动Excel软件。点击工具栏上的“fx”按钮，弹出“插入函数”对话框。先在“选择类别”中选择“财务”，然后在 “选择函数（N）”栏中选择“FV”。最后点击对话框下端的“确定”按钮。

（2）在弹出的“FV”函数对话框中，Rate栏键入8％，Nper栏键入10，PV栏键入1000出现该界面

（3）点击确定，在A1表格中出现下面的结果。

（4）同理维修费用的终值操作如下，操作步骤同上

（5）点击确定得出A2表格里的值-200.95

（6）总的付款结果的计算：在”A3”的表格输入“=A1+A2”

（7）点击回车键即可得出如下A3表格里的结果：-2359.88.即为最终值。

实验二：Excel在技术方案经济评价方面的应用

一．【实验目的】

（1）掌握运用Excel计算时间性评价指标； （2）掌握运用Excel计算价值性评价指标； （3）掌握运用Excel计算比率性评价指标；

二．【实验内容】

技术方案经济评价指标概述

投资回收期 时间性指标 追加投资回收期 净现值 经济评价指标 价值性指标 净年值 净终值 净现值率 比率性指标 内部收益率 外部收益率 差额投资内部收益率

Excel函数介绍 NPER函数

NPER（Rate，Pmt，Pv，Fv,Type） 其中：Rate——利率；

Pmt——各期所应收取的金额；

Pv——一系列未来付款当前值的累计和；

Fv－－未来值；Type只有数值0或者1，0或忽略表示收付款时间是期末，1表示收付款时间是期初。

NPER函数计算的是基于固定利率和等额分期付款方式，返回一项投资或贷款的期数。

三：【实验练习内容】

某拟建项目有两个方案可供选择。方案一寿命期为8年，第一年投资100万，第二年到第八年的年净收益为35万；方案二寿命期为6年，第一年投资300万，第2年到第6年的年净收益为100万。贴现率为10％。试根据静态投资回收期、动态投资回收期、净现值、净年值、净现值率和内部收益率指标对两个方案进行比较。

四：【实验练习的过程及结果】

1.根据静态投资回收期比较两个方案

（1）启动Excel软件，建立如图所示的Excel表格，计算累计净现金流量。具体做法是：在单元格B4中键入公式：“＝B3”，在单元格C4中键入公式“＝B4+C3”，然后拖动单元格C4右下角的复制柄，直至单元格J4。

(2) 单元格B5中输入内插公式“＝F2－1-E4/F3”，然后按“回车”键。单元格B5中显示计算结果3.857143，即第一方案的静态投资回收期为3.857143。

(3)同理制作方案二的现金流量表。操作步骤如上面方案一的步骤，得出方案二的静态投资回收期为4

（4）由上面的实验结果得出的结论是：按静态投资回收期指标，Pt1

2. 比较两种方案的动态投资回收期

（1）启动Excel软件，建立如下表格，计算各年净现金流量的现值。具体做法如下：在单元格B4中键入公示：“=PV(10%,B2,,-B3)”，然后拖动单元格B4右下角的复制柄，直至单元格J4.

(2) 计算累计净现值。在单元格B5中键入公式：“＝B4”，在单元格C5中键入公式“＝B5＋C4”，然后拖动单元格C5右下角的复制柄，直至单元格J5。

(3) 在单元格B6中输入内插公示“＝G2－1－F5/G4”，然后按“回车”键。单元格B6的计算结果为4.9604571，即该方案的投资回收期为4.9604571年。

(4)同理方案二的动态投资回收期为5.209583 年，其操作步骤同上面方案一

的动态投资回收期的步骤。

(5) 由上面的实验结果得出的结论是：按动态投资回收期指标，Pt1

3.根据净现值比较两个方案

计算步骤如下：

(1)启动Excel软件，建立如图工作表。

(2) 计算现金流量序列的净现值，选中单元格B4，点击工具栏上的“fx”按钮，弹出插入函数对话框。先在“选择类别”中选择“财务”，然后在 “选择函数（N）”栏中选择“NPV。最后点击对话框下端的“确定”按钮。

(3) 在弹出的NPV对话框中，Rate栏中键入10%，点击“Value”栏右端的“图标，然后选择单元格“C3：J3”，再点击“然后点击“确定”按钮。

”

”图标，回到NPV函数对话框。

(4)结果如下B4的结果是154.90万元。

（5）计算净现值。在单元格B5中输入公式：“＝B3+B4”，得到净现值为54.9万元。

(6) 同理制作方案二的现金流量表。操作步骤如上面方案一的步骤，得出方案二的净现金值为44.62万元。

(7) 由上面的实验结果得出的结论是：按净现值指标，NPV 1>NPV2,净现值越大，方案越好。所以方案一相对较好。

4.根据净年值比较两个方案

计算过程如下：

(1)启动Excel软件，建立如图所示工作表。

(2) 在单元格C6和E6中分别输入以下公式： C7： =C5-PMT(10%,C6,-C4) E7：＝E5-PMT(10%,E6,-E4)

单元格C7和E7中分别显示计算结果为1625598700%和3111.78%，即方案A的净

年值为16.26万元，方案B的净年值为31.12万元

(3) 由上面的实验结果得出的结论是：按净年值指标，NAV 1

5.根据净年值率比较两个方案

方案一的净现值是54.9万元，投资现值是100万元。方案二的净现值是44.62万元，投资现值是300万元。 计算过程如下：

（1） 启动Excel软件，建立如图的工作表。

(2)在单元格C5中输入公式：＝C3/C4，单元格C5中显示的数为 0.549即方案一的净现值率为54.9%；在单元格E5中输入公式：＝E3/E4单元格E5中显示计算结果为0.1487333方案二的净现值率为14.87333%。

(3)由上面的实验结果得出的结论是：按净现值率指标，NPV 1>NPV2,净现值率越大，方案越好。所以方案一相对较好。

6.根据内部收益率指标比较两个方案

计算过程如下：

(1)启动Excel软件，建立如图所示的工作表。

(2)计算现金流量序列的内部收益率。选中单元格B4，点击工具栏上的“fx” 按钮，弹出“插入函数”对话框。先在“选择类别”中选择“财务”，然后在“选择函数”中选择“IRR”。最后点击对话框下的“确定”按钮。

(3)在弹出的IRR函数对话框中，点击“Valuel”栏右端的“择B3:J3，再点击“

”图标，然后选

”图标，回到IRR函数对话框。最后点击“确定”按

（4）单元格B4中显示计算结果为21%，即方案一的内部收益率为21%。

（5）方案二的内部收益率的操作步骤筒方案一的操作步骤一样，因此如图所示，得出方案二的单元格B9中显示计算结果为14％即内部收益率为14%。

（6）由上面的实验结果得出的结论是：按内部收益率指标，IRR 1>IRR2,，内部收益率越大，方案越好。所以方案一相对较好。

实验三 EXCEl在工程技术方案风险分析方面的应用

一．【实验目的】

1.掌握应用Excel进行盈亏平衡分析的方法； 2.掌握应用Excel进行敏感性分析的方法；

3.掌握应用Excel进行蒙特卡罗风险分析的方法。

二、【实验内容】

1.掌握盈亏平衡分析，并能运用此分析方案来进行方案风险分析。 2.会进行敏感性分析。

3．会进行双因素敏感性分析 4.掌握蒙特卡罗风险分析。

三．【实验练习内容及其实验过程和结果】

练习题1:某建设项目年设计生产能力为10000台，产品单台销售价格为800元，

年固定成本为132万元，单台产品可变成本为400元。试求盈亏平衡点的生产能力利用率并作图。 计算步骤如下：

（1） 启动Excel软件，建立如图所示的工作表。

（2）在有关单元格中输入公式： C7：=C4/(C3-C5)； C8：=C7/C2；

得到如图结果。C7表格中显示的是3300即盈亏平衡产量是3300

（3）C8表格中显示的是0.33即盈亏平衡生产能力利用率为33%

（4）接下来绘制盈亏平衡分析图。步骤如下：

建立如图所示的工作表。

(5) 为了作图的需要，我们应界定销量的开始值和终止值。本例中，设开始值为0，终止值为5000。在单元格C8中键入0，在单元格D8中键入2500，在单元格F8中键入5000，然后再单元格C9、C10和C11中分别输入下列公式： C9：=C6*C8； C10：=C5*C8+C4 C11：=C9-C10。

（6）选中C4：C6区域，拖拽其右下角的填充柄至单元格E6。选中C9：C11区域，拖拽其右下角的填充柄至单元格E11。

（7）[转换为Excel2003的版本进行操作]

点击主菜单栏上的“插入”命令，然后在下拉菜单中选择“图表”选项（也可直接点击工具栏上的“

” 即“图表向导”按钮），弹出图表向导-4步

骤之1-图表类型”对话框。在“自定义类型”选项中选择“平滑直线图”然

后单击“下一步”按钮（如果选择“标准类型” 中的“折线图” ，其效果较差）。

(7)在弹出的图标向导-4步骤之2-图表源数据对话框中进行如下操作： 在“数据区域（D）”选项中选择C9：E11区域，并选择系列在“行”产生。

(8)在“系列（S）”选项中将“系列1”、“系列2”和“系列3”的名称分别命名为“收入”、“成本”和“利润”，“分类（X）轴标志（T）”选择为C8：E8区域，然后单击“下一步”按钮。

(9) 在弹出的“图表向导-4步骤之3-图表选项”对话框中进行如下操作： 在“标题”选项中，将“图表标题（T）”命名为“盈亏平衡分析图”，将“分类（X）轴（C）”命名为“产量”，将“数值(Y)轴（V）”命名为“收入/成本/利润”。

（10）在“网格线”选项中，选择“分类（X）轴”中的“主要网格线”。

（11）在“数据标志”选项中选择“值”。 单击“下一步”，再单击“完成”，或直接单击“完成”，在本表中生成盈亏平衡分析图。

【练习题2】某企业一投资方案，项目寿命期8年，初始投资100万元，建设

期1年，第2年到第10年每年销售收入为60万元，经营成本25万元。由于对未来影响经济环境的某些因素把握不大，投资额、经营成本和销售收入均有可能在±20％的范围内变动。以内部收益率为指标，对上述三个不确定因素作单因素敏感性分析。 计算过程如下：

（1） 启动Excel软件，建立如图所示的工作表

（2）计算现金流量序列的内部收益率。选中单元格D3，点击工具栏上的“fx” 按钮，弹出“插入函数”对话框。先在“选择类别”中选择“财务”，然后在“选择函数”中选择“IRR”。最后点击对话框下的“确定”按钮。

（3）在弹出的IRR函数对话框中，点击“Valuel”栏右端的“

”图标，然后

选择D2:N2，再点击“钮。

”图标，回到IRR函数对话框。最后点击“确定”按

(4)单元格D3中显示计算结果为24％。

(5) 求出内部收益率后，开始分析投资额的敏感性。【此时转换成EXCEL2003版本的做】在C3：C12区域中生成一个初值为-20%、终值为20%、步长为5%的数据系列。具体操作：在单元格C3中键入“-20%”，在单元格C12中键入“-15%”，然后选中C3：C4，拖动其右下角的填充柄，直至鼠标右侧的黄色标注显示“20%”。 然后在D生成投资额变动相应百分比之后的数值。具体操作为在单元格D3入公式“=-100*（1+D7）”，拖动其右下角的填充柄，直至单元格D12。在第二行输入相应的年份，然后在第三行输入相应的现金流，选中E3:N3并拖动N3右下角的填充柄填充N12.即可得到入如下图表

（6）选定单元格O3，点击工具栏上的“fx”按钮，弹出“插入函数对话框”。先在选择类别中选择“财务”，然后在“选择函数“中选择“IRR”，最后点击对话框下的“确定”按钮。

（7）在弹出的IRR对话框中，点击”Valuel”栏右端的“择“D3:N3”,再点击“钮。

”图标，然后选

”图标，回到IRR函数对话框。最后点击“确定”按

（8）得出计算结果是24%，拉动O3的填充柄填充至O12，得到结果如下表。

（9）类似的，还可以分析经营成本和销售收入的敏感性。经营成本的变动额如图所示。同时制得的经营变动成本引起的现金流量的变化如图所示。在表格F16中输入“=60-C16”点击回车键，拉动F16的填充柄填充至F25，将 F16至F25表格中的数据复制后，选中G16,点击鼠标右键，在弹出的对话框里，选择“选择性粘贴”再点击“数值”再点击“确认”中，即可将拉动G25右下角的填充柄填充至N25.得到结果如下表F16至F25的数据复制到G16至G25的表格中，然后选中G16至G25拖拽G25右下角的填充柄填充至N25.即可得如下图表

（10）选定单元格O16，点击工具栏上的“fx”按钮，弹出“插入函数对话框”。先在选择类别中选择“财务”，然后在“选择函数“中选择“IRR”，最后点击对话框下的“确定”按钮。

（11）在弹出的IRR对话框中，点击”Valuel”栏右端的“择“D16:N16”,再点击“

”图标，然后选

”图标，回到IRR函数对话框。最后点击“确定”

按钮。得出计算结果是24%。

（12）拉动O16的右下角的填充柄填充至O25，得到结果如下表

（13）销售收入的敏感性分析的操作步骤如同经营成本的敏感性分析的操作步骤。因此得出销售收入敏感性分析的结果如下：

（14）绘制单因素敏感分析图。①点击主菜单栏上的“插入”命令，然后在下拉菜单中选择“图标”选项（也可直接点击工具栏上的“

”即“图表向导”按

钮），弹出“图表向导-4步骤之1-图标类型”对话框。在“标准类型”的“图表类型（C）”选项中选择“XY散点图”，在“子图表类型（T）”选项中选择第五个类型，即“无数据点折线图”，然后单击“下一步”按钮。

（15）在弹出的“图表向导4－步骤之2－图表源数据”对话框中，选择“系列”选项，点击“系列S下”的“添加”，将“系列1”的“名称”编辑框中输入“投资额”，点击“X值（X）”编辑框右侧的“击“

”图标，选中B2：B11区域，再点

”图标回到“源数据”对话框；点击“Y值（Y）”编辑框右侧的“”图

标，选中D2：D11区域，再点击“”图标回到“源数据”对话框。 类似地，在“系列2”的“名称”编辑框中输入“经营成本”， “X值（X）”选择为B2：B11区域，“Y值（Y）”选择F2：F11区域；在“系列3”的“名称”编辑框中输入“销售收入”， “X值（X）”选择为B2：B11区域，“Y值（Y）”选择H2：H11区域。最后点击“下一步”按钮。

（16）在弹出的“图表向导4－步骤之3－图表选项”对话框中，在“标题”选项中，将“图表标题（T）”命名为“单因素敏感性分析”，将“分类（X）轴（V）”命名为“变动率”，将“数值（Y）轴（V）”命名为“IRR”，单击“下一步”，再单击“完成”，或直接单击“完成”。这样就得到了投资额、年经营成本和年销售收入的敏感性分析图。

（17）从图中可以看出，在三个不确定因素中，销售收入是最敏感的因素，经营成本是最不敏感的因素。

【练习题3】某项目全部固定资产投资为10000万元，流动资金1000万元，

项目两年建成，第三年投产，当年达产，不含增值税，年销售收入为5000万元，经营成本2000万元，附加税及营业外支出为年50万元，项目计算期12年，项目要求达到的财务内部收益率为15％，求内部收益率低于15％的概率。

分析得：由题意知，固定投资的悲观值是13000万元，一般值是10000万元，最乐观的值是9000万元。年销售收入期望值是5000万元，标准差是300万元；经营成本的期望值是2000万元，标准差是100万元。寿命期是8年。

（1）根据以上内容，建立如下表格。同时在单元格G2和H2，I2中分别输入以下公式：

G2：＝9000+INT(RAND()*(13000-9000))。 H2：= NORMINV(RAND(),B3,B4)； I2： =NORMINV(RAND(),B5,B6)； 同时在F2输入“1”，F3输入“2”。同时选中单元格F2和F3，拖动F3右下角的填充柄填充至F1003.然后隐藏部分表格，便于表格的清晰。得到如下表格

（2）在单元格F2中键入“1”，在单元格F3中键入“2”，选中F2：F3，拖动其右下角的填充柄，直至鼠标右侧的黄色标注显示“1001”。隐藏大部分表格，然后选中F2：I1002区域，选择主菜单栏上“数据”命令中的“模拟运算表”选项，在接着出现的“模拟运算表”对话框中点击“输入引用列的单元格”栏右侧的“

”

图标，弹出“模拟运算表－输入引用列的单元格”对话框，再点击任意一个单元格（例如D10），“模拟运算表－输入引用列的单元格”对话框中将显示“$D$10”，再点击“

”图标，回到“模拟运算表”，单击“确定”按钮。

(3)为了获得进一步的统计资料，我们将工作表中的F3：I1003区域内的数据复制到一个新工作表的B2：E1002区域中，并且在此复制后生成的数据固定下来，同时隐藏部分数据。根据固定的数值算的第三年年末至第12年年末内每年的净现金流。

(4)在F1中输入“现金流量”计算的是第3年年末到第12年年末的现金流量，在G2中输入“=D2*(1-25%)-E2-50+C2/10”这里的企业所得税为25%。固定资产

年末无残值，均分到十年内。点击G2右下角的填充柄，填充至G1002.

(5)在求出现金流的基础上制作表格,在B1005的表格中输入“=-C2”点击右下角的填充柄，填充至B2005；在C2中输入“-1000”，点击右下角的填充柄填充至C2005。在D2中输入“0”， 点击右下角的填充柄填充至D2005。分别在E2至O2中输入“=G2”，分别点击E2至O2右下角的填充柄填充至E2005至O2005，得到如下的表格。

(6)计算现金流量序列的内部收益率。选中单元格P1005，点击工具栏上的“fx” 按钮，弹出“插入函数”对话框。先在“选择类别”中选择“财务”，然后在“选择函数”中选择“IRR”。最后点击对话框下的“确定”按钮。

（7）在弹出的IRR函数对话框中，点击“Valuel”栏右端的“选择B1005:O1005，再点击“定”按钮。

”图标，然后

”图标，回到IRR函数对话框。最后点击“确

(8)得出P1005里的结果是12%。点击P1005，拖拽其右下角的填充柄至P2005.得到如图所示的结果。

(9)下面制作1000个IRR的样本观测值的样本分布。在单元格Q1005和Q1006中分别输入“样本极大值”和“样本极小值”。在单元格R1005和R1006中试用MAX（）和MIN（）函数求出范围：P2005中的极大值和极小值。输入公式如下： F3：＝MAX（P1005：P2005）； F4：＝MIN（P1005：P2005）。

(10)选择主菜单栏上“工具”命令中的“数据分析”选项，然后在“数据分析”对话框中选中“直方图”，然后单击“确定”按钮。在“直方图”对话框中将“输入区域”设置为P1005：P2005，将“接受区域”设置为S1005：S1016，将“输出区域”设置为当前工作表的单元格T1004，单击“确定”按钮后，在T1004：T1016区域中就会得到如图所示的结果。

(11)激活单元格U1018，点击“∑”即“自动求和”按钮，得到总实验数1001。在单元格V1005中输入公式“＝U1005/1001”。拖动V1005右下角的填充柄，直至单元格V1017，这样在V1005：V1017区域中便可得到与各个间隔相应的IRR发生频率。

(12)利用在这个区域得到的数据制作一个直方图，就得到了被研究的投资项目的IRR样本分布图。点击工具栏上的“

”图标，弹出“图表向导4－步骤之1

－图表类型”对话框，在左侧的“图表类型”中选择“柱形图”，在右侧的“字图标类型”选择第一个类型，即“簇状柱形图”。

(13)点击“下一步”按钮，弹出“图表向导4－步骤之2－图表源数据”对话框，选择“系列”，点击系列下面的“添加”即可添加系列。在“系列1”的名称栏中输入“IRR”，点击“值”右侧的““

”图标后，选中V1005：V1017，再点击

”

”图标回到“源数据”对话框。点击“分类（X）轴标志（T）”右侧的“

”图标回到“源数据”对话框。

图标，选中T1005：T1017，再点击“

(14)点击“下一步”，得到“图表向导4－步骤之3－图表选项”对话框。选择“标题”，在图表标题栏中输入“1001次实验所得到的IRR样本分布”，在“分类（X）轴（V）”中输入“IRR”，在“数值轴（Y）轴（V）”中输入“频率”，点击“完成”按钮。

（15）下面再来计算1001次实验中IRR低于15%发生的概率。首先将P1005：P1015区域内的1001个数字做一次按大小降序的排序操作。具体操作：选中P1005：P1015，右击鼠标，点击复制，选中表格B2014右击鼠标，选择“复制性粘贴”，在弹出的对话框里，选定“数值”，单击“确定”，会出现以下的数据，在B2103中输入IRR样本，并隐藏一部分数据，便于操作。

（16）选中B2014至B3014中的数据，隐藏部分数据，点击工具栏上的“即“降序”按钮。

”

（17）然后在C2014：C3014区域中生成一个初值为0.001、终值为1.011、步长为0.001的数据系列，再在单元格02019中生成一个表示IRR上限值的数字“0.15”，在单元格02018中键入公式“＝INDEX（C2014：C3014，MATCH（O2018，C2014：C30144，-1））”。