Intermediate Excel Analysis and PivotTables

This unit advances students’ Excel skills in statistical analysis, forecasting, data consolidation, PivotTable management, and basic financial modeling. Learners will use built-in Excel functions and features to calculate descriptive and inferential statistics, create forecasts and trend lines, consolidate and style PivotTables, and model loan payments in preparation for advanced analysis in the final unit.

Learning Objectives

• Analyze datasets by calculating descriptive and inferential statistics—including mean, median, variance, covariance, correlation, percentiles, ranks, minimums/maximums—and create frequency and grouped frequency distributions; generate random samples to support sampling decisions and interpretation.
• Apply regression and forecasting techniques to compute linear and exponential trend equations, use LINEST/FORECAST (and related) functions to generate forecasted values, and plot trend lines for data-driven predictions.
• Demonstrate proficiency building and managing PivotTables by consolidating data from multiple worksheets, adding multiple fields and custom calculated items, creating and applying custom styles, refreshing data sources, and preserving formatting.
• Evaluate financial scenarios by calculating loan payments and required interest rates, decomposing payments into principal and interest (using PMT, IPMT, PPMT), and calculating cumulative principal and interest over time to inform decision-making.