I need to start a Microsoft Access database for the following costing problem
I have a "product" view (Product A, Product B, Product C..which has to
be extensible) and a "activity" view (Activity A, Activity B... which
also must be extensible
A product can have any of the activities and activities may be shared
across different products as shown below
Activity A Activity B Activity C Activity D
Product A X
Product B X
Product C
As you can see some Activities are not applicable to some product
I have the volumes for the products (A,B,C) which I have to allocate
based on a matrix every month
Volume Matrix
--------------
Product A 100% 60% 90% 0%
Product B 100% 40% 0% 40%
Product C 100% 70% 90% 100%
i.e 100% of Product A's volume go through Activity A but only 60%
through Activity B
Note that this has to be extensible as I should be able to add a
product or a activity
Also, I have my expenses month on month which is allocated to activities
Expense Matrix
-------------
Activity A 5%
Activity B 65%
Activity C 20%
Activity D 10%
My data entry will consist of the following every month
1. Absolute volumes for each product
2. Total Expenses
So let us say for Jun05 my volumes are as follows
Product A 1000
Product B 2000
Product C 3000
Volume for Activities are
volume for Actvity A = 100% of Product A + 100% of Product B+ 100% of Product C
= 1000+2000+3000=6000
Volume for Actvity B= 0.6*1000+0.4*2000+0.7*3000=600+800+2100 = 3500
Volume for Activity C = 0.9*1000+0*2000+0.9*3000 = 900+2700 = 3600
Volume for Activity D = 0*1000+ 0.4*2000+1*3000= 500+3000= 3500
Volumes Jun 05
--------------
Activity A 6000
Activity B 3500
Activity C 3600
ACtivity D 3500
and my total expenses are $100000
Therefore from my expense matrix
Expenses Jun 05
---------------
Activity A 5000
Activity B 65000
Activity C 20000
Activity D 10000
With this kind of data, I must be able to calculate cost month on
month via queries for a particular period. Eg A report
shown below
Cost for Product A = Unit Cost for Activity A to C (No D) = 5000/6000+
65000/3500+20000/3600 = 0.83 + 18.57 +5.56 = 24.96
So essentially I can generate reports like one below
Cost Jan 05 Feb 05 Mar 05........
Product A 10 15 12
Product B 10 22 16
.....
I am stuck at how to design the database to accomplish the above. Kindly help me |
