Randall O. from Kansas City, MO. I've built an Excel model to compare energy costs under different block rate structures, but I'm struggling with how to handle the transition points efficiently. For example, Evergy Missouri has declining block rates (first 1000 kWh at $0.09, next 4000 kWh at $0.07, excess at $0.06) while Kansas City Board of Public Utilities uses inclining blocks (first 600 kWh at $0.08, excess at $0.11). Has anyone created a flexible formula that works for both inclining and declining structures?
Excel model for comparing inclining vs declining block rates
Ruth from Columbus, OH. I use nested IF statements with VLOOKUP tables for the block breakpoints. Set up a table with cumulative kWh thresholds and corresponding rates, then the formula calculates energy costs incrementally through each block. Works for both inclining and declining structures. Randall, are you trying to optimize for different usage levels or just calculate costs at fixed usage?
Chuck B. from Cincinnati. Ruth's approach works well. I also add conditional formatting to highlight which blocks get used at different consumption levels. Randall, for Missouri vs Kansas comparison, don't forget Evergy also has that customer charge difference - think it's $12 for residential vs $15 for BPU.
Ruth, I'm doing both - calculating costs at specific usage levels and finding breakeven points between the two utilities. Chuck, good point about customer charges, I've got those in separate cells. Ruth, can you share an example of your VLOOKUP table structure? I think that might be cleaner than my current nested IF approach.
Randall, I set up columns for Block End kWh, Block Rate, and Block Max Cost. So row 1 might be 600 kWh, $0.08, $48. Row 2 would be 1000 kWh, $0.11, $92 (the $48 from first block plus $44 for 400 kWh at $0.11). Then use SUMPRODUCT to calculate total cost based on actual usage. Works great for sensitivity analysis across different usage ranges.
Randy Dawson here. For complex rate comparisons like this, I typically build a rate calculation function in Excel or even move to Python for more sophisticated modeling. Randall, if you're doing this type of analysis regularly, consider building a more robust tool that can handle demand charges, time-of-use periods, and seasonal variations too. The VLOOKUP approach Ruth described is solid for block rates, but you'll need additional modules for other tariff components. Missouri utilities also have fuel adjustment clauses that change monthly, so factor those into your forecasting.
Randy, I've thought about Python but trying to keep this accessible for colleagues who only know Excel. Ruth, the SUMPRODUCT approach sounds perfect - I'll restructure my model that way. This client analysis is just block rates but I can see building out modules for demand and TOU later. Thanks for the guidance everyone.
Karen S. from Green Bay, Wisconsin. We use a similar Excel setup for Wisconsin Public Service rate comparisons. Randall, one thing to watch with Kansas City BPU - they adjust their block thresholds seasonally. Summer months have different kWh breakpoints than winter. Make sure you're using the right seasonal schedule for your analysis period.
Karen, I didn't know about the seasonal block adjustments - thanks for the heads up! I'll check BPU's winter schedule. This is exactly the kind of detail that can throw off a rate comparison if you miss it.