Create a query to shoe each activity cost and total cost

Question

Job Costing system - case study

XXY   is a company that makes and sells frozen meats to locals. The meats are categorised by ingredients: beef, lamb, fish and pork.

Product Code	Ingredient
1	Beef
2	Lamb
3	Fish
4	Pork

 

XYZ meats are made to orders', and each order is treated as a job. The order table recorded by the sales, is below

Job Order Number	Product Code	Qty in unit	Job order selling price	Order Date
100	3	30	$50	01/07/2007
101	2 4 3	30 20 20	$60	01/07/2007
102	1 2	20 10	$30	01/07/2007
103	3 4 1	30 20 20	$60	01/07/2007

 

Production is arranged in batches, with each producing 50 meats. The production table, which is recorded by the supervisor, is show:

 

Production Batch Number	Job Order Number	Production Date	Beef	Lamb	Fish	Pork
1000	100 101	10/07/2007		10	30	10
1001	101	10/07/2007		20	20	10
1002	102 103	10/07/2007	20	10	20
1003	103	10/07/2007	20		10	20

Each batch involves 5 production activities, which comprise the cost of overhead: Case preparation, ingredients preparation, meat assembly, packaging and freezing. For overhead allocation, the number of hours spend on each process is recorded as below:

Production Batch Number	Case preparation	Ingredient preparation	Meat assembly	Packaging	Freeezing
1000	0.14	0.42	0.06	0.01	0.25
1001	0.16	0.42	0.07	0.01	0.25
1002	0.15	0.45	0.06	0.02	0.25
1003	0.12	0.40	0.08	0.01	0.25

 

XYZ applies a uniform overhead rate of $20 per hour to all the production activities.

XYZ uses an MS access database for sharing order, production and other information for job costing. As a cost accountant, you are required to:

1. identify entities (tables) and attributes for a normalised data model. Show their primary keys and foreign keys, and present your indenfitication in a table with heading:

Table	Attribute name	Primary Key	Foreign Key

 

1. Present the normalised data model (Entity Relationship) diagram in a screenshot of MS Access.
2. Create a query to shoe each activity cost and total cost for each of the four orders. Present the query in screenshots of both design view and table view in MS Access"

Order Number	Case preparation cost	Ingredient preparation cost	Meat assembly cost	Packaging cost	Freezing Cost	Total Cost