Lesson Summary

The data flow in the file is designed through three distinct tabs:

• Data Tab:
  • Used exclusively for raw data input without any formulas.
  • Ideal for pasting income statement, balance sheet, and other raw financial data.
  • Ensures data is not disrupted by formulas, allowing easy copying and pasting.
• Staging Tab:
  • Contains formulas that process and calculate key performance indicators (KPIs).
  • Uses functions like INDEX MATCH to retrieve specific numbers such as revenue and gross profit from the data tab.
  • Calculates important metrics like gross margin based on the raw data.
  • Serves as an intermediary where all formula-driven data processing happens.
• Dashboard Tab:
  • Pulls final calculated values from the staging tab using formulas, again leveraging INDEX MATCH for precision.
  • Displays KPIs such as gross margin for specific months (e.g., February 2021).
  • Includes targets and other metrics retrieved by matching against criteria like month and value type.
  • Provides a clean, user-friendly interface for viewing the processed data and KPIs.

Summary of the Design Choice:

• Separates raw data from calculations to maintain data integrity and ease of updates.
• Allows easy pasting of raw data without worrying about accidental formula overwrite.
• Centralizes all computations in the staging tab for clarity and easier troubleshooting.
• Keeps the dashboard focused on presenting finalized insights rather than data manipulation.
• Overall, this modular approach simplifies data management and visualization workflow.