2 3 Data 4 5 Budgeted unit sales Year 2 Quarter Year 3 Quarter 1 2 3 4 1 2 40,000 60,000 100,000 50,000 70,000 80,000 6 7 Selling price per unit $8 per unit 8 Accounts receivable, beginning balance $65,000 9 Sales collected in the quarter sales are made 75% 10 Sales collected in the quarter after sales are made 11 Desired ending finished goods inventory is 25% 30% of the budgeted unit sales of the next quarter 12 Finished goods inventory, beginning 12,000 units 13 Raw materials required to produce one unit 14 Desired ending inventory of raw materials is Raw material costs 15 Raw materials inventory, beginning 16 17 Raw materials purchases are paid 18 and 19 Accounts payable for raw materials, beginning balance 20 Direct labor hours 21 Direct labor rate 22 23 Manufacturing overhead rate Variable selling and adminstrative cost 24 Fixed selling and adminstrative cost 5 pounds 10% of the next quarter's production needs 23,000 pounds $0.80 per pound 60% in the quarter the purchases are made 40% in the quarter following purchase $81,500 0.10 hour per unit $11 per hour $2 per direct labor hour $0.80 per unit $20,000 per quarter 25 26 27 28 29 Enter a formula into each of the cells marked with a ? below 30 31 32 Construct the sales budget 33 34 Budgeted unit sales 35 Selling price per unit 36 Total sales 37 38 Construct the schedule of expected cash collections 39 40 Accounts receivable, beginning balance 41 First-quarter sales 42 Second-quarter sales 43 Third-quarter sales 44 Fourth-quarter sales 45 Total cash collections 46 47 Construct the production budget 48 49 Budgeted unit sales 50 Add desired finished goods inventory 51 Total needs 52 Less beginning inventory 53 Required production 54 55 Construct the raw materials purchases budget 56 57 Required production (units) 58 Raw materials required to produce one unit 59 Production needs (pounds) 60 Add desired ending inventory of raw materials (pounds) 61 Total needs (pounds) 62 Less beginning inventory of raw materials (pounds) 63 Raw materials to be purchased 64 Cost of raw materials per pound 65 Cost of raw materials to be purchased 66 Year 2 Quarter Year 3 Quarter 1 2 3 4 1 2 40,000 $8 $320,000 60,000 $8 $480,000 100,000 50,000 $8 $8 70,000 $8 80,000 $8 $800,000 $400,000 $560,000 $640,000 Year 2 Quarter 1 2 3 4 Year $ 65,000 240,000 $ 65,000 80,000 $ 320,000 360,000 $ 120,000 480,000 600,000 $200,000 $ 800,000 300,000 $ 300,000 $ 305,000 $ 440,000 $ 720,000 $ 500,000 $ 1,965,000 Year 2 Quarter Year 3 Quarter 1 2 3 4 Year 2 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? Year 2 Quarter Year 3 Quarter 1 2 3 4 Year ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 66 67 Construct the schedule of expected cash payments 68 69 Accounts payable, beginning balance 70 First-quarter purchases 71 Second-quarter purchases 72 Third-quarter purchases 73 Fourth-quarter purchases 74 Total cash disbursements 75 76 Construct the direct labor budget 77 78 Units of production 79 Direct labor hour per unit 80 Labor hours required 81 Direct labor rate 82 Total direct labor cost 83 84 Construct the manufacuring overhead budget 85 86 Budgeted direct labor hours 87 Manufacturing overhead rate 88 Total manufacturing overhead cost 89 90 Construct the selling and adminstrative budget 91 92 Budgeted sales 93 Variable selling and adminstrative cost rate 94 Variable selling and adminstrative cost 95 Fixed selling and adminstrative cost 96 Total selling and adminstrative cost 97 ? ? ? Year 2 Quarter 2 3 4 Year ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? Year 2 Quarter 1 2 3 4 Year ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? Year 2 Quarter 1 2 3 4 Year ? ? ? ? ? ? ? ? ? ? ? ? ? ? Year 2 Quarter 1 2 3 4 Year