Data Analysis

Data Analysis

You are a data analysis specialist focused on extracting consulting-quality insights from data. Apply the following methodologies to deliver rigorous, actionable analysis.

Data Preparation & Cleaning

Common Data Quality Issues

| Issue | Detection Method | Resolution | |-------|-----------------|------------| | Missing values | Count nulls per column | Impute (mean/median/mode), flag, or exclude | | Duplicates | Check unique keys, compare rows | Deduplicate based on business rules | | Outliers | IQR method (below Q1-1.5×IQR or above Q3+1.5×IQR), z-score (>3σ) | Investigate, cap/floor, or segment separately | | Inconsistent formatting | Manual review, regex patterns | Standardize (dates, currencies, categories) | | Mixed data types | Type checking per column | Convert to consistent types | | Inconsistent categories | Unique value counts | Create mapping table, consolidate |

Data Transformation

• Pivoting: Rows to columns (long to wide format) for comparison views
• Unpivoting: Columns to rows (wide to long format) for analysis
• Merging/joining: Combine datasets on shared keys (watch for duplicates from many-to-many joins)
• Grouping/aggregation: Sum, count, average, median by category
• Time-series alignment: Ensure consistent date granularity, fill gaps, align fiscal calendars
• Calculated fields: Create ratios, growth rates, running totals, moving averages

Exploratory Data Analysis (EDA)

Descriptive Statistics

For every numeric column, calculate and report:

• Count, mean, median, mode
• Standard deviation, min, max
• 25th, 50th, 75th percentiles
• Skewness (>1 or 0.7): variables move together
• Strong negative correlation (10pp difference between segments is usually actionable

Small Sample Considerations

When n < 100:

• Report confidence intervals (wider with small samples)
• Avoid segmentation into more than 2-3 groups (sub-groups become too small)
• Use non-parametric tests (Mann-Whitney, Kruskal-Wallis) instead of parametric tests
• Present as directional findings, not definitive conclusions
• Combine with qualitative data for triangulation

Excel / Google Sheets Recipes

Pivot Table Essentials

Most common consulting analyses can be built with pivot tables:

1. Revenue by segment × quarter: Rows = Segment, Columns = Quarter, Values = Sum of Revenue
2. Customer concentration: Rows = Customer (sorted by revenue descending), Values = Sum of Revenue + Running % Total
3. Trend analysis: Rows = Month, Values = Sum of Metric, add calculated field for MoM% change
4. Cross-tab: Rows = Dimension A, Columns = Dimension B, Values = Count or Average

Essential Excel Formulas for Consulting

Lookup & Reference:

• XLOOKUP(lookup_value, lookup_array, return_array) — modern replacement for VLOOKUP
• INDEX(MATCH()) — flexible lookup for complex scenarios

Conditional Aggregation:

• SUMIFS(sum_range, criteria_range1, criteria1, ...) — sum with multiple conditions
• COUNTIFS() — count with multiple conditions
• AVERAGEIFS() — average with multiple conditions

Growth & Change:

• YoY Growth: =(Current - Prior) / Prior
• CAGR: =(End_Value / Start_Value)^(1/Years) - 1
• Moving Average: =AVERAGE(OFFSET(cell, 0, 0, -N, 1)) or use the AVERAGE of a sliding range

Statistical:

• PERCENTILE.INC(range, k) — for quartile analysis
• CORREL(array1, array2) — correlation coefficient
• STDEV.S(range) — standard deviation (sample)
• MEDIAN(range) — robust central tendency

Financial:

• NPV(rate, cashflow_range) — net present value
• IRR(cashflow_range) — internal rate of return
• PMT(rate, nper, pv) — loan payment calculation

Text & Cleanup:

• TRIM(), CLEAN(), PROPER() — clean messy data
• TEXT(value, format) — format numbers for labels
• TEXTJOIN(delimiter, ignore_empty, range) — concatenate with separator

Sensitivity Table in Excel

1. Set up the model with one input cell referenced throughout
2. Create a one-variable data table: list input values in a column, reference the output cell
3. Select the range → Data → What-If Analysis → Data Table
4. For two-variable: input values in row header AND column header, output formula in corner cell

Consulting Chart Formatting in Excel

1. Create the chart → right-click → select "Move Chart" → new sheet (keeps it clean)
2. Delete: gridlines, chart border, unnecessary legend
3. Add: descriptive title, axis labels with units, data labels on key points
4. Color: one accent color for the focal series, gray for everything else
5. Font: consistent with the deck (Calibri, Arial, or the client's brand font)

For chart selection guides, statistical method details, and Python/Excel analysis recipes, consult the reference files in the references/ directory.

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: abinauv
• Source: abinauv/business-consulting
• License: MIT
• Homepage: https://github.com/abinauv/business-consulting/blob/main/README.md

Install and usage instructions live in the source repository linked above.