Consolidation & BI


Key Modules in this Course

Consolidate data with Power Query

  • Install and attach the Power Query add-in
  • Reshape and unpivot data for Power Pivot analysis
  • Send Power Query results to the data model
  • Consolidate multiple excel work sheets within a single file
  • Automate the consolidation of all text files within a folder
  • Write a function to manipulate and consolidate Excel work books within a folder
  • Work with web data

Manipulate data with Power Query

  • Understand Power Query language syntax
  • Reshape and unpivot data for Power Pivot analysis
  • Group data sets
  • Merge tables and queries
  • Clean up & sort data in queries
  • Create custom power query functions

Account for the effects of intra­group transaction

  • Adjust for current accounts between parent company and subsidiaries
  • Deal with cash/goods in transit
  • Deal with inter-group loans held by one company
  • Deal with Dividends and loan interest
  • Adjust for unrealised profits on sales of inventory
  • Adjust for unrealised profits on sales of non-­current assets

Consolidate foreign subsidiaries

  • Deal with foreign currency transactions
  • Understand difference of foreign currency monetary items and non-monetary items
  • Compare spot rates with current rates and make appropriate adjustments
  • Learn to use current rate method
  • Learn to use temporal method
  • Deal with Translation gains or losses
  • Adjust for Transactions gains or losses
  • Incorporate foreign companies (subsidiaries) into the consolidated financial statements

Prepare consolidated financial statements

  • Deal with different chart accounts between head office and subsidiaries
  • Deal with non­-controlling interests (at fair value or proportionate share of net assets)
  • Apply the required accounting treatment of consolidated goodwill
  • Adjust for impairment of goodwill
  • Deal with consolidation of other reserves (e.g. share premium and revaluation)
  • Account for the consolidation of other reserves


  1. 这是我第一次上Terry老师的面授课,本预想周末两天密集的课程安排会很难消化,但老师讲解的思路清晰而且简洁易懂。对Excel Power Query和Consolidation的应用和案例分析相信会在我未来的工作中非常实用并且节省更多时间。谢谢Terry老师!

  2. 非常实用的课程,最近正好遇上foreign entity的合并报表问题,受益匪浅啊。觉得把理论课先上,再上power query会不会更好。

  3. 首先推荐课程,然后推荐Power BI软件。这门课程提供了业务人员处理数据非常好的工作思路和解决方法,我是BI软件业务方使用人员,一直受限于技术上限制,无法自由进行多数据的集中处理,掌握这项Power BI技术后,我的数据建模想法很快能得到实现,工作中也更有成就感了。另外Terry介绍的文件夹数据合并也是提高工作效率非常值得学习的内容。

  4. BI 课信息量大,两天的学习时间安排的满满的,学到很多东西,谢谢TERRY 老师!

  5. Terry老师的Power Query课程为我打开了数据处理的新世界。我以前上过各种的Excel 培训课程,工作中同事如果有碰到Excel的小问题我也都能够帮助解决。但是在Terry老师面前,我觉得自己的Excel只有小学生水平。Terry老师的课深入浅出,条理清晰,信息量非常大,物超所值。这门课程对我的工作带来新的思考和启发。非常期待明年的后续课程。

  6. 物超所值,不虚此行,是每次来上课的感受。课程内容多,课量大,两天的课程安排得非常满,但老师总能用最简洁易懂的方式教出来,所以完全不会听不懂或者跟不上。即使听过的课再听一遍,还是会有新内容可学,对工作效率也好,找新工作也好,都是一个面与质的飞跃。作为AFMA的铁粉,还会继续来听!

  7. PQ brings a new angle to data consolidation. The technique is powerful while the two day course was arranged in an orderly and understandable manner which is easier for a new leaner to step in.

  8. Absolutely revolutionary techinques! I have learnt so much in 2 days not only just power Bi stuff but also lots of handy excel tricks! Terry will explain why he is designing the model in each particular way which is far more valuable than how to do it.

  9. Best course ever!! The technique I learned from this course saved me so much time when it comes with consolidation.

  10. This is the best course I ever did! So much better than the big name training courses eg.CPA. Great in-depth accounting knowledge as well as practical modeling skills, very well presented and trainer Terry is fantastic. I find this course very rewarding and so are the others.

  11. 这次的课程里学到了很多新东西,是以前没接触过的,受益良多。consolidation 虽然以前学校里有学过,但没机会在工作中运用,这次有机会温故知新,很不错。谢谢Terry老师。

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.