第二章EXCEL在财务管理与分析中的应用解析.docx
《第二章EXCEL在财务管理与分析中的应用解析.docx》由会员分享，可在线阅读，更多相关《第二章EXCEL在财务管理与分析中的应用解析.docx（15页珍藏版）》请在冰豆网上搜索。
第二章EXCEL在财务管理与分析中的应用解析
第二章Excel在财务管理分析中的应用
第一节公式及函数的高级应用
一、数组公式及其应用
数组公式就是可以同时进行多重计算并返回一种或多种结果的公式。
在数组公式中使用两组或多组数据成为数组参数，数组参数可以是一个数据区域，也可以是数组常量。
数组公式中的每个数组必须有相同数量的行和列。
㈠数组公式的输入、编辑及删除
1、数组公式的输入步骤
⑴选定单元格或单元格区域。
如果数组公式将返回一个结果，单击输入数组的单元格；如果数组公式将返回多个结果，则要选定需要输入数组公式的单元格区域。
⑵输入数组公式
⑶同时按“Ctrl+Shift+Enter”组合键，则Excel会自动在公式的两边加上大括号｛｝。
在数组公式中，通常都是用单元格区域引用，但也可以直接键入数组数值，这样键入的数组数值被成为数组常量。
生成数组常量，必须按如下操作：
⑴直接在公式中输入数值，并用大括号“｛｝”括起来。
⑵不同列的数值用逗号“，”分开。
⑶不同行的数值用分号“；”分开
例2－1：
输入数组常量。
见图2-1、2-2
例2－2：
输入公式数组。
输入步骤同输入常量数组，见图2-3
2、编辑数组公式
数组公式的特征之一就是不能单独编辑、清除或移动数组公式所涉及的单元格区域中的某一个单元格。
若在数组公式输入后发现错误需要修改，则需要按以下步骤进行：
⑴在数组区域中单击任一单元格。
⑵单击公式编辑栏，当编辑栏被激活时，大括号“｛｝”在数组公式中消失。
⑶编辑数组公式内容。
⑷修改完毕后，按“Ctrl+Shift+Enter”组合键。
3、删除数组公式
删除数组公式的步骤是：
首先选定存放数组公式的所有单元格，然后按Delete键。
4、数组运算规则
以数字的数学运算为例
数字数组{5,2,8,4}与另一个数字数组{1,2,3,4}的加法运算如下：
{5,2,8,4}+{1,2,3,4}
={5+1,2+2,8+3,4+4}={6,4,11,8}
其余的乘、除、减的运算与加法雷同。
都是对应数字与对应数字运算后返回。
㈡数组公式的应用
1、用数组公式计算两个数据区域的乘积
例2－3：
已知A企业1－12月份的销售量与价格，用数组公式计算1－12月份的销售额和月平均销售额。
1-12月份的销售额建立过程:
第一步：
建立如图2-4所示的模型结构，并输入数据；
第二步：
选定单元格区域C7:
N7，并输入公式“＝C5:
N5*C6:
N6”
第三步：
同时按“CTRL+SHIFT+ENTER"
月平均销售的计算过程：
第一步：
选定I10单元格
第二步：
输入公式“＝AVERAGE（C5:
N5*C6:
N6）”
第三步：
同时按“CTRL+SHIFT+ENTER"
2、用数组公式计算多个数据区域的和
例2－4：
某企业2004年销售3种产品，用数组公式计算该公司2004年的总销售额。
计算月销售总额过程：
第一步：
选取单元格区域C10:
N10，见图2-5
第二步：
输入公式=C4:
N4*C5:
N5+C6:
N6*C7:
N7+C8:
N8*C9:
N9"
第三步：
按数组生成组合键。
计算三年销售总额：
第一步：
选取单元格C11;
第二步：
输入公式"=SUM（C4:
N4*C5:
N5+C6:
N6*C7:
N7+C8:
N8*C9:
N9）"；
第三步：
选取单元格区域C11:
N11；
第四步：
单击合并居中按钮。
二、常用函数及其应用
第二节图表处理
动态图标的建立
例2-5：
某企业的8月各销售部门一年内个月销售数据如下表所示：
建立各部门的动态图表。
第一步：
在单元格B16中输入公式"=INDEX（B4:
B11,B17）&"销售图",显示选中某部门的销售图，如选中的部门2，则显示的结果为“部门2销售图”。
第二步：
单击[视图]→窗体→组合框，在适当位置画组合框，并进行设置，见设置控件格式图。
第三步：
在单元格C16中输入公式"=INDEX（C4:
C11,$B$17）"。
第四步：
将C16中的公式复制到D16:
N16。
第五步：
绘图
第三节数据分析处理
第四节数据分析工具的应用
Excel提供了非常使用的数据分析工具，利用这些工具，可以解决财务管理中的许多问题，例如财务分析工具、统计分析工具、工程分析工具、规划求解工具、方案管理器等。
下面介绍财务管理与分析中常用的一些数据分析工具。
一、模拟运算表
模拟运算表就是将工作表中的一个单元格区域的数据进行模拟计算，测试使用一个或两个以上变量对运算结果的影响。
在Excel中，可以构造两种模拟运算表：
单变量模拟运算表和多变量模拟运算表。
模拟运算表可以运用在敏感性分析领域。
所谓敏感性分析是指通过多次使用不同的参数，对某个或某一组公式的运算结果进行综合对比，从而了解不同参数在其可能的取值范围内，对运算结果的影响程度。
㈠单变量模拟运算表
单变量模拟运算表就是基于一个输入变量，用它来测试对公式计算结果的影响。
例2－6：
企业向银行贷款10000元，期限5年，测试不同利率对月还款额的影响。
步骤：
第一步：
设计模拟运算表结构，如图2-8。
第二步：
在单元格C7中输入公式“=ABS（PMT（贷款利率/12,贷款期限*12,贷款总额））”，计算月换款额。
第三步：
选取单元格区域B7:
C16。
第四步：
单击[数据]，选择[模拟运算表]，弹出[模拟运算表]对话窗，如图2-4图
第五步：
在本例中引用的是列数据，所以在[输入引用列的单元格]中输入$E$2。
单击[确定]按钮，既单变量模拟运算表，见图2-9。
㈡双变量模拟运算表
例2－7：
某投资项目，投资10000，项目期为5年。
每年的净现金流量为2000，贴现率为8％。
对项目净现值进行敏感性分析。
设计过程：
第一步：
设计模拟运算表结构，如图2-10。
第二步：
在单元格C7中输入公式“=PV（C6,C4,-C5）-C3”，计算净现值。
第三步：
在单元格B13中输入公式“=C7”。
第四步：
选中单元格区域B13:
G19。
第五步：
单击[数据]，选择[模拟运算表]，弹出[模拟运算表]对话窗，图2-10。
第六步：
在本例中引用的是双变量，所以在[输入引用行的单元格]和[输入引用列的单元格]中输入变量。
单击[确定]按钮，既的导弹变量模拟运算表，见图2-11。
㈢单变量求解
单变量求解就是求解只有一个变量的方程的根，方程可以是线性的，也可以是非线性的。
单变量求解工具可以解决财务管理中涉及到一个变量的求解问题。
例2－8：
某投资项目，投资10000，项目期为5年，贴现率为8％。
若要求该项的净现值达到5000，则每年的净现金流量应为多少？
第一步：
设计如图2-12所示的计算表格。
第二步：
在单元格C7中输入公式“=PV（C6,C4,-C5）-C3”，计算净现值。
第三步：
单击[工具]菜单，选择[弹变量求解]项，弹出[弹变量求解]对话框，如图2-13。
第四步：
在[目标单元格]中输入“C7”，在[目标值]中输入“50000”，在[可变单元格]中输入“$C$5”,单击[确定]按钮，则系统计算出结果，如图2-12所示，即实现净现值50000元，年净现金流量应达到3255.93元。
（四）方案管理器
所谓方案，是保存在工作表当中并可以相互之间进行切换的数据。
例如，对于一组现金流量分别使用不同的贴现率计算净现值，就可以把不同条件下的结果保存在若干方案中，通过Excel的方案管理器在不同的结果间进行切换，以对比和评价各方案之间的差异。
例2－9：
某投资项目，投资10000，项目期为5年。
项目的现金流量和贴现率受经济形势的影响如果经济不景气，预计每年的现金流量为1800，贴现率为6％；如果经济繁荣，预计每年现金流量为2800，贴现率为10％；如果经济形势一般，则现金流量为2200，贴现率为8％。
要求对项目进行评价？
第一步：
建立如图2-13所示的方案管理器数据结果。
并在C4、C5、C6、C7单元格输入数据。
在单元格C8中输入公式“=PV（C7,C5,-C6）-C4”。
单击[工具]菜单，选择[方案]项，弹出[方案管理器]对话框。
第二步：
单击[添加]按钮，打开添加方案对话框。
根据题意添加3次，形成三个方案，分别是繁荣、一般、不景气。
如图2-14。
第三步：
单击[方案管理器]的[摘要]按钮，可生成当前保存的全部方案党的汇总。
如图2-15。
（五）规划求解
通过单变量求解，可以根据给定的公式结果，计算出某个参数的值。
对于更复杂的情况，如要求使公式结果最大或最小，同时对公式内的各参数存在某些约束条件，这时就需要使用规划求解工具。
借助规划求解，可求得工作表上某个单元格（称为目标单元格）中公式的最优值。
规划求解将对直接或间接与目标单元格中公式相关联的一组单元格中的数值进行调整，最终使目标单元格中的公式得到期望的结果。
在创建模型过程中，要对规划求解模型中的可变单元格数值设置约束条件，而且约束条件可以引用其他影响目标单元格公式的单元格。
升级会员 