Finance Query for a Newby (2nd time)

[Vic]

A discussion with one participant convinced me that I need to put in a
little more detail on my problem. Here it is



I have a financial forecast DB with 12 identically structured tables, one
corresponding to each month. Each table contains 28 records (rows),
corresponding to a different product family. There are 9 columns; each
corresponds to a sales source.



1) I would like to create a total monthly forecast by product family (sum
across each row) and a total monthly forecast by sales source (sum down each
column). Next, I would like to sum three tables together to get a quarterly
forecast by product family and a quarterly forecast by sales source.



If this proves successful, the complexity will grow but will be manageable.
Each monthly table will actually have multiple siblings: forecast
transactions, forecast revenue, actual transactions, and actual revenue. So
rather than 12 monthly tables, there will really be 48 plus a 49th --
product name table.

2) I can get IT to create the actual transaction and actual revenue tables
each month and just down load them monthly. That will have a major impact on
my workload.

3) I will need to compare actuals to forecasts to create deviation analyses.

4) I will need to use revenue data and transaction data to get revenue per
unit.

I was hoping to stay with the structure of the data because it fits the way
our company operates.

Ideas for solving the issue in 1)?

Vic

 

[Steve Schapel]

Vic,

In designing a database, "the way our company operates" has to be
largely subservient to database design principles. Based on what you
have said so far, I would strongly recommend that you do not pursue your
current plan... it will inevitably become unwieldy.

But my main question is this... Why are you using Access? On the face
of it, this appears to primarily be spreadsheet functionality, so why
not use a spreadsheet programme?

 

[Vic]

Steve,

Thank you for looking at this. I'm attempting to assist our Sales Analyst
who has to create elaborate spreadsheets only to loose all of her work when
the quarterly plan changes or when there is a forecast revision within the
quarter. Her massive linked spreadsheets are extremely unwieldy and prone to
errors.

The advantage for her with Access is that she could save and slightly modify
queries. The structure and methodology that Access introduces would show up
in the result. All of this would be a huge savings in time for her, and I
would be able to receive timely product analyses on my product families. So
this is win-win for both the Sales Analyst and me.

Can you provide some help in answering 1)?

Thank you in advance,

Vic