EXCEL_7章 假设分析

1、Excel 数据管理与分析第七章 假设设分析12 王小明夫妇近日准备购屋王小明夫妇近日准备购屋, ,已经存了已经存了4040万的存款可以交首万的存款可以交首付款，而看上的房价是付款，而看上的房价是190190万万, ,所以余额所以余额150150万必须向银行贷款万必须向银行贷款： （1）若银行愿意提供20年期，利率为固定年利率4.25%，采每月缴纳本息，此状况下每月必须偿还的房屋贷款是多少？ （2）夫妻每月薪资总收入2.3万元，三分之一用来缴纳房贷，是否足以负担？ （3）若负担不起，在相同的条件与状态下，改买总价较低的房子，请问他们可以买得起总价多少的房子？ （4）若改成每月薪资总收入的一半来

2、缴纳房贷，利率(固定年利率4.25%)与贷款金额不变(150万)下，最快可以多久偿还房贷？ （5）若仍维持每月薪资总收入的三分之一来缴纳房贷，仍是贷款20年的状态下，寻求其他愿意提供较低利率的贷款银行，则利率要降低到多少才有能力购买这190万元的房子？计算在固定利率下,每期偿还贷款的金额： PMT(贷款利率,还款期数,贷款总额)每期还款额每期还款额= PMT(贷款利率,还款期数,贷款总额)每期还款额每期还款额= PMT(贷款利率,还款期数,贷款总额)每期还款额每期还款额= PMT(贷款利率,还款期数,贷款总额)3 假设分析解决什么样的问题假设分析解决什么样的问题? ? 一、单变量求解一、单变量

3、求解 若银行愿意提供150万的贷款，20年期，固定年利率4.25%，若采用每月还贷，此状况下每月必须偿还的房屋贷款是多少？条件结果在能承受的在能承受的还贷还贷情情况况下，下，请请问问他他们们可以可以买买得起得起总总价多少价多少的房子？的房子？“ “ 结果条件 类似于数学中的反函数类似于数学中的反函数 自变量和因变量发生角色转换新问题4二、模二、模拟数拟数据表和方案管理器据表和方案管理器 贷款利率组合运算： Excel 2007提供了与假设前提相关的三种简单的数据分析方法： 单变量求解单变量求解 模拟数据表模拟数据表 方案管理器方案管理器5u Excel假设分析综述u 单变量求解u 模拟运算数据

4、表u 方案管理器6uExcel 2007提供了与假设前提相关的三种简单的数据分析方法（单变量求解，模拟数据单变量求解，模拟数据表、方案管理器表、方案管理器），他们的共同特点是问题的求解都由：一个待求解目标模型，和一组与模型相关的变量组成。系统通过假设求解过程可以获得两种类型的结果： 1、在假设目标下，求与目标相关的某因素可行解 2、能够在指定变量值域范围内，观察各变量对于模型所产生的影响7数据选项卡数据选项卡/ /数据工具组数据工具组/ /假设分析按钮假设分析按钮8u Excel的单变量求解，是在待求解问题数学模型（公式）已经被确定的前提下，根据对模型所描述目标的确定要求，利用数学模型倒推条件

5、（自变量）指标的逆向分析过程。910u用例子说明假设王小明夫妇要购买一处新住宅贷方提供80的贷款（20为预付定金），要求贷20年、4.25%的固定利率王小明夫妇每月可以提供8000的还贷问王小明夫妇能够买得起总价多少的房子？每期还贷每期还贷= PMT(贷款利率,还款期数,贷款总额)建立正确的数学模型（表达式）确定模型的目标(前提条件)与待求解的变量(只能有一个)建立包含已知前提条件和与其相关的数学模型的数据表。应用“数据数据工具组假设分析按钮单变量求解选项”进行问题求解。 我们可以通过具体问题和相应的求解过程了解单变量求解的应用。11问题说明u 假设购买设备需要一定数量的贷款贷款，已知贷款的还

6、款期限还款期限、每月能够提供的还款数额还款数额上限，需要确定所能够承受的贷款利率贷款利率u 数学模型的确定：问题需要计算符合目标要求的贷款利率，使用 Excel财务函数中PMT (计算在固定利率下，贷款的等额分期偿还额)函数，计算月还款金额12PMT(rate ,nper ,pv)PMT(rate ,nper ,pv)贷贷款利率付款期总数总数现值现值数据表设计目标模型为：=PMT(B3/12,B2,B1)计算月还款金额可变单元格可变单元格待求解变量待求解变量目标模型目标模型问题限制条件问题限制条件13数据表设计目标模型为：=PMT(B3/12,B2,B1)计算月还款金额可变单元格可变单元格待求

