Excel金融应用.ppt

1、，第3部分：有效使用EXCEL进行管理和财务分析，I，EXCEL操作技术II，如何使用EXCEL的公式和函数III，如何对分析IV使用透视表，创建和分析各种财务报告和管理报表v，实际案例练习，I，EXCEL操作技术，I， 假设分析工具，1，方案管理器2，模拟表3，单个变量求解4，规划求解，数据输入，Excel数据输入，*对于数据量较少的数据，手动输入，自动创建，数据库导入，*创建web查询，数据-导入web查询-新建web查询，打开新建web查询对话框，在地址栏中输入数据源的网址，打开web页，单击所需数据旁边的“”标志，然后单击导入，即可将数据导入Excel。Excel具有数据-刷新命令，使

2、您可以更新从web导入的数据。数据处理、排序：排序根据一个或多个特定字段重新排列数据。*“排序”按钮“排序”按钮可选择要排序的单元格区域，单击主页工具栏上的“排序”按钮可按升序或降序对选定区域排序。*排序命令选择排序顺序，方法是首先选择整个数据区域(标题行除外)，然后运行“数据-排序”命令以打开“排序”对话框，然后从“主键”、“辅助键”和“第三个关键字”中选择标题。从选项按钮打开“排序选项”对话框，然后设置排序以确定完成数据的排序。在自定义排序中，预先唯一于“选项”的“自定义序列”的序列顺序、数据处理、筛选：筛选是查找和处理区域中数据子集的更快方法。*自动过滤适用于简单过滤。选择要筛选的项，然

3、后运行“数据-筛选-自动筛选”命令，在工作表中每个列标题行的右侧将出现“筛选”按钮，单击筛选按钮以筛选下拉列表框中的数据。主要行业的下拉列表框中包含车辆、摩托车、汽车制造、汽车部件和许多其他过滤标准，过滤按钮和行标签以蓝色显示。此外，多列筛选、数据处理、筛选：筛选是在区域中查找和处理数据子集的更快方法，在自动筛选的下拉列表中有“自定义”筛选器，在“自定义自动筛选标准”对话框中输入筛选条件，是查找和处理每股收益大于零的上市公司、数据处理、筛选：筛选是在区域中查找和处理数据子集的更快方法。*高级过滤用于复杂条件的过滤。数据-筛选器-运行高级筛选器命令，然后在高级筛选器对话框中，选择要筛选的数据、筛

4、选条件和筛选器结果区域。在数据列表、筛选器结果、条件区域条件区域中，列标志条件区域和数据列表之间必须至少有一个空行。通常输入筛选器，在有助于处理数据的条件区域的数据列表中插入三个或更多空行。筛选：筛选是寻找和处理区域中资料子集的更快方法。表1，高级筛选将重点放在条件区域，然后在筛选数据的工作表的条件区域设置筛选条件。其他变量的条件位于表示 and 的同一行中。单个行表示“或”；单个列具有多个筛选条件；纸张行业公司筛选表1、小于0或大于10的主要业务利润率；表2、表3、多列使用单个条件在多个列中查找所需数据时，在条件区域(如表2、主要业务利润率0和销售总利润率0)的同一行中输入条件。在多个行或列

5、中具有单个条件的数据满足多个行或列条件的情况下，筛选表3、主要营业利润率0或资产负债比率0的公司、数据处理、筛选：筛选是查找和处理该区域的数据子集的更快方法。高级筛选的卓越功能：重复数据消除实际上经常发生重复数据。在这种情况下，如果逐个删除数据，大量数据会很麻烦。我们可以熟练地使用高级审查来解决这个问题。在下图中，您可以在“数据选择-筛选器-高级筛选器-高级筛选器-高级筛选器”对话框中选择无重复记录-检查后获得无重复的结果。复制公式时，资料的运算和参考、相对参考和绝对参考、相对参考储存格的相对参考以及储存格参考会调整到新位置。储存格D2=B2*C2，D3: D4复制D2的公式，参考位置相对变更

