Description

The course is presented in four parts.

First, brief introduction to what is Excel modeling and how to make an Excel model great:

• Accuracy
• Simplicity
• Robustness

Second part brings a not-so-great Excel model as a business case, and introduces a financial report that is supposed to calculated the DSO (Days Sales Outstanding) measure. By examination, this course demonstrates to the audience 10 DONTS in Excel modeling, including:

• Do NOT copy/paste data multiple files and stack them up
• Do NOT leave comparable data in different columns
• Do NOT process data “one at a time”

Third, we demonstrate how to correct the problems and bring up the list of the 10 DOS, including:

• Connect or link to source data rather than copy and paste
• Keep comparable data in the same column
• Keep related data processing in “one” table!

Last, the course culminates in summarizing the 10 Dos and Don’ts into 4 categories:

• Data source
• Data transformation
• Formulas
• Validation

Course Key Concepts: Power Query, Excel modeling, DSO, Group by, Meta-data, Parameters, Validation.