二维码 购物车
部落窝在线教育欢迎您!

excel单变量求解原理以及三个excel单变量求解案例

 

作者:小花来源:部落窝教育发布时间:2018-07-13 10:12:14点击:29205

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

小编有话说:作为一名财务人员,要想升职加薪,肯定不能只会统计数据,还需要根据公司的需要,做一些数据的预测,如果人工去预测,需要经过很复杂的数学运算,其实excel中为我们提供了一款工具可以很简便的完成数据预测,那就是Excel单变量求解功能。


当下,分析预测越来越成为财务人员工作中的重要内容。其中最为常见有效的就是单变量分析。完成单变量分析的方法有很多,比如插值法、试错法等等。但对于一个Excel达人,单变量求解无疑才是最佳答案。


一、什么是单变量求解?

如下图所示,B2单元格为常量,而B4单元格是B2经过某种特定运算的结果。我们可以通过改变B2的数值,从而使B4也跟着改变。这里我们称B2为可变单元格,B4为目标单元格。

excel单变量求解

这种顺序计算非常简单,比如B295B4就为200。那么问题来了,如果我们想让目标单元格(B4)等于某一特定的值,与之对应的可变单元格(B2)值应该怎么求解呢?就好比小学数学里的,我们知道X值可以求得Y值,但如何根据Y值求出X值呢?你需要单变量求解的帮助。单变量求解是函数公式的逆运算。

那么Excel中单变量求解在哪里,以及Excel中单变量求解原理、Excel单变量求解案例咱们都会在本文告诉大家。

举一个例子,我们假设目标值为250,具体操作如下:

Step 01 点击Excel【数据】选项卡—【模拟分析】—【单变量求解】按钮,弹出【单变量求解】对话框。

excel单变量求解案例

Step 02 在对话框中,目标单元格设置为$B$4,再手动输入目标单元格的目标值为250,设置可变单元格为$B$2。如下图所示。

excel中单变量求解实例

需要注意的是,目标值250并不是随便设置的,而需要满足以下两个条件:

1.它是一个整数或小数,不能输入文字或逻辑值;

2.它应该包含在目标单元格值域中,即存在某一可变单元格值使目标单元格值等于目标值,否则,单变量求解将会运行得较长且最终提示无法获得满足条件的解。

Step 03 点击确定按钮,执行单变量求解。excel自动进行迭代运算,最终得出使目标单元格(B4)等于目标值(250)时的可变单元格值(120),并自动赋予可变单元格(B2)。如下所示,点击确定。

excel中单变量求解在哪里


二、Excel单变量求解实例

Excel单变量求解案例贷款利率问题

A员工是甲公司的融资专员,公司计划向银行申请五年期贷款3500万,采取每月等额偿还本息的方法归还贷款本金并支付利息。按目前银行初步提出的年利率6.3%方案,A员工经过计算得出,甲公司每月需支付68.15万。

如下图所示,使用公式:=PMI(年利率/12,期数,贷款额),就可以计算出每月归还利息为68.15万。

excel中单变量求解原理

但经过测算,公司取得贷款后,每月可用于还贷的资金预计只有67.5万。财务总监要求A员工算出公司能接受的最大年利率,以便与银行商议。

A员工分析,年利率B5单元格是可变单元格,每月归还本息额B6是目标单元格,目标值是67.5,这不正是单变量求解吗?

Step 01 根据已经设置好的数据关系,点击【单变量求解】按钮,设置目标单元格为$B$6,目标值为67.5,可变单元格为$B$5,点击【确定】进行单变量求解。

excel贷款利率案例

Step 02  经过迭代运算,excel很快给出了满足条件的解,点击【确定】,完成单变量求解。如下所示。

excel利率函数

从上图我们可以看出,公司可以接受的最大年利率为5.9%。这就是单变量求解,不仅高效,而且精准!

excel盈亏平衡分析

Excel单变量求解案例等效利率问题

B员工是某公司的投资经理,公司计划取得一项新项目,需投入16亿元(累计投资额现值),计划单方售价18800,预计平均回款年限5年,经计算,公司在该项目投资额的等效利率为8.8%

如下图所示,使用公式:=RRI(回款年限,总投资现值,销售收入),可以求出当前投资值对应未来值的实际等效利率。

根据公司内部要求,10亿级项目的投资等效利率不能低于10%,所以需要调整售价。

B员工将售价B3作为可变单元格,等效利率B8作为目标单元格,目标值为10%

Step 01 根据已经设置好的数据关系,点击【单变量求解】按钮,设置目标单元格为$B$8,目标值为10%,可变单元格为$B$3,点击【确定】进行单变量求解。

Step 02 经过迭代运算,excel很快给出了满足条件的解,点击【确定】,完成单变量求解。

如上图我们可以看到结果,最低售价调整为19,879.09元,就可以达到公司的要求。

Excel单变量求解案例盈亏平衡点问题

C员工是某制造公司的财务经理,公司有一工厂,由于市场表现不佳,公司计划缩减工厂产量至20万,C员工需要测算出在该产量下该工厂的盈亏情况。已知产品平均售价随产量上升而下降,三费与销售和收入成一定的比例。经计算,20万产量下的利润总额为1383.68万元。如下图所示。

公司产品市场表现继续下滑,公司高层考虑继续缩减产量,要求将产量控制在盈亏平衡点左右。

C员工思考,将产量B3作为可变单元格,利润总额B12作为目标单元格,目标值为0,一下子就计算出了盈亏平衡产量。

Step 01 根据已经设置好的数据关系,点击【单变量求解】按钮,设置目标单元格为$B$12,目标值为0,可变单元格为$B$3,点击【确定】进行单变量求解。

Step 02 经过迭代运算,excel很快给出了满足条件的解,点击【确定】,完成单变量求解。

根据上图我们可以看到,公司将生产量控制在8.19万附近,可以盈亏平衡。

单变量求解算得上Excel中的一个高能冷门运用,颇有说破不值钱,不说累死人的感觉。以前在这类单变量分析问题上,大家可能花很多时间手动尝试求解,从现在起,你可以从这样的低效率劳作中解放出来了,单变量求解将成为你的得力助手。


本文配套的练习课件请加入QQ群:316492581下载。

如果您因工作所需使用到Excel,不妨关注部落窝教育的《一周Excel直通车》视频课或Excel极速贯通班》直播课系统学习。

如果您想要随时随地学习excel,扫下方二维码,可关注公众号,每日为您推送优质excel教程:


Excel教程相关推荐:

玩转Excel图表系列