Hello,
I am in the process of building a spreadsheet that is for the purpose of
displaying information for various financial ratios such as Gross Profit
Margin Ratio, Net Profit Margin Ratio, etc.... I have a worksheet named DATA
that contains the info for Net Income, Net Sales, Gross Profi, etc...Each
criteria is given a named range such as Net Sales is "NS", Gross Profit -
"GP", Net Income - "NI", etc.... I then have a second sheet named RATIOS that
obtains its data from the DATA worksheet using a formula containing the SUM
and INDIRECT functions. The layout of the RATIOS sheet is as such:
A B C D
E F
1 GPMR 2003 2004 2005
2 Company1 0.46 0.41 0.48
GP
3 Company2 1.63 1.85
NS
4
5 NPMR Company1 0.05 0.03 0.06 NI
6 Company2 0.06 0.47 0.38
NS
Column A contains the ratio name and Row 1 the years. The GP, NI, NS in
column F are the references used by the INDIRECT function to get the
appropriate data from the named ranges on the DATA sheet. My question is; How
can I make charts dynamically for this spreadsheet. For example; I am going
to enter the 2006 info on the DATA sheet for the required financial
information; when I add a column for 2006 on the RATIOS sheet and copy over
the formulas how can I have the charts for each ratio automatically update
when the formulas create the 2006 data? I would like to leave the layout of
the RATIOS spreadsheet as it is if possible. Any help, suggestions, or links
will be greatly appreciated. Thank you.
Dave Y
|
Similar Threads
