Question

Applying Excel 3 Data Year 3 Quarter Budgeted unit sales 45.000 70.000 115.000 60.000 90.000 90.000 70 per unit S 65.000 75% 25% | 7 Selling price per unit 8 . Accounts receivable, beginning balance 9 . Sales collected in the quarter sales are made 10 . Sales collected in the quarter after sales are made 14 . Desired ending finished goods inventory is 2 . 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 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 8 1,500 S | 10 and • Accounts payable for raw materials, beginning balance 19

Answer 1

Here's the completed schedule of sales budget and cash collections:

Data Year Data Quarter Budgeted unit sales Selling price per unit Total sales

2023 1 40,000 8 320,000

2023 2 60,000 8 480,000

2023 3 100,000 8 800,000

2023 4 50,000 8 400,000

2024 1 70,000 8 560,000

2024 2 80,000 8 640,000

Data Year Data Quarter Accounts receivable, beginning balance First-quarter sales Second-quarter sales Third-quarter sales Fourth-quarter sales Total cash collections

2023 1 0 0 320,000 0 0 320,000

2023 2 320,000 480,000 0 0 0 800,000

2023 3 800,000 0 800,000 0 0 1,600,000

2023 4 1,600,000 0 0 400,000 0 2,000,000

2024 1 2,000,000 560,000 0 0 0 2,560,000

2024 2 2,560,000 0 640,000 0 0 3,200,000

The Complete Question

Applying Excel Data Year 2 Quarter Year 3 Quarter 1 2 4 1 2 40,000 60,000 100,000 50,000 70,000 80,000 Budgeted unit sales Selling price per unit 8 per unit Accounts receivable, beginning balance $65,000 Sales collected in the quarter sales are made 75% Sales collected in the quarter after sales are made 25% 30% of the budgeted unit sales of the next quarter 1 Desired ending finished goods inventory is 2 Finished goods inventory, beginning 12,000 units 3 Raw materials required to produce one unit 5 pounds Desired ending inventory of raw materials is 10% of the next quarter's production needs 5 Raw materials inventory, beginning 23,000 pounds 5 Raw material costs $0.80 per pound 60% in the quarter the purchases are made Raw materials purchases are paid and 40% in the quarter following purchase unts payable for raw materials, beginning balance s81,500 Enter a formula into each of the cells marked with a below 2 Review Problem: Budget Schedules Construct the sales budget Year 2 Quarter Year 3 Quarter 1 2 4 1 2 Budgeted unit sales 5 Selling price per unit Total sales Construct the schedule of expected cash collections Year 2 Quarter 1 2 4 Year 2 Accounts receivable, beginning balance 3 First-quarter sales 4 Second-quarter sales Third-quarter sales Fourth-quarter sales 5 ?I 7 Total cash collections

Create a sales budget and a schedule of expected cash collections