Programme overview
Introduction:
Sales, cost and customer figures sit in spreadsheets across most organisations, yet monthly reports rarely go beyond totals and averages, errors in exported records pass unnoticed and managers receive numbers without an explanation of what moved or why. This Core Concept course develops data analysis techniques in Excel for professionals who build these reports themselves: scoping the problem, repairing and structuring records, summarising with statistics, and examining trends, budget variances and customer cohorts through PivotTables, formulas and the Analysis ToolPak. Participants build an Excel Evidence Workbook and Insight Memo on realistic sales, finance and operations files.
Course Objectives:
- Translate a manager's request into a scoped problem statement with defined measures, required fields and a planned method
- Repair, validate and restructure exported spreadsheet records into a consistent table ready for reliable calculation
- Summarise sales, cost and volume figures with averages, spread, distributions and outlier checks using Excel functions and the Analysis ToolPak
- Examine trends, budget variances and customer retention cohorts with PivotTables, calculated fields and structured formulas
- Check differences and relationships with t-tests, ANOVA, correlation and simple linear regression and interpret p-values and coefficients correctly
- Produce an Excel Evidence Workbook and Insight Memo that record the method, results, limitations and recommended actions
Target Audience:
- Professionals who prepare recurring sales, operational or financial reports from spreadsheet exports
- Team leaders responsible for tracking unit performance, costs and volumes
- Finance and budgeting staff who explain actual-versus-plan variances each period
- Marketing and customer relationship staff who examine campaign, customer and retention figures
- Operations and supply staff who monitor throughput, stock and quality measures
- Human resources and administration staff who report headcount, attendance and payroll cost
Course Outline:
Day 1: Problem Scoping and Spreadsheet Foundations
- Request-to-Problem Statement Scoping Worksheet
- Measure Definitions: Counts, Rates, Ratios and Weighted Averages
- Field Types, Granularity and the One-Row-per-Record Layout
- Excel Tables, Named Ranges and Structured References
- Workbook Plan Template: Purpose, Sources, Method and Output
Day 2: Record Repair and Preparation in Excel
- Duplicate, Blank and Inconsistent Entry Detection with Conditional Formatting
- Text and Date Repair with TRIM, TEXTSPLIT and DATEVALUE
- Lookup and Merge Techniques with XLOOKUP and INDEX-MATCH
- Validation Rules and Error Traps with IFERROR and COUNTIFS
- Get and Transform Query Steps for Repeatable Preparation
Day 3: Summary Statistics and PivotTable Techniques
- Central Tendency and Spread with AVERAGE, MEDIAN and STDEV.S
- Analysis ToolPak Summary Statistics and Histogram Output
- Outlier Screening with Quartiles, IQR Fences and Z-Scores
- PivotTable Grouping, Calculated Fields and Show Values As Options
- Segment Comparison with Slicers and GETPIVOTDATA Formulas
Day 4: Trend, Variance, Cohort and Significance Checks
- Period-over-Period Growth, Moving Averages and Index Numbers
- Budget-versus-Actual Variance Decomposition: Price, Volume and Mix
- Customer Retention Cohort Matrix Built with PivotTables
- t-Test and ANOVA Output from the Analysis ToolPak
- Correlation and Simple Linear Regression: Coefficients, R-Squared and P-Values
Day 5: Modelling Build and the Excel Evidence Workbook
- Retail Sales File Exercise: From Raw Export to Insight
- Operations Cost File Exercise: Variance and Driver Decomposition
- Chart Choice for Spreadsheet Users: Column, Line, Scatter and Box Plot
- Evidence Workbook Assembly with Documented Assumptions and Checks
- Insight Memo Drafting and Peer Challenge of Conclusions
Skills You Will Gain:
- Problem Scoping
- Spreadsheet Record Repair
- Summary Statistics
- PivotTable Techniques
- Budget Variance Decomposition
- Cohort Retention Tracking
- Significance Checking
- Regression Interpretation
Why Attend This Course:
- Return with an Excel Evidence Workbook and Insight Memo built on realistic sales and finance files and challenged by peers
- Spend less time fixing exported records by applying a repeatable preparation routine to each new file
- Explain what drove a change in results, not only report that the total moved
- Compare spreadsheet practice with professionals from other sectors and countries who report on similar figures
Conclusion:
Reliable reporting depends on a scoped problem, sound records and methods that separate real change from noise. This course moves from problem scoping and spreadsheet structure, through record repair and preparation in Excel, to summary statistics and PivotTable techniques, and then to trend, variance, cohort and significance checks. The final day applies the full sequence to realistic sales and operations files in a modelling build, producing an Excel Evidence Workbook and Insight Memo that participants can reuse in their next reporting cycle.