Excel在集团预算管控中的应用

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

1

利用Excel 跟踪集团预算关键经营指标

预算编制完成经过一定程序批准后， 就进入执行阶段。及时高效地跟踪预算，将各种发生的数据与预算数据对比，以判断预算的执行效果。预算管理的成效，极大程序上取决于对预算的跟踪和分析。

除了专门的预算软件或ERP中的预算模块外，还有很多企业利用通用的软件，如excel作为预算管理的工具。 对于一般企业甚至小型集团企业，应用Excel也可以达到预算管理工作对数据处理的要求。本例介绍用excel的Power Query 和透视表功能，实现对集团预算跟踪管理的思路和操作方法。

基本背景：A集团旗下拥有甲、乙和丙3家子公司。2018年预算已编制完成，依据集团管理重点，需跟踪关键经营指标：营业收入、销售费用、管理费用、净利润、经营净现金流量、资本性支出、应收帐款和存货，及时掌握集团公司及各子公司的预算预算执行情况。

部分结果展示： 集团和甲乙丙各公司2018年1-9月各月及累计营业收入预算执行结果。

2

实施的基本思路

新建一工作表A，用于整理3家公司2018年预算需重点跟踪的指标数据，从左至右，分别列示1-12月的相关数据，此格式符合人们的日常习惯，直观方便。

另建一工作表B，按以上工作表的格式，收集 2018年各公司的1-12月的实际数据。

再新建工作表C，利用Power Query,将以上两表追加合并成一张工作表。当以上工作表数据有更新时，在本工作表中可点击右键刷新。

通过追加合并形成的工作表C，也是直观的格式（从左到右分列1-12月的数据）。为便于后期方便利用数据透视表处理数据，需将工作表的直观格式，通过Power Query 转变成有数据库特点的，即通常所说的一维格式的数据集合， 并存放于工作表D中。

利用工作表D中的数据，建立数据透视表，进行多种布局组合，就可实现对多种经营指标预算和实际数据的对比分析。

在"2018年实际 指标”表中，在指定月份列下，输入各公司实际经营指标，或手动更新预算执行结果数据，或录制宏，实现高效自动更新。

3

实施的基本过程--整理数据

1

创建2018年各公司各月预算指标表

将集团所属的3家公司甲乙丙2018年预算指标通过手工或从系统导入，按以下格式输入， 工作表命名为"2018年预算指标"。

除了字段"公司名称"、"类别"、"指标名称"外，将1-12月数据从左到右填入1-12月各列下。字段"类别"中填入“预算”，表示此表是预算数据。 该表中的数据，在预算批准后，一次性录入，通常全年不变。

2

创建2018年各公司实际指标工作表

该表与以上预算工作表格式一致，字段"类别" 下输入"实际"，是2018年的各指标的实际数据，工作表命名为"2018年实际指标"。各月结帐后，录入集团所属3家公司需跟踪指标的实际值。

3

追加合并以上两个工作表

光标定于工作表"2018年预算指标"中, 点击"数据"->"从表格"，进入Power Query编辑器， 将"属性"下的"表"改名为"2018年预算指标",如下图：

点击左上角的"关闭并上载"，点击下拉菜单中的"关闭并上载至"后，再点选"仅创建连接"，最后点击 "加载", 如下图：

用同样方法将工作表"2018年实际指标"，调入编辑器并创建连接查询，名称为"2018年实际指标"。这样就建立了两个查询连接："2018年预算指标"和"2018年实际指标"。

将光标定于任意一工作表中， 点击"数据", 再点" 新建查询"，点"追加查询"，按下图选项：

点击 ”确定“后，双击左上角的"关闭并上载"，就在工作簿中自动生成了一张新工作表，改名为"2018年预算实际指标集合"，工作表内容显示如下：

这样工作表"2018年预算指标"和"2018年实际指标"，就追加组合成了一张结构相同的工作表了。

4

转换多维表为一维表

光标任意于工作表"2018年预算实际指标集合"中，点＂数据＂－＞＂从表＂ ，进入“Power Query 编辑器”，选定2018年1月至12月各栏，点击 ＂转换＂－＞双点"逆透视列"，得到：

双击左上角的"关闭并上载"，在工作簿中生成一张新工作表，改名为"2018年预算实际指标库结构"。

通过以上操作，就将多维表"2018年预算实际指标集合"转换为具有数据库特征的一维表数据。在一维表的基础上，数据透视表就有了用武之地。

4

