Dynamic Business Report

Duration: 2 days
Workshops:  10-11 Mar 2018
            08-09 Dec 2018

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

  2. 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!

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

  4. 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..

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

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

  7. 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 *