Create a spreadsheet to complete the following budgets.

a. The sales price for each unit is $18. The estimated sales for the first five months of the year are as follows:

Month

Sales in Units

January

20,000

February

18,000

March

22,000

April

23,000

May

21,000

Prepare the sales budget for the first quarter.

Sales Budget

Quarter 1

January

February

March

Quarter 1

Units

20,000

60,000

Sales Price

$18

Sales Revenue

$360,000

$1,080,000

b. The production manager has determined that 20% of the following months budgeted sales should be kept on hand. The ending inventory at December 31 was 3,500 units.

Prepare a Production Budget for the first quarter.

Production Budget

Quarter 1

January

February

March

Quarter 1

Sales

20,000

Desired Ending Inventory (20%)

3,600

Beginning Inventory

(3,500)

(3,600)

Budgeted Production

20,100

18,800

22,200

61,100

c. Each unit requires 4 pounds of material that cost $1.50 per pound. Management wants to keep an extra 30% of the following months production needs in ending inventory each month. Ending materials inventory at December 31 is 18,000 pounds.

Prepare the direct materials budget and purchases budget for the first quarter.

Direct Materials Budget

Quarter 1

January

February

March

Quarter 1

Budgeted Production

20,100

Material Requirements

4

4

4

Budgeted Materials

80,400

Desired Ending Inventory (30%)

22,560

Beginning Inventory

(18,000)

(22,560)

Material Purchases

84,960

79,280

89,280

253,520

Material Purchases Budget

Quarter 1

January

February

March

Quarter 1

Material Purchases

84,960

Price

1.50

1.50

1.50

Purchase Cost

127,440

118,920

133,920

380,280

d. Past sales have been 30% cash and 70% credit.

Using the information from the sales budget prepared in part a, create a Forecasted Cash and Credit sales budget.

Forecasted Cash Sales and Credit Sales

Quarter 1

January

February

March

Quarter 1

Total Sales

360,000

1,080,000

Cash Sales (30%)

108,000

324,000

Credit Sales (70%)

252,000

756,000

The company has determined that 60% of the credit sales are collected in the month of the sale, and 40% of the credit sales are collected in the month after the sale. The credit sales for December were $302,000.

Schedule of Cash Receipts

Quarter 1

January

February

March

Quarter 1

Cash Sales (from above)

108,000

---

---

324,000

December credit sales (40%)

120,800

---

---

120,800

January credit sales (60%, 40%)

151,200

100,800

---

252,000

February credit sales (60%, 40%)

---

226,800

March credit sales (60%)

---

---

166,320

Budgeted Cash Receipts

380,000

334,080

375,480

1,089,920

e. Purchases are paid 70% in the month of purchase and 30% in the following month. The credit purchases for December were $140,000.

Using the purchases budget prepared in part c, create the cash disbursement schedule for Quarter 1.

Schedule of Cash Disbursements

Quarter 1

January

February

March

Quarter 1

December (30%)

42,000

---

---

42,000

January purchases (70%, 30%)

89,208

38,232

---

127,440

February purchases

---

118,920

March purchases

---

---

93,744

Budgeted Cash Disbursements

131,208

121,476

129,420

382,104