DAY 21: Excel - Statistical Analysis & Forecasting

Learning Objectives: - Perform statistical analysis in Excel - Use regression analysis tools - Create forecasts with FORECAST functions - Understand correlation and trends - Apply data analysis tools

Topics Covered (60 min):

1. Descriptive Statistics - Data Analysis Toolpak (Add-in) - Descriptive Statistics tool - Mean, median, mode, standard deviation - Quartiles, range, variance - Skewness and kurtosis

2. Correlation and Regression - CORREL() function - relationship strength - Regression tool (Y vs X analysis) - Interpret R-squared value - Trend lines in charts - Example: Sales vs Marketing Spend

3. Forecasting Functions - FORECAST.LINEAR() - linear trend projection - FORECAST.ETS() - exponential smoothing (seasonality) - Moving averages for smoothing - Seasonal decomposition

4. What-If Scenarios - Probability distributions - Monte Carlo simulation basics - Confidence intervals - Sensitivity analysis with Data Tables

Video Resources:

Power Pivot and Data Modeling in MS Excel (00:00)
https://www.youtube.com/watch?v=7DaxA15Q-QI
How to use Power Pivot - Microsoft Excel Tutorial (1:43)
https://www.youtube.com/watch?v=kyGhgreDNUQ
Excel Power Pivot & Data Model explained (8:20)
https://www.youtube.com/watch?v=Gf4HmkR7_FE

AI Learning Prompts (Concept Understanding Only):

Understanding Statistical Analysis:

I'm learning statistical analysis in Excel. Please explain:

1. What statistics should I calculate for sales data (mean, median, std dev)?
2. How do I interpret correlation between two variables?
3. Show me how to perform regression analysis
4. What is R-squared and what does it mean?
5. Provide examples using sales and marketing data

Use beginner-friendly explanations.

Understanding Forecasting:

Help me understand Excel forecasting:

1. What is the difference between FORECAST.LINEAR and FORECAST.ETS?
2. When should I use each forecasting method?
3. How do I account for seasonality in forecasts?
4. Show me how to create a 12-month sales forecast
5. How do I measure forecast accuracy?

Provide practical examples with sales data.

Guided Practice (60 min):

📝 How to approach Guided Practice:

Follow step-by-step: Work through each exercise in order
Use provided resources: Reference the video tutorials and AI prompts if you get stuck
Verify your work: Check your results match expected outcomes
Take notes: Document key learnings and shortcuts you discover
Ask for help: Use AI assistants to clarify concepts you don't understand

Exercise 1: Calculate Descriptive Statistics with Data Analysis ToolPak

Goal: Use Excel's built-in statistical tools to analyze data distribution.

Step-by-Step Instructions:

Enable Data Analysis ToolPak: File → Options → Add-ins → Analysis ToolPak → Go → Check it → OK
Prepare sales data: Have monthly sales in column (e.g., A1:A12)
Run Descriptive Statistics: Data tab → Data Analysis → Descriptive Statistics
Configure: Input Range: A1:A12, Check "Summary statistics", Output Range: C1
Analyze output: Mean, Median, Mode, Std Deviation, Variance, Min, Max, etc.

💡 Tips:

Mean vs Median: Mean affected by outliers, median more robust
Standard deviation: Measures spread - higher = more variation
Quartiles: Help identify data distribution (Q1=25th percentile, Q3=75th)
Quick formulas: Use =AVERAGE(), =MEDIAN(), =STDEV.S() for quick stats
Visual check: Create histogram to see distribution shape

✅ Expected Result: Statistical summary showing mean, median, std dev, and other key metrics

Exercise 2: Perform Correlation Analysis

Goal: Measure relationships between variables using CORREL function.

Step-by-Step Instructions:

Setup data: Column A: Price, Column B: Sales Volume
Use CORREL function: =CORREL(A2:A13,B2:B13)
Interpret result: Values: -1 (perfect negative) to +1 (perfect positive), 0 (no correlation)
Create scatter plot: Select both columns → Insert → Scatter Chart
Add trendline: Click chart → Chart Design → Add Trendline → Display R-squared

💡 Tips:

