Excel财务应用教程.docx

Excel财务应用教程.docx

《Excel财务应用教程.docx》由会员分享，可在线阅读，更多相关《Excel财务应用教程.docx（40页珍藏版）》请在冰豆网上搜索。

Excel财务应用教程

在市场经济的今天，投资活动愈发显得频繁和重要。

由于投资活动充满不确定性，所以任何投资总要承担一定的风险。

如果决策面临的不确定性比较大，足以影响投资方案的选择，就应该对不同的方案进行计量，例如计算比较各种方案的期望净现值，作为投资决策的依据。

Excel中大量的财务、统计等各种函数及其强大的表格功能，加上简单易行的操作，使其成为辅助投资风险分析的良好助手。

其中的“方案管理器”更有助于如投资决策这种多方案问题的分析。

本例中，某企业现在面临两种投资方案：

新建厂房生产新产品和扩建厂房生产现有产品。

新建厂房须投资300万元，扩建厂房须投资100万元。

产品的市场前景不能确定。

究竟使用那种方案，须考虑多种因素，而两种方案的预计净现值比较是必须考虑的重要依据。

本例目标：

●学习使用IF函数

●学习设置单元格的有效数据范围

●学习使用NPV函数计算净现值

●学习在工作表中进行方案管理

●学习设置共享工作簿

步骤一：

建立工作表

该企业目前面临5种可能的市场前景，各前景的说明及预计发生概率如表11-1。

已知基本折现率为15%，厂房使用年限为4年。

表11-1各前景的说明及发生概率

前景

说明

概率

前景1

新产品畅销，现有产品滞销

25%

前景2

第一年现有产品畅销，一年后新产品畅销

35%

前景3

前两年现有产品畅销，两年后新产品畅销

20%

前景4

前三年现有产品畅销，三年后新产品畅销

10%

前景5

现有产品畅销，新产品滞销

10%

首先新建名为“投资风险分析”的工作簿，并在其中建立计算净现值的表格（如图11-1所示）。

拟在工作表中先由各种前景的概率计算出各年的期望年净收益，再用函数计算净现值。

图11-1建立净现值计算表格

步骤二：

输入逻辑公式

在本例中，要计算两种不同的方案的预计净现值，并加以比较。

为在一张表格中计算两种不同的方案的预计净现值，使用逻辑函数IF来计算各种前景下的各年净收益。

一、IF函数简介

IF函数用于执行真假值判断，根据逻辑测试的真假值，返回不同的结果。

可以使用函数IF对数值和公式进行条件检测。

语法：

IF（logical_test,value_if_true,value_if_false）

参数：

Logical_test可以是计算结果为TRUE或FALSE的任何数值或表达式。

Value_if_true是Logical_test为TRUE时函数的返回值。

如果logical_test为TRUE并且省略value_if_true，则返回TRUE。

Value_if_true可以为某一个公式。

Value_if_false是Logical_test为FALSE时函数的返回值。

如果logical_test为FALSE并且省略value_if_false，则返回FALSE。

Value_if_false可以为某一个公式。

说明：

函数IF可以嵌套七层，用value_if_false及value_if_true参数可以构造复杂的检测条件。

在计算参数value_if_true和value_if_false后，函数IF返回相应语句执行后的返回值。

如果函数IF的参数包含数组，则在执行IF语句时，数组中的每一个元素都将计算。

如果某些value_if_true和value_if_false参数为操作提取函数，则执行所有的操作。

二、使用IF函数

下面使用IF函数计算各年净现值。

已知如果产品畅销，预计年净收益为180万元。

如果产品滞销，预计年净收益为20万元。

操作步骤如下：

1.将单元格B4命名为“投资”，将单元格B5命名为“产品”。

2.单击选中B10单元格。

由前景说明可知该单元格对应的情况下，新产品畅销，现有产品滞销。

也就是说如果企业生产的产品为新产品，则年净收益为180万元，如果企业生产的产品是现有产品，则年净收益为20万元。