6、。下图绝对参考储存格的绝对参考(例如$A$1)，其中$ a $1(按F4 键实作)是指定的位置参考储存格，复制公式时，储存格参考绝对不会变更。在单元格E4=D4*$E$1，E5: E6中复制E4的公式，绝对引用$E$1的位置保持不变，如下图所示。资料运算和参照，命名：名称表示储存格、储存格区域、公式或常数值的文字或字串。选择要命名的单元格或单元格区域，在Excel名称框中输入名称，然后按enter键，这样比行标志更直观、更易于理解和使用。*指定单元和区域名称：选取区域B1:B5，在名称方块中输入unit，然后按Enter结束，选取Excel功能表的插入-名称-定义指令，然后输入名称和指定的区域

7、。在当前工作簿的名称中输入“项目”，在引用位置中要命名的区域=sheet1！输入或选择$A$1:$A$5，数据操作和参照，命名：名称表示单元、单元区域、公式或常量值的单词或字符串。名称比行标志更直观、更易于理解，并且具有两个或两行数据：列或动作文本id(字段名)、另一列或动作特定的数值(数值自动名称)。选择区域，然后在Excel菜单中选择使用“插入-名称-指定”命令标识的位置并进行确认。选择A4:E9，打开命名对话框，然后选中最左侧的列复选框以确定和命名结束、数据的计算和引用，如下图所示。名称是表示单元格、单元格区域、公式或常量值的单词或字符串。名称比行标志更直观、更易于理解，*常量命名：此方

8、法指定单元格中不显示的数字名称。在Excel菜单中，使用“插入”-“名称”-“定义”命令，在当前工作簿的名称中输入名称，然后在引用位置中输入数值。在使用Excel工作表计算分析时，多次使用变量“销售税=0.075”，但工作表中不显示此变量。打开“定义名称”对话框，输入“销售税”作为当前工作簿的名称，输入0.075作为参考位置，以处理下图、数据的计算和参考、函数：Excel是计算工具软件，它通过函数和内置工具计算来处理数据。Excel提供了范围广泛的内置函数，可执行具有强大数据处理功能的特定运算。Excel按功能和角色进行划分，如财务、日期和函数、数学和三角函数、统计函数、查找和引用函数、数据库

9、函数、文本函数、逻辑函数、信息函数、自定义和工程函数。*这些函数在以下课程中单独介绍。例如，说明如何在单元格中输入“=”以插入函数，以及如何直接输入函数以使用一个逻辑函数(IF函数)。对于熟悉的人，打开函数、选择函数必需的函数、输入函数必需的参数、确认、OR、图表处理、图表是帮助您在使用Excel执行各种计算分析时更直观地了解数据之间的内部关系和变化趋势的有用工具。Excel中提供了14种内置图表类型，某些类型还包含其他更改。处理图表、处理默认图表、*创建图表、单击常规工具栏上的按钮、显示图表向导、选择正确的图表类型、确定数据源、设置图表选项、处理图表、处理默认图表、*设置图表格式方法：右键单

10、击图表的每个元素，例如图表节、轴、图表网格线图例，然后从快捷菜单中选择图表区域，图表区域格式-填充效果-纹理、图表区域、图表区域格式-填充效果-双色、图表处理、组合图是指在同一图表中使用不同的图表类型表示不同组的数据。这是绘制多个数据集之间的对比度关系的常用工具。添加股息支付率系列后，因为数值都很高，所以提高纵坐标坐标值的话，股息收益率图不太显示，如何形成对比呢？图表处理、次轴次轴是设置两个不同纵坐标轴的技术，以便在组合图表中多个数据集的数据范围差异很大的情况下，更好地表示多个数据集的对比度关系。How？在Step1:中选择股息收益系列，右键单击“图表类型”，在step 23360中选择图表类

11、型中的条形图，在step :中选择股息收益系列，然后右键单击“数据系列格式”，在Step4:中，在“数据系列格式”选项卡上单击“轴-副轴”原则上，该功能是基于统计中的回归和预测理论。Forexample:是财务定量分析中的常用工具，在1990-2002年间计算了KJIA 30家企业的收益率平均值和标准偏差，并绘制了图表，以说明风险和收入的关系。使用散点图，因为无法提前知道两者的关系。观察到存在线性关系，可以使用Excel的趋势线功能以数学方式更准确地验证此推测。step 1:单击图表区域，显示“图表”菜单栏时，选择“添加趋势线”，step 23360选择有效的趋势线类型，您可以依次选择Step