实施的基本过程--建立数据透视表

1

集团营业收入预算执行结果

将光标定于工作表"2018年预算实际指标库结构"任意单元格，在新工作表中插入数据透视表，并改名为"营业收入"。

将字段"公司名称"和"指标名称"拖入筛选区域，将字段"属性"拖入列区域，将字段 "类别" 拖入行区域，将字段"值"拖入数值区域，在筛选区域的"指标名称"右侧下拉菜单中选"营业收入";

在"数据透视表工具"下的"分析"项下，选点" 字段、项目和集"，点"计算项"，在公式中输入=(实际-预算)/(预算)，公式名定为"预算执行率";

插入筛选器，选定字段"属性"，编辑为两列，同时选定2018年1月到时2018年9月共9项（也可以选其他月份组合）。

这样得到了3家公司合计的营业收入2018年1-9月各月及累计的预算数、实际数和预算执行率数据如下：

2

甲乙丙营业收入分月预算执行结果

复制上面的数据透视表， 并粘贴于其下面，将字段"公司名称"从筛选区域拖入行区域的字段"类别"前面，取消分类汇总，得到：

这就是3家公司各月及累计的营业收入预算执行数据。

03

净利润经营净现金流量预算执行结果

有了上面数据透视表的基础，通过一些简单的操作，就能很方便地得到其他营业指标的数据，如净利润、经营净现金流等等。

将透视表"营业收入"拷贝于另一工作表，取名为"净利润"， 在"指标名称"选项下，改选为"净利润", 就即刻得到净利润预算执行情况的数据：

同样的方法，可以得到经营净现金流量预算执行情况数据：

04

应收帐款的预算执行结果

同样方法建立另一透视表后，改"指标名称" 字段下选项为"应收帐款", 由于应收帐款是时间指标，累计数据不合适，故勾选掉。

其他经营指标销售费用、管理费用、资本性支出和存货，按此方法，同样可轻易可得到。

05

关联月份选项与多种透视表

以上建立的4张透视表“营业收入”、"净利润”、“经营现金流量"和"应收帐款"的数据，都与月份选项有关， 如果每张表分别选择月份，不仅很麻烦，而且容易出错。可以通过将"属性"筛选器中的月份选项与多种透视表关联，实现一次选择，多表适用的效果。

在"营业收入"透视表中，选中"筛选器"， 点击"筛选器工具"下的"选项"后，再点击左上角的"报表连接"， 勾选菜单左边的选项， 这样"属性"， 也就是月份与其他透视表“净利润”、“经营现金流“和”应收帐款“连接起来了。 当在透视表“营业收入”中点选筛选器中的月份， 所选的月份对其他透视表“净利润”、“经营现金流“和”应收帐款“同样取作用。

5

实施的基本过程--更新数据

在以上操作过程中形成的各种表格中，透视表是以工作表"2018年预算实际指标库结构”为数据源的，而工作表"2018年预算实际指标库结构”是 "2018预算实际指标集合”逆透视查询结果，"2018预算实际指标集合”是由工作表"2018年预算指标"和"2018年实际指标"的追加查询结果。

当A、B中的数据发生变化时，由此而生成的C、D、E 诸表中的数据都需要更新，遗憾的是当A、B 中的数据变化时， D、C、E 中的数据不能自动更新，需要手动分别完成更新。

但是可以通过录制宏来实现自动更新。A中预算数据在预算编制完成后，通常会固定，一年之中不会变化。 而B中的数据每月会有新数据输入。

宏的录制主要包括3种更新，点按开始录制宏后， 将光标定于C表中，右击，点击刷新; 将光移到D表，右击，点击刷新; 再将光移到E类表如"营业收入"中，点击"数据透视表工具"下"分析"下的"刷新"，再点"全部刷新", 光标移到工作表"营业收入"中。 在工作表"2018年实际指标"中插入控件按钮,取名"刷新结果",并将其与刚录制的宏连接起来。

这样，当每月在B表中输入各公司指标数据后，只需点击"刷新结果"按钮，表 D、C和E表中的各备战指标预算执行结果数据全部都同时更新。

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

6

结语

1）Power Query 能方便将多维数据转换为容易处理的一维数据；

2）数据透视表能十分灵活的分析处理数据；

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

4）本例预算执行结果可以进一步以其他形式的图表输出；

5）数据分门别类，存放于不同的工作表，便于操作；

6）输入输出表用直观多维格式，数据处理以一维表为基础。

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