I'm leaning toward VBA solution building formulas as long as you have the
documentation of how things work somewhere to convince the auditors and to
quickly refresh your memory if need be. Those are times you really don't
want to look too hesitant or unsure, and fumbling around trying to come up
with the explanation is not a great confidence builder. So the documentation
part is to your advantage. Besides, I'm more of a coder than a spreadsheet
function type person anyhow.

Within your VBA code, if you are going to reference something more than once
and it may need to be changed globally at some time in the future as with
your driver and/or frequency examples, I'd definitely set those up as
constants somewhere so that one change in one place makes "repairs" to the
entire process. Same for values picked up from worksheets for use within the
code.

Performance speed really isn't much of an issue these days, not unless you
are performing huge amounts of data moving, analysis and calculation. You'd
just need to make sure that everything has been updated, including formulas
through VBA operation, when getting ready for things. Things to watch out
for would be a situation where you pick a variable up off of a worksheet, and
place that value, as a value and not as a reference, into a formula and
someone comes along and changes it (as from frequency from 1 to 12 or 4).

Example
myFrequency = Sheets('Sheet1'!A1).Value
or even
myFrequency = Range("theFrequency").Value
then you build a formula as
myFormula = "=(14*33)/" & myFrequency
the formula would actually end up as "=(14*33)/1"
or 4 or 12 or whatever 'theFrequency' value was at the time the code was run.
You'd want to buld your formulas using any workbook/worksheet names that
were in use as
myFormula = "=(14*33)/theFrequency"

But I think you already understand this part of it.

Questions like these are all design phase questions that it is wise to ask.
Your decision has to be based on many factors including:
ease of maintenance
ease of use
ease of understanding of outputs
additional requirements that may come from sources such as potential auditor
questions about 'how things work'. What priority you put on these factors
and any others you come up with also has to be decided upon; user friendly
usually means maintenance or development intensive. Ease of use and/or
understanding are both subjective judgements - work closely with the end user
to get a good balance.

" wrote:

Thanks for the response. Okay, let's assume I use VBA code to create
equations, not values, that will populate the output cells. And these
equations will employ a combination of native Excel formulas, defined
names, and constants based on inputs to the opex table, a couple of
immediate considerations arise: use of names vs. constants and
frequency of updating formulas.

The reason I would use VBA is so that the equations could be simplified
and easier to read. I could bypass VBA completely, but that would
produce huge unreadable formulas or would require add'l cells to house
supporting equations (which I do not want to use). So for example,
looking at the frequency of expense factor, the following equation:

driver * if (frequency = year, 1, if frequency = month, 12, if
frequency = qtr, 4, etc...)

could be simplified using VBA to output:

driver * 12
(assuming the month is the input for frequency)

Do you see any potential problems in this approach? Speed
considerations?

Would it be better to use a defined name instead of a constant, such
as: driver * frequency, where frequency is a defined name set equal to
1 (year), 12 (month), 4 (qtr) etc.?

I think the formulas would have to be recalculated anytime a variable
not defined with a name changes, like in the above example, if the user
changes the frequency to 1. Named variables would not need to change,
since Excel should update those automatically.

If anyone has done something like this in the past, any suggestions are
appreciated.