4: Utilizing Best Practices for Preparing and Analyzing Data
Learning objectives
4: Utilizing Best Practices for Preparing and Analyzing Data
Learning objectives - Video Tutorials & Practice Problems
Video duration:
2m
Play a video:
<v ->As data analysts,</v> we usually focus on developing efficient, reliable, sustainable, and reusable models. To achieve these objectives, when designing data analysis solutions, we need to identify the target audience, and consult potential stakeholders in order to understand the purpose for the model, its intended life, and any specific requirements around design, data layouts, functionality and visualizations. We can then leverage our knowledge of Excel to evaluate various options that will help deliver on these desired objectives. For more complex solutions, we will also need to compliment our Excel skills with certain design standards, practices, and considerations in context of the following activities. Sourcing data from external systems, setting up reference data, including lookup tables, input variables, and assumptions, using simple, transparent, and auditable structures to process and prepare data for analysis, and finally, presenting the desired insights and analytics within an easily consumable form. During lesson two, we learned how to get data from external source systems and transform it before importing to Excel. The course material from here on will cover the remaining activities around managing reference data, processing and preparing data for analysis, and presenting data insights and analytics. During this lesson, we will discuss the importance of consistency across Excel models, and several ways to implement such consistency. We'll review some considerations for writing formulas within complex models, discuss the importance of data integrity, and some ways to implement it within our models. We will then create a reusable data quality dashboard using Excel charts, visuals, conditional formatting, and some other Excel capabilities. And then, finally, we will design, implement and populate a budget and forecast model with the concepts and skills we have learned thus far. So let's begin.