关于用Excel做budget的简化问题

e500490

来新公司上班一个月了，被如此manual的process惊到了，感觉好像一下回到了解放前，accounts只能用一个乱字形容。
公司一共有九百多charts of accounts，一共九个部门，每个部门都有自己的一套code，而且还不是规律的，因为有些code可能从一个部门转到另一个部门的budget里了。
这个历史遗留问题先不说他。公司每个部门都要做一份budget，而且要求的内容非常非常的细，每一项都有一个spreadsheet做note，然后每个部门的总表就直接等于每个note里面的数。而这个note的格式也是五花八门并不统一。直接导致每一个部门的budget有九十个tab要一个一个update，而且formula就是简单的等于，非常容易出错，费时费力。每个人都抱怨这种格式，可本人能力有限，暂时想不出更好的model。请问高手们做budget都是用什么样的model呢？尤其像我们这种要求非常细的，每个detail都要写进去的，怎样建模版比较好呢？
说半天也不知道说明白没。谢谢

thundom

做excel数据管理分析最害怕的就是tab过多，十个以内的tab还勉强可以，一旦超过十个，大家都有为了找一个tab费好大力气的经验吧，而且，tab建立formula其实是非常难办的，如果格式不规范，用macro做一样麻烦。

应该同样数据结构利用不同维度进行管理。

换句话说，类似于Pivot table，十个tab其实就相当于多了一个column，然后里面加上一个新的项目而已。

我的建议是，进行一次数据架构设计，首先把你们公司的budget通用，必要的数据项找出来，比如会计科目，每个月的budget，年度总和，YTD，然后设计一个通用的格式模板。

下一步是清理数据，把当前的数据，按照这个格式进行归类，标注，清理。

在这个过程当中，可以同步进行历史科目的清理，把所有部门乱七八糟的科目做一个总结和列表，然后沟通各个部门设计一个通用的代码规则。

下一步，把所有的清理，分类好的各部门budget，整理统一格式，做一个数据源。

Excel大型数据管理的一个比较通用的principle就是，数据在后台，呈现在前台利用formula实现。原因很简单，前台呈现的总表，肯定有很多formula，如果你手工数据和formula掺和一起，一旦出错，或者一不小心删除一个formula，那就够你喝一壶了。后台数据就是一个个标准格式，可以直接pivot table的表，检查错误，数据分析非常轻松，一旦误删除什么立马就找得到。

后台数据可以参考星型数据库，把数据源分门别类，进行输入管理。（具体什么事星型数据库可以google，不用害怕，其实很简单的概念，你可以参考那个概念，作为思路建立不同Excel files）


=====================================

最终目标是，前台呈现的最终budget，要全部用动态连接连到相应workbook或者spreadsheet上去。升级修改数据，通过修改后台的rawdata worksheet进行。

数据清理和数据更新的过程其实可以用很多macro解决问题，后期可以考虑利用数据库，用powerbi呈现，因为我讲的是基于Excel，但是如果利用Dax和M language，建立这种数据模型要轻松很多，比Excel效率要高。

对了，对这种项目，Excel file最好保存成Xlsb，size小速度快，不然修改一下数据有的file可能几百兆，会郁闷死你。

以上。

e500490

非常感谢你的回复，听着好高大上啊。我Excel只会一些formula和pivot table，macro和 数据库那些不太在行。像你说的在后台运行应该可行，但我可能做不到。
还有个问题就是如果在后台，其他部门还能用吗？我们目前是几个部门的head负责做他们自己部门的budget，他们要一个tab一个tab的更新，而且每个tab格式都不同，有些一个tab里面有三个account code，有些里面的数据还要有另外一些数据支持，tab里面有一些表格链接，比如说球队出去打比赛的住宿，总和就是几个晚上都在哪里住宿以及人数的total，再比如member费用，也是按照不同种类和价格加总。总之每个tab的格式都不尽相同，total也都在不同列。而且还要写comment。每次他们也都是怨声载道。而且都没有protect，很容易就出错了，那里忘了连了，或者连错了。我汇总的时候更头疼，要一个一个tab检查，确定formula都连好了，数据都连到总表了。
暂时我还做不成数据库那种。临时想的办法就是protect worksheet，但他们要输入数据，而且每个tab都不同，不能统一protect，要挨个tab做，也很浪费时间。真是黔驴技穷了。
当务之急是统一格式，但也不容易。真愁人

