作为一名财务人员,要想升职加薪,肯定不能只会统计数据,还需要根据公司的需要,做一些数据的预测,如果人工去预测,需要经过很复杂的数学运算,其实excel中为我们提供了一款工具可以很简便的完成数据预测,那就是Excel单变量求解功能。
手机如何做表格:点击查看
当下,分析预测越来越成为财务人员工作中的重要内容。其中最为常见有效的就是单变量分析。完成单变量分析的方法有很多,比如插值法、试错法等等。但对于一个Excel达人,单变量求解无疑才是最佳答案。
一、什么是单变量求解?
如下图所示,B2单元格为常量,而B4单元格是B2经过某种特定运算的结果。我们可以通过改变B2的数值,从而使B4也跟着改变。这里我们称B2为可变单元格,B4为目标单元格。
这种顺序计算非常简单,比如B2为95,B4就为200。那么问题来了,如果我们想让目标单元格(B4)等于某一特定的值,与之对应的可变单元格(B2)值应该怎么求解呢?就好比小学数学里的,我们知道X值可以求得Y值,但如何根据Y值求出X值呢?你需要单变量求解的帮助。单变量求解是函数公式的逆运算。
那么Excel中单变量求解在哪里,以及Excel中单变量求解原理、Excel单变量求解案例咱们都会在本文告诉大家。
举一个例子,我们假设目标值为250,具体操作如下:
Step 01 点击Excel【数据】选项卡—【模拟分析】—【单变量求解】按钮,弹出【单变量求解】对话框。
Step 02 在对话框中,目标单元格设置为$B$4,再手动输入目标单元格的目标值为250,设置可变单元格为$B$2。如下图所示。
需要注意的是,目标值250并不是随便设置的,而需要满足以下两个条件:
1.它是一个整数或小数,不能输入文字或逻辑值;
2.它应该包含在目标单元格值域中,即存在某一可变单元格值使目标单元格值等于目标值,否则,单变量求解将会运行得较长且最终提示无法获得满足条件的解。
Step 03 点击确定按钮,执行单变量求解。excel自动进行迭代运算,最终得出使目标单元格(B4)等于目标值(250)时的可变单元格值(120),并自动赋予可变单元格(B2)。如下所示,点击确定。
二、Excel单变量求解实例
Excel单变量求解案例1 贷款利率问题
A员工是甲公司的融资专员,公司计划向银行申请五年期贷款3500万,采取每月等额偿还本息的方法归还贷款本金并支付利息。按目前银行初步提出的年利率6.3%方案,A员工经过计算得出,甲公司每月需支付68.15万。
如下图所示,使用公式:=PMI(年利率/12,期数,贷款额),就可以计算出每月归还利息为68.15万。
但经过测算,公司取得贷款后,每月可用于还贷的资金预计只有67.5万。财务总监要求A员工算出公司能接受的最大年利率,以便与银行商议。
A员工分析,年利率B5单元格是可变单元格,每月归还本息额B6是目标单元格,目标值是67.5,这不正是单变量求解吗?
Step 01 根据已经设置好的数据关系,点击【单变量求解】按钮,设置目标单元格为$B$6,目标值为67.5,可变单元格为$B$5,点击【确定】进行单变量求解。
Step 02 经过迭代运算,excel很快给出了满足条件的解,点击【确定】,完成单变量求解。如下所示。
从上图我们可以看出,公司可以接受的最大年利率为5.9%。这就是单变量求解,不仅高效,而且精准!
Excel单变量求解案例2 等效利率问题
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单变量求解案例3 盈亏平衡点问题
C员工是某制造公司的财务经理,公司有一工厂,由于市场表现不佳,公司计划缩减工厂产量至20万,C员工需要测算出在该产量下该工厂的盈亏情况。已知产品平均售价随产量上升而下降,三费与销售和收入成一定的比例。经计算,20万产量下的利润总额为1383.68万元。如下图所示。
公司产品市场表现继续下滑,公司高层考虑继续缩减产量,要求将产量控制在盈亏平衡点左右。
C员工思考,将产量B3作为可变单元格,利润总额B12作为目标单元格,目标值为0,一下子就计算出了盈亏平衡产量。
Step 01 根据已经设置好的数据关系,点击【单变量求解】按钮,设置目标单元格为$B$12,目标值为0,可变单元格为$B$3,点击【确定】进行单变量求解。
Step 02 经过迭代运算,excel很快给出了满足条件的解,点击【确定】,完成单变量求解。
根据上图我们可以看到,公司将生产量控制在8.19万附近,可以盈亏平衡。
单变量求解算得上Excel中的一个高能冷门运用,颇有“说破不值钱,不说累死人”的感觉。以前在这类单变量分析问题上,大家可能花很多时间手动尝试求解,从现在起,你可以从这样的低效率劳作中解放出来了,单变量求解将成为你的得力助手。
相关学习推荐:excel教程