Dynamic Business Reports
current icon CURRENT
camput icon On Campus
Start Anytime
Industry Recognised
Workplace Simulation
Price: $980
DOWNLOAD COURSE BROCHURE
CERTIFICATES
FLEXIBLE DELIVERY
LIVE SUPPORT
INTERNSHIP AVAILABLE

ABOUT THE COURSE

*The course will be delivered in Mandarin (中文授课). 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 skills & practices. In this course, students will learn all the tools and techniques needed for a dashboard, and students will also learn how to apply advanced modelling skills to build complex, robust and user-friendly dynamic business & management reports.

UNITS

Module 1 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
  • Standardise business report through best practice procedures
  • Case: Common errors in business reports
  • Separated input, calculation, and output in a business report model
  • Case: Use Name and Array for area calculation
  • Case: Dynamic report shows sales/costs/profits/new customers based on selection

Module 2 Use Dynamic Elements in Business Reports

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

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

  • Design a professional report layout
  • Understand three seconds rule & ten seconds rule
  • Feed a single database to multiple worksheets
  • Connect data source through an indirect method
  • Compare the target’s performance with a weighted average
  • Draw interrelationship map among different databases as one to many, many to one
  • Show a running MTD & YTD figure
  • Choose the correct type of chart to demonstrate the desired result
  • Build a 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 the attention of end user
  • Application of colour sets to emphasize the focus
  • Conversion of your model into an ‘eye feast’ presentation
  • Essential crosschecking and documentation

UPON COMPLETION

This course is eligible for CPD hours.

DURATION

2 days
* Please contact us for the timetable.

Trainer’s Profile

  • 17 Years of Dedicated Best-Practice Financial Modeller Extensive Experience in Building & Reviewing Complex Financial Model
  • Experienced Financial Controller of an ASX listed companies
  • Managing Director of AFMA (Australian Financial Modeller Association)
  • Committee Member of ACAA (Association of Chinese Accountants Australia)
  • Special Guest Trainer of ABC Television (Australian Broadcasting Corporation)
  • Guest BI (Business Intelligence) Lecturer of CPA Australia
  • Lecturer of Shanghai University of Finance and Economics MBA
  • Experienced Financial Analyst and Financial Modeller
TESTIMONIALS
PARTNERSHIP & COLLABORATORS
Call Me Back