Data Modeling and Dashboard Reporting using Excel
Course overview
This training on Data Modeling and Dashboard Reporting using Excel in Dubai will provide opportunities to professionals to learn and equip them with Excel and PowerBI to design and prepare effective Dashboards.
As a finance professional, you cannot imagine a life without Microsoft Excel, no matter how sophisticated ERP and database you have in your organization. Did you know that Excel has advanced data analysis features with a database that lives right inside Excel workbook? With this, you can analyze data containing millions of rows right on your desktop and within a blink of eye. Learn how to:
- Import millions of rows of data from multiple data sources
- Enjoy fast calculations and analysis
- Create interactive reporting dashboards in Excel
- Design interactive visual dashboards in Power BI
- Publish and share your dashboards on web and mobile applications
No technical IT skills needed!
This course is designed for accountants & auditors, finance professionals and business & financial analysts. Suggested audience titles include accounts manager, finance manager, finance director, CEO, CFO, audit manager, MIS manager etc.
Pre-requisites
- Microsoft Excel basic working knowledge
- Business acumen
- Basic knowledge of windows and web browsing
Takeaways
- Import and combine data from multiple sources and tables
- Use Microsoft Power Pivot add-in to create your first data
- Create interactive dashboard with Power Pivot
- Understand basic working of Power BI
- Create visualization based dashboard in Power BI
- Publish your dashboard to web & mobile
Course outline
Data Modeling & Dashboard Reporting in Excel
| Duration (mins) | Sort Order | Description | Lab |
Data Modeling |
|||
| 10 | 1 | Data modeling & analytics revolution – what’s new in Excel | |
| 10 | 2 | Excel tables vs range | |
| 10 | 3 | Creating & managing Excel tables | Y |
| 10 | 4 | Excel formulas with tables | |
| 10 | 5 | Database basic concepts | |
| 10 | 6 | Importing tables into data model | Y |
| 15 | 7 | Understanding primary and foreign keys | |
| 20 | 8 | Understanding & creating relations between tables | Y |
| 10 | 9 | Dimension vs fact tables | |
| 15 | 10 | Creating pivot table based on data model | Y |
| 11 | **BREAK** | ||
Microsoft Excel Power Pivot Add-in |
|||
| 15 | 12 | Introducing Power Pivot add-in | |
| 10 | 13 | Managing data model with Power Pivot | Y |
| 15 | 14 | Understanding data connections & refresh | |
| 20 | 15 | Creating customized data dashboard with CUBE formulas | Y |
| 16 | **LUNCH BREAK** | ||
DAX – Data Analysis eXpression |
|||
| 5 | 17 | Implicit calculated fields | |
| 10 | 18 | Introducing DAX | Y |
| 15 | 19 | Calculated columns | Y |
| 15 | 20 | Calculated fields | Y |
| 21 | **BREAK** | ||
| 20 | 22 | Evaluation contexts | |
| 30 | 23 | Creating basic DAX formulas & CALCULATE
Real world examples – using averages, percentages, calculating profit by store or product etc. |
Y |
| 10 | 24 | Setting up Calendar table | Y |
| 15 | 25 | DAX Time Intelligence Functions
Real world examples – using time intelligence functions for growth analysis, year to date and for the month calculations etc. |
Y |
| 26 | **BREAK** | ||
| 60 | 27 | Practice and Q&A with live examples | |
| 28 | — DAY 1 END— | ||