Excel在预算管控中的应用

关注后回复"bud-1"，可下载Excel 文档！

1

Excel 在预算管理中的作用

全面预算管理，作为综合管理的工具，被越来越多的企业采用，以实现战略落地、持续改善经营的目的。在预算管理工作中，需要大量的处理数据。一方面编制预算时要收集、综合、预测众多数据，编制销售预算、 生产预算、采购预算、费用预算、资金预算和资本支出预算，最终形成三张财务预算主表， 另一方面在实施预算控制环节，则又要将各种实际发生的数据与预算数据比较分析，以判断预算的执行效果。

企业通常会花1-2个月的时间组织编制预算， 而要每月分析预算的实施效果。预算工作的成效，除了取决预算的编制合理外，更重要的取决于在预算的跟踪和分析。

预算管理工作需要信息化管理工具的支持。有条件的企业通常选择专门的预算软件，也有的企业利用ERP中的预算模块。 更多的企业可能还是处于用些非专门的预算管理工具，比如用excel作为预算管理的主要工具。 对于一般企业甚至小型集团企业，应用Excel也是可以达到预算管理工作对数据处理的要求。

2

利润表预算跟踪举例

本例以利润表为例，每月对比利润表各项目的实际数据和预算数据，及时按要求揭示各项目的差异。

已知2018年各月的预算利润表的数据如下：

2018年实际利润表中输入当月的实际数据及当年剩余各月的预测数据：

任务要求：

1） 对比当月利润表各项的预算数与实际数，计算各项目的完成率；

2）对比当年利润表各项累计的预算数与实际数，计算各项目的完成率；

3）对比全年预测（已过月份的实际数+剩余月份的预测数）数与当全年预算利润表各项。

在经过必要的一次性准备工作后，每月只要更新利润表的各项实际数和预测数后， 可以在极短的时间内 （如5分钟内）完成以上3项目任务。

参考动图：

3

实操过程

Excel与其他以数据库为基础的软件相比，最大的特点就是直观。比如上面的利润表，格式与平时所用的格式基本一样，12个月度的数据分别从左至右排列，利润表项目从上至下排列，非常直观。

但这样直观的数据，与数据库相比起来，分析计算组合数据时会受到很大的限制。

有没有一种方法可将这样直观的数据转化中数据库中的数据？而且当直观的数据发生改变后，数据库中的数据也可以实现更新？可以兼顾直观性与数据处理的便利性？

Power Query可以将如上的直观利润表格式中的数据转化为数据库中的数据。

在有了符合数据库规则的数据后，再利用Excel 的工具对这些数据进行处理就方便很多。

在"2018年预算利润表 ”和“2018年实际利润表”工作表中数据的基础上， 先利用Power Query 将其分别转化为2个数据库数据，再将其合二为一，最后利用Excel数据透视表功能，进行本例要求的多种分析。

第一， 将"2018年预算利润表 " 转换为“2018年预算利润数据库”。

在Excel 2016中，Power Query在"数据"选项卡下的"获取与转换"中。在"2018年预算利润表 "中，点击"数据"，在"获取与转换"中再点击"从表格"，进入Power Query编辑器，可得到：

在Power Query编辑器中，点击"转换", 同时选定1月到12月的各列，如下图：

点击转换选项卡中的"逆透视列", 就得到如下数据库：

在Power Query编辑器中，点击"开始"后，再点击"关闭并上载"，就在工作簿中增加了一个如下的新工表，将其改名为"2018年预算利润数据库”。

以上连续操作请见以下动图：

第二，将"2018年实际利润表 " 转换为“2018年实际利润数据库”，方法同上。

第三， 将得到＂2018年预算利润数据库”与“2018年实际利润数据库”合二为一。

在工作表“2018年实际利润数据库”中，点击"数据"，再点击"从表格"，进入Power Query编辑器，点击 "开始"，在"追加查询"选项下，点击"将查询追加为新查询"，如下：

点击确定后，就将两个表联接起来：

点击左上角的"关闭并上载"，就在原工作簿中增加了一新工作表，将其改名为"2018年预算实际利润数据库合并"。

第四， 利用Excel的数据透视表功能对数据处理，完成任务要示。

在经过以上操作后，得到了"2018年预算实际利润数据库合并"工作表， 该表已将2018年预算和实际的各月利润表项目的各项数据按数据库的要求组织在一起，并且当在利润表中的数据发生改变时，只要在"2018年预算实际利润数据库合并"刷新，数据就实现了同步更新。

任务要求：1） 对比当月利润表各项的预算数与实际数，计算各项目的完成率；

在"2018年预算实际利润数据库合并"工作表中，Crtl+A全选数据，插入数据透视表，调整项目顺序，按下图布局，再增加"计算项"完全率(公式为(实际-预算)/预算*100%），在筛选区中选择2018年8月，即可得到2018年8月预算和实际利润表各项目数据及预算完成率数据：

任务要求：2）对比当年利润表各项累计的预算数与实际数，计算各项目的完成率；

如果在筛选区区域中勾选2018年1月至2018年8月，就得到前8月累计预算实际利润各项目的数据：

任务要求：3）对比全年预测（已过月份的实际数+剩余月份的预测数）数与当全年预算利润表各项。

以"2018年预算实际利润数据库合并"数据，新建一个透视表，同时插入数据透视图，选择折线图并修饰后，在筛选区，选"营业收入", 可得到：

在筛选区，选"净利润", 可得到：

综合以上数据分析结果：

4

结语

1）Power Query 可以将固定格式的数据转换为符合数据库规则的数据；

2）以上方法，可以方便实现利润表各项目的预算实际数据对比，同样也可以用于分析其他项目的变动，如销售收入预算实际变动，只要有各月预算和实际的各产品的销售量和价格信息；

3）同样适用带二级科目的数据分析；

4）对于小型集团，只要数据组织得当，利用这样的方也可以实现对集团各成员单位进行预算管控。

关注后回复"bud-1"，可下载Excel 文档！

下一编： Excel 在小型集团预算管控中的应用

关注后回复"bud-1"，可下载Excel 文档！