The overall goal of the Capstone Project is for students to demonstrate their proficiency in Excel by completing a multi-module case study that incorporates skills from Modules 1-3. The case study simulates real-world business scenarios where students are asked to analyze data, perform calculations, and present findings.

In Module 1, students are introduced to a fictional company called Contoso, Ltd that manufactures and sells sporting goods. They are provided with sales data for different product lines and must complete the following tasks:

Set up a workbook with multiple worksheets to effectively organize the sales data which includes items sold, revenue, costs, profit margins, etc. This tests their ability to design an Excel workbook and structure worksheets appropriately.

Enter formulas to calculate totals for various metrics like revenue from each product category, total costs, gross profit, net profit, etc. from the raw sales data. This evaluates their knowledge of basic formulas like SUM, AVERAGE, COUNT, etc.

Format cells with proper number formatting like currency for dollar values, percentage for margins. Students must also conditionally format cells to easily identify values like high costs, low profits, etc. This validates their skills in number formatting and conditional formatting.

Use Excel functions like IF, SUMIF, COUNTIF to analyze the data. For example, calculating the total revenue from sales of a product in a specific region. This assesses their proficiency in using Excel functions for analysis.

Chart the data visually using appropriate chart types like column charts or pie charts. Students must select the relevant data ranges and format the chart to clearly present analysis. This tests their visualization skills.

Address errors or inconsistencies in the provided source data by troubleshooting formulas. Students need to identify and resolve any errors in the workbook.

In Module 2, students build on the existing workbook created in Module 1. They are asked to:

Consolidate data from a new sheet containing additional sales information into the existing workbook in a logical way. For example, adding a new product category or integrating profit and loss data by month.

Perform “What If” analysis using Excel tools like Goals Seek or Data Tables to determine the impact of changes. For example, calculating breakeven point, changes to costs/prices and how they affect profits.

Create macros to automate repetitive tasks like formatting or calculations. Students need to write simple macros using the Macro Recorder and assign them to Form Control buttons for ease of use.

Conduct forecasting of future sales using built-in functions like TREND or GROWTH. Students use historical data to predict revenues for upcoming periods.

Enhance visualization of key metrics by building more advanced charts with things like data labels, trend lines, filters etc. Present analysis findings clearly through customized charts and visuals.

In Module 3, students expand the analysis of the business by:

Merging data from multiple worksheets or workbooks into a master workbook to get a consolidated view. This could include integrating financial statements, budgets, previous year data etc.

Performing advanced calculations comparing actual vs budgeted metrics using logical/mathematical and financial functions like IFERROR, DATE, PMT etc.

Conducting comprehensive “What If” and scenario analysis to develop forecasts under different assumptions around variables like volume, costs etc. Students vary inputs to model outcomes.

Building interactive dashboards using tools like Slicers and Timeline to allow dynamic visualization and exploration of the data.

Documenting all workbook details, any assumptions made, and overall analysis conclusions in separate professional report sheets within the workbook. Proper documentation standards are assessed.

The above detailed examples give an idea of the progressively complex objectives and skills assessed through the Excel Capstone Project’s modules. Students must demonstrate proficiency in a wide range of Excel tasks covering various topics like data organization, calculations, functions, charting, tools for analysis, macros, forecasting and presentation of insights. The multi-module format evaluates both their ability to complete individual tasks as well as their overall problem-solving and analysis skills when building out an integrated workbook solution over 15,000+ characters as requested.