12、3:设置趋势线选项和显示公式。预测还具有“正向”功能，您可以从中选择相应的单位。假设分析工具方案管理器是存储在工作表中的数据，可以相互切换。例如，如果现金流集使用不同的折现率计算净现值，则可以在多个方案中存储不同的条件结果，使用Excel的方案管理器比较和评估方案之间的差异，以假定投资项目、投资金额10000和项目周期5年。项目的预计现金流量和折扣率都受经济状况的影响。如果经济不景气，预计年现金流为1200，贴现率为6%。如果经济繁荣，预计年现金流为2000，贴现率为10%。经济状况一般情况下，现金流为1600，折扣率为8%。要求对这个项目进行评价。，step 23360菜单工具-打开方案、方

13、案管理器，然后单击添加，在编辑step 23360方案中，输入方案名称和变量单元格，Step3:输入方案值(例如添加方案)，然后重新打开step 23360方案管理器以执行方案管理。选择方案并单击“显示”后，显示方案更改时，Excel中相应的数值也会更改。模拟计算表模拟计算表通过提供三个不同条件集的参数值，获得了项目的评估结果。假设分析工具是一个非常理想的敏感度分析工具，广泛用于财务领域，如资本预算的敏感度分析。在前面的实例中，方案管理器提供了三个不同条件集的参数值。模拟表使您可以对项目的净现值执行敏感度分析，并更详细地研究折扣率和年度现金流对评估结果的影响。在单元格B13中输入净现值的计算公

14、式，在公式单元格B13的右侧输入估计折现率集，然后在下面输入年度现金流的可能值集。使用滑鼠选取B13:G19区域，从功能表执行资料-模拟表格，在模拟表格的对话方块中输入相关参数，产生模拟表格，然后根据这些结果建立图表，以更直观地展示评估结果对参数的敏感度。分析工具、模拟表模拟表是财务领域广泛使用的非常理想的敏感度分析工具。假设分析工具(如资本预算的敏感度分析)、单变量求解通过在数量分析过程中更改公式中的某个变量值，可以起到其它参数变量的作用，在数学运算中具有反函数作用。财务计算中存在很多这样的问题，包括解决资本预算中的内部收益率、在选项定价问题中根据选项的市场价格计算的资产可变性。Forexa

15、mple:上例中的投资评估假定此项目的NPV=5000，Rate=10%解决了年度现金流需要多少。现在，可以通过单个变量求解来解决此问题。在菜单中，单击工具-解析单个变量，然后在目标单元格和目标值以及变化单元格中输入相应参数即可。II、EXCEL如何使用公式和函数、一、公用函数、1、统计函数2、财务函数、1、最终值和当前值2、利率和期间3、养老保险4、名义利率和实际利率、第二、将EXCEL应用于货币时间值、公用函数、公用函数财务计算中常用的统计函数：用于计算平均值的AVERAGE()、用于计算方差的VAR()或VARP()、用于计算标准差的STDEV()或STDEVP()、标准正态分布NORM

16、SDIST()和norms inv()线性回归和预测函数LINEST()、FORECAST()、TREND()、线性方程的倾斜函数SLOPE()、指数回归和预测函数LOGEST()、growth()；相关系数函数CORREL()和产品力矩相关系数(也称为广义相关系数)函数RSQ()用于数据分析。典型函数，Excel中内置了50多个与财务相关的函数。有九个函数计算货币的时间价值，其中五个一般函数在财务计算中扮演着非常重要的角色。现值PV()、最终值FV()、利率比率()、定期现金流RATE()和期间NPER()。有五个固定资产折旧类函数：直线折旧方法SLN()、寿命合计方法SYD()、固定馀额减少方法DB()、双重馀额减少方法DDB()和VDB()，前者使用馀额减少方法计算某个期间内的累计折旧额。有五个投资评估类函数：净现值NPV()、内部收益率IRR()、内部收益率修正MIRR()和计算非定期现金流净现值和内部收益率的函数xpv()和XIRR()。关于债券计算有26个函数。将EXCEL应用于货币时间价值计算，现值(PV):在市场经济条件下