Question

А Chapter 8: Applvina Excel Data Year 2 Quarter Year 3 Quarter 4 2 60,000 Budgeted unit sales 40,000 100,000 50,000 70,000 80,000 • Selling price per unit • Accounts receivable, beginning balance • Sales collected in the quarter sales are made • Sales collected in the quarter after sales are made • Desired ending finished goods inventory is • Finished goods inventory, beginning • Raw materials required to produce one unit • Desired ending inventory of raw materials is • Raw materials inventory, beginning • Raw material costs • Raw materials purchases are paid and • Accounts payable for raw materials, beginning balance $8 per unit $65,000 75% 25% 30% of the budgeted unit sales of the next quarter 12,000 units 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 Enter a formula into each of the cells marked with a ? below Review Problem: Budget Schedules Construct the sales budget Year 2 Quarter Year 3 Quarter 2 Budgeted unit sales Selling price per unit Total sales Construct the schedule of expected cash collections Year 2 Quarter Year ? ? ? Accounts receivable, beginning balance First-quarter sales Second-quarter sales Third-quarter sales Fourth-quarter sales Total cash collections Construct the production budget Year 2 Quarter Year Year 3 Quarter 12 ? Budgeted unit sales Add desired finished goods inventory Total needs Less beginning inventory Required production COCO Construct the raw materials purchases budget Year 2 Quarter Year 3 Quarter Year Required production (units) Raw materials required to produce one unit Production needs (pounds) Add desired ending inventory of raw materials (pounds) Total needs (pounds) Less beginning inventory of raw materials (pounds) Raw materials to be purchased Cost of raw materials per pound Cost of raw materials to be purchased Construct the schedule of expected cash payments Year 2 Quarter Year ? . .. Accounts payable, beginning balance First-quarter purchases Second-quarter purchases Third-quarter purchases Fourth-quarter purchases Total cash disbursements

Answer 1

Solutions:

Particulars Budgeted unit sales selling price per unit Total sales Schedule of expected cash collection Quarter 1 Quarter 2 Quarter 3 40000 60000 1000000 $8.00 $8.00 $8.00 $320,000.00 $ 480,000.00 $800,000.00 Quarter 4 50000 $8.00 $400,000.00 Year 3 Quarer 1 70000 $8.00 $560,000.00 Year 3 Quarer 2 80000 $8.00 $640,000.00 8 9. 10 12 Quarter 4 | 13 14 Schedule of expected cash collection Particulars Quarter 1 Quarter 2 Quarter 3 Accounts receivables beginning $65,000.00 Cash received for Q1 Sale (Units * $8*% collection) $240,000.00 $80,000.00 Cash received for Q2 Sale $360,000.00 $120,000.00 Cash received for Q3 Sale $600,000.00 Cash received for Q4 Sale Budgeted Cash Collection $305,000.00 $440,000.00 $720,000.00 Year $65,000.00 $320,000.00 $480,000.00 $800,000.00 $300,000.00 $1,965,000.00 16 17 18 $200,000.00 $300,000.00 $500,000.00 19 b. 20 21 Particulars Budgeted sales unit Add: ending inventory (30% of next quarter sales) Less: Beginning inventory Production Budget Quarter 1 Quarter 2 Quarter 3 40000 60000 100000 18000 30000 15000 12000 18000 30000 Quarter 4 | 50000 21000 15000 Year Quarter 1, next year Quarter 2, next year 250000 70000 80000 21000 24000 12000 21000 23 24 25 26 27 28 29 30 Estimated production unit 46000 72000 85000 560001 259000 73000 2) Sheet393 (3) Sheet708 Sheet253 (2) Sheet710 Sheet711 Sheet574 (5) Sheet665 III 0 s

DE 30 31 a Budgeted Cost of raw material purchases Particulars Quarter 1 Quarter 2 Quarter 3 Budgeted Production units 46000 72000 85000 Raw material per unit (In Pounds) Total requirement of raw materials 230000 360000 425000 Add: Desired ending inventory (10% of next quarter production needs) 360001 425000 28000 Less: Beginning inventory 230001 36000 42500 Quarter 4 Year Quarter 1, next year 56000 259000 73000 55 280000 1295000 365000 5 36500 28000 36500 23000 38 Budgeted purchase units of raw material (In Pounds) Raw material cost per pound 243000 $0.80 366500 $0.80 410500 $0.80 288500 $0.80 1308500 $0.80 40 41 Budgeted cost of purchases $194,400.00 $293,200.00 $328,400.00 $230,800.00 $1,046,800.00 46 Quarter 4 47 48 49 Particulars Accounts Payable, Beginning Q1 Purchases Q2 Purchases Q3 Purchases C4 Purchases Total payments Schedule of expected cash disbursement for merchandise purchases | Quarter 1 Quarter 2 Quarter 3 $81,500.00 $116,640.00 $77,760.00 $175,920.00 $117,280.00 $197,040.00 Year $81,500.00 $194,400.00 $293,200.00 $328,400.00 $138,480.00 $1,035,980.00 50 51 52 $131,360.00 $138,480.00 $269,840.00 $198,140.00 $253,680.00 $314,320.00 53 54 2) Sheet393 (3) Sheet708 Sheet253 (2) Sheet710 Sheet711 Sheet574 (5) Sheet665