Anyone have a good Excel template for tracking demand ratchets across multiple billing periods? We Energies has some complex ratchet provisions and I'm tired of manually calculating these every month. Looking for something that can handle seasonal ratchets and different percentage factors. Would be willing to share what I've built so far if others have improvements.
Excel template for demand ratchet tracking?
Linda, I've got a decent one I built for Duquesne Light's Schedule LP tariff. Handles 12-month rolling ratchets at 75% factor. The key is setting up the formulas to automatically pull the highest demand from the lookback period. Email me if you want me to send it over - walt.d.auditor@email.com.
I use a Google Sheets template that's shared with my whole team. JEA's ratchet is pretty straightforward - 80% of highest demand in past 11 months. The nice thing about Google Sheets is multiple people can update it and it auto-saves. Plus you can set up email alerts when ratchets are about to roll off.
Rob that's smart about the alerts. Georgia Power has seasonal ratchets (summer vs winter) which makes it more complex. I built conditional formatting so the cells turn red when we're approaching a new 12-month high. Saved us about $8,400 last year on one account when we caught a ratchet that was about to reset.
For MLGW accounts I use a simple pivot table approach. Their Schedule GSA has a 75% ratchet but it's based on coincident peak rather than individual meter demand. The pivot table lets me quickly see patterns across multiple accounts. Key is making sure your data import is clean - garbage in, garbage out.
Anyone tried Power BI for this? PG&E's billing data exports work great with it and the visualizations help spot anomalies. Built a dashboard that shows ratchet trends across our whole portfolio. Management loves the pretty charts and it actually caught a billing error worth $12k on an A-6 schedule.
Walt that template worked perfectly! Had to adjust for We Energies' specific tariff language but the core formulas saved me hours. Dan, haven't tried Power BI yet but might be worth looking into for our larger clients. Thanks everyone for the suggestions.
Linda glad it helped! One tip - make sure you're accounting for demand multipliers in your calculations. Georgia Power applies different multipliers depending on voltage level and it can throw off your ratchet calculations if you're not careful. Learned that the hard way on a 46kV account.