Dynamic Naming for Financial Statements

McGu

Board Regular
Joined
Oct 10, 2006
Messages
135
Okay so here's another question. Hopefully I'm not limited to one question each day! :biggrin:

Below is an example of my financial spreadsheet with historical information. I would like to use some type of dynamic naming system that will allow me to analyze the data more effeciently and the ability to understand and verify my formulas. For example, I would like to name the line EBIT EBIT02, EBIT03, EBIT04 and so on relating to the year. Rather than having to select each and every one each time I open a new spread to perform the same analysis. At the same time, I would like to identify EBITDA as EBITDA02 which is equal to EBIT02 + DepAm02. I'm thinking that I'll need a separate sheet for this. Thoughts? Thanks.
Financial Model V.9.xls
ABCDEFGH
1IncomeStatementforXYZCompany
2ThousandsofDollars
3Historical
42002(05)2003(05)2004(05)2005
5Sales$1,235100.0%$1,252100.0%$1,300100.0%$1,334
6CostofSales$67955.0%$65952.6%$68152.4%$667
7GrossOperatingIncome$55645.0%$59347.4%$61947.6%$667
8
9Selling,General&Admn.Expenses$34027.5%$34927.9%$35127.0%$373
10DepreciationandAmortization$483.8%$524.2%$564.3%$75
11OtherNet(Income)/Expenses($12)-1.0%($8)-0.6%($7)-0.5%($8)
12EBIT$18014.6%$20016.0%$21916.8%$227
Model
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Hi:

Have you considered naming the ranges of interest and working with the list of range names?

plettieri
 
Upvote 0
Hi McGu

Naming ranges:

select the cell(s) of interest
Insert (on the toolbar)
Define
Then name your range (ie EBIT06...)

OK

repeat as necessary for you list of ranges desired

The ranges can be pulled down for the the name box located on the lower left portion of your toolbars. These range name can then be used as you wish in formulas.

HTH

plettieri
 
Upvote 0
Thanks, I understand how to name cells/ranges that way, but I am looking for a more dynamic way to do it. For example one that would allow for the dynamic name change relative to the year because my historical information will continue to change even though my model will only allow for the previous 5 years of historcial data. I don't want my formula to say EBIT02 + DepAm02 = EBITDA03 when I am looking at 2003. It needs to read EBIT03 + DepAm03 = EBITDA03 and I don't want to have to go into each and every cell and update the names as the years pass, or I decide to increase the number of historical periods. Thanks.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top