Too much to handle for Excel?

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

Bart,

Even a small processor should gobble it up.

Are you able to send me a workbook, and we could work on it together?

--
HTH

Bob Phillips

"Bart Snel" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

I'd be very much obliged.
The mail I sent you just now didn't arrive, so please sent me a message so I
can reply:
excel at bartsnel.nl

Thanx!

Bart

"Bob Phillips" <[email protected]> schreef in bericht
news:[email protected]...
> Bart,
>
> Even a small processor should gobble it up.
>
> Are you able to send me a workbook, and we could work on it together?
>
> --
> HTH
>
> Bob Phillips
>
> "Bart Snel" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>
>

I'm bob dot phillips at tiscali dot co dot uk

do the obvious

--
HTH

Bob Phillips

"Bart Snel" <[email protected]> wrote in message
news:[email protected]...
> I'd be very much obliged.
> The mail I sent you just now didn't arrive, so please sent me a message so
I
> can reply:
> excel at bartsnel.nl
>
> Thanx!
>
> Bart
>
> "Bob Phillips" <[email protected]> schreef in bericht
> news:[email protected]...
> > Bart,
> >
> > Even a small processor should gobble it up.
> >
> > Are you able to send me a workbook, and we could work on it together?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Bart Snel" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >>
> >
> >
>
>