Elliot Co. manufactures a product with seasonal variations in demand. The follow

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 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

Explanation / Answer

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

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.