kWh Sales Forecasting & Analysis

1. Connect to the database
Click the ○ Offline badge in the top-right toolbar. The app will sign in to Supabase using your JPS credentials and sync all tables. The badge turns ● Live when connected.

2. First-time setup (Admin only)
If the badge shows ⚠ Partial, the database tables need to be created. Go to Admin → DB Schema, copy the SQL block, and run it in Supabase Dashboard → SQL Editor → New Query. Then re-sync.

3. Upload your first data file
Go to Admin → Upload Data and click Choose Excel File. Your workbook must contain at minimum an Actuals sheet. Download the template from the same page to see the exact column format required.

4. Set the reporting period
Use the Period bar below the topbar to select MTD, QTD, YTD, or Annual. Use the Year and Month dropdowns to change the reference period. All charts and KPIs update immediately.

Your Excel workbook can contain any combination of these sheets. Only sheets that are present will be processed:

Upload modes:
Overwrite month — deletes all existing rows for the selected year/month first, then inserts. Use when re-uploading corrected data for a period already in the DB.
Append / upsert — adds new rows and updates existing ones based on the unique key. Use for incremental updates.

Residential vs Commercial in Actuals:
Commercial rows leave consumption_bucket blank and fill demand_kva.
Residential rows fill consumption_bucket (e.g. "0-150", "150-300") and set demand_kva = 0.

Period selector: MTD = month-to-date (label shows Mar-2026), QTD = quarter-to-date (Q1-2026), YTD = year-to-date (YTD Mar-2026), Annual = full calendar year (2026). Use the Year and Month dropdowns to change the reference period. All charts and KPIs update immediately.

Traffic-light KPI colours:
🟢 Green = ≥ +5% vs prior period / budget
🟡 Amber = between -5% and +5%
🔴 Red = < -5% vs prior period / budget

Overview section: Shows total kWh billed across all rate classes. The Profile bar charts break down by Customer (top accounts), Industry, Rate Class, and Parish. Click any bar to drilldown to that segment. The Variance Summary table shows MTD, YTD, and YoY comparison for each rate class.

Chart controls: Use 🖨 Print to open a print-ready version and 💾 Save to download the chart as a PNG image. ⬇ CSV downloads the underlying table data.

Customers tab: KPI cards show MTD commercial GWh, RT40/RT50 gap vs budget, top industry, and defector count. The GWh by Rate Class chart and AI Commentary panel are shown side-by-side — click Generate Analysis to produce a written narrative. Click any chart bar to filter the customer table to that rate class. Use search and dropdowns to filter further. Increasing ↑ / Declining ↓ trend filter compares current YTD to prior YTD.

KAM & Commentary tab: Shows the KAM Performance table with Forecast Accuracy % (100 − MAPE, comparing entered forecasts vs actuals for the current month). KAMs are ranked from most to least accurate. Click any KAM row to filter the customer table to that KAM's accounts. The AI commentary here focuses on KAM-level performance. Assign KAMs in Admin → KAM Assignment or upload a KAM Excel sheet.

Defection tab: Flags customers whose 3-month rolling average kWh has dropped below 80% of the same 3-month window in the prior year. Severity grades: Critical <50%, Severe 50–65%, Moderate 65–80%. Each flagged account requires a validated cause (Solar/DG, Hurricane, Closure, Unknown) before it is counted as a confirmed structural defector.

Risk & Opportunity tab: Manual pipeline of commercial risks and opportunities. Risks are negative expected kWh impact (e.g. planned DG install, site closure). Opportunities are positive impact (e.g. new connection, expanded load). Use this to track and manage the forward commercial outlook.

KPIs: MTD residential GWh, customer count YoY, average kWh per customer. Items showing "--" need actuals data uploaded via the Actuals sheet.

Parish × Consumption Bracket table: Shows kWh billed by parish broken down into consumption brackets (0–150, 150–300, 300–500, 500+). Use the bracket filter to focus on a specific usage band. This is key for identifying under- and over-performing parishes relative to prior year.

EV chart: Tracks EV accounts and associated MWh. Early growth signal for load addition from EV charging.

Forecast Accuracy section: Compares entered forecasts to actuals. MAPE (Mean Absolute Percentage Error) is the primary accuracy metric — target <5%. Accounts are flagged "On trend" or "Off trend" based on whether actual performance matches the forecast within tolerance.

LE Forecast section: The Latest Estimate view. The LE is a single unified in-year forecast built on top of actuals with adjustments. Sub-tabs include Overview (summary KPIs), Monthly Trend, Customer Risk, Variance (LE vs Budget vs Prior Year), and Pivot & Movers. Versions (snapshots) can be saved at any point for comparison. There are no separate Base/Customer/RC/Advanced scenarios — all adjustments are applied through the unified LE adjustment layer.

Entering a forecast: Navigate to Forecast Accuracy → 6-Month Forecast Entry. Select the account, enter the forecast kWh for each future month, and click Save. Forecasts are stored in the database and visible to all users.

Exec Summary: One-page snapshot of the most important KPIs, commentary, and charts. Designed to be printed or exported as a PDF for management reporting. Populate the narrative boxes via Monthly Review → Commentary.

Variance Bridge: Waterfall chart decomposing the kWh or revenue variance vs budget or prior year into contributing factors (volume, mix, rate, weather, etc.). The bridge is DB-driven — populate via the Revenue Recovery drivers if available.