thundom

e500490 发表于 2018-9-23 08:04
非常感谢你的回复，听着好高大上啊。我Excel只会一些formula和pivot table，macro和 数据库那些不太在行。 ...

我说的后台，是后台的excel，每个部门有一个做data s ource的excel file。

格式参考pivot table的 data source，如果你会sql你就知道了，就是数据库的格式，一个field一列

thundom

e500490 发表于 2018-9-23 08:04
非常感谢你的回复，听着好高大上啊。我Excel只会一些formula和pivot table，macro和 数据库那些不太在行。 ...

这是一个大的project，对你的能力是大挑战和提升。

第一，no pain no gain。你能做出来你就牛了。

第二，可以好好锻炼你的沟通能力，对于事业大有帮助。搞得好你年薪翻一番。


我一直说，澳洲是讲实力的地方，别人搞不定你能搞定，你就有资格多拿钱，当然还有一个前提是你要能说会讲

jhsun3

最简单的，数据全部进入access,然后你需要一个gap table,也就是一个map table把各部门的不同部门的codes全面和总部的一套code对应起来，也就是说无论那些部门用什么codes来做他们的内部budgets或forecasting,那些代码最终都能和你们总汇表格里的代码能连上。。这样做起来就简单多了。。。


话说，这个工作量是一个BA要做的，这个需要把他们各个部门用的代码全部列出来，然后把你们总汇里的各项也列出来，一个个部门要他们自己去填写每个代码对应的是哪个总汇里的项目。。。一套business rule要endorse by 各个部门的头，这样大家才能在同一个page 上。 这个工作量，真不是一个会计能完成的。。。但如果你做成了，那离升迁也不远了。。。

e500490

jhsun3 发表于 2018-9-23 10:11
最简单的，数据全部进入access,然后你需要一个gap table,也就是一个map table把各部门的不同部门的codes全 ...

谢谢你的建议。我真没这能力呵呵，也没这个精力时间，所有的budget马上就due了，我根本没时间研究新的方式了，光是挨个tab检查数据formula都够我做一气了，九个workbook五六百个tab。只不过目前的状态实在太太太manual了，而且非常容易出错。多年积压下的问题不是一朝一夕解决的了的。明后年要换系统，到时候又要大变，不知道现在搞是否有意义。就是苦了我这几天苦哈哈的做这么没有技术含量的活，就怕哪里照顾不到总表数据就不对了，也没个check，真是心里好没底。作为会计，无论啥东西都想reconcile有个check，这个基本无解，心里不舒服。

e500490

thundom 发表于 2018-9-23 10:10
这是一个大的project，对你的能力是大挑战和提升。

第一，no pain no gain。你能做出来你就牛了。

唉，sql都还给老师了。。现在发现it数据库真是太太太有用了。以前公司的经理是数据库出身，把我们会计系统数据搞的非常的auto，基本一个键子就搞定了，现在这个真的是让我欲哭无泪啊。老板又是个非常非常追求细节的人，一切能放的细节都不能放过，才搞成现在这样乱七八糟。我真理解经理为啥辞职走人连交接都没有了，真是一笔乱帐啊，看着我都恶心

jasonliu234

亲，了解一下  excel 中的data model 不输于数据库。

e500490

jasonliu234 发表于 2018-9-23 10:39
亲，了解一下  excel 中的data model 不输于数据库。

哦谢谢。我还在一个tab一个tab检查，浪费时间浪费生命，气的我想骂人！太他妈手工了！而且费老大劲还不知道对错

thundom

我个人不推荐access 功能实在是太弱了 查询界面和其他数据源的连接也非常弱

flyinfree

jhsun3 发表于 2018-9-23 10:11
最简单的，数据全部进入access,然后你需要一个gap table,也就是一个map table把各部门的不同部门的codes全 ...

