Sheet 2: Sheet 2- Worksheet

Data

Year 2 Quarter

Year 3 Quarter

1

2

3

4

1

2

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

Remember to enter a cell reference or formula into each of the cells marked with a ? below. Do not just enter a number.

Create the sales budget

Year 2 Quarter

Year 3 Quarter

1

2

3

4

1

2

Budgeted unit sales

?

?

?

?

?

?

Selling price per unit

?

?

?

?

?

?

Total sales

?

?

?

?

?

?

Create 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

?

?

?

?

?