Module-4
DATA ANALYSIS TECHNIQUES
Descriptive statistics
Descriptive statistics are methods used to summarize and describe the main features of a dataset.
Common examples include the mean, median, mode, range, and standard deviation.
1. MEAN(AVERAGE)
The mean is the average of a set of numbers. You calculate it by adding up all the numbers and then
dividing by how many numbers there are.
Formula:
=AVERAGE(range)
2. MEDIAN(Middle value)
The median is the middle value of a set of numbers when they are arranged in order from
smallest to largest.
If there’s an odd number of numbers, the median is the one in the middle.
If there’s an even number of numbers, the median is the average of the two middle
numbers.
Formula:
=MEDIAN(range)
Example:
For the numbers 3, 5, 7, 10, 15
The median is 7 (the middle value).
3. MODE
The mode is the number that appears most frequently in a set of data.
Dataset can have no mode, one mode, or multiple modes.
Formula
=MODE(A1:A5)
4. STANDARD DEVIATION
Standard deviation is a measure of how spread out the numbers in a data set are. It tells you
how much the values in the set deviate from the mean (average).
A low standard deviation means the values are close to the mean.
A high standard deviation means the values are spread out over a wider range.
Formula
for a sample of data:
=STDEV.S(range)
For a population dataset
=STDEVP.P(range)
Another method for using descriptive statistics
1. Goto Data->Data Analysis->Descriptive statistics
2. select your data range and check summary statistics.
3. click ok to get all statistics at once.
Correlation and Regression Analysis
Correlation and Regression Analysis are powerful statistical tools to understand
relationships between two or more variables.
Correlation Analysis
Correlation measures the strength and direction of the relationship between two variables. It
is represented by the correlation coefficient (r), which ranges from -1 to +1:
+1: Perfect positive correlation.
-1: Perfect negative correlation.
0: No correlation.
Formula:
=CORREL(range1, range2)
range1: The first set of data.
range2: The second set of data.
Result is -ve correlation.
Regression Analysis
Regression analysis is a statistical method used to understand the relationship between two or more
variables. It helps to predict the value of one variable (called the dependent variable) based on the
value(s) of other variable(s) (called independent variables).
formula
Y=a+bX
Where:
Y is the dependent variable (the value you want to predict).
X is the independent variable (the value you're using to predict Y).
a is the y-intercept (the value of Y when X is 0).
b is the slope (the change in Y for a one-unit change in X).
Scenario Analysis
Testing different situations(scenarios) by changing values to see how the result changes
You have 3 scenarios:
Base case-> Normal situation
Best case-> High sales, more profit
Worst case-> Low sales, less profit
This is especially helpful in:
Business planning: To predict profit under different conditions.
Financial analysis: To see how changes in market conditions affect your bottom line.
Risk management: To understand the impact of negative events (like a cost increase)
or positive events (like higher demand).
Example
A company wants to analyse different revenue and profit outcomes based on cost. The
company considers three scenarios
Step 1: Enter the data
Step 2: Open scenario manager
1. Go to Data->what-If Analysis-> Scenario Manager
2. Click Add to create a new scenario
Step 3: Create Different Scenarios
A. Base case
Revenue and cost values enter
Click ok and Add another scenario
B. Best case
Best case values enter
Click ok and Add another scenario
C. Worst case
Worst case values enter
Click ok and then show to switch between scenarios
Step 4: Generate a Summary Report
Final output Scenario Summary
What-IF Analysis
What-If Analysis in Excel is a powerful set of tools that allows you to explore different
outcomes by changing input values in your data models.
Key Types of What-If Analysis in Excel:
1. Scenario Manager
2. Data Tables
3. Goal Seek
4. Solver (not as commonly used but useful for more complex problems)
Uses
Project Management
Investment Analysis
Loan & Debt Management
Manufacturing & Supply Chain
Pricing Models & Profitability:
Human Resources
Economic/Policy Impact
Goal Seek
Goal Seek in Excel is a tool that helps you find the input value needed to reach a specific target
result.
Steps:
1. Go to Data-> what-if analysis->goal seek
2. Set cell
3. enter the value 300
4. select by changing cell
5. click ok-> excel calculates the required units.
2. Data Tables
A Data Table lets you test how changes in one or two input values (like interest rate, price,
etc.) affect a result (like monthly payment, total cost, etc.).
Steps:
1. given values for qty, price , GM
2. we have to find profit(=B1*B2*B3)
3. go to data-> what- if analysis-> data table
4. input value as 500
[Link] value as 5
6. click ok
Finally get results
3. Scenario Manager
Scenario Manager in Excel is a tool that helps you create and compare different what-if
situations. It allows you to change multiple inputs (like price, cost, or sales volume) to see
how these changes affect the result (like profit).
Step 1: Enter the data
Step 2: Open scenario manager
1. Go to Data->what-If Analysis-> Scenario Manager
2. Click Add to create a new scenario
Step 3: Create Different Scenarios
Put name as Regular,
Click ok and add another scenario
Put name as Premium
Then click show, we can see the results.
Step 4:
Excel generates a scenario summary report comparing all cases.
Introduction to DAX (Data analysis expressions) for complex calculations
What is DAX? (In Simple Terms)
DAX stands for Data Analysis Expressions. It is a formula language used in Excel (via Power
Pivot), Power BI, and other Microsoft tools to create custom calculations on your data. Think
of it like the formulas you.
DAX is used to perform calculations like:
Summing values (e.g., total sales).
Averaging values (e.g., average revenue).
Comparing data over time (e.g., sales this year vs last year).
Creating custom metrics based on conditions (e.g., profit margin, growth rates).
DAX Functions and Syntax Examples
Aggregation Functions
SUM: Adds up all the values in a column.
Total Revenue = SUM(Sales[Revenue])
AVERAGE: Calculates the average of a column.
Average Revenue = AVERAGE(Sales[Revenue])
COUNT: Counts the number of rows in a column.
Total Sales Count = COUNT(Sales[OrderID])
MAX: Returns the maximum value in a column.
Max Revenue = MAX(Sales[Revenue])
Conditional Functions
IF: Checks a condition and returns one value if TRUE, another if FALSE.
Profit Status = IF(Sales[Revenue] > Sales[Cost], "Profit", "Loss")
SWITCH: Similar to IF but used for multiple conditions.
Sales Category = SWITCH(TRUE(),
Sales[Revenue] > 1000, "High",
Sales[Revenue] > 500, "Medium",
"Low")
Conclusion (In Simple Terms)
DAX (Data Analysis Expressions) is a powerful formula language used in Microsoft tools
like Power BI, Excel, and SQL Server Analysis Services (SSAS). It helps you perform
advanced calculations, create custom metrics, and analyze
DAX is mainly used for
Dynamic calculations: It allows you to create measures and calculated columns that
change based on filters and data context.
Time-based analysis: It simplifies tasks like calculating Year-to-Date (YTD), comparing
sales this year vs last year, and more.
Business intelligence: It helps businesses analyze their data, track performance, and make
informed decisions by calculating things like profits, sales growth, and customer behavior.