3.单击“粘贴函数”按钮，弹出“粘贴函数”对话框（如图11-2所示）。

图11-2粘贴IF函数

4.在“函数分类”列表框中单击选中“逻辑”，在“函数名”列表框中单击选中“IF”，单击“确定”，弹出“IF函数”框（如图11-3所示）。

图11-3使用IF函数

5.在“Logical_test”编辑框中键入“产品="新产品"”。

6.在“Value_if_true”编辑框中键入“180”，在“Value_if_false”编辑框中键入“20”，单击“确定”按钮。

由于“产品”单元格中还没有数值，即不为“新产品”，所以B10单元格中显示数值“20”（如图11-4所示）。

图11-4逻辑函数计算结果

将B10单元格中的公式复制到所有对应新产品畅销的单元格中。

然后在对应现有产品畅销的单元格中输入逻辑计算公式。

在熟悉IF函数以后，也可以直接在编辑栏中键入引用IF函数的公式，而不必使用“粘贴函数”按钮。

操作步骤如下：

1.单击B11单元格。

2.在编辑栏中键入“=IF（产品="现有产品"，180，20）”。

3.单击“输入”按钮。

4.将B11单元格中的公式复制到所有对应现有产品畅销的单元格中。

由于“产品”单元格中没有数值，即既不为“新产品”，也不为“现有产品”，所以所有的年净收益单元格中都显示数值“20”。

按照生产新产品的方案在“投资”单元格和“产品”单元格中键入数据，计算表格中显示对应的年净收益数值，如图11-5所示。

图11-5生产新产品时的年净收益

步骤三：

设置单元格的有效数据范围

在上个步骤中，用于计算年净收益的逻辑函数引用了“产品”单元格。

计算结果由“产品”单元格中的数据决定。

如果在向该单元格中输入数据时稍出差错，例如，不小心多键入了一个空格，将会造成年净收益的计算错误。

为了避免这种情况的发生，为该单元格设置有效的数据范围，使该单元格的数据输入只能从下拉列表中选择。

操作步骤如下：

1.选定“产品”单元格（B5）。

2.单击“数据”菜单中的“有效数据”命令，弹出“有效数据”对话框（如图11-6所示）。

图11-6设置单元格的有效数据范围

3.在“许可”下拉列表中选择“序列”。

4.在“来源”编辑框中键入“新产品,现有产品”。

注意：

在“来源”编辑框中键入的可选单元格数据系列中，必须用英文输入法下的逗号来分隔。

5.选中“提供下拉箭头”复选框。

6.单击“错误警告”选项卡（如图11-7所示）。

图11-7设置错误警告信息

7.确定选中“输入无效数据时显示出错警告”复选框。

8.在“图案样式”下拉列表框中选择“信息”。

9.在“错误信息”编辑框中键入“请在下拉列表中选择输入选项”。

10.单击“确定”按钮。

经过上述步骤，Excel为“产品”单元格设置下拉列表。

当单击该单元格时，将在单元格右侧显示下拉箭头按钮，单击下拉箭头按钮，显示在“有效数据”对话框中设置的列表选项，如图11-8所示。

图11-8为单元格设置下拉列表

如果在单元格中输入了错误的数据，例如在“新”字与“产”字之间多输入了一个空格，完成输入时，Excel将显示出错信息，如图11-9所示。

图11-9显示出错信息

步骤四：

计算净现值

下面计算投资的净现值。

所谓净现值是指未来各期支出（负值）和收入（正值）的当前值的总和。

它是用来比较方案优劣的重要指标。

一、NPV函数简介

NPV函数基于一系列现金流和固定的各期贴现率，返回一项投资的净现值。

语法：

NPV（rate、value1、value2……）

参数：

rate为各期贴现率，是一固定值。

value1，value2……代表1到29笔支出及收入的参数值。

value1、value2……所属各期间的长度必须相等，而且支付及收入的时间都发生在期末。

