Tutorial Summary: How to Design a Dynamic Excel Financial Dashboard (with Zebra BI Add-In)

This tutorial demonstrates creating a dynamic and interactive financial dashboard in Excel, comparing Actuals, Budget (Plan), and Previous Year data for store locations. The process includes data connections, formula setup, automations, and advanced visualizations using both native Excel tools and the Zebra BI add-in.

---

Objectives

• Build a financial dashboard in Excel from scratch.
• Automate data refreshes and visualizations for efficient monthly reporting.
• Calculate variances and present insights with charts.
• Use Zebra BI add-in to create polished, interactive, and professional reports.

---

Key Steps

1. Data Preparation

• Data Sources:
  • "Actuals" tab: Year-to-date sales by year, month, store location, and value (as a table named TSales).
  • "Plan" tab: Budget/Plan data for the current year, with store locations as rows and months as columns.
• Data Loading Options: Manual data entry, copy-paste, Power Query, or third-party add-ins.

---

2. User Inputs

• Dropdown for Month Selection:
  • Use Data Validation → List, sourcing months from the "Plan" sheet.
• Year Selection:
  • Use a dynamic formula (e.g., =YEAR(TODAY())) for current year.
  • Format for non-editable display and lock the cell for sharing.

---

3. Data Retrieval with Formulas

• Actuals and Previous Year Lookup:
  • Use XLOOKUP to fetch values matching the selected Year, Month, and Store.

=XLOOKUP(
    [Year]&[Month]&[Store],
    Actuals[Year]&Actuals[Month]&Actuals[Store],
    Actuals[Sales]
)

• For previous year: subtract 1 from Year reference.

• Plan Lookup (Different Table Shape):

  • Use XLOOKUP (or INDEX/MATCH) to match Store in rows, and Month in columns.

---

4. Variance Calculations

• Formulas:
  • Variance to Previous Year: (Actual / PreviousYear) - 1
  • Variance to Plan: (Actual / Plan) - 1
  • Wrap in IFERROR() to avoid display errors.
• Formatting: Use number formatting for clarity (comma separator, zero decimals).

---

5. Visualizations with Native Excel

• Bar/Column Charts:
  • Compare Actual vs. Plan using bar charts.
  • Visualize variance (positive/negative) with color-coded columns (e.g., green for positive, light red for negative).
  • Adjust chart formatting for management clarity.
• Interactivity: All charts update dynamically when user changes Month or Year.

---

6. Advanced Visualization with Zebra BI Add-in

• Installation:
  • Available for Excel 2016+ via the "Add-ins" menu.
  • Add Zebra BI Tables for Office and Charts for Office.
• Features:
  • Auto-recognizes and calculates Actual, Previous Year, Plan, Variances.
  • Rearranging columns, sorting, and formatting is intuitive.
  • Fully dynamic—filters (month, year) update the visualizations instantly.
  • Integrated comments/annotations directly from your data source.
  • Variety of chart types (integrated bar, waterfall, etc.)
  • IBCS certification ensures clarity and standardized reporting.
• Additional Perks:
  • Free version available (with watermark).
  • Extended 30-day Pro trial via sponsored link.

---

Tips and Best Practices

• Use structured tables to simplify references in formulas and charts.
• Protect dynamic formula cells to prevent accidental overwrites.
• Adjust chart colors and layouts to align with your organization’s guidelines.
• Use add-ins like Zebra BI for fast, professional, IBCS-compliant reporting.
• Add comments/annotations directly in your dataset for enhanced storytelling.

---

Call to Action

• Try building your own Excel dashboard with dynamic elements.
• Experiment with the Zebra BI add-in (using the extended free trial link).
• Like and subscribe for more Excel tutorials.
• Post questions or share your dashboards in the comments.

---

Special Offer & Credits

• Thanks to Zebra BI for sponsoring.
• Check the video description for an exclusive 30-day free Pro trial of Zebra BI.

---

Summary:
With these techniques, you can build an interactive dashboard in Excel, automate monthly reporting, and add a polished, insightful layer using Zebra BI. These skills not only save time but also empower clear, decision-ready reporting for finance teams.