Requirement 2: 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 Year 3 Quarter Year 2 Quarter 1 85,000 1 45,000 3 2 Data 4 Budgeted unit sales Selling price per unit 60,000 65,000 110,000 90,000 $7 A C D E F G Chapter 8: Applying Excel 1 3 Data Year Quarter 4 1 2 3 4 1 2 5 Budgeted unit sales 45,000 65,000 110,000 60,000 85,000 90 6 7 Selling price per unit 7 per unit Accounts receivable, beginning balance $ 65,000 75% Sales collected in the quarter sales are made 10Sales collected in the quarter after sales are made 25% 30% of the budgeted unit sales of the next quarter 11 Desired ending finished goods inventory is 12,000 units Finished goods inventory, beginning 12 5 pounds 13 Raw materials required to produce one unit 10% of the next quarter's production needs 14 Desired ending inventory of raw materials is Raw materials inventory, beginning 23,000 pounds 15 0.80 per pound 16 Raw material costs the quarterthe purchases are made Raw materials purchases are paid 17 60% the quarter following purchase 18 40% and Prey 1 of 1 Next 19 $ 81,500 Accounts payable for raw materials, beginning balance a. What are the total expected cash collections for the year under this revised budget? Expected cash collections for the year 1,885,000 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 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 A C E G H Chapter 8: Applying Excel Data Year 3 Quarter Year 2 Quarter 2 1 5 Budgeted unit sales 100,000 50,000 40,000 60,000 70,000 80,000 6 Selling price per unit Accounts rece ivable, 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, beginn ing Raw materials required to produce one unit Desired ending inventory of raw materials is Raw materials inventory, beginning 16 $8 per unit 7 8 $65,000 9 75% 25% 10 11 30% f the budgeted unit sales of the next quarter 12 12,000 units 5 pounds f the next quarter's production needs 13 14 10% O000 pounds $0.80 per pound 23 Raw material costs Raw materials purchases are paid the quarter the purchases are made the quarter following purchase 60% 17 18 and 40% $81,500 19 Accounts payable for raw materials, beginning balance 20 21 Enter a formula into each of the cells marked with a Review Problem: Budget Schedules below 23 B C F H 47 Construct the raw materials purchases budget Year 2 Quarter Year 3 Quarter 48 2 3 Year 4 49 Required production (units) 50 Raw materials required to produce one unit 51 Production needs (pounds) 52 Add desired ending inventory of raw materials (pounds) 53 Total needs (pounds) 54 Less beginn ing inventory of raw materials (pounds) 55 Raw materials to be purchased 56 Cost of raw materials per pound 57 Cost of raw materials to be purchased 7 2 ? 2 2 2. 2 2 2. 2. 2 ? 2 2 58 59 Construct the schedule of expected cash payments Year Quarter 4 60 1 2 Year 61 Accounts payable, beginning balance 62 First-quarter purchases 63 Second-quarter purchases 64 Third-quarter purchases 65 Fourth-quarter purchases 66 Total cash disbursements 7 ? ? 2 ? ? ? 2. 67 68 69
