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 selection

Use form control for user interaction: spin button, scrollbar, checkbox, combo box, option button & group box

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 KPI

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 perspective

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

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