Matrix structure of data

[Sandip]

Hi,

We have a budgeting process which will kick off in some time and would
like to know if the following is possible in excel. Let me explain the
existing structure prior to the output required. The following
structure is visible via a pivot table and hence the original data is
in the form of a database.


Product A Product B

SubProduct A1, SP A2 SP B1, SP B2, SP B3

Level 1
SubLevel1A
SubLevel1B

Level 2
Sublevel2A
Sublevel2B

Level 3
Sublevel3A
Sublevel3b
Sublevel3C

Grand Total

Hence the various row and columns are filled up by actual data for Year
2006. There are assumptions made for 2007 % growth for each product,
subproduct, levels and sublevels. Is it possible to derive an output
for 2007 numbers (either as a database or a table) which will take into
consideration the various growth percentages by row and columns. Please
note there are many more levels and product/subproduct. The above is
just a sample.

Hence a manager viewing his product (vertical view) sees the right
growth figures while the managers responsible for various levels see
their own growth number(horizontal view).

The matrix for 2007 has to be populated by combining the various
percentages in horizontal and vertical view to arrive at the new growth
numbers.

Is this possible via some macro or function..

Regards
Sandip.

 

[Tom Ogilvy]

Possibly do the projections on each row of the original data. Guess it
depends on how that data is laid out to make it meaningful.

if you lay it out vertically so there is only one number in each row with a
column header like amount, then you would just add a second number column
with a column header like Projected_Amount. Even it if isn't laid out that
way right now, you might be able to restructure it.