Final Project – Part 2: Building the Calculations Layer HTML pr

Final Project – Part 2: Building the Calculations Layer

In this second part of the Final Project, you will create the calculations layer that powers your Sales Dashboard. This layer transforms raw data into meaningful insights using formulas, helper columns, KPIs, and summary metrics.

A well‑designed calculations layer is essential for any professional dashboard because it ensures:

• Clean and reliable results
• Easy updates when new data is added
• Clear separation between raw data and analysis
• Consistent formulas across the entire workbook

You will apply skills from Modules 2, 3, 4, 5, and 6 to build a solid analytical foundation.

---

1. Create a New Sheet for Calculations

Create a worksheet named Calculations. This sheet will contain:

• Summary metrics (KPIs)
• Helper tables
• Monthly totals
• Category totals
• Region totals

Keeping calculations separate from the dashboard improves clarity and prevents accidental edits.

---

2. Create Key Performance Indicators (KPIs)

Start by calculating the main KPIs that will appear in the dashboard:

• Total Sales
• Total Quantity Sold
• Average Order Value
• Top Product
• Best Region

• Total Sales

=SUM(Table1[Total Sales])

• Total Quantity Sold

=SUM(Table1[Quantity])

• Average Order Value

=AVERAGE(Table1[Total Sales])

See: Lesson 5.5 – Basic Statistics

---

3. Create Monthly Sales Summary

To build line charts and trend analysis, create a monthly summary table.

Steps:

1. Create a column with all months (Jan–Dec).
2. Use SUMIFS to calculate monthly totals.

• Monthly Total Sales

=SUMIFS(Table1[Total Sales], Table1[Date], ">=1/"&A2&"/"&YEAR(TODAY()), Table1[Date], "<=31/"&A2&"/"&YEAR(TODAY()))

Replace A2 with the cell containing the month number (1–12).

See: Lesson 3.3 – Math Functions

---

4. Create Category Summary

This summary will feed your pie chart and category analysis.

• Total Sales by Category

=SUMIFS(Table1[Total Sales], Table1[Category], A2)

Where A2 contains the category name.

See: Lesson 4.3 – Filtering Data

---

5. Create Region Summary

This summary will be used for bar charts and regional comparisons.

• Total Sales by Region

=SUMIFS(Table1[Total Sales], Table1[Region], A2)

Where A2 contains the region name.

---

6. Create Helper Columns (Optional but Recommended)

Helper columns make your dashboard more flexible and easier to maintain.

• Year

=YEAR([@Date])

• Month Number

=MONTH([@Date])

• Month Name

=TEXT([@Date], "mmm")

These columns help with sorting, grouping, and chart creation.

See: Lesson 3.5 – Text Functions

---

7. Apply Conditional Formatting to KPIs

Highlight key values to make them easier to read:

• Green for positive trends
• Red for low performance
• Bold formatting for totals

See: Lesson 4.4 – Conditional Formatting

---

8. Organize the Calculations Sheet

A clean calculations sheet is essential. Use:

• Clear section titles
• Light borders
• Consistent formatting
• Freeze Panes to keep headers visible

See: Lesson 6.2 – Freeze Panes

---

9. Practical Exercise

1. Create a worksheet named FP_Part2_Calculations.
2. Calculate Total Sales, Total Quantity, and Average Order Value.
3. Create a monthly summary table using SUMIFS.
4. Create category and region summary tables.
5. Add helper columns to your main dataset.
6. Apply conditional formatting to highlight KPIs.
7. Organize the sheet with clear sections and formatting.

---

Next Part

Final Project – Part 3: Creating Visualizations