Hi there,

I am struggling to set up a system and would appreciate any guidance.

I am trying to come up with a new price model for energy usage. Here is some background:

The first 700 cost X1, the next 700 to 1300 cost X2 and everything else X3. As a result, the bill is equal to the sum X11*kWh1 + X21*kWh2 + X31*kWh3.

I need to introduce a new demand charge that is multiplied by the maximum kW demand that month which ranges from 1-7 usually, so a few orders of magnitudes less than the kWh. The new bill will look like Demand*kW + X12*kWh1 + X22*kWh2 +X 32*kWh3.

Notice, one costumer can fall into all three of these tiers. The first is typically flat with no slope, the next has a steep slope up to the next tier and the last has a little slope and tapers off. For example, if a costumer used 2300 we have X11*700 + 600*X12 + 1000*X13.

The problem is, I need to come up with new charges Demand, X12, X22, X32 such that average costumer bill with these new coefficients is equal to old bill with charges with X11, X21, X31.

I am working with Excel Solver to come up with a solution. I was thinking I could set up the problem by setting the sums equal for each tier (i.e, first 700, next 700-1300, over 1300) like so:

SUM Demand*kW + X12*kWh1 = SUM X11*kWh1

SUM Demand*kW + X22*kWh2 = SUM X21*kWh2

SUM Demand*kW + X32*kWh3 = SUM X31*kWh3

And minimize the squared error between the old bill and new bill.

This gives me sensible answers for the new X11, X12, X13, but blows up the Demand charge or sets it to zero and that is because I have 4 variables and 3 equations, but I can’t think of a fourth equation to obtain the solution I am looking for.

thanks