4 A B D E F G . 1 J Budgeting: Applying Excel Data 1 2 3 4 5 6 7 Quarter 1 50,000 Year 2 Quarter 2 Quarter 3 75,000 35,000 Quarter 4 90,000 Year 3 Quarter 1 Quarter 2 55,000 85,000 Budgeted unit sales 8 9 10 11 12 13 14 . 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 $20 $95,000 70% 30% 35% of the budgeted unit sales of the next quarter 8,000 units 3 pounds 40% of the next quarter's production needs 25,000 pounds $1.25 per pound 80% in the quarter the purchases are made 20% in the quarter following purchase $65,000 . 15 16 17 18 19 20 O-O Enter a formula into the cells below to complete the following budgets: Sales Budget 21 22 23 24 25 26 27 28 Year 2 Quarter 2 Quarter 3 Year 3 Quarter 1 Quarter 2 Quarter 1 Quarter 4 Year 2 Total Budgeted unit sales Selling price per unit Total sales 29 Schedule of Expected Cash Collections Budget 30 31 Year 2 Quarter 2 Quarter 3 Quarter 1 Quarter 4 Year 2 Total 32 33 34 Beginning balance accounts receivable First-quarter sales Second-quarter sales Third-quarter sales Fourth-quarter sales Total cash collections 35 36 37 38 39 Production Budget Year 2 Quarter 2 Quarter 3 Year 3 Quarter 4 Year 2 Total Quarter 1 Quarter 2 Quarter 1 40 41 42 43 44 45 Budgeted unit sales Add desired ending finished goods inventory Total needs Less beginning finished goods inventory Required production in units 46 47 48 Raw Materials Purchases Budget Year 2 Quarter 2 Quarter 3 Year 3 Quarter 1 Quarter 1 Quarter 4 Year 2 Total 49 50 51 52 53 54 55 56 57 Required production (units) Raw materials required to produce one unit (pounds) 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 (pounds) Cost of raw materials per pound Cost of raw materials to be purchased Schedule of Expected Cash Disbursements Year 2 Quarter 1 Quarter 2 Quarter 3 Quarter 4 Year 2 Total 58 3 59 60 61 62 63 64 65 66 Beginning balance accounts payable First-quarter purchases Second-quarter purchases Third-quarter purchases Fourth-quarter purchases Total cash disbursements 67 68 69