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:
- 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
|
|
|
|
|
|
- Present the normalised data model (Entity Relationship) diagram in a screenshot of MS Access.
- 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
|
|
|
|
|
|
|
|
|