EXCEL方案管理器在弹性预算中的应用

顾中林 王洪海 吴茂富

[摘 要] 论文从弹性预算常见问题入手，利用EXCEL软件提供的方案管理器，分别对量本利分析、筹资预算以及现金预算编制进行了分析，并设计了不同环境下的多方案比较，为企业实施预算管理、适应市场变化提供了有益的分析思路与借鉴意义。

[关键词] 预算管理；弹性预算；方案管理器

doi ： 10 . 3969 / j . issn . 1673 - 0194 . 2018. 07. 029

[中图分类号] F232 [文献标识码] A [文章编号] 1673 - 0194（2018）07- 0069- 07

0 前 言

预算（Budget）最早是从法文baguette演变而来，其原意为财务人员的公文包，最初预算是按照固定业务量进行编制的，当市场变化较大或较快时，固定预算往往误差很大，这时有必要采取弹性预算，即以预算期间可能发生的多种业务量水平为基础进行编制。如果采用手工或者直接输入电子表格，这时预算编制工作量会较大，对此本文引入EXCEL提供的方案管理器，即应用管理多变量环境下的数量变动工具，对弹性预算编制进行探讨。

1 利用方案管理器进行量本利分析

案例1：某企业产品销售单价为100元，销售数量为1 750件，销售数量为1 750件，单位变动成本50元，固定成本30 000元，由于市场变化很大，通过市场促销，销售数量可能提升为1 850件、1 900件、1 950件、2 000件，试利用方案管理器对利润进行分析，见图1。

当销售数量=1 850件时，销售利润=销售收入–固定成本–变动成本

=销售数量（销售单价–单位变动成本）–固定成本

=1 750（100-50）-30 000=57 500元

如果销售数量变化时，还需要采用同样的程序进行计算，但是在实际财务工作时，通常涉及的财务数据很多，其中有部分因素变动，这时引入方案管理器就会减少工作量、提高工作效率，而且应用方案管理器摘要功能可以清晰反映变动因素对目标指标的影响，操作过程为：打开EXCEL软件中的[数据]菜单→找到[模拟分析] →选择[方案管理器]，输入相应的变量并计算，其运行结果见图2。

从图中可知销售数量变动时相应的利润数据。

2 利用方案管理器進行筹资预测

案例2：已知某公司2018年预计销售收入比去年增长15%～20%，为了实现上述目标，公司需要增加固定资产投资150万元，根据历史数据分析，公司流动资产与流动负债随销售收入同比例变化。

2017年资产负债如表1所示。

2017年销售收入为20 000万元，该公司近几年销售净利率为12%，分配给投资者的利润占比为60%，试分析销售增长率对筹资额度的影响。

这是一个应用销售百分比预测资金的题目，是各类会计师典型考题，原考题假设销售增长率为固定数，例如销售增长率为15%，那么对外筹资额度，应用如下计算公式：

F=（A-L）■+M-S1R（1-d）

其中：F表示对外筹资额度；

A表示敏感资产；

L表示敏感负债；

M表示固定资产投资；

S1表示预测期销售收入；

S0表示基期销售收入；

R表示销售净利润率；

d表示股利发放比例。

将相关资料录入EXCEL表格，并设置相应的计算公式，详细情况见图3，则对外筹集资金F为：

F=（1 000+3 000+6 000）-（1 000+2 000）×15%+150-20 000（1+15%）×12%（1-60%）=96万元

3 利用方案管理器编制现金预算

案例3：某企业2017年有关预算资料如下：

（1）某公司3-7月份销售收入分别为40 000元、50 000元、60 000元、70 000元以及80 000元。每月销售收入中，应收账款回收可能情况如下：

A方案当月收取现金30%、次月收取现金70%；

B方案当月收取现金40%、次月收取现金60%；

C方案当月收取现金50%、次月收取现金50%；

D方案当月收取现金60%、次月收取现金40%。

（2）各月直接材料采购成本按下一个月销售收入的60%计算。所购材料款于当月支付现金50%，下月支付现金50%；

（3）该公司4-6月份的制造费用分别为4 000元、4 500元和4 200元。每月制造费用中包括固定资产折旧费1 000元；

（4）该公司4月份购置固定资产，需要现金15 000元；

（5）该公司在现金不足时，向银行借款（借款为1 000元的倍数）；在现金有余时，归还银行借款（还款为1 000元的倍数）。借款在期初，还款在期末，借款年利率为12%，并按所借款项目计算利息；

（6）该公司期末现金余额最低为6 000元。其他资料见现金预算。

要求：根据以上资料，完成该企业4月份现金预算的编制工作。

这是一个常见的现金预算编制题目，是各类会计师典型考题，原考题假设应收账款回款为固定比例，例如A方案，当月收取现金30%、次月收取现金70%，那么预算编制如下图5。

只要熟悉手工编制原理，在EXCEL表格中设置公式还是比较容易的，这里需要特别注意的是“现金余缺”指标，如果该指标为正，这时需要偿还银行贷款；如果该指标为负，这时需要向银行借款，为了使EXCEL预算编制实现自动判断应采用IF语句，其标准格式如下：

IF（logical_test，value_if_true，value_if_false），其意义为假设条件性函数，即执行真假值判断，根据逻辑真假值，返回不同结果。

在财务实际借贷工作中，通常需要采用整数，例如案例3中银行借贷均为1 000的倍数，为此引入Roundup与Rounddown函数，其标准格式如下：

Roundup（number，num_digits），其意义为用于按照指定的位数对数据进行向上舍入；Rounddown（number，num_digits），其意义为用于按照指定的位数对数据进行向下舍入，两个相似函数对比见表3。