NameBadgeFinal

Excel uses several Names. I’ll take a look at a few beginning with Named Formulas. When developing budgets and forecasts, it is convenient to create an annualized amount for various accounts and spread the amount over time in your model whether it be 52 weeks, 12 months, 360 days, whatever your model calls for – generally driven by your domain or sphere of activity.

Named Formulas

Spread1

I have entered a name for my formula in $E$3 and I entered some random values in $F$3:$Q$3 that SUM to 1.

Spread2F

I highlighted the name that I will use for the Named Formula and the 12 random values that I created.

Spread3

I hit [Ctrl]+[Shift]+[F3] on my keyboard and the Create Names From Selection Dialog popped up. The Name for my Named Formula is in the left-most column, so I accepted the default, Create names from values in the: Left Column.

Spread4

I entered the random value of 98,7654.32 in $D$5 and entered the formula: =$D$5*MySpread in $F$5:$Q$5 below my spread technique. You can see that the 98K is allocated properly to each month based on the percentage I entered for each month in the spread technique – that my friends is wicked cool!

Imagine, if this were a 52 week model, I just went from 52 entries to 1. Additionally, I can go back and change my spread technique anytime and any formulas that use that Named Formula will calculate new values – huge time saver!

Business Case

In reality, I would not have Named Formulas on the same worksheet with inputs and analysis – so I’ll separate them.

Spread5

I moved the Named Formula to the Formula Tab. I left the inputs and spread analysis on the Analysis Tab.

Spread7

The spread is working correctly. I added a formula to sum up all cells in the spread less the input value to make sure 100% of the input value has been spread. Looking good!

Storing Fomulas, Constants, Etc…

Recall, I started my Named Formula on cell $E$3. In reality, I normally start such as items in $A of $B, I’ll move the Named Formula to $B.

Spread8

That’s better – but look what happened to my spread:

Spread9

Since the Named Formula and the Analysis are now starting in different Columns, the Analysis formula is not working correctly. I need a better formula that is more flexible and dynamic that can be used anywhere in the model and not return #VALUE! errors.

The INDEX() and COLUMNS() Functions

Spread10

In my new formula, I begin by using the INDEX() Function. As you see in the image, the INDEX() function may have up to 3 arguments,

  1. Array : I’m using the Named Formula as the array
  2. Row Number : I’m using 0 since the Named Formula is really a Vector Array with 1 dimension
  3. Column Number : I’m using the COLUMNS() Function to count the number of Columns I want and passing that number to the function.

Note in the COlUMNS() Function I am using COLUMNS($F1:F1) this will return 1. As I continue to copy the formula to the right the F1 will update to G1, H1 and so forth thus returning 2 and 3 and so forth respectively.

Spread11

I copied my formula to all cells in my Analysis worksheet and everything ties out once again.

Many Named Formulas

Generally, a budget or forecast model is going to need many different Named Formulas to handle many different spreads. Sales, Revenues and Expenses may, and most likely will, behave in many different ways. I may have a depreciation expense that is straight-line for 5 years at the same time that I am opening or closing for the Fall Semester at a university.

You may also use this strategy for quickly changing slight variations of the same spread technique to tweak timing of expenses and the amount that is allocated to each time period. For example, assume you setup 12 different spreads to allocate expenses related to some investment. All 12 may (should) tie-out to allocate 100% of the expense, they just allocate the expense a little bit differently to different time periods.

Spread12

I quickly added 11 more Named Formulas to my workbook. These names have no purpose and are demonstrative only. In reality, your Named Formulas should be well planned and self-documenting – describing to the reader precisely what the intent of the Formula is. Note in the scrren shot that the values in each of the Named Formulas are the same. I just copied down the original values from the first Named Formula.

Spread12

Spread13

I highlighted the list of all of the Named Formulas and named the list, “lstNamedFormulas” because I am creative like that. Normally, I would make this a dynamic Named Range, but that is another topic for another day.

Spread14

I selected a cell on my Analysis worksheet and added some Data Validation to allow only items from the Named Range I just created.

Spread15

Spread16

I updated my Formula one last time to use the INDIRECT() Function to look at the value that the user selected from the Data Validation List and return the name as the Array back to the INDEX() Function. I’m no fan of the INDIRECT() Function and I use it as sparingly as possible – however, it is appropriate in this context.

Tidy Up

That’s it for today. What are your thoughts on Named Formulas? Do you use them in your daily work for other purposes? Do you abuse the INDIRECT() Function? Let us know.

, , ,