Budgeted unit sales

40,000

60,000

100,000

50,000

70,000

80,000

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%

Desired ending finished goods inventory is

30%

of the budgeted unit sales of the next quarter

Finished goods inventory, beginning

12,000

units

Raw materials required to produce one unit

5

pounds

Desired ending inventory of raw materials is

10%

of the next quarter's production needs

Raw materials inventory, beginning

23,000

pounds

Raw material costs

$0.80

per pound

Raw materials purchases are paid

60%

in the quarter the purchases are made

and

40%

in the quarter following purchase

Accounts payable for raw materials, beginning balance

$81,500

Enter a formula into each of the cells marked with a ? below

Construct the schedule of expected cash collections

Year 2 Quarter

1

2

3

4

Year

Accounts receivable, beginning balance

?

?

First-quarter sales

?

?

?

Second-quarter sales

?

?

?

Third-quarter sales

?

?

?

Fourth-quarter sales

?

?

Total cash collections

?

?

?

?

?

Chapter 8 Applying Excel Studen ACROBAT FORMULAS REVIEW VIEW DATA PAGE LAYOUT HOME INSERT E Cut Wrap Text General Arial AA 10 Copy te Conditional F A E EE Merge & Center $% BIU Formatting Format Painter Number Font Alignment Cipboard Chapter 8: Applying Excel A E C G H 2 3 4 5 Budgeted unt sales 40,000 60,000 100,000 50,000 70,000 80,000 6 7 Selling price per unit 8 Accounts recevable, beginning balance 9 Sales collected in the quarter sales are made 10 Sales colected in the quarter after sales are made 11 Desired ending 5nished goods inventory is 12 Finished goods imventory, beginning Raw materials required to produce one unit $8 per unt $65,000 75 % 25% 30% of the budgeted unit sales of the next quarter 12.000 unts 13 Desired ending inventory of raw materials is 15 Raw materials inventory, beginning 16 Raw material costs 17 Raw matenals purchases are paid 5 pounds 10% of the next quarter's production needs 23.000 pounds $0.80 per pound 60% in the guarter the purchases are made 14 18 and 40% in the quarter following purchase $81.500 19 Accounts payable for raw materials, beginning balance 20 21 Enter a formula into each of the cels marked with a ? below 22 Review Problem: Budget Schedules 23 24 Construct the sales budget Year 2 Quarter 25 Year 3 Quarter 2 26 Budgeted unt sales 27 Selling price per unit 28 Total sales 3 29 30 Construct the schedule of expected cash collections Year 2 Quarter 2 Accounts recevable beginning balances Frst-quater sales Year Chapter For READH hp VITO REVIEY URTA FORMULAS PAGE LAYOU INSERT HOME General Wrap Text XCut 10-A A Arial Conditional Fou Formatting T Copy Format Painter $-% A EE EE Merge & Center BIU- Number Alignment Font Clipboard Chapter 8: Applying Excel D E F G C 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 no Sales colected in the quarter after sales are made 11 Desired ending finished goods inventory is 12 Finished goods inventory, beginning 13 Raw matenials required to produce one unit 14 Desired ending inventory of raw materials is Raw materials inventory, beginning 15 16 Raw material costs 17Raw materials purchases are paid and 18 19 Accounts payable for raw materials, beginning balance 20 21 Enber a fomula into each of the cells marked wth a ? below 22 Review Problem: Budget Schedules 20 24 Construct the sales budget Year 2 Quarter Year 3 Quarten 25 2 2 26 Budgeted unit sales 27 Seling price per unt 28 Total sales 29 20 ConstrucE the schedule of expected cash collections 7 Year 2 Quarter 2 Accounts eceable beginning balance 33 First-quater sales 4 Second-qater sales Thed-quarter sales Fourth quater sales Total cash collections Year Construct the production bdget Year 2 Quater Year 3 Quat Chagter Fom F10 FI1