vba output: formulas or values

[sloth]

Having limitted VBA programming experience (although enough to get
around), I would appreciate some advice on a design problem I'm facing.

I'm creating a table that allows the user to define expense line items
in terms of their characteristics and also apply various labels to each
line item in order to simplify and facilitate analysis. For example,
here's an example of some of the fields from one line item:

Opex Item: Marketing Acquisition: Customer Type A
Expense Type: Variable
Frequency: Month
Driver: Customer Type A - Gross Adds
Unit Cost: $100.0
Price ∆: -1.0%
Price ∆ Period: Year
Threshold: $50.0 (min or max value)
Opex Category: Marketing
Provider: External
Provider Detail: Marketing Firm Name

My goal is to setup a function that takes these inputs and creates
output in a P&L or DCF output worksheet across a number of periods as
defined by the user. Does anyone have any guidance as to what form is
best for the output, i.e. values or equations/Excel functions? There
does need to be some level of description on how the formulas are
calculated or users will not trust the results.

For example, in the first case, I would have VBA code do all the
calculations and then just write the resulting values to the output
cells, i.e. $50,000 for mnth 24 expense. This is somewhat of a
blackbox for the user. I guess I could create a field in the output
area that showed the formula used.

In the second case, I would have VBA code create formulas, as if I
wasn't using VBA and was using Excel's non-VBA functionality only. A
simplified output might look something like:
=(CustAGrossAdds_t*$100*((1-PriceChange)^t)*12. Compared to the first,
this seems to use more processing power than necessary, but might be
more user friendly.

Am I looking at this incorrectly? Any help is appreciated.

 

[JLatham]

You might consider putting a comment in the target cells for the results of
your VBA calculations to explain how they were obtained, if you elect to go
with VBa. Add a little 'navigation' information so they can validate the
results. Maybe a comment something like:
=CustAGrossAdds_t * $100 *((1-PriceChange)^t)*12
CustAGrossAdd_t : from Sheet99!A44
PriceChage : from Sheet14!B73
t : constant on Sheet1!A5

Now some of those no doubt will be from tables and you could make mention of
how the individual items are chosen from the tables.

I think generally using 'native' worksheet functions is faster than doing
something in VBA and it's somewhat self-documenting. But as you said, user
friendliness is a factor also. And from working with auditors in the past,
auditor friendly is usually a must-have.

Having the actual formulas in a cell has the potentially added advantage of
allowing you to use the 'Formula Auditing' toolbar to assist in identifying
where the data in a formula is coming from if the auditor really wants to get
down to counting grains of sand vs just the beans. But before offering to
use it, you should become familiar with it, but it can be a great help in
showing where the numbers came from and where the number calculated is going
to further downstream.

 

[electronicmailmessage]

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.

 

[JLatham]

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.