Creating Interactive Kpi Dashboards

Python
import pandas as pd
import numpy as np
from datetime import datetime, timedelta
import openpyxl
from openpyxl.chart import LineChart, Reference
from openpyxl.formatting.rule import ColorScaleRule, CellIsRule
from openpyxl.formatting import PatternFill
from openpyxl.utils.dataframe import dataframe_to_rows

# Create CFO Dashboard
def create_cfo_dashboard():
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "CFO Dashboard"
    
    # Header setup
    ws['A1'] = "CFO EXECUTIVE DASHBOARD"
    ws['A1'].font = openpyxl.styles.Font(size=16, bold=True)
    
    # KPI sections with formulas
    setup_revenue_section(ws, start_row=3)
    setup_margin_section(ws, start_row=12)
    setup_cash_section(ws, start_row=21)
    
    wb.save("CFO_Dashboard.xlsx")

Progress:

• Step 1: Define KPI structure and data sources
• Step 2: Create data model with time series
• Step 3: Build comparison frameworks (Prior/Budget/Forecast)
• Step 4: Implement RAG status logic
• Step 5: Add sparklines and trend visualizations
• Step 6: Create interactive slicers
• Step 7: Format and test dashboard

Step 1: KPI Structure Setup

Python
kpi_structure = {
    'Revenue': ['Total', 'By Segment', 'By Geography'],
    'Margins': ['Gross Margin %', 'EBITDA Margin %'],
    'Cash': ['Cash Conversion Cycle', 'DSO', 'DPO', 'DIO'],
    'Capital': ['Working Capital', 'Debt/Equity', 'ROE'],
    'Productivity': ['Revenue/Employee', 'EBITDA/Employee']
}

Step 2: Data Model Creation

Python
def create_data_model():
    periods = ['Current', 'Prior', 'Budget', 'Forecast']
    segments = ['Product A', 'Product B', 'Services']
    regions = ['North America', 'Europe', 'APAC']
    
    # Sample data structure
    data = {
        'Period': periods * 20,
        'Segment': segments * 26,
        'Region': regions * 26,
        'Revenue': np.random.normal(1000000, 100000, 80),
        'Gross_Margin': np.random.normal(0.35, 0.05, 80),
        'EBITDA_Margin': np.random.normal(0.15, 0.03, 80)
    }
    return pd.DataFrame(data)

Step 3: RAG Status Implementation

Python
def apply_rag_status(ws, value_cell, target_cell):
    # Red: <90% of target, Amber: 90-95%, Green: >95%
    red_fill = PatternFill(start_color="FF0000", fill_type="solid")
    amber_fill = PatternFill(start_color="FFA500", fill_type="solid")
    green_fill = PatternFill(start_color="00FF00", fill_type="solid")
    
    ws.conditional_formatting.add(
        value_cell,
        CellIsRule(operator='lessThan', formula=[f'{target_cell}*0.9'], fill=red_fill)
    )

Step 4: Sparklines Setup

Python
def add_sparklines(ws, data_range, sparkline_cell):
    chart = LineChart()
    chart.height = 2
    chart.width = 6
    chart.legend = None
    chart.x_axis.tickLblPos = "none"
    chart.y_axis.tickLblPos = "none"
    
    data = Reference(ws, min_col=1, min_row=1, max_col=12, max_row=2)
    chart.add_data(data)
    ws.add_chart(chart, sparkline_cell)

Example 1: Revenue Dashboard Section Input: Revenue data by segment for 12 months Output:

REVENUE METRICS                Current    Prior    Budget   Forecast   Var%   RAG
Total Revenue ($M)             125.5     118.2    130.0    128.0     6.2%   🟡
  - Product A                  65.2      58.9     70.0     68.0     10.7%   🟢  
  - Product B                  40.3      42.1     42.0     41.0     -4.3%   🔴
  - Services                   20.0      17.2     18.0     19.0     16.3%   🟢
Geographic Split:
  - North America              75.3      70.9     78.0     76.8     6.2%   🟡
  - Europe                     35.2      33.8     36.0     35.5     4.1%   🟡
  - APAC                       15.0      13.5     16.0     15.7     11.1%   🟢

Example 2: Cash Conversion Cycle Input: DSO=45, DPO=30, DIO=60 days Output:

CASH MANAGEMENT              Current   Prior   Budget   Target   Trend
Cash Conversion Cycle        75 days   82 days  70 days  65 days  📈 ▲
  - Days Sales Outstanding   45 days   48 days  42 days  40 days  📈 ▲
  - Days Payable Outstanding 30 days   28 days  32 days  35 days  📉 ▼
  - Days Inventory Outstanding 60 days  62 days  60 days  55 days  📈 ▲
Working Capital ($M)         45.2      48.9     42.0     40.0     📈 ▲

Dashboard Design:

• Use consistent color coding (Green=Good, Red=Attention, Amber=Watch)
• Group related metrics together
• Show absolute values AND percentages
• Include trend indicators (arrows, sparklines)

Data Organization:

• Create separate data sheets for each metric category
• Use named ranges for formulas
• Build pivot tables as data sources for slicers
• Implement data validation for input cells

Interactive Elements:

Python
# Add slicers for Period, Segment, Region
pivot_table = ws.pivot_table(
    data=data, 
    values='Revenue', 
    index='Segment', 
    columns='Period'
)

# Create slicer connections
slicer_cache = wb.slicer_caches.add(pivot_table, "Segment")
slicer = wb.slicers.add(slicer_cache, "Segment Filter")

Formula Templates:

EXCEL
# Variance calculation
=IFERROR((Current-Prior)/Prior, 0)

# RAG Status
=IF(Current/Budget>=0.95,"🟢",IF(Current/Budget>=0.90,"🟡","🔴"))

# Trend Arrow
=IF(Current>Prior,"▲",IF(Current<Prior,"▼","►"))

• Over-cluttering: Don't show every possible metric. Focus on top 10-15 KPIs
• Static comparisons: Always include multiple comparison points (Prior/Budget/Forecast)
• Missing context: Include target ranges and explanation of what's "good"
• Poor formatting: Inconsistent number formats confuse users
• No drill-down: Executive dashboards should link to detailed analysis
• Outdated data: Build in data refresh mechanisms and timestamps
• No mobile consideration: Ensure dashboard works on tablets/phones

Technical Issues:

• Test slicer performance with large datasets
• Validate formulas across all scenarios
• Include error handling for missing data
• Document data sources and update procedures