NPV按次序使用value1、value2……来注释现金流的次序。

所以一定要保证支出和收入的数额按正确的顺序输入。

如果参数是数值、空白单元格、逻辑值或表示数值的文字表达式，则都会计算在内；如果参数是错误值或不能转化为数值的文字，则被忽略。

如果参数是一个数组或引用，只有其中的数值部分计算在内。

忽略数组或引用中的空白单元格、逻辑值、文字及错误值。

说明：

函数NPV假定投资开始于value1现金流所在日期的前一期，并结束于最后一笔现金流的当期。

函数NPV依据未来的现金流计算。

如果第一笔现金流发生在第一个周期的期初，则第一笔现金必须加入到函数NPV的结果中，而不应包含在values参数中。

如果n是values参数表中的现金流的次数，则NPV的公式为：

二、计算年净收益期望值

在用NPV函数计算净现值时，需要用到各期的净收益值。

在计算净现值之前首先计算年净收益期望值。

在B15单元格中键入计算第一年期望净现值的计算公式

“=B10*$F10+B11*$F11+B12*$F12+B13*$F13+B14*$F14”

选中该单元格，用鼠标拖拉填充柄，将公式复制到其他各年的期望年净收益单元格中。

计算表格中显示各期的年净收益期望值，如图11-10所示。

图11-10计算年净收益期望值

三、使用NPV函数计算净现值

下面用NPV函数计算净现值，操作步骤如下：

1.单击B16单元格。

提示：

在对单元格进行合并及居中操作后，合并的单元格的引用采用合并区域左上角的单元格的引用。

2.单击“粘贴函数”按钮。

3.在“粘贴函数”对话框中，在“函数分类”列表框中选择“财务”，在“函数名”列表框中选择“NPV”。

4.单击“确定”，弹出“NPV函数”框（如图11-11所示）。

5.在“Rate”编辑框中键入“基准折现率”单元格的引用。

6.在各“Value”编辑框中键入各期年净收益期望值的单元格引用。

图11-11设置NPV函数的参数

7.单击“确定”。

8.由于NPV函数没有计算本年度的现金流，还应在原来的基础上减去投资额。

在编辑框中原公式的后面加上“-投资”，公式成为“=NPV（B3,B15,C15,D15,E15）-投资”。

经过上述步骤，计算表格中显示出净现值的计算结果（如图11-12所示）。

图11-12计算净现值

步骤五：

多方案求解

在图11-12所示的计算表格中，只显示出一种方案的计算结果。

在基本数据表格中，输入另一种方案的数据，可得出另一方案的净现值。

对于这种多方案的问题，使用“方案管理器”可以更好地管理数据和信息。

还可创建方案总结报告和方案数据透视表，便于对各方案进行分析比较。

一、创建方案

创建方案的步骤如下：

1.单击“工具”菜单中的“方案”命令，弹出“方案管理器”对话框（如图11-13所示）。

图11-13“方案管理器”对话框

2.单击“添加”按钮，弹出“添加方案”对话框（如图11-14所示）。

图11-14添加方案

3.在“方案名”编辑框中，键入方案名称“扩建厂”。

4.在“可变单元格”编辑框中，键入“投资，产品”。

提示：

对话框中“保护”标题下的复选框用于设置对方案的保护。

选中“防止更改”复选框可禁止对方案的更改，选中“隐藏”复选框，可隐藏方案。

在这里设置了对方案的保护后，还应对工作表进行保护，方法是：

将鼠标指向“工具”菜单中的“保护”选项，然后单击子菜单中的“保护工作表”命令。

在编辑或删除方案之前，必须清除“防止更改”复选框。

5.单击“确定”按钮，弹出“方案变量值”对话框（如图11-15所示）。

图11-15输入方案变量值

6.在“方案变量值”对话框中，键入投资额为“100”，产品为“现有产品”。

7.单击“添加”按钮，重复步骤3到步骤6，创建名为“新建厂”的方案，投资额为“300”，产品为“