Excel提供了非常实用的数据分析工具doc.doc

Excel提供了非常实用的数据分析工具，利用这些分析工具，可解决财务管理中的许多问题，例如财务分析工具、统计分析工具、工程分析工具、规划求解工具、方案管理器等等。下面介绍财务管理与分析中常用的一些数据分析工具。 2.4.1 模拟运算表 模拟运算表就是将工作表中的一个单元格区域的数据进行模拟计算，测试使用一个或两个变量对运算结果的影响。在Excel中，可以构造两种模拟运算表：单变量模拟运算表和多变量模拟运算表。 2.4.1.1 单变量模拟运算表 单变量模拟运算表就是基于一个输入变量，用它来测试对公式计算结果的影响。 【例2-13】企业向银行贷款10000元，期限5年，则可以使用【模拟运算表】工具来测试不同的利率对月还款额的影响，步骤如下： （1）设计模拟运算表结构，如图2-62所示。 图2-62 单变量模拟运算表 （2）在单元格B4中输入公式“=PMT(A4/12,5*12,B1)”。 （3）选取包括公式和需要进行模拟运算的单元格区域A4:B13。 （4）单击【数据】菜单，选择【模拟运算表】项，弹出【模拟运算表】对话框，如图2-63。 图2-63 【模拟运算表】对话框 （5）由于本例中引用的是列数据，故在【输入引用列的单元格】中输入“$A$4”。单击【确定】按钮，即得到单变量的模拟运算表，如图2-62所示。 2.4.1.2 双变量模拟运算表 双变量模拟运算表就是考虑两个变量的变化对公式计算结果的影响，在财务管理中应用最多的是长期借款双变量分析模型，有关详细内容可参阅第3章的有关章节。 2.4.2 单变量求解 单变量求解就是求解只有一个变量的方程的根，方程可以是线性方程，也可以是非线性方程。单变量求解工具可以解决许多财务管理中涉及到一个变量的求解问题。 【例2-14】某企业拟向银行以7%的年利率借入期限为5年的长期借款，企业每年的偿还能力为100万元，那么企业最多总共可贷款多少？ 设计如图2-64所示的计算表格，在单元格B2中输入公式“=PMT(B1,B3,B4)”，单击【工具】菜单，选择【单变量求解】项，则弹出【单变量求解】对话框，如图2-65所示，在【目标单元格】中输入“B2”，在【目标值】中输入“100”，在【可变单元格】中输入“$B$4”，然后单击【确定】按钮，则系统立即计算出结果，如图2-64所示，即企业最多总共可贷款410.02万元。 ?????? ?? ??????????????????????????????????? ???图2-64 贷款总额计算??????? ????????图2-65 【单变量求解】对话框 2.4.3 规划求解 规划求解是Excel的一个非常有用的工具，不仅可以解决运筹学、线性规划等问题，还可以用来求解线性方程组及非线性方程组。 【规划求解】加载宏是Excel的一个可选安装模块，在安装Microsoft Excel时，如果采用【典型安装】，则【规划求解】工具没有被安装，只有在选择【完全/定制安装】时才可选择安装这个模块。在安装完成进入Excel后，单击【工具】菜单，选择【加载宏】项，在【加载宏】对话框中选定【规划求解】复选框，然后单击【确定】按钮，则系统就安装和加载【规划求解】工具，可以使用它了。 2.4.3.1 求解优化问题 财务管理中涉及到很多的优化问题，如最大利润、最小成本、最优投资组合、目标规划、线性回归及非线性回归等等。下面仅举一个简单的例子来说明利用规划求解工具解决最大利润的问题，有关规划求解工具的更多实际应用可参阅后面的有关章节。 【例2-15】某企业在某月份生产甲、乙两种产品，其有关资料如图2-66所示，则企业应如何安排两种产品的产销组合，使企业获得最大销售利润？ 利用规划求解工具求解这个问题的步骤如下： 图2-66 产品有关资料及优化结果 1）首先建立优化模型，（设x和y分别表示甲产品和乙产品的生产量）： 目标函数：max{销售利润}= (140－60)×x + (180－100)×y 约束条件：6x + 9y ≤ 360 ????????? 7x + 4y ≤ 240 ????????? 18x + 15y ≤ 850 ????????? y ≤ 30 ????????? x ≥ 0, y ≥ 0，且为整数 （2）单元格B11和C11为可变单元格，分别存放甲、乙产品的生产量。 （3）单元格B12为目标单元格（销售利润），计算公式为“=SUMPRODUCT(B4:C4-B5:C5,B11:C11)”； （4）在单元格B14中输入产品消耗工时合计计算公式“=SUMPRODUCT(B6:C6,B11:C11)”。在单元格B15中输入产品消耗材料合计计算公式“=SUMPRODUCT(B7:C7,B11:C11)”，在单元格B16中输入产品消耗能源合计计算公式“=SUMPROD