Question

Four grams of musk oil are required for each bottle of Mink Caress, a very popular perfume made by a small company in western Siberia. The cost of the musk oil is $2.10 per gram. Budgeted production of Mink Caress is given below by quarters for Year 2 and for the first quarter of Year 3: Musk oil has become so popular as a perfume ingredient that it has become necessary to carry large inventories as a precaution against stock-outs. For this reason, the inventory of musk oil at the end of a quarter must be equal to 20% of the following quarter's production needs. Some 78, 400 grams of musk oil will be on hand to start the first quarter of Year 2. Required: Prepare a direct materials budget for musk oil, by quarter and in total, for Year 2.

Answer 1

A1	B	C	D	E	F	G	H	I	J	K
2		Inventory of Gram Oil in the beginning of Year2 first quarter is 78,400.
3		Ending Inventory required is	20%	of next quarter requirement
4		Total materials needed should be calculated by adding the production requirement for the quarter with the ending inventory required for next quarter production.
5		The amount needs to be purchased is calculated by substracting beginning inventory from quantity of materials needed.
6				Year 2				Year3
7				Quarter				Quarter
8				First	Second	Third	Fourth	First
9		Required Production in units of finished goods		98000	128000	188000	138000	108000
10		Units of raw materials needed per unit of finished goods		4	4	4	4	4
11		Units of raw materials needed to meet production		392000	512000	752000	552000	432000
12		Required Ending Inventory		102400	150400	110400	86400
13		Total units of materials needed		494400	662400	862400	638400
14		Beginning Inventory		78400	102400	150400	110400
15		Units of raw materials to be purchased		416000	560000	712000	528000
16		Unit cost of raw materials		$2.10	$2.10	$2.10	$2.10
17		Cost of raw materials to purchased		$873,600.00	$1,176,000.00	$1,495,200.00	$1,108,800.00
18

Formula sheet

A1	B	C	D	E	F	G	H	I	J	K
2		Inventory of Gram Oil in the beginning of Year2 first quarter is 78,400.
3		Ending Inventory required is	0.2	of next quarter requirement
4		Total materials needed should be caculated by adding the production requirement for the quarter with the ending invetory required for next quarter production.
5		The amount need to be purchased is calculated by substracting beginning inventory from quanitity of materials needed.
6				Year 2				Year3
7				Quarter				Quarter
8				First	Second	Third	Fourth	First
9		Required Production in units of finished goods		98000	128000	188000	138000	108000
10		Units of raw materials needed per unit of finished goods		4	4	4	4	4
11		Units of raw materials needed to meet production		=E9*E10	=F9*F10	=G9*G10	=H9*H10	=I9*I10
12		Required Ending Inventory		=F11*$D$3	=G11*$D$3	=H11*$D$3	=I11*$D$3
13		Total units of materials needed		=E11+E12	=F11+F12	=G11+G12	=H11+H12
14		Beginning Inventory		=78400	=E12	=F12	=G12
15		Units of raw materials to be purchased		=E13-E14	=F13-F14	=G13-G14	=H13-H14
16		Unit cost of raw materials		2.1	2.1	2.1	2.1
17		Cost of raw materials to purchased		=E15*E16	=F15*F16	=G15*G16	=H15*H16
18