Data Structure Best Practice

[ellistyle]

Interested in your thoughts on this.
On a monthly basis I aggregate forecast data for a rolling 12 month period. I am trying to arrive at a data format suits this activity. For simplicity's sake, lets say there is static data (e.g. ustomer name, market, product) and dynamic data (e.g. monthly quantity and price)

Which strategy would your recommend (or perhaps recommend an alternative):

1. Add a new set of columns for each period. Month and year.
   • The sheet becomes very wide, but none of the static data is duplicated.
   • The column names will always have to be added to capture the dynamic data
2. Keep a consistent column structure, and add a year column.
   • The sheets gets very long
   • Static data is duplicated

I am inclined towards #2 so as to keep a consistent column set forever. But the duplicated static data will create a very large file very quickly.

What do you think?

 

[vletm]

ellistyle
For me, easier to use if
... add new set of row for each period ...
but ...
all depends
what do You really have there ?
... how many items of data per period?
and so on ...

 

[ellistyle]

In each month, I would likely have 5k rows of data. There are about 15 columns of static data. The remaining columns contain the new monthly input. I didn't mention before that I am analyzing variation between forecast periods (or between the current forecast period and a budget). I would like to append each month's new input into one file. For that reason, I'd like to keep columns consistent (instead of adding a new column each rolling period).

 

[vletm]

Please, sample file ... no need to have 5k rows ... 500 is enough!
with information,
what You really would like to get / to do...
sample results are bonus ...

 

[ellistyle]

I didn't give you 500 lines (It would take awhile to anonymize/cleanse my current data for public consumption).

The 2 tabs show the 2 strategies--both with the same data input across 2 forecast periods.

In the add columns method, as the forecast period rolls, I would need to add columns to capture that month's new data.

In the add rows method, there would be 2x as many rows, but always the same column set.

• I need a means to compare variations between forecast periods.
• I would like to capture all of the data in one sheet and pivot from there.
• All previous input is kept as a reference

Thanks for your help. I appreciate hearing how others have likely handled a similar situation across periods of reporting.

 

[Hui]

2
But I would also question why you need 15 columns of static data
You should only need 4
Date
Cost code
Description
Actual/Budget

Yes sometimes you can have sub-categories of the cost code

 

[ellistyle]

2
But I would also question why you need 15 columns of static data
You should only need 4
Date
Cost code
Description
Actual/Budget

Yes sometimes you can have sub-categories of the cost code

Do you mean let the static data drive from a single Customer code? You are right--the static data can be reduced. I am not really concerned about the structure of that.

The advice I am seeking is really about the most efficient way to capture the data across years.

I think option number 2 it is.

Thanks!

 

[Hui]

2 gives you so much more flexibility

 

[vletm]

ellistyle - as I tried to write:
with information,
what You really would like to get / to do...
sample results are bonus ...
> Two part numbers ..
> If You would like to use Pivot, then 'data' should be as it needs - with dates.
> I didn't get idea with those samples, what do You would like to get, sorry.