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 文档!

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20181119G0BTM400?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 yunjia_community@tencent.com 删除。

同媒体快讯

扫码关注云+社区

领取腾讯云代金券