Question

Enter Your Name Here BASE DATA: Foundation Data for Solving questions 1, 2 and 3: Budgeted Direct Materials Cost Per UNIT 15.00 10.00 Budgeted Direct Manufacturing Labor Per UNIT Budgeted Manufacturing Overhead Per UNIT 5.00 IN ADDITION TO ABOVE USE THE FOLLOWING: This is Next Year's Budgeted Data for XYZ Budgeted Sales Price Per UNIT 20.00 Budgeted Sales - UNITS 15,000 Units Target Ending Finished Goods Inventory in UNITS 2,500 Units Target Beginning Finished Goods Inventory in UNITS 1,850 Units Target Ending Direct Materials Inventory in UNITS 8,500 Units Target Beginning Direct Materials Inventory in UNITS 6,500 Units Note: It takes one unit of direct materials to make one production unit. WITH THE ABOVE DATA SOLVE THE FOLLOWING QUESTIONS REGARDING YOUR PRODUCT 1) How many units should be produced next year? Record your answer, using excel formulas, here

Answer 1

Please find below table useful to compute desired results: -

B 1) Budgeted Production 2 Budgeted Sales Units 15000 3 Target Ending Finished Goods Inventory 2500 4 Beg. Finished Goods inventory -1850 5 Units to be produced =SUM(B2:B4) 6 7. 2) Dollar amount budgeted for direct material purchased 8 Budgeted Material consumption during the next year 1=B5 9 Target Ending Direct Materials Inventory 18500 10 Target Beg. Direct Materials Inventory -6500 11 Budgeted Direct Material =SUM(B8:B10) 12 Direct Material Cost per Unit 15 13 Dollar amount of direct material Purchases =B11*B12 14 15 3) Budgeted Revenues for next Year 16 Budgeted Sales(Units) 15000 17 Budgeted Selling Price 18 Budgeted Revenue =B16 B17 19 20 4) Budgeted Cash Collections for the month of May: 21 May Sales Collections =15000*50% 22 April Sales Collections =7500*30% 23 March Sales Collections =4000*15% 24 Total Cash Collections I=SUM(B21-B23)

End results would be as follows: -

B 1 2 3 4 5 1) Budgeted Production Budgeted Sales Units Target Ending Finished Goods Inventory Beg. Finished Goods inventory Units to be produced 15,000 2,500 (1,850) 15,650 6 7 2) Dollar amount budgeted for direct material purchased 8 Budgeted Material consumption during the next year 15,650 9 Target Ending Direct Materials Inventory 8,500 10 Target Beg. Direct Materials Inventory (6,500) 11 Budgeted Direct Material 17,650 12 Direct Material Cost per Unit 15 13 Dollar amount of direct material Purchases 264,750 14 3) Budgeted Revenues for next Year 16 Budgeted Sales(Units) 15,000 17 Budgeted Selling Price 20 18 Budgeted Revenue 300,000 15 19 20 4 ) Budgeted Cash Collections for the month of May 21 May Sales Collections 7,500 22 April Sales Collections 2,250 23 March Sales Collections 24 Total Cash Collections 10.350 600