Revenue Recovery: Tracks USD revenue performance across the billing cycle. Shows budget, quantity effect, mix effect, FX effect, fuel and IPP costs. Upload data via the rr_data table or enter manually.

Net Generation: Tracks distributed generation (DG) output by month and technology type (Solar, Wind, CHP, ALL). Compare DG growth against grid consumption to quantify load displacement risk. Upload via the Net_Generation sheet.

Purpose: The Monthly Review section provides a structured area for the Sales team to document the story behind the numbers. Notes are stored in the database and visible to all authorised users.

Period Close: Managers and Admins can lock a period (year + month) in Admin → Period Close. A locked period prevents further edits to commentary and actuals for that month. This creates a clean audit trail for month-end sign-off.

AI Commentary: Use the Generate Analysis button in the AI Commentary cards (available in Commercial and Overview sections) to auto-generate a narrative draft. The commentary is based on the loaded DB data and can be copied into the Monthly Review notes box, edited, and saved.

Role assignments are managed in Admin → Users & Roles. Each user can have a different role for the Sales platform and the FP&A platform — they share the same login credentials but have independent role assignments.

Why are commercial kWh down vs budget?
1. Check Commercial → Customers — which rate class is driving the gap? RT40, RT50, or RT70?
2. Go to Data Validation — run validation to see GWh Deflections (accounts <80% of prior year) and Dropped Accounts.
3. Cross-reference with Defection — are the declining accounts flagged? What is the validated cause?
4. Check Net Generation — is DG growth absorbing load from specific rate classes?

How to identify new solar installs causing defection?
1. Go to Defection tab in Commercial. Filter by cause = "Solar/DG".
2. Cross-reference flagged accounts with the R&O Pipeline — does a risk entry exist?
3. Validate with the KAM assigned to the account — add a note via the customer drilldown table.

How to build the monthly narrative for management?
1. Sync DB to load latest actuals.
2. Set the period to the month being reported.
3. In Commercial → Customers, click Generate Analysis in the AI Commentary card.
4. For KAM-level narrative, go to Commercial → KAM & Commentary and use the Generate Analysis button there.
5. Copy, refine, and paste into Monthly Review → Commentary. Lock the period in Admin → Period Close.

How to track a large account moving to self-generation?
1. Note the account in R&O Pipeline as a Risk with expected kWh impact and timeline.
2. Flag the account in the Defection register once the 3-month trigger fires.
3. Add KAM commentary to the customer note in Commercial → Customer Detail.
4. Monitor the account month-on-month in Data Validation → MoM Anomalies.

How to verify data quality after uploading actuals?
1. After upload, go to Data Validation and click Run Validation.
2. Check Dropped Accounts — are any prior-year accounts missing? If unexpected, there may be an account number change in the source data.
3. Check GWh Deflections — accounts at <80% of prior year. Are these genuine declines or missing months?
4. Check MoM Anomalies — swings >25% could indicate a billing adjustment, estimated read, or real load event. Investigate before publishing analysis.

Run validation after every data upload to confirm completeness before publishing analysis. Navigate to Data Validation in the sidebar and click Run Validation.

Dropped Accounts: Accounts present in prior-year actuals that have zero rows in the current year. Could be closures, tariff transfers, or data gaps. Export to CSV and investigate any unexpected drops before reporting.

GWh Deflections: Accounts where current YTD kWh is below 80% of the same YTD period in the prior year. Severity: Critical (<50%) or Moderate (50–80%). Cross-reference with Defection Analysis to confirm cause.

Month-on-Month Anomalies: Consecutive months where the same account's kWh swings more than 25%. Common causes: billing adjustment, estimated read, catch-up read, or real load event. Always investigate before using in management reporting.

Reloading data: Go to Admin → Upload Data. Use the 🗑 Delete Month panel to wipe a specific period, or use 🗑 Wipe ALL Actuals (requires typing confirmation code WIPE-ALL) to clear everything before a full reload. Always re-run Data Validation after reloading.

Upload Data tab: Upload an Excel workbook (.xlsx) with one or more of the supported sheet names (Actuals, Budget, Residential_Budget, KAM, Industries, Net_Generation, Net_Billers, EV_Accounts). Use Overwrite Month to replace a period, or Append/Upsert for incremental updates. Click ⬇ Download Full Template to get a pre-built JPS_Data_Template.xls file with all 8 sheets and sample rows — opens in Excel, no internet needed.

Delete Month: Removes all actuals rows for a selected year and month. Use before re-uploading corrected data for a specific period.

Wipe ALL Actuals: Deletes every row across all years. Requires typing the confirmation code WIPE-ALL in the confirmation modal. Use only before a full data reload. This action writes an entry to the Audit Trail.

KAM Assignment tab: Assign Key Account Managers to accounts individually or in bulk. Changes save immediately. Alternatively, upload a KAM sheet in the Excel file. KAM assignments affect the Commercial tab filter dropdowns, KAM Performance table, and forecast accuracy ranking.

Period Close tab: Lock a month to prevent further edits to actuals and commentary for that period. Locked periods show a red banner on data-entry screens. Managers and Admins can lock/unlock periods.

Audit Trail tab: Every data upload, delete, wipe, period lock, and KAM change is logged here with timestamp, user, and action details. Non-repudiation record for compliance purposes.