Karl J. here in Santa Ana. Working with Southern California Edison's TOU-GS-3 rate schedule and I'm trying to build an Excel model that properly handles their declining block structure. The energy charges drop from $0.14/kWh for the first 250 kWh to $0.09/kWh for usage over 3,000 kWh, but there are like 5 different tiers and it's different for each TOU period. Anyone have a template or formula approach that works well?
Excel model for declining block rates - anyone have a template?
Randy Dawson here. Karl, declining block calculations in Excel can get messy with multiple TOU periods. The key is using nested IF statements or better yet, a lookup table with cumulative thresholds. For SCE's complex rate structure, I'd recommend breaking it down by TOU period first, then applying the tiered calculations within each period. You need to track cumulative kWh usage and apply the marginal rates correctly. I can send you a template framework - what's your email? The tricky part with SCE is that their "super off-peak" period has different tiers than peak and part-peak periods.
Randy, that would be amazing! My email is karl.utility@gmail.com. You're absolutely right about the super off-peak complexity. I've been trying to use VLOOKUP but it's not handling the cumulative nature correctly. This client has very uneven usage patterns across TOU periods so getting it right is critical.
Franklin T. from Carson City. I've wrestled with similar issues on NV Energy rates. One approach that worked was creating separate calculation columns for each tier, then summing only the applicable portions. So Column D calculates the first tier (min of usage and tier 1 limit), Column E calculates second tier (min of remaining usage and tier 2 limit), etc. Clunky but it works.
Kent N. in St. Pete. Franklin's approach is solid. I use something similar for Florida Power & Light's declining blocks. The key is building a helper column that calculates "remaining kWh" after each tier. Then your formulas become much cleaner. For TOU rates, I do this calculation separately for each time period, then sum the total costs.
Franklin and Kent, thanks for the tips! I'm making progress with the helper column approach. Randy sent me a great template that handles the cumulative calculations perfectly. Now I'm stuck on how to handle SCE's "baseline" allowance that affects the first few tiers. It varies by season and climate zone. Anyone dealt with baseline rates in their models?
Karl, the baseline allowance is definitely tricky because it's customer-specific based on their service territory and usage history. I typically handle this with a lookup table by zip code and season, then multiply by the customer's baseline percentage (usually 100% unless they're on CARE/FERA programs). The baseline rates are much lower than standard declining block rates, so getting this right can significantly impact your cost calculations for residential and small commercial customers.
Perfect, Randy. I found SCE's baseline territory map and I think I can build a lookup table. This is way more complex than the flat rate structures I'm used to! One last question - do you typically model demand charges separately or integrate them into the same worksheet as energy charges?
I keep demand charges on a separate tab usually, especially for TOU rates where demand is measured differently across periods. SCE's TOU-GS-3 has both maximum demand and time-of-use demand charges, so you need to track peak demand by TOU period plus overall maximum demand. Gets messy if you try to combine everything on one sheet.
Agreed on separate tabs. I learned that the hard way trying to cram everything into one sheet. My current template has tabs for: Customer Info, Energy Calculations, Demand Calculations, Summary, and Rate Comparison. Much cleaner and easier to audit your work.
This has been incredibly helpful, everyone. I've got a working model now that handles the declining blocks, TOU periods, baseline allowances, and separate demand calculations. My client is going to save about $1,200/month by optimizing their usage patterns based on the model outputs. Thanks especially to Randy for the template!