The company has just hired a new marketing manager who insists that unit sales can be dramatically increased by dropping the selling price from $8 to $7. The marketing manager would like to use the following projections in the budget Data Budgeted unit sales Year 2 Quarter 2 Year 3 Quarter 1 Budgeted unit sales 45,000 65,000 115,000 70,000 90,000 95,000 Selling price per unit $7 Selling price per unit Accounts receivable, beginning balance Year 3 Quarter 1 100,000 50,000 70,000 2 80,000 Year 2 Quarter 2 40,000 60,000 $8 per unit $65,000 75% 25% A 1 Chapter 8: Applying Excel 2 3 Data 4 1 2 5 Budgeted unit sales 45,000 65,000 3 115.000 4 Year 3 Quarter 1 B C D E F G Finished goods inventory, beginning Raw materials required to produce one unit Desired ending inventory of raw materials is Sales collected in the quarter sales are made Sales collected in the quarter after sales are made Desired ending finished goods inventory is 30% of the budgeted unit sales of the nest quarter 12,000 units 5 pounds 2 Rav materials inventory, beginning -Raw material costs 70,000 G0,000 95,000 Raw materials purchases are paid 6 and 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 7 Seling price per unit 7 per unit Accounts payable for raw materials, beginning balance $81,500 Accounts receivable, beginning balance $ 05.000 Sales collected in the quarter sales are made 75% 9 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 Enter a formula into each of the cells marked with a ? below Review Problem: Budget Schedules Finished goods inventory, beginning 12,000 units Construct the sales budget 12 Raw materials required to produce one unit 5 pounds 13 Budgeted unit sales Desired ending inventory of raw materials is 14 Raw materials inventory, beginning 10% of the next quarter's production needs 23,000 pounds Selling price per unit Total sales 15 Raw material costs $ 0.80 per pound 16 Raw materials purchases are paid 00% in the quarter the purchases are made Construct the schedule of expected cash co. 17 and 40% in the quarter following purchase 18 Accounts payable for raw materials, beginning balance $ 81,500 19 a. What are the total expected cash collections for the year under this revised budget? Expected cash collections for the year b. What is the total required production for the year under this revised budget? Total required production for the year c. What is the total cost of raw materials to be purchased for the year under this revised budget? Total cost of raw materials to be purchased for the year Year 2 Quarter Year 3 Quarter , 2 3 ' 2 40,000 $8 $320,000 60,000 $8 $480,000 100,000 $8 $800,000 50,000 $8 $400,000 70,000 $8 $560,000 80,000 $8 $640,000 Year 2 Quarter 2 $ Accounts receivable, beginning balance First-quarter sales Second-quarter sales Third-quarter sales Fourth-quarter sales Total cash collections $ 65,000 $ Year 65,000 240,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 Construct the production budget Year 2 Quarter Year 3 Quarter 2 Budgeted unit sales 40,000 60,000 3 100,000 Add desired finished goods inventory 18,000 30,000 4 50,000 15,000 21,000 Year 250,000 70,000 2 80,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 Required production 46,000 72,000 85,000 56,000 259,000 73,000 Construct the raw materials purchases budge Year 2 Quarter Year 3 Quarter 2 3 Required production (units) 46,000 72,000 85,000 56,000 Year 259,000 73,000 Raw materials required to produce one unit Production needs (pounds) 5 230,000 Add desired ending inventory of raw materials (pounds) Total needs (pounds) 36,000 266,000 Less beginning inventory of raw materials (pounds) 23,000 Raw materials to be purchased 243,000 Cost of raw materials per pound $0.80 5 360,000 42,500 402,500 36,000 366,500 $0.80 Cost of raw materials to be purchased $194,400 $293,200 5 425,000 28,000 36,500 453,000 316,500 42,500 28,000 410,500 288,500 $0.80 $0.80 $328,400 $230,800 5 5 280,000 1,295,000 36,500 5 365,000 1,331,500 23,000 1,308,500 $0.80 $1,046,800 Construct the schedule of expected cash pay Year 2 Quarter 2 3 4 Year $ 81,500 $ 81,500 116,640 $ 77,760 175,920 $ 194,400 $117,280 197,040 $131,360 $ 328,400 138,480 $138,480 $ 293,200 d. What are the total expected cash disbursements for raw materials for the year under this revised budget? Total expected cash disbursements for raw materials for the year Accounts payable, beginning balance First-quarter purchases Second-quarter purchases Third-quarter purchases Fourth-quarter purchases Total cash disbursements $ 198,140 $253,680 $314,320 $269,840 $1,035,980