I'm a fairly experienced Excel-user but I've been pondering this problem for
days now so I hope anyone out there can help me. I'm from Holland so forgive
me for my English.

I work at the headquarters of a company that owns over 20 bookstores. Every
week I receive a large CSV-file with the weekly revenues(?). First thing I
do is to organize these into 10 productgroups, so my "Source-file" looks
like this:
Column A: Storename
Column B: Productgroup
Column C: Year
Column D: Weeknumber
Column E: Amount

In the weekly reports I make for the management, they want to see the weekly
revenues, compared with those of last year. No problem there; I've made a
pivot-table and graphs which do the job.

Here comes the problem:
Management has determined the budgets for this year for every store and
every productgroup. They also want to see in my reports the comparison
between the real revenue and the one budgetted. The weekly budget for this
year is calculated as follows:

For instanstance: the weekly budget for Store 1, Productgroup1, week 1 2005
is
(Week 1 2004 / Total 2004) * Budget 2005

It all seems very easy and in smaller files I've solved this problem with
Vertical Sum and VLOOKUP but when I try this on the real data, Excel keeps
getting stuck. I have over 200 rows a week, starting in 2003 and I guess
that's just too much to handle for my processor.

Does any know a "smarter" way to handle this?
Any help is much appreciated!

Bart Snel

You have some responses to your post in .excel.

Bart Snel wrote:

I'm a fairly experienced Excel-user but I've been pondering this problem for
days now so I hope anyone out there can help me. I'm from Holland so forgive
me for my English.

I work at the headquarters of a company that owns over 20 bookstores. Every
week I receive a large CSV-file with the weekly revenues(?). First thing I
do is to organize these into 10 productgroups, so my "Source-file" looks
like this:
Column A: Storename
Column B: Productgroup
Column C: Year
Column D: Weeknumber
Column E: Amount

In the weekly reports I make for the management, they want to see the weekly
revenues, compared with those of last year. No problem there; I've made a
pivot-table and graphs which do the job.

Here comes the problem:
Management has determined the budgets for this year for every store and
every productgroup. They also want to see in my reports the comparison
between the real revenue and the one budgetted. The weekly budget for this
year is calculated as follows:

For instanstance: the weekly budget for Store 1, Productgroup1, week 1 2005
is
(Week 1 2004 / Total 2004) * Budget 2005

It all seems very easy and in smaller files I've solved this problem with
Vertical Sum and VLOOKUP but when I try this on the real data, Excel keeps
getting stuck. I have over 200 rows a week, starting in 2003 and I guess
that's just too much to handle for my processor.

Does any know a "smarter" way to handle this?
Any help is much appreciated!

Bart Snel

--

Dave Peterson