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.
Learning Objectives
- Discover to establish instant connection to multiple data sources rather than copy/paste and stack-up.
- Explore how to structure a table with related data in 1 column for easy pivoting.
- Explore how to build uniform formulas in Excel models to avoid errors.
- Recognize effective ways of keeping source data, entry data, and formulas all separate in models.
- Identify and understand how Power Query can help prevent dragging processed data from table to table.
13 Reviews (52 ratings)
Prerequisites
Basic Excel and Power Query knowledge.
Example: Be able to open one Excel file and connect to external data files, etc.