Unit II · Inferential & Prescriptive Analysis + VBA (≈12 hours)
1. Hypothesis Testing
- t-tests (one/two sample, paired), ANOVA (one-way), chi-square (independence, GOF) via Analysis ToolPak.
- Effect size basics (Cohen’s d, η²), confidence intervals, assumptions checklist.
2. Regression & Correlation
- Simple linear regression (LINEST/Analysis ToolPak), residual diagnostics.
- Multiple regression: dummy vars, multicollinearity cues (VIF via auxiliary calcs).
- Correlation matrix:
CORREL / PEARSON; scatter-matrix with pivots.
3. Prescriptive Tools
- What-If: Goal Seek, Scenario Manager, Data Tables (1- & 2-variable).
- Solver: linear constraints, integer decisions; sensitivity with parameter sweeps.
- Finance:
PMT, IPMT, NPER, RATE, NPV, IRR.
- Time series: date handling, seasonality proxies, moving averages, decomposition overview.
4. VBA for Analysis Automation
' Alt+F11 to open the editor
Sub CleanAndSummarize()
Dim ws As Worksheet: Set ws = ActiveSheet
ws.Range("A1").CurrentRegion.RemoveDuplicates Columns:=Array(1,2), Header:=xlYes
Dim rng As Range: Set rng = ws.Range("B2:B999")
ws.Range("H2").Value = WorksheetFunction.Average(rng)
ws.Range("H3").Value = WorksheetFunction.StDev_S(rng)
MsgBox "Done! Recomputed mean & stdev.", vbInformation
End Sub
Domain activities: Healthcare treatment comparison (t-test/ANOVA), production planning with Solver, portfolio toy-model using Data Tables.