Anyone have a good Excel template for tracking demand ratchets across multiple months? Working on a large Idaho Power account and their Schedule 9 has some complex billing demand rules. Need something that can handle seasonal variations and calculate the financial impact of different ratchet percentages. Currently doing this manually and it's eating up too much time.
Excel template for tracking demand ratchets?
Warren, I've got a template I use for PECO accounts that might work. It handles 12-month rolling demand calculations and has conditional formatting to highlight ratchet periods. The formulas are set up to automatically calculate demand charges based on the higher of actual or ratcheted demand. Can email it to you if interested.
Phil's template sounds good. I built something similar for Duquesne Light accounts but mine includes a pivot table that summarizes potential savings by month. Found over $18,000 in overbilled demand charges last year using it. The key is making sure your formulas account for different ratchet percentages - some utilities use 80%, others 85% or 90%.
That would be great Phil, thanks! Walt, good point about the different percentages. Idaho Power uses 85% for Schedule 9 but I've seen 75% on some of their older contracts. The pivot table idea is brilliant - would save me from manually calculating monthly variances.
For what it's worth, I use a macro-enabled workbook that automatically pulls demand data from PDF bills. Takes some setup but once configured, it saves hours per audit. Works great with Eversource bills since their format is fairly consistent. The macro extracts kW readings and populates the ratchet calculations automatically.
Vince, that sounds interesting but probably beyond my VBA skills. Warren, sent you the template. Let me know if the formulas need tweaking for Idaho Power's specific tariff structure. I included notes explaining each calculation so you can modify as needed.
Got it Phil, this is exactly what I needed! The conditional formatting makes it easy to spot months where the ratchet is active. Already found a billing error on one account - they were applying the ratchet incorrectly for three consecutive months. Customer is looking at about $4,200 in refunds.
Nice find Warren! That's exactly why these templates are so valuable. Human error on ratchet calculations is more common than utilities want to admit. I always double-check the math, especially during seasonal transitions when demand patterns change significantly.