Excel Modeling 101: 10 Dos and Donts (Video) (Course Id 1780)

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

Please Note: This course’s author is working on the providing transcripts, PDFs, and slides where applicable. Unfortunately until then we will not be able to offer them with the course but felt the course was valuable enough as it is so have chosen to include it on our site. Our sincere apologies for any inconvenience and please let us know any question using the support bubble on the bottom right of all pages on our site. 

Course Learning Objectives

• Establish instant connection to multiple data sources rather than copy/paste and stack-up
• Structure a table with related data in 1 column for easy pivoting
• Build uniform formulas in Excel models to avoid errors
• Recognize effective ways of keeping source data, entry data, and formulas all separate in models
• Understand how Power Query can help prevent dragging processed data from table to table