Create the following: (Excel layouts below) Schedule 1: Sales Budget Schedule 2:

Question

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

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).

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 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

Explanation / Answer

Cozy Collar In 2018 Schedule 1 Sales Budget for Cozy Collar Q1 Q2 Q3 Q4 Total Sales in Units 4000 3600 3780 2268 13648 X Sales price 62 62 62 62 Sales Revenue 248000 223200 234360 140616 846176 Schedule 2 Cash Receipt Q1 Q2 Q3 Q4 Total Cash from sales 163680 147312 154678 92807 558476 Accounts receivable collected 6000 84320 75888 79682 245890 Total Cash Receipts 169680 231632 230565.6 172489 804367 Schedule 3 Production Budgets in units Q1 Q2 Q3 Q4 Total Sales in Units 4000 3600 3780 2268 13648 Plus desired ending balance inventory(FG) in units 900 945 567 851 Total Inventory needed in units 4900 4545 4347 3119 Less desired ending balance inventory(FG) in units 997 900 945 567 units to be produced 3903 3645 3402 2552 13502 Schedule 4 DM Purchase - nylone rope Q1 Q2 Q3 Q4 Total units to be produced 3903 3645 3402 2552 13502 Amt of nylone rope per unit 11 11 11 11 Total nylone rope needed for production - inches 42933 40095 37422 28067 148517 Plus Desired ending inventory of nylone rope(RM)-Inches 4009.5 3742.2 2806.65 3929.2 Total nylone rope needed - inches 46942.5 43837.2 40228.65 31995.7 163004 Less Desired beginning inventory of nylone rope(RM)-inches 5489 4009.5 3742.2 2806.65 16047 Nylone rope to be purchased-inches 41454 39828 36486 29189 146957 Nylone rope cost per inch 0.68 0.68 0.68 0.68 Total nylone rope purchase cost 28188 27083 24811 19849 99931 Schedule 4 DM Purchase - Buckles Q1 Q2 Q3 Q4 Total units to be produced 3903 3645 3402 2552 13502 Amt of Buckles per unit 4 4 4 4 Total Buckles needed for production - inches 15612 14580 13608 10206 54006 Plus Desired ending inventory of Buckles(RM)-Inches 1458 1361 1021 1429 5268 Total Buckles needed - inches 17070 15941 14629 11635 59274 Less Desired beginning inventory of Buckles(RM)-inches 0 1458 1360.8 1020.6 Buckles to be purchased-inches 17070 14483 13268 10614 55435 Buckles cost per inch 0.25 0.25 0.25 0.25 Total Buckles purchase cost 4268 3621 3317 2654 13859 As per policy of Chegg, we are supposed to answer maximum of four sub-parts of a question We appreciate the rating of our answers Thank You

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.