Correlation ≠ causation: High correlation doesn't mean one causes the other
R-squared: Shows % of variance explained (0.8 = 80% explained)
Positive correlation: Both variables move same direction
Negative correlation: Variables move opposite directions
Multiple correlations: Use Data Analysis → Correlation for multiple variables

✅ Expected Result: Correlation coefficient and scatter plot with trendline

Exercise 3: Create Linear Forecast with FORECAST.LINEAR

Goal: Project future values using historical trend.

Step-by-Step Instructions:

Historical data: Months 1-12 in column A, Sales in column B
Create forecast for month 13: =FORECAST.LINEAR(13,B2:B13,A2:A13)
Forecast next 6 months: In A14:A19 enter 13-18, in B14 enter formula above, copy down
Visualize: Create line chart showing historical + forecast data
Format forecast: Use different line style/color to distinguish forecast from actual

💡 Tips:

Linear assumption: Works best when data shows steady linear trend
Forecast accuracy: Less reliable further into future
Alternative: Use TREND() function for same result
Confidence intervals: Add ±2*STDEV.S() bands to show uncertainty
Check fit: Use R-squared from regression to assess fit quality

✅ Expected Result: Forecast values for next 6 months with visualization

Exercise 4: Perform Regression Analysis

Goal: Analyze relationship between independent and dependent variables.

Step-by-Step Instructions:

Setup: Marketing Spend (X) in column A, Revenue (Y) in column B
Run Regression: Data → Data Analysis → Regression
Configure: Y Range: B1:B13, X Range: A1:A13, Output Range: D1
Check "Line Fit Plots" and "Residual Plots"
Interpret: R-squared (fit quality), Coefficients (equation), P-values (significance)

💡 Tips:

R-squared interpretation: >0.7 is good fit, >0.9 is excellent
P-value: <0.05 means statistically significant relationship
Equation: Y = Intercept + (Coefficient × X)
Residuals: Check residual plot for patterns (should be random)
Multiple regression: Can include multiple X variables

✅ Expected Result: Regression statistics, equation, and fit plots

Exercise 5: Create Time Series Forecast with FORECAST.ETS

Goal: Account for seasonality using exponential smoothing.

Step-by-Step Instructions:

Seasonal data: Monthly sales for 2-3 years showing seasonal pattern
Forecast next month: =FORECAST.ETS(target_date, values, timeline, [seasonality])
Example: =FORECAST.ETS(DATE(2025,1,1),B2:B37,A2:A37,12)
Seasonality parameter: 12 for monthly data with yearly pattern, 4 for quarterly
Create forecast table: Generate forecasts for next 12 months

💡 Tips:

ETS vs LINEAR: ETS handles seasonality, LINEAR doesn't
Data requirement: Need at least 2 full seasonal cycles
Auto-detect seasonality: Omit seasonality parameter to auto-detect
Confidence intervals: Use FORECAST.ETS.CONFINT() for uncertainty bands
Validation: Hold out last period, forecast it, compare to actual

✅ Expected Result: Seasonal forecast accounting for repeating patterns

🔄 Connection to SQL: Statistical analysis in Excel complements SQL's data aggregation from Day 22!

Independent Practice (60 min):

💡 Hints for Independent Practice:

Start simple: Begin with basic variations before attempting complex modifications
Break it down: Divide complex problems into smaller, manageable steps
Test incrementally: Verify each step works before moving to the next
Use documentation: Refer to official docs or tutorials when needed
Experiment: Try different approaches to find what works best

Apply the concepts learned to different datasets and scenarios.

Daily Assignment (60 min):

Assignment:

Descriptive Stats:Use Data Analysis Toolpak on sales data (mean, median, std dev, quartiles)
Correlation Analysis:Calculate correlation between Price and Sales Volume
Regression:Analyze relationship between Marketing Spend and Revenue
Dashboard:Create summary showing key statistics, trend chart, forecast chart

Expected Outcomes:

Perform statistical analysis in Excel
Use regression analysis tools
Create forecasts with FORECAST functions
Understand correlation and trends
Apply data analysis tools

DAY 21: Excel - Statistical Analysis & Forecasting