Microsoft Excel Training
BI, Data Analytics and Financial Modelling using MS Excel
Microsoft Excel is on every PC. Exploit it’s POWER
We can show you how.
Microsoft Excel Business Intelligence
Microsoft Excel is the top Business Intelligence tool in use today and it will continue its reign despite marketing efforts of other BI technology vendors. It is also interesting to note that most BI products in the market use an “export to Excel” feature as their selling proposition. That is because when it comes to buying a BI solution, end users look for the ability of product to export data to Excel as a major criteria for selection. Since most of the organizations already have highly skilled Excel users, they migrate swiftly to a self service BI capability and tend to create their own BI solution intuitively without much dependence on IT.
In most businesses, irrespective of their size and scale, users prefer Microsoft Excel to perform analysis. This approach limits the capabilities and the extent to which end users can access enterprise data sources. Not only that, it can compromise data consistency and integrity.
This course, therefore, ensures that you get accurate, complete and timely reports from your enterprise system.
Duration: 5 Days
- Comprehend Excel Reports and Dashboard
- Comprehend OLAP integration
- Comprehend Power Pivot
- Comprehend Power Query
- Comprehend SharePoint Integration
- Comprehend Data mining
Module 1: Power Pivot
- Introduction to Data model in Excel 2013
- Introduction to Power Pivot
- Data loading from different sources in Excel
- Data modeling best practices
Module 2: DAX
- DAX Fundamentals
- Using DAX to create calculated columns and Measures
Module 3: Power View
- Introduction to Power View
- Visualizing Data with Power View
Module 4: Power Query
- Basic Understanding
- Query WebQuery
- Working with other sources
Module 5: Data Mining
- Overview of Data Mining
- Creating a Data Mining Solution
- Validating a Data Mining Model
Module 6: OLAP Integration
- OLAP with Excel
- OLAP with Power Pivot
- MDX and Excel
- Offline OLAP Cube
Module 7: Excel Dashboard and Reports
- Excel Chart basics
- Going Beyond Tables and Charts
- Creating Advanced Dashboard Components
Microsoft Excel Advanced Data Analytics
Microsoft Excel is a very powerful spreadsheet application designed to save you time so that you can spend more time generating ideas to drive your business forward. This practical Course has been designed for Professionals like you. To make you more efficient and effective as you let the power of Microsoft Excel do the crunching for you.
The world now is awash with systems collecting data day in day out. The velocity at which this data comes in is overwhelming. To tackle and effectively analyse this data, you need skills. However, most office staff can use Excel to a basic level for data entry and formatting cells. Few make it to the next level of analysis to be able to build models, conduct scenario analyses, apply checks to flag errors, create a dashboard or format the data to a client-ready standard.
Our best-selling, award-winning, accredited course will solve all the above short comings efficiently, by directing your attention to those areas of Excel most relevant to you.
Do not wait to be overwhelmed. Make the move now and start on the journey to becoming a proficient data analyst. It is only then that you will be able to make sense out of the data you receive and help your business grow.
Duration: 5 Days
- Get up to speed with Microsoft Excel
- Easily use advanced functions & formulas
- Organize worksheet and table data using multiple techniques
- Summarize data quickly using Pivot Tables, and generate Management Reports
- Create and modify advanced charts & graphs like the Pareto Chart
Module 1
- Working with Advanced Data Forms
- Advanced Data Validation
Module 2
- Advanced Conditional Formatting
- Filtering and Querying Data using Advanced Filters
Module 3
- Excel Tables
- Excel Functions
- Excel Pivot Tables
- Excel Power Pivot
Module 4
- What-if analysis
- Analysing Data using the Analysis Tool Pack
Module 5
- Security
- Working with Excel Macros
Financial Modelling in Excel
How can you put data to work for you? Specifically, how can numbers in a spreadsheet tell us about present and past business activities, and how can we use them to forecast the future? The answer is in building financial models, and this course is designed to help you understand the fundamentals of this critical business skill. Through a series of demonstrations, and assignments, you will learn the key ideas and process of financial modelling so that you can begin to create your own models for your own business or enterprise. By the end of this course, you will have seen a variety of practical commonly used financial models as well as the building blocks that will allow you to start structuring your own models.
Duration: 3 Days
- Deliver skills so that you may be able to generate efficient financial models
- Increase your productivity
MODULE 1: Introducing Financial Modeling
- Defining Financial Modelling
- Looking at Examples of Financial Models
MODULE 2: Getting Acquainted with Excel
- Making Sense of the Different Versions of Excel
- Defining Modern Excel
- Recognizing the Dangers of Using Excel
- Looking at Alternatives and Supplements to Excel
MODULE 3: Planning and Designing Your Financial Model
- Identifying the Problem That Your Financial Model Needs to Solve
- Designing How the Problem’s Answer Will Look
- Gathering Data to Put in Your Model
- Documenting the Limitations of Your Model
- Considering the Layout and Design of Your Model
MODULE 4: Building a Financial Model by the Rulebook
- Document Your Assumptions
- Create Dynamic Formulas Using Links
- Only Enter Data Once
- Model with Consistent Formulas
- Build in Error Checks
- Format and Label for Clarity
MODULE 5: Using Someone Else’s Financial Model
- Considering Templates for Building a Financial Model
- Inheriting a File: What to Check For
- Using Audit Tools to Find and Correct Errors
MODULE 6: Excel Tools and Techniques for Financial Modeling
- Referencing Cells
- Naming Ranges
- Linking in Excel
- Using Shortcuts
- Restricting and Validating Data
- Goal Seeking
MODULE 7: Using Functions in Excel
- Identifying the Difference between a Formula and a Function
- Finding the Function You Need
- Getting Familiar with the Most Important Functions
- Being Aware of Advanced Functions and Functionality
MODULE 8: Applying Scenarios to Your Financial Model
- Identifying the Differences between Types of Analysis
- Building Drop-Down Scenarios
- Applying Sensitivity Analysis with Data Tables
- Using Scenario Manager to Model Loan Calculations
MODULE 9: Charting and Presenting Model Output
- Deciding Which Data to Display
- Conveying Your Message by Charting Scenarios
- Deciding Which Type of Chart to Use
- Dynamic Charting
- Preparing a Presentation
MODULE 10: Building an Integrated Financial Statements Model
- Getting to Know the Case Study
- Entering Assumptions
- Calculating Revenue
- Calculating Expenses
- Building the Income Statement
- Building the Cash Flow Statement
- Building the Balance Sheet
- Building Scenarios
MODULE 11: Building a Discounted Cash Flow Valuation
- Understanding How the Discounted Cash Flow Valuation Works
- Step 1: Calculating Free Cash Flow to Firm
- Step 2: Calculating Weighted Average Cost of Capital
- Step 3: Finding the Terminal Value Discounting Cash Flows and Valuation
MODULE 12: Budgeting for Capital Expenditure and Depreciation
- Getting Started
- Output 1: Calculating Cash Required for Budgeted Asset Purchases
- Output 2: Calculating Budgeted Depreciation
- Output 3: Calculating the Written-Down Value of Assets for the Balance Sheet
MODULE 13: Ten Strategies for Reducing Error
- Using the Enter Key
- Checking Your Work
- Checking It Again
- Getting Someone Else to Check Your Work
- Documenting Assumptions
- Documenting Methodology with a Flowchart
- Stress-Testing with Sensitivity Analysis
- Conducting a Scenario Analysis
- Taking Note of Excel Error Values
- Including Error Checks
MODULE 14: Ten Common Pitfalls to Avoid
- The Numbers Don’t Add Up
- You are Getting #REF! Errors
- You Have Circular References
- The Model Has Too Much Detail
- The File Size Is Out of Control
- Your Model Is Full of “Spaghetti” Links
- The Formulas Are Unnecessarily Long and Complicated
- No One Is Paying Attention to the Model
- You Don’t Want to Let Go
- Someone Messes Up Your Model