Create the following: (Excel layouts below)

Schedule 1: Sales Budget

Schedule 2: Cash Receipts

Schedule 3: Production Budget in Units

Schedule 4: Direct Materials Purchases

Schedule 5: Cash Purchases Budget

Schedule 6: Direct Labor Budget

Schedule 7: Overhead Budget

Schedule 8: Selling and Administrative Expense Budget

Schedule 9: Ending Finished Goods

Schedule 10: Budgeted cost of goods sold

Schedule 11: Budgeted income statement

Schedule 12: Cash Budget

Schedule 13: Budgeted Balance Sheet

Cozy Collar Inc. Balance Sheet

	January 1
	2018
Cash	$52,250
Accounts Receivable	$6,000
Inventory Raw Materials	$3,733
Inventory Finished Goods	$18, 943
Land	$14,000
Buildings & Equipment	$120,000
Accumulated Depreciation	($22,000)
Total Assets	$192,926

Accounts Payable	$4,000
Capital Stock	$13,150
Retained Earnings	$175,776
Total Liabilities and Equity	$192,926

Use the following to complete the project:

The marketing department projects the following sales levels:

Quarter 1, 2018 ________________________ 4,000 pet collars

Quarter 2, 2018 ________________________ 3,600 pet collars

Quarter 3, 2018 ________________________ 3,780 pet collars

Quarter 4, 2018 ________________________ 2,268 pet collars

Quarter 1, 2019 ________________________ 3,402 pet collars

Quarter 2, 2019 ________________________ 4,082 pet collars

1. The pet collars are expected to sell for $62 dolalrs each. Cozy Collar sells all merchandise on credit. Historically, Cozy Collar receives 66% of each quater's sales during the quarter and 34% in the next quarter. Collections are always rounded down to the nearest dollar in the first month as a courtesey to customers.

2. Cozy Collar plans to stock an ending inventory of finished goods to equal 25% of the next quarter's sales. Finished goods inventory at 1/1/2018 is 997 pet collars at a cost of $19 per unit. Cozy Collar uses FIFO to account for the finished goods inventory.

3. The pet collars are made with both nylon rope and buckles. The pet collars each require 11 inches of nylon rope at a cost of $0.68 per inch. In addition, the pet collars require 4 units of buckles at $0.25 per unit.

At 12/31/2017, Cozy Collar had 5489 inches of nylon rope on hand and 0 units of buckles on hand. Cozy Collar plans to keep 10% of the nylon rope and buckles required for next quarter's production in inventory at the end of each quarter. ( note: the smallest unit of measure recognized by Cozy Colar's nylon rope supplies is inches.) Cozy Collar utilizes the following payment schedule for all of thier purchases: 60% in the quarter of purchase and 40% in the quarter following the purchase.

4. Each pet collar requires 0.5 hours of direct labor. Cozy Collar's factory is located in Ohio and the compant pays employess the minimum wage. Employes are paid on the last day of each month for that month's work. All costs are paid in the quarter unless otherwise noted.

5. Variable overhead is estimated at $0.15 per direct labor hour. Fized overhead costs are $150,000 per year. Fixed overhead includes depreciation of $20,000 per year.

6. Variable selling and administrative costs are $2.52 per pet collar. Fixed selling and admin csts are $500,000 per year. (This includes $10,000 of depreciation and $100,000 in CEO wages per year)

7. Cozy Collar makes quarterly income tax payments of $5,000 each. The corporate income tax rate is 30% of net income before taxes (consider any payments on your balance sheet).

8. Cozy Collar pays quarterly dividends of $1,100.

9. Cozy Collar will purchase additional equipment on the followin payment schedule (depreciation is already included above):

Quarter 1 ______________ $1,600

Quarter 2 ______________ $2,400

Quarter 3 ______________ $ -

Quarter 4 ______________ $ -

10. Cozy Collar has to maintain a minimum cash balance of $17,000. Any projected borrowings are assumed to be borrowed at the end of the quarter. Payments are made at the end of the following quarters to the extent cash is available.

All borrowings and repayments are made in $15,000 increments. Interest is paid at the time of repayment and is calculated at 9% per year, rounded up to the nearest dollar (no compounding).

2018 Total Q1 Q3 Schedule 1: Sales Budget for Cozy Collar Sales in Units x sales price Sales revenue Q2 Q4 Q1 Q3 Total Schedule 2: Cash receipts Cash from Sales Acounts Receivable Collected Total Cash Receipts Q2 Q4 Schedule 3: Production Budget in Units Sales in units plus Desired ending balance inventory (FG) in units Total inventory needs in units less Beginning balance inventory (FG) in units Units to be produced Q1 Q2 Q3 Q4 Total Schedule 4: DM Purchases: nylon rope Units to be produced Amt of nylon rope per uni Total nylon rope needed for production -inches plus Desired ending inventory: nylon rope (RM) inches Total nylon rope needed inches less Beginning inventory: nylon rope (RM)- inches nylon rope to be purchased inches nylon rope cost per inch Total nylon rope purchase cost Q1 Q2 Q3 Q4 Total 2018 Total Q1 Q3 Schedule 1: Sales Budget for Cozy Collar Sales in Units x sales price Sales revenue Q2 Q4 Q1 Q3 Total Schedule 2: Cash receipts Cash from Sales Acounts Receivable Collected Total Cash Receipts Q2 Q4 Schedule 3: Production Budget in Units Sales in units plus Desired ending balance inventory (FG) in units Total inventory needs in units less Beginning balance inventory (FG) in units Units to be produced Q1 Q2 Q3 Q4 Total Schedule 4: DM Purchases: nylon rope Units to be produced Amt of nylon rope per uni Total nylon rope needed for production -inches plus Desired ending inventory: nylon rope (RM) inches Total nylon rope needed inches less Beginning inventory: nylon rope (RM)- inches nylon rope to be purchased inches nylon rope cost per inch Total nylon rope purchase cost Q1 Q2 Q3 Q4 Total