Sharing this painful story as a cautionary tale. Was doing a major audit for a Minneapolis hospital system on their Xcel Energy bills under Schedule A-23 (Large General Service). Built what I thought was a bulletproof Excel model to calculate demand charges over 24 months. Found $186,000 in apparent overcharges and presented to the C-suite with full confidence. Turns out I had a cell reference error in my demand ratchet calculation - was pulling the wrong month's data. The utility was billing correctly all along. Had to return my $50,000 fee and grovel for forgiveness. The hospital terminated our contract and I lost three referral opportunities. Triple-check your formulas, people. This business is unforgiving of math errors.
Excel formula error cost me $50,000 in credibility
Ted, that's brutal but thank you for sharing. I had a similar Excel disaster with Entergy Mississippi on Schedule LGS-3. My VLOOKUP formula was off by one column and I was calculating wrong energy charges for six months. Caught it before presenting but it was a wake-up call. Now I have a colleague review every major spreadsheet before client meetings. Peer review should be mandatory for high-dollar findings.
Excel errors are my worst nightmare. Wisconsin Electric's Schedule Cg-4 has complex time-of-use calculations and I once had a formula that was adding instead of averaging peak demands. Didn't catch it until the utility questioned my findings. Now I use Excel's audit tools religiously and color-code all my cell references. Also print out the formulas on paper to review visually.
El Paso Electric Schedule No. 32 caught me with a similar issue. Had an absolute reference that should have been relative in my power factor calculation. Took me two days to figure out why my numbers were wrong. The lesson: build your models incrementally and test each component separately before combining them. Don't try to build the entire analysis in one giant formula.
Duke Energy Progress here in North Carolina has some nasty fuel adjustment calculations that I botched with a circular reference error. Spent hours wondering why Excel was giving me #REF! errors. Now I use Excel's formula auditing tools before every client meeting. The 'Trace Precedents' feature has saved me multiple times from embarrassing mistakes.
Steve brings up a great point about circular references. Those are sneaky and Excel doesn't always catch them right away. I've started using a standardized template for all my audits with built-in error checking. Each calculation has a 'sanity check' column that flags results that seem unreasonable.
This thread is giving me flashbacks. Duke Energy Ohio Schedule DP-TOD has time-of-use periods that I miscoded in Excel. Was calculating on-peak charges during off-peak hours for three months of data. The client caught it before I did, which was mortifying. Now I validate my time period logic with sample calculations done by hand.
CPS Energy's Schedule LIPS has demand charge calculations that I messed up with an INDEX/MATCH error. Was pulling demand values from the wrong month's data. Cost me credibility with a major client. The solution: I now use data validation rules in Excel to ensure my lookup values are within expected ranges. Catches most reference errors before they become problems.
Excel conditional formatting has become my best friend for catching errors. I set up rules that highlight cells with unusual values - like negative demand charges or energy usage over 999,999 kWh. Catches most mistakes before they make it into client reports. Duke Energy's bills are complex enough without adding Excel errors to the mix.
Great suggestions everyone. I've started using Excel's 'Evaluate Formula' feature to step through complex calculations cell by cell. Saved me from a major error on Ameren Missouri Schedule LPS last month. The step-by-step evaluation showed me that my ratchet comparison was using the wrong operator (> instead of <).
Ted, thanks for having the courage to share this story. These kinds of mistakes happen to all of us, but most people don't talk about them openly. Your Excel error checklist idea is excellent - I'm going to implement something similar. The key is building multiple validation layers so no single error can make it through to the client. Peer review, automated checks, and manual spot-checking should all be part of the process.