Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba output: formulas or values
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba output: formulas or values
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. "sloth" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba output: formulas or values
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba output: formulas or values
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to convert list of formulas in text format to output value | Excel Discussion (Misc queries) | |||
Formatting text output within formulas | Excel Worksheet Functions | |||
Biff - SPSS Output Exported as Values and Not Image | Excel Discussion (Misc queries) | |||
Searching a date range to output values according to a constant. | Excel Discussion (Misc queries) | |||
Need to output the dependency tree for formulas | Excel Discussion (Misc queries) |