Practical Business Analysis with Excel

Power Pivot is the biggest improvement that happened in Excel in the last ten years.

Technically, it is called a self-service business intelligence tool.

The difference between what you can do with power pivot and what you can do with only standard excel is like a difference between an electrical vehicle versus a horse carriage.

There are two ways to import data: through Power Pivot or through Power Query. We will learn both and will see which is the recommended one.

After importing the tables we create Relationships between them. We declare the Date Table. We solve a date formatting issue. And we custom sort one column based on another column. Please find in the Resources the tables on which we will develop the Data Model (these are the public AdventureWorks sample datasets from Microsoft).

We learn to create Calculated Columns in Power Pivot as well which is the best practice.

What are DAX Measures and how we can create them.

Here we have a cool trick with which we can extract at once and put in a table all the DAX Measures in our Data Model. This helps us analyse easier and compare the different Measures.

By grasping the concepts and usage of CALCULATE, you can optimize performance, create advanced calculations, and unlock the full potential of DAX for data analysis and modelling.

Time Intelligence functions are very powerful and let you create complex calculations. In this lecture we learn these functions: TOTALYTD, SAMEPRIODLASTYEAR, DATEADD and PARALLELPERIOD.

We continue the Time Intelligence chapter with the functions DATESBETWEEN and DATESINPERIOD.

Understanding Filter Context, Row Context and the Context Transition is fundamental for getting to the next level in DAX.

Putting it all together: import and transform data with Power Query, create the data model and measures with Power Pivot, use Slicers and Timeline and Refresh Data.

We start from a standard Excel chart and remake it into a visually appealing chart, using colour, hierarchy, formatting and action titles.

The 100% Stacked Bar chart is a useful visualisation for many kinds of scenarios. Here we use survey data to bring meaningful insights about the Beer preference of consumers.

We transform a standard Excel line chart (called a spaghetti chart) into a nicely formatted and informative chart.

The RFM analysis assigns each customer a numerical score based on three factors: recency frequency and monetary.

You want to know when a customer last bought something, how often they buy things, or how much their average ticket is.

Next, identify a set of profitable clients and assign certain benefits for them. This is called Customer segmentation.

6 Methods of Linear Regression using Excel.

Part 1 of 2. Students will understand the Dummy Variables method for doing Regression with Seasonality and Trend.

Part 2 of 2.

K-Means Clustering is a popular machine learning technique used to group data points into clusters based on the similarity.

It's a powerful tool that can help us make sense of complex datasets and identify hidden patterns that might not be immediately obvious.

Customer churn analysis provides actionable data to support decision-making processes across various departments, including marketing, sales, and customer support.

Using Power Query, you learn to isolate transactions featuring only your selected products—providing clarity on the number of instances where Product A and Product B (or any combination of more than 2 products) are exclusively purchased.

In this video, we embark on an enlightening journey into the world of logistic regression. Discover how to harness the power of Excel to predict binary outcomes with precision. We'll demystify complex concepts, starting with data normalization and advancing to calculating the logit, transforming odds, and deriving probabilities. Understand the significance of maximizing log likelihoods to fine-tune your model for accurate predictions. By the end, you'll be equipped to unravel patterns within data and predict outcomes with confidence.