Working on a complex demand ratchet analysis for NV Energy and realized my current spreadsheet setup is pretty crude. Anyone have a good Excel template that handles multi-year ratchets with seasonal variations? This particular account has a 12-month ratchet but different percentages for summer vs winter months. Getting confused tracking all the variables.
Spreadsheet template for demand ratchet calculations
Kim, demand ratchets are tricky to model well. I built a template for Kentucky Power that handles seasonal variations and multiple ratchet periods. Key is using nested IF statements with XLOOKUP functions to grab the right percentage for each billing period. Also important to track both contract demand and billed demand separately. Can send you a sanitized version.
Be careful with NV Energy's ratchet calculations - they have some unusual provisions in their tariffs. Make sure you're accounting for the "new customer" exception and how they handle partial billing periods. I caught a $12,000 error on a Las Vegas casino account because the utility wasn't properly resetting the ratchet after a service upgrade.
Thanks Oz and Clem! That's exactly the kind of detail I need to watch for. Clem, the "new customer" exception - is that in the general service schedule or buried in the special conditions? This account had some meter changes that might qualify. Oz, I'd definitely appreciate seeing your template structure.
It's in Schedule MGS section 4.2 if I remember correctly. Also check their "Changes in Service" provisions - meter upgrades sometimes reset the ratchet baseline but not always. NV Energy is pretty good about applying these correctly but I've seen errors when there are multiple service modifications in a short period. Worth pulling the original service agreement too.
One more thing to watch with NV Energy - their summer season definition changed a few years back from May-October to June-September. If you're analyzing historical data, make sure you're using the right seasonal periods for each year. I made that mistake once and had to redo months of analysis.
Willa, good catch! I hadn't noticed the season change. That could definitely affect the ratchet percentages I'm using for 2018-2019 data. This is why I love this forum - you guys catch details I would have missed completely. Going to double-check all my seasonal assumptions now.
Kim, sent you the template via email. I included some notes on the formulas and a sample calculation sheet. The conditional formatting helps visualize when the ratchet is active vs when current demand is driving the bill. Let me know if you have questions about any of the logic.
Great discussion here. Demand ratchets are one of those billing components that seem straightforward until you dig into the details. The seasonal variations and service modification exceptions can really trip you up. Kim, once you get your analysis sorted out, curious what kind of savings opportunity you find. NV Energy's demand charges are pretty steep in their commercial rates.