Anyone have a good Excel macro for calculating demand charges across different rate schedules? I'm working with FirstEnergy here in Ohio and their GS-2 tariff has some complex demand ratchet provisions. Manually calculating the 12-month rolling average is eating up too much time on these larger accounts.
Excel macro for demand charge calculations?
Jim, I've got something that might help. Built a macro last year for Ohio Edison accounts that handles the demand ratchet calculations. It pulls the highest demand from the previous 11 months and compares to current month. Can email it to you if you want.
I use a similar approach for Duke Energy here in Charlotte. The key is setting up your data input correctly - month, year, actual demand, and billed demand in separate columns. Then the macro can loop through and find discrepancies where they're not billing the correct ratcheted amount.
Frank, could you send that macro my way too? I'm seeing similar issues with TVA industrial accounts here in Tennessee. Found a $3,200 annual overcharge last month where they weren't properly applying the seasonal demand provisions.
Has anyone tried using Access instead of Excel for this stuff? I've been thinking about building a database that could handle multiple utilities and rate schedules. CPS Energy here in San Antonio has like 15 different commercial rate schedules.
Angela, Access would be overkill for most audits. I stick with Excel but use pivot tables extensively. Can slice and dice the data by month, rate schedule, demand tier, whatever. Found $8,900 in errors for a Connecticut Light & Power account just last week using this approach.
Vince is right about Excel being sufficient for most work. I'll clean up my macro and post it in the file sharing section. Should work for any utility that uses 12-month demand ratchets. Just need to adjust the percentage factors for different tariffs.