7、解变量待求解变量目标模型目标模型问题限制条件问题限制条件14单变量求解设置单击：下拉菜单中的单变量求解进入单变量求解窗口目标模型为：=PMT(B3/12,B2,B1)目标值为付出，需要填写负数151617问题：已知第一季度产量400，季度增长率15%，计算各季度的产量与全年产量。18问题：若已知需要年产量达到某固定值，已知目前一季度的产量为400，那么季度增长率为多少全年产量才能达到目标呢？19应用单变量求解：如果需要年产量达到2400，求季度增长率需要达到多少？20应用单变量求解：如果需要年产量达到2400，求季度增长率需要达到多少？21问题：需要年产量达到2400，季度增长率如果保持不变第

8、一季度产量需要达到多少22 王小明夫妇近日准备购屋，已经存到了王小明夫妇近日准备购屋，已经存到了4040万的存款可以万的存款可以缴付头期款，而看上的房价是缴付头期款，而看上的房价是190190万，所以余额万，所以余额150150万必须向万必须向银行贷款银行贷款： （1）若银行愿意提供20年期，利率为固定年利率4.25%，采每月缴纳本息，此状况下每月必须偿还的房屋贷款是多少？ （2）夫妻每月薪资总收入2.3万元，三分之一用来缴纳房贷，是否足以负担？ （3）若负担不起，在相同的条件与状态下，改买总价较低的房子，请问他们可以买得起总价多少的房子？ （4）若改成每月薪资总收入的一半来缴纳房贷，利率(固

9、定年利率4.25%)与贷款金额不变(150万)下，最快可以多久偿还房贷？ （5）若仍维持每月薪资总收入的三分之一来缴纳房贷，仍是贷款20年的状态下，寻求其他愿意提供较低利率的贷款银行，则利率要降低到多少才有能力购买这190万元的房子？u模拟运算数据表，为用户提供了对指定数学模型的观察手段，支持用户观察当模型中1至2个变量在指定范围内变化时，对数学模型所产生的影响。u假设分析中数据表的构成包括三个部分：问题(模型)前提条件的描述(参与模型计算的数据量，主要指常量)模型涉及的变量及其取值范围(只能包括1-2个变量，只涉及一个变量称为单变量数据表，涉及两个变量的称为双变量数据表)问题的数学模型(公式

10、)23u假设投资一项目：初始投资200,000元，经过分析论证,估计项目的建设可以获得年收益6%左右，预计投资时间为10年，需要了解投资各个时期资金总额和相关收益情况。u年收益总额的数学模型为： 初始投资总额 *（1+年收益百分比） 投资年24u年收益总额的数学模型为： 初始投资总额 *（1+年收益百分比） 投资年25u年收益总额的数学模型为： 初始投资总额 *（1+年收益百分比） 投资年u希望观察：在年收益百分比不同情况下收益总额的不同情况。26u年收益总额的数学模型为： 初始投资总额 *（1+年收益百分比） 投资年u希望观察：在年收益百分比不同情况下收益总额的不同情况。27u选择区域：C1

11、:L2u选择数据表计算操作命令：数据选项卡数据工具组假设分析按钮数据表选项进入数据表对话窗口28根据引用对象的存储根据引用对象的存储形式形式，行或列只形式形式，行或列只使用一个使用一个u选择区域：C1:L2u选择数据表计算操作命令：数据选项卡数据工具组假设分析按钮数据表选项进入数据表对话窗口29u设计三个待观察计算目标：到期总额，总收益，总收益率30u设计三个待观察计算目标：到其总额，总收益，总收益率31 本实例中考虑的两个变量为：年收益率和投资时间，计算公式选择为总收益率，年收益率变量的输入值组织与单变量数据表的组织形式相同，投资时间的变量输入值组织为列格式列表，并且位置必须设置在行输入值列表的左侧，设置的输入值为可参考的投资年限范围：6，7，15。32u设置的输入值为可参考的投资年限范围：6，7，15。33u设置的输入值为可参考的投资年限范围：6，7，15。34u设置的输入值为可参考的投资年限范围：6，7，15。35u Excel 方案管理器提供了层次性的数据管理方案与计算功能。每个方案在变量与公式计算定义的基础上