Addins that contain worksheet functions

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

As you know, Excel has many worksheet functions that you can use in formulas. Chances are that you've needed a particular function, only to find that it doesn't exist. The solution? Create your own by using VBA. Custom worksheet functions can often simplify your formulas and make your spreadsheet easier to maintain.

By a single-block budget, I mean a spreadsheet (not necessarily a budget model) that essentially consists of one block of cells. The top row might contain names that correspond to time (months, quarters, or years), and the left column usually contains categories of some type. Typically, the bottom row and right column contain formulas that add the numbers together. There may or may not be formulas that compute subtotals within the block.

This is a very common type of spreadsheet. In most cases, simple single-block budget models are not good candidates for applications because they are simple to begin with, but there are exceptions. For example, you might consider converting such a spreadsheet into an application if the model is an unwieldy 3-D spreadsheet, needs to include consolidations from other files, or will be used by departmental managers who might not understand spreadsheets.

Many consider the what-if model category to be the epitome of spreadsheets at their best. The ability to instantly recalculate thousands of formulas makes spreadsheet software the ideal tool for financial modeling and other models that depend on the values of several variables. If you think about it, just about any spreadsheet that contains formulas is a what-if model (which are often distributed as templates). Changing the value of a cell used in a formula is akin to asking "what if ...?" My view of this category, however, is a bit more sophisticated. It includes spreadsheets designed exclusively for systematically analyzing the effects of various inputs.

What-if models often benefit from additional work to make them more user-friendly, especially if the model will be used for a lengthy period of time. Creating a good user interface on an application can make it very easy for anyone to use, including computer-illiterates. As an example, you might create an interface that lets users provide names for various sets of assumptions and then lets them instantly view the results of a selected scenario and create a perfectly formatted summary chart with the click of a button.

Was this article helpful?

0 0

Post a comment