PDA

View Full Version : Efficient Macro - Suggestions



ajrob
05-07-2013, 10:21 PM
Hello,

I'd like some advice in putting together an efficient macro that steps through the combinations of Gas Price and Electric Price on the Cost of Gen tab (attached). In each case, a given combination should be plugged into the Fuel Price and Electric Price on the GenCalc tab, and the Effective Price stored in the table on Cost of Gen.

Does this make sense? Can someone help?

Thanks,

Adam

sassora
05-08-2013, 01:31 AM
Hi

Based on the first tab, should the Fuel cost = $3 and Elec cost = $50 be $21.59? (the effect price)

ajrob
05-08-2013, 09:36 AM
Yes, that is correct. So the desired outcome is the "effect" price at the intersection at each of the natural gas and electric price assumptions.

SamT
05-08-2013, 11:54 AM
In the chart, you are comparing Cost MMBtu HHV to Cost of MW hours. How does that work? The conversion of MMBTU to HHV isn't shown in your formulas and we have no reference to
C:\Users\d34237\Documents\Dept 819\Plant NPV Analysis\[1211 Site Performance Estimator (Master).xls]

I would truly love to find a generator fuel the only costs 1/2 cent per MMBtu.

I really tried hard to convert your formulas, large green border, even though there are a lot of hidden calculations involved.

I went on the web and gathered three constants. Kw/Gal diesel, Btu/Gal Diesel, and Btu/KW and plugged them into a new set of formulas, large blue border. Constants are blue and formulas are yellow in the attachment.

I made very few assumptions in my new formulas; the KW load was the same for Grid and Generator power, and the Generator fuel was diesel.

Your formulas calculate cost per MBtu at $.05 for diesel and $.0000015 for electricity. I think there's a problem in your math, how ever...

My formulas (with internet constants) calculated hourly cost (5MW/hr) at $250/hr for electricity at $50/MW, and $3,740/hr for Diesel fuel at $3.299/gal, so maybe your formulas aren't too far off.

You need to correct your comparison cost to just compare hourly operating costs for both gen and grid.

sassora
05-08-2013, 12:44 PM
I've attached formulas based on the original workbook.

Paul_Hossler
05-09-2013, 07:23 AM
Maybe a user defined function?


Option Explicit

Function EffectiveCost( _
Load As Double, Utilization As Double, HeatRate As Double, FuelCost As Double, _
ChilledWater As Double, Efficiency As Double, ElectCost As Double) As Double

Dim x1 As Double, x2 As Double, x3 As Double

x1 = HeatRate * FuelCost / 1000#
x2 = ChilledWater * Efficiency / 1000#
x3 = Utilization * x2 * ElectCost / 1000#

EffectiveCost = ((x1 * Load * Utilization) - x3) / (Load * Utilization) * 0.9

End Function


The math matchs the GenCalc example, but the Cost of Gen sheet doesn't have formulas, but I can't be sure that the matrix is correct (due to the links)

Paul