使用模拟分析功能分析数据

文摘   2024-12-13 12:00   天津  

接上文:使用数据透视表分析数据

今天,接着讲《财务BP从数据分析到管理报表应用实战》。

第六章 使用模拟分析功能分析数据

模拟分析又称假设分析,Excel附带了三种模拟分析工具,选择【数据-模拟分析】可以调用。

一、方案管理器

假设具有若干个投资方案,可以使用方案管理器在同一工作表中创建这若干个方案,并在这些方案之间进行切换,分析结果会相应地更新,方便比较不同方案的效果。操作步骤如下:

1、建立方案计算表:将基本的模型计算框架搭建好。

2、创建方案:在方案管理器对话框中,点击“添加”按钮,打开“添加方案”对话框。在“方案名”文本框中输入方案名称,在“可变单元格”文本框中输入可变单元格的地址。这些可变单元格是方案中将要改变的单元格。

3、输入方案变量值:点击“确定”后,会打开“方案变量值”对话框,在文本框中输入此方案的变量值,然后点击“确定”。

4、添加其他方案:如果需要,可以重复以上步骤来添加更多的方案,以便进行不同情况下的预测和分析。

5、显示方案结果:在方案管理器中,选择需要查看的方案,然后点击“显示”按钮,Excel会显示该方案的结果。

6、生成方案摘要:在方案管理器中点击“摘要”按钮,打开“方案摘要”对话框,选择创建摘要报表的类型,然后点击“确定”。Excel会创建一个名为“方案摘要”的工作表,显示所有方案的结果。

二、单变量求解

常用于逆向模拟分析,根据目标或结果求相关变量,例如已知税后工资,倒推税前工资等。

在使用单变量求解工具之前,需要先建立正确的数据模型,这个数据模型通常与正向模拟分析时的模型相同。

【案例】某租赁公司购买了一台100万的设备,希望出租这台设备能得到20%的收益,该设备可以使用5年,目前市场的平均利率为8%。

假设该设备出租5年,每年的租金相等且年底支付租金,该设备每年的租金应设定为多少?

1、在工作表中输入设备购买价、租赁年数和市场平均利率。

2、在D7单元格输入公式=

D6/(1+D5)+D6/(1+D5)^2+D6/(1+D5)^3+D6/(1+D5)^4+D6/(1+D5)^5

3、点击【数据-模拟分析-单变量求解】,分别在“目标单元格”录入“D7”,在“目标值”录入“120”,在“可变单元格”录入“D6”,点击确定后,即求解出设备每年的租金应设定为30.05万。

三、模拟运算表

模拟运算表是一个单元格区域,它能够显示一个或多个公式中替换不同值时的结果。

【案例】某人购买一套120平方米的住房,每平方米单价为3万元,总价款为360万元,首付金额为总价款的30%,总价款的70%拟银行贷款,年利率为5.20%。

要求1:测算还款年限为10年、15年、20年、25年和30年时,每月的月还款额。

要求2:假设银行贷款年利率为4.8%、5%、5.2%、5.4%时,测算还款年限为10年、15年、20年、25年和30年时,每月的月还款额。

1、构建计算表,在B10单元格输入公式=C8,其中C8单元格输入的公式=PMT(B2/12,B8,A8)。

2、点击【数据-模拟分析-模拟运算表】,在“输入引用行的单元格”的框内保持空白,在“输入引用列的单元格”的框内录入“E5”,点击确定,不同还款年限对应的月还款额显示在B11:B15区域。

3、最后增加货款年利率变量,模拟运算区域扩大为单元格区域A10:E15,在A10单元格输入公式=C8,选中模拟运算区域(单元格区域A10:E15),点击【数据-模拟分析-模拟运算表】,在“输入引用行的单元格”录入B2,在“输入引用列的单元格”录入E5,点击确定,不同还款年限和银行年利率对应的月还款额显示在B11:E15区域。

今天就到这,下期我们来讲第七章《使用规划求解工具分析数据》。

白明锐
业务财务,注册会计师,定居天津。 学会计,做财务,在职场。 分享财务干货300+篇,付费学员300+人。 探索「财务分析训练营」,验证商业化可行性。
 最新文章