Intermediate Excel Data Analysis

This unit expands foundational Excel skills into intermediate data analysis techniques, covering data import and transformation, PivotTables/PivotCharts, and applied statistical and financial functions. Students will perform scenario analysis, forecasting, and optimization to summarize and interpret business finance datasets and prepare for advanced modeling tasks.

Learning Objectives

• Analyze datasets by creating and customizing PivotTables and PivotCharts—including grouping, subtotals, custom calculated fields, displaying details, and using the Data Model—to summarize and visualize business finance information
• Apply data integration and transformation techniques to import external data, query databases, transform tables, and prepare data for analysis and Pivot-based reporting
• Demonstrate proficiency with financial and statistical functions and tools (PV, PMT, moving average, rank, standard deviation, t-test, z-test) to compute present value, loan payments, measures of central tendency and dispersion, and significance tests
• Evaluate scenarios, forecasts, and optimization outcomes using Scenario Manager, Solver, linear trend extensions, and forecasting tools to support decision-making and model refinement