第13章_决策分析_2009

1,于洪彦刘金星张洪利,Excel统计分析与决策第13章决策分析,2,主要内容,13.1使用公式进行预测分析13.2使用模拟运算表进行单变量预测分析13.3方案及方案管理器13.4反转的假设分析及单变量求解,3,13.1使用公式进行预测分析,一般的数学模型都包括输入、决策变量和输出。一个简化的投资案例假设房价40万元，首付需要房价的10%，贷款期限30年，月利率为0.65%，我们来看看如果选择不同的贷款方案后贷款总额、月供金额、付款金额和总利息的情况？打开“第13章决策分析”工作簿，选择“投资决策”工作表,4,13.1使用公式进行预测分析,将购买房价和利率作为输入变量，首期付款和贷款期限作为决策变量，月供金额作为主要的输出变量。粗边框加底纹单元格是输入单元格，粗边框单元格是决策变量，双线单元格是输出单元格。,5,13.1使用公式进行预测分析,通过这个计算表，我们可以回答下面的假设分析（what-if）问题如果首期付款金额改变，月供金额如何变化？如果贷款期限变为20年，月供金额如何变化？,6,13.2使用模拟运算表进行单变量预测分析,例如，首期付款金额，不同人的理财观念不一样，选择的可能是10%，20%或者50%，贷款期限，可能选择10年或者20年，购房者很想知道不同的条件下月供金额是多少，从而根据自己的经济能力做出决策。,7,13.2使用模拟运算表进行单变量预测分析,横向模拟运算表,8,13.2使用模拟运算表进行单变量预测分析,横向模拟运算表在D6单元格中输入拟选择的贷款期限60（5年），E6中输入贷款期限120（10年），然后同时选中D6:E6单元格区域，向右填充至I6单元格，就会得到一组不同的由5年到30年的贷款期限在C7单元格中输入公式=B7选定单元格区域C6：I7，单击菜单“数据”“模拟运算表”，在弹出的对话框中“输入引用行的单元格”文本框中单击，然后将鼠标指向B3单元格并单击，则文本框中自动出现“$B$3”；单击“确定”按钮，关闭对话框,9,13.2使用模拟运算表进行单变量预测分析,横向模拟运算表图中C7：I7即为模拟运算表所生成的结果显示区域,10,13.2使用模拟运算表进行单变量预测分析,纵向模拟运算表以计算住房抵押贷款为例，创建一个纵向模拟运算表进行分析决策，假设贷款期限为30年，来测算不同的首期付款情况下付款总额的变化情况。,11,13.2使用模拟运算表进行单变量预测分析,纵向模拟运算表在单元格区域D12：D21中依次输入5%50%，在E11单元格内输入“=B8”,12,13.2使用模拟运算表进行单变量预测分析,纵向模拟运算表选定单元格区域D11：E21，单击菜单“数据”“模拟运算表”，在弹出的对话框中“输入引用列的单元格”文本框中单击，然后将鼠标指向B2单元格并单击，则文本框中自动出现“$B$2”,13,13.2使用模拟运算表进行单变量预测分析,纵向模拟运算表单击“确定”按钮，关闭对话框。图中E13：E21即为模拟运算表所生成的结果显示区域。,14,13.2使用模拟运算表进行单变量预测分析,单输入多项目模拟运算表在决策中经常遇到一个（自）变量的变化影响多个目标结果（因变量）的情况，这个时候就可以使用模拟运算表同时对这些目标进行模拟运算。以计算住房抵押贷款为例，来同时测算首期付款在5%50%之间，贷款总额、月供金额、付款总额和总利息的变化情况。,15,13.2使用模拟运算表进行单变量预测分析,单输入多项目模拟运算表在单元格E11内输入公式“=B6”，在单元格F11内输入公式“=B7”,在单元格G11内输入公式“=B8”,单元格H11内输入公式“=B9”。,16,13.2使用模拟运算表进行单变量预测分析,单输入多项目模拟运算表选中单元格区域11：H21，单击菜单“数据”“模拟运算表”，在弹出的“模拟运算表”对话框中“输入引用列的单元格”文本框中单击，然后将鼠标指向B2单元格并单击，则文本框中自动出现“$B$2”,17,13.2使用模拟运算表进行单变量预测分析,单输入多项目模拟运算表单击“确定”按钮，关闭对话框。为了避免E11：H11单元格的显示影响结果区域数据的可读性，将E11：H11单元格区域的文字颜色设置为白色，或者在不影响所在行其他数据结果显示的前提下，可以将该行隐藏。,18,13.2使用模拟运算表进行单变量预测分析,创建双输入数据表进行预测分析要对两个变量同时变化的模型进行预测分析，可以同时基于行列方向创建二维的模拟运算表，以帮助用户同时分析两个因素对目标变量结果的影响。仍以计算住房抵押贷款为例，使用模拟运算表对双变量进行分析，来看看在不同的首期付款比例和贷款期限下，月供金额是多少，从而可以选择更适合的方案。,19,13.2使用模拟运算表进行单变量预测分析,创建双输入数据表进行预测分析在D12单元格内输入首期付款比例5%，在E12单元格中输入首期付款比例10%。然后同时选中D12：E12单元格区域，向右填充至M12，得到一组不同的首期付款比例。用同样方法，在单元格区域C13：C18内创建一组60360的数值作为“贷款期限”。,20,13.2使用模拟运算表进行单变量预测分析,创建双输入数据表进行预测分析在C12单元格内输入单元格引用“=B7”。,21,13.2使用模拟运算表进行单变量预测分析,创建双输入数据表进行预测分析选中单元格区域C13：M18，单击菜单“数据”“模拟运算表”，在弹出的“模拟运算表”对话框中“输入引用行的单元格”文本框中单击，然后将鼠标指向B2单元格并单击，则文本框中自动出现“$B$2”，“输入引用列的单元格”文本框中单击，然后将鼠标指向B3单元格并单击，则文本框中自动出现“$B$3”,22,13.2使用模拟运算表进行单变量预测分析,创建双输入数据表进行预测分析单击“确定”按钮，关闭“模拟运算表”对话框,23,13.2使用模拟运算表进行单变量预测分析,创建双输入数据表进行预测分析在相同的条件下利用这个模拟运算表来分析其他的变量对C13单元格中的公式或单元格引用进行修改在双变量影响下“付款总额”将C13单元格中的公式修改为“=B8”,24,13.3方案及方案管理器,通过方案进行多变量模拟运算例如，计算住房抵押贷款，由“首期付款”、“贷款期限”和“利率”等三个因素构成贷款分析模型，使用方案对3个变量同时变化进行模拟运算,25,13.3方案及方案管理器,通过方案进行多变量模拟运算选定单元格区域A1:B9，单击菜单“插入”“名称”“指定”打开“指定名称”对话框，勾选“最左列”复选框，然后单击“确定”按钮,26,13.3方案及方案管理器,通过方案进行多变量模拟运算打开方案管理器单击菜单“工具”“方案”，弹出“方案管理器”对话框。,27,13.3方案及方案管理器,通过方案进行多变量模拟运算添加新方案单击“添加”按钮，打开“添加方案”对话框，在“方案名”文本框中可以为当前添加的方案命名在工作表区域内选取变量参数所在的单元格，将“可变单元格”的引用地址添加到文本框中,28,13.3方案及方案管理器,通过方案进行多变量模拟运算编辑方案点击“确定”按钮，打开“方案变量值”对话框来设置该方案的变量数值,29,13.3方案及方案管理器,通过方案进行多变量模拟运算可以编辑每个可变单元格的值，在输入过程中可以使用“Tab”键在各输入框中切换，然后单击“确定”按钮保存相应的方案，返回“方案管理器”对话框,30,13.3方案及方案管理器,通过方案进行多变量模拟运算为对比多组变量变化的影响，可以再创建几个方案。单击“添加”按钮，重复上述步骤,31,13.3方案及方案管理器,通过方案进行多变量模拟运算添加多个方案以后，可以选择显示任意一个方案的取值及其相应的运算结果。在“方案管理器”对话框的“方案”列表中选择需要显示的方案，单击“显示”按钮即可在数据表中显示相应的结果。,32,13.3方案及方案管理器,方案报告的生成在“方案管理器”对话框的“方案”列表框中选中任意一个方案，然后单击“摘要”按钮，弹出“方案摘要”对话框，在其中选择相应的结果类型：“方案摘要”或“方案数据透视表”。,33,13.3方案及方案管理器,方案报告的生成比如选中“方案摘要”单选按钮，然后将光标定位于“结果单元格”文本框，在数据表上选择要显示结果的单元格。可以选择一个单元格，也可以同时选中多个单元格以便在报告中同时显示。本例中选取B6:B9单元格区域作为“结果单元格”,34,13.3方案及方案管理器,方案报告的生成单击“确定”按钮，则会关闭“方案摘要”对话框并自动创建一张名为“方案摘要”的工作表,35,13.3方案及方案管理器,方案报告的生成数据透视表形式的报告如果选中的“结果类型”是“方案数据透视表”，则会自动地创建一个名为“方案数据透视表”的工作表，并激活此工作表显示数据透视表形式的方案报告,36,13.4反转的假设分析及单变量求解,仍以计算住房贷款为例，来看看用户能够支付的最高月供金额为2000元条件下，可以购买房价为多少的房子？选中B7单元格，单击菜单“工具”“单变量求解”，弹出“单变量求解”对话框,37,13.4反转的假设分析及单变量求解,在“目标单元格”文本框中输入模型结果存放的单元格位置，此处会自动填入当前选定单元格的位置，B7，即“月供金额”在“目标值”文本框中输入计算结果的具体取值，“月供金额”最高值为2000元,38,13.4反转的假设分析及单变量求解,在“可变单元格