同学,一般你说的这种量化用的CODES的话都是资源管理系统如SAP之类的啦,估计楼主的公司还没有大到需要用到这个的程度........

jhsun3

flyinfree 发表于 2018-9-23 12:12
同学,一般你说的这种量化用的CODES的话都是资源管理系统如SAP之类的啦,估计楼主的公司还没有大到需 ...

我说的code不是sql script..而是公司通常用的缩写。楼主的意思是每个部门做自己budget,用的每个项目的abbreviations都不一样，所以很难整合， 那就需要一个mapping table了。。让每个部门的列项缩写可以互相对照，然后用sql (或者在excel 里的 match）把它们自动联系起来。。。

flyinfree

e500490 发表于 2018-9-23 10:33
谢谢你的建议。我真没这能力呵呵，也没这个精力时间，所有的budget马上就due了，我根本没时间研究新的方 ...

可以理解,别人自个创建的EXCEL报表模式对于他们自身使用是OK的,但他们的表格不适合用于大规模的合并和大量的数据更新.

所以我基本上都是把有可能会再次使用的表格给重组了,通过交流和讨论后,把丫们的表格全部FORMULA化,用统一的DATA SHEET来更新.

像你这样的活,复杂的只在于确保各个SHEET的DATA ENTRY精确度和FORMULA是否正确使用. 对于这样的情况,你一般要考虑:
1, 随便吧,先用着丫的垃圾表格模式,咱就人工手动排查,先把活干完再说.
2, 忍无可忍,活干到一小半,发现继续下去浪费的时间远比我重新组合一个的工作量还多,那就硬吃吧.........

flyinfree

jhsun3 发表于 2018-9-23 12:16
我说的code不是sql script..而是公司通常用的缩写。楼主的意思是每个部门做自己budget,用的每个项目的abb ...

看各个公司的情况吧,我司使用SAP系统,但是SAP是死的,人的需求是万能的,所以我们把数据导出SAP后还是需要用EXCEL做成各类报表, 楼主公司的情况可能在于没有一套类似SAP一样的系统来管理数据,所有的原始数据都要从报表中获得,这样的话就要对整体的各个DATA SHEET进行整理了,我一般会按部分,把丫的报表统合成一类,然后丫的汇总数据LINKED到总分析报表里面.

只要把关系清顺,其实就是时间而已,总报表可以尽量用PIVOT TABLE来汇聚成各类型的表达方式. 和老板直接交流,按他/她的要求,尽可能把数据汇总最后用图示表达出来,会更好一些.

jhsun3

e500490 发表于 2018-9-23 10:33
谢谢你的建议。我真没这能力呵呵，也没这个精力时间，所有的budget马上就due了，我根本没时间研究新的方 ...

excel里多放几个offset (match()) formulas ...可以临时减轻繁重的manual intervention.

因为不知道你具体怎么个繁琐法，不然倒是可以给你点实质性的简便的streamline processes

flyinfree

此外,我是经常做数据纠错分析的,你把你的工作弄完后,如果有上一期的数据,就全导出去,经行大量的数据对比,一般来说,相邻两期的数据波动不会太大,你设定一个波动数值(5%或10%),超过的就人工排查,否则就当数据准确看待.

jhsun3

thundom 发表于 2018-9-23 11:40
我个人不推荐access 功能实在是太弱了 查询界面和其他数据源的连接也非常弱 ...

你这要考虑到他公司目前有什么系统， 哪些是现拿现用，不需要IT dept同意的。。而且他的requirements，access就足够了，可以很简便的从excel里调取。

thundom

flyinfree 发表于 2018-9-23 12:18
可以理解,别人自个创建的EXCEL报表模式对于他们自身使用是OK的,但他们的表格不适合用于大规模的合并和大 ...

握手，咱俩思路基本一样

thundom

flyinfree 发表于 2018-9-23 12:18
可以理解,别人自个创建的EXCEL报表模式对于他们自身使用是OK的,但他们的表格不适合用于大规模的合并和大 ...

再就是如果有时候excel卡死就试试用VBA，我有个工具有个tab坏了打不开删不掉一选就死掉，用VBA删掉就好了。

flyinfree

thundom 发表于 2018-9-23 12:51
再就是如果有时候excel卡死就试试用VBA，我有个工具有个tab坏了打不开删不掉一选就死掉，用VBA删掉就好了 ...

我好像很少碰上这样的情况,我的卡死全是因为由于报表包含的数据量太大,在进行修改时的重新计算时造成的冗机,经常就这样死了.......然后SB公司还一直说无法对我的系统升级,因为没需要,我操.......

thundom

flyinfree 发表于 2018-9-23 13:09
我好像很少碰上这样的情况,我的卡死全是因为由于报表包含的数据量太大,在进行修改时的重新计算时造成的冗 ...

加内存，试试power pivot，换大公司

flyinfree

thundom 发表于 2018-9-23 13:22
加内存，试试power pivot，换大公司

本来准备投SOUTH32,结果刚好截止了.......唉,就等下一期吧,公司HR说明年会对我REVIEW,可能会有PROMOTION,但还要半年,我感觉意义也不是很大,毕竟我司在土澳算是很大的制造企业了,在全球范围上来看,虽然进不了五百强,但也是一千强左右的位置.

e500490

jhsun3 发表于 2018-9-23 12:29
excel里多放几个offset (match()) formulas ...可以临时减轻繁重的manual intervention.

因为不知道你具 ...

我现在就是需要streamline，其他数据库vba高大上的东西是远水解不了近渴。很想知道有什么streamline的方法

e500490

flyinfree 发表于 2018-9-23 12:18
可以理解,别人自个创建的EXCEL报表模式对于他们自身使用是OK的,但他们的表格不适合用于大规模的合并和大 ...

我现在是1了一阵子就开始骂娘，然后发现2又不行又回到1，已经要疯了。
其实根源在于系统chart of account就很messy，九百多个，然后budget template也是更加凌乱，想想六百多p&l的code分到九个部门，平均一个部门五六十个tab，每个tab格式都不同，连到总表公式也是低级的等于而已，之前还换过code换过名字换了部门这些就不说了，挪行移位的也有发生。难怪以前经理走了，这乱的啊，没点本事真不行

e500490

jhsun3 发表于 2018-9-23 12:29
excel里多放几个offset (match()) formulas ...可以临时减轻繁重的manual intervention.

因为不知道你具 ...

offsetmatch这类的也要有一个tab一个tab的连吧？而且也不保证之后格式有变化的情况吧？

thundom

e500490 发表于 2018-9-23 13:33
我现在就是需要streamline，其他数据库vba高大上的东西是远水解不了近渴。很想知道有什么streamline的方 ...

数据线先行，你raw data不clean up其他都是白搭。

rubbish in， rubbish out。

而且基础结构不好，任何结构改动都会更复杂

e500490

thundom 发表于 2018-9-23 11:40
我个人不推荐access 功能实在是太弱了 查询界面和其他数据源的连接也非常弱 ...

access对于我来说已经高大上了。
我们的系统也是非常烂，reckon，都不是erp，说是明年底后年考虑换系统。目前实在顾不过来

jhsun3

e500490 发表于 2018-9-23 13:33
我现在就是需要streamline，其他数据库vba高大上的东西是远水解不了近渴。很想知道有什么streamline的方 ...

看你自己的时间了，如果可以先手动自己画个flowchart，每个数据/报表/charts 从哪个进来，最后怎么汇总到一起。。可以帮你理清自己的思路。。如果不会vba的话，那就只能用formulas了。每当自己遇到formula难题，不知道formula怎么写才能达到目的，直接google，一般都有类似的formulas在网上可以查到。。。

e500490

thundom 发表于 2018-9-23 13:40
数据线先行，你raw data不clean up其他都是白搭。

rubbish in， rubbish out。

你说的有道理，基础结构的确很糟糕。看来只能先这么对付上，变骂娘边干，然后再想其他了。好在一年才做一次，我发誓明年一定不用这破template了，至少要protect一下。
只是目前不知道对错很闹心