Question

Elliot Co. manufactures a product with seasonal variations in demand. The following budget data for this product has been collected for the upcoming year (2014).   In addition, predictions were also made for the first quarter of 2015.
													2014
				Quarter 1	Quarter 2	Quarter 3	Quarter 4
-Forecasted sales in units:				40,000	60,000	100,000	50,000
-Forecasted sales in units for the first quarter of 2015:								70,000
-Sales price per unit								$8.00
-Desired ending inventory of finished units as a percentage of next quarter sales:								30%
(Assume the same requirements relating to desired finished units ending inventory were in effect for 2013).
-Desired ending inventory of finished units for the first quarter of 2015:								24,000
-Yards of materials required to complete one unit of finished product:								5
-Desired ending inventory of materials as a percentage of next quarter production requrements (expressed in yards):								10%
(Assume the same requirements relating to desired ending materials inventory were in effect for 2013).
-Materials cost per yard								$0.80
1. Use the data above to prepare the Sales Budget. Enter titles, cell references, and formulas where indicated.
Sales Budget								Year 2015 Quarter
Title
For the Year Ended 2014
Quarter			1	2	3	4	Total	1
Sales			40,000	60,000	100,000	50,000	250,000	70,000
Sales price			$8.00	$8.00	$8.00	$8.00	$8.00
Total sales			$320,000	$480,000	$800,000	$400,000	$2,000,000
2. Use the data above to prepare the Production Budget. Enter titles, cell references, and formulas where indicated.
Production Budget								Year 2015 Quarter
Title
Title
Quarter			1	2	3	4	Total	1
Units ot be Sold			40,000	60,000	100,000	50,000	250,000	70,000
Ending Inventory			18,000	30,000	15,000	21,000	21,000	24,000
Total Needs			58,000	90,000	115,000	71,000	271,000	94,000
Less: Beginning Inventory			12,000	18,000	30,000	15,000	12,000	21,000
Units to be Produced			46,000	72,000	85,000	56,000	259,000	73,000
3. Use the data above to prepare the Materials Budget. Enter titles, cell references, and formulas where indicated.
Title								Year 2015 Quarter
Title
Title
Quarter			1	2	3	4	Total	1
Direct Material Budget				cell reference	cell reference	cell reference	Formula	cell reference
Budget Production			46,000	72,000	85,000	56,000	259,000	cell reference
Material Beeded for Production			7,200	8,500	5,600	7,300	Formula	formula
Add: Desired Ending Material Inventory			$ Formula	$ Formula	$ Formula	Formula	cell reference
Total Material Requirement			Formula	Formula	Formula	Formula	Formula
Less: Beginning Material Inventory			Formula	cell reference	cell reference	cell reference	cell reference
Materials to be Purchased			Formula	Formula	Formula	Formula	Formula
Cost Per Pound			$ cell reference	$ cell reference	$ cell reference	$ cell reference	$ cell reference
Total Cost of Materials			Formula	Formula	Formula	Formula	Formula

Answer 1

3) Materials Budget (Amounts in $)

Particulars	Qtr 1	Qtr 2	Qtr 3	Qtr 4	Total	Year 2015 Qtr 1
a) Budgeted production (in units)	46,000	72,000	85,000	56,000	259,000	73,000
b) Material required per unit	5 yards	5 yards	5 yards	5 yards	5 yards	5 yards
c) Material needed for production (a*b) (in yards)	230,000	360,000	425,000	280,000	1,295,000	365,000
d) Desired Ending Material Inventory (10% of next Qtr material needs)	36,000	42,500	28,000	36,500	36,500
e) Total Material Requirement (c+d)	266,000	402,500	453,000	316,500	1,331,500
f) Beginning Material Inventory (10% of current material needs)	23,000	36,000	42,500	28,000	23,000
g) Materials to be Purchased (e-f)	243,000	366,500	410,500	288,500	1,308,500
h) Cost per yard	$0.80	$0.80	$0.80	$0.80	$0.80
i) Total Cost of Materials (g*h)	194,400	293,200	328,400	230,800	1,046,800