Dynamic Business Report

Dashboards provide visual, at-a-glance display of business key information and help managers make quick decisions. Excel is an excellent tool to make powerful dashboards, but creating a dashboard takes a lot of skill & practice.

In this course you will learn all the tools and techniques needed for dashboard, and you will also learn how to apply advanced modelling skills to build complex, robust and user-friendly dynamic business & management reports.


Key Modules in this Course

Principles of Dynamic Reporting

  • Introduce ETL methodology
  • Design the blueprint for dynamic dashboard
  • Introduce dashboard design tips & tricks
  • Discover the critical issues of dynamic charts
  • Standardize business report through best practice procedures
  • Case: Common errors in business reports
  • Separated input, calculation and output in business report model
  • Case: Use Name and Array for area calculation
  • Case: Dynamic report shows sales/costs/profits/new customers based on selectionNameRange

Use Dynamic Elements in Business Reports

  • Use form control for user interaction: spin button, scrollbar, checkbox, combo box, option button & group boxFormControl
  • Build in-cell drop down list
  • Learn to use Filter in dynamic reports
  • Learn to use Camera tool for report display
  • Case – Dynamic chart: show different cost center KPIModelStructure & DynamicReport
  • Use advanced formula for dynamic reports
  • Case: Index and Match for dynamic data range
  • Case: Choose formula for data areas selection
  • Case: Offset formula for dynamic data range
  • Case: Sumif formula for data consolidation
  • Case: Indirect formula for data input manipulation
  • Case: Interactive sales report: product/month/city perspectiveVlookup & DynmamicReport
  • Apply fixed and dynamic range names with lookup and reference function
  • Understand pros and cons of using array functions in financial modelling
  • Use other Excel tools in dashboard

Comprehensive Case: Build Complex, Robust and User-Friendly Dynamic Dashboard

  • Design a professional report layout
  • Understand three seconds rule & ten seconds rule
  • Feed single database to multiple worksheets
  • Connect data source through indirect method
  • Compare target’s performance with weighted average
  • Draw interrelationship map among different databases as one to many, many to one
  • Show a running MTD & YTD figure
  • Choose correct type of chart to demonstrate the desired result
  • Build chart with dynamic data ranges
  • Automation – Highlight suspicious/uncommon value through Traffic Alert
  • Automation – Display top/bottom certain number of records
  • Application of interactive picture to fit varied tables into a maintainable report
  • Application of conditional format to draw attention of end user
  • Application of colour sets to emphasis the focus
  • Conversion of your model into an ‘eye feast’ presentation
  • Essential crosschecking and documentation



  1. 1. 课程节奏适中,后半程comprehensive example 需要消化吸收。2. 着眼于讲解structure/mechanisam的授课方式对构架思路的理解有极大帮助。2. 授课中穿插的tricks & tips是extra bonus, 源于多年经验累积。3. 转行commerical的敲门砖,结合其他BI report更能快捷直观地从不同角度讲述一个商业故事。4. 老师耐心细致,课堂气氛轻松。Challenging but obvious a fun day!

  2. 老师很好,课程很好。美中不足的是,课室太过热情了,有点闷热,下午会很难集中精力。其他同学也有类似感觉。如果可以把风扇放在外面,往里吹会不会好一点?

  3. 学到的不少东西,非常喜欢做Terry教的report用的思路和如何根据这些思路来决定要用的公式。感觉学到的知识第二天就能在工作上派上用场!

  4. The value of the course is great! Loads of information in two days and Terry explained clearly. A lot to digest though, hope Terry could come back to Brisbane more often and we can review more then. Thank you Terry!

  5. 讲得很好,很实用,令人大开眼界。但可能是由于时间的关系,讲得太快,跟不上,这也是我和不少同学的共同感觉。也许是我们平时Excel公式用的比较少的原因。总体感觉课程很好,准备申请重听一次。

  6. I have built a GP model using the technique learned from Terry’s course. My CFO was so impressed. Look forward to the Big Data course in September..

    • 我这次是重听。每次听完都会对老师讲的原理,技术有新的理解。很喜欢Terry 老师的课,不仅仅讲怎么做,更重要的是讲为什么这么做。感觉可以开阔视野和思路。谢谢老师精彩的讲解。

  7. 太炫了, 上完课回去要好好消化下,用到我的报表里,下次可以秀给我老板看了。

  8. 套用俗话就是全是干货。很多小的技巧马上就可以运用到工作中。整个构架模型的思路是高屋建瓴,站在十多年的工作经验之上。不管你是在会计从业哪个阶段,都是很有用的。

  9. Amazing Course ! Fantastic practical skills taught. This course is perfect for all levels of management accountants and financial analysts. One of the great courses I’ve ever attend! will continue to do the other courses Terry offers

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.