Use this Excel Culminating Project Template to help you get started with your bu

Question

Use this Excel Culminating Project Template to help you get started with your budget.

You are the president of Campus Sweaters, Inc. Campus Sweaters manufacturers wool pullover v-neck sweaters of various sizes and colors. You are preparing the budgets for the first quarter of 2016 (January, February, and March). You have the following historical and projected sales in units:

It takes ten skeins of yarn to make one sweater. Each skein costs $1.30. Past experience shows you need to have enough sweaters on-hand to fill the next month and one half of sales (approximately forty-five days). Also, you need enough yarn to manufacture the next month’s production.

You will have 12,000 sweaters in finished inventory and 80,000 skeins of yarn in raw materials inventory as of December 31, 2015. You purchased $90,000 of yarn in December that must be paid for in January. The Company incurred $7,500 of overhead cost during December 2015, and $13,500 of selling expenses in the last half of December. These also must be paid in January. The company policy is to pay prior month's charges on account on the tenth day of the following month unless otherwise designated.

Income Statements

A worker, using a knitting machine, can make five sweaters in an hour. The cost of direct labor per hour, including fringe, is $20.00. You incurred $13,000 of direct labor cost between December 16 and December 31, 2015 which will be paid on January 7, 2016. The manufacturing overhead rate is $5.00 per direct labor hour. All sweaters are sold wholesale to retail outlets at $30.00 each.

Salaries and wages are paid as follows: The pay period from the first to the fifteenth of the month is paid on the twenty-second day of each month; the pay period from the sixteenth to the thirty-first is paid on the seventh day of the following month.

Rent is paid in advance on the first day of each month. Fifty percent of the selling expenses are paid in the month incurred, and fifty percent in the following month. All manufacturing overhead and administrative costs are paid on the tenth day of the following month.

The cash in the bank on December 31, 2015 was forecast at $30,000. There were no outstanding borrowings. The company has a $500,000 line of credit at 12% per annum at the Old Rusty Bucket State Bank of Oreana. All borrowings, and any subsequent repayments, must be made on the fifteenth day of the month. All loan takedowns must be repaid by December 31, 2016. Repayments can be made when extra cash is available, but are due on the fifteenth day of any month. The company has the policy to have at least $25,000 in the bank account at the end of each month even if they have to borrow it. However, more may be required depending on cash needs during the first week of the following month.

20% of the sales are collected in the month of sale. Seventy percent are collected in the next month, and five percent are collected in the third month.

>Use the information above to complete the following activities:

Step 01: Prepare a production budget for Campus Sweaters, Inc. for each of the following months: January, February, March 2016.

Step 02: Prepare a raw materials budget for each month.

Step 03: Prepare a raw materials budget in dollars for each month.

Step 04: Prepare a cost of goods manufactured schedule for each month.

Step 05: Prepare a cash budget for each month.

Actual or Projected Month Units Actual November 9,000 Actual December 8,000 Projected January 11,000 Projected February 10,000 Projected March 6,000 Projected April 7,000 Projected May 7,000 Projected June 7,000

Explanation / Answer

Sales budget January February March Total Sales (units) 11000 10000 6000 27000 Price/unit 30 30 30 30 Total sales 330000 300000 180000 810000 Collection from sales 20% Sales 66000 60000 36000 162000 70% sales 189000 231000 210000 630000 5% sales 12000 13500 16500 42000 Total sales collections 267000 304500 262500 834000 Production Budget January February March Total Sales (units) 11000 10000 6000 27000 Ending Inv.reqd.(next mth.sales*1.5) 15000 9000 10500 10500 Total needed 26000 19000 16500 37500 Less: Avail.in op.stock 12000 15000 9000 12000 Production reqd. 14000 4000 7500 25500 Raw materials (units) Budget Skiens reqd.for prodn. 140000 40000 75000 255000 Add:Ending inv.reqd.(next mth. Prodn.) 40000 75000 70000 70000 Total needed 180000 115000 145000 325000 Less: Available in Op.stock 80000 40000 75000 80000 Purchases needed 100000 75000 70000 245000 Raw materials ($ ) Budget Purchases needed 130000 97500 91000 318500 Direct Labor budget Production reqd. 14000 4000 7500 25500 No.of sweaters in 1 hr. 5 5 5 5 No.of lab.hrs.reqd. 2800 800 1500 5100 Lab.cost /hr 20 20 20 20 Total d/l cost 56000 16000 30000 102000 Payment for direct Labor Dec 16-31,2016 13000 13000 1-15 payment 28000 8000 15000 51000 16-31 payment 28000 8000 36000 Total labor cost payment 41000 36000 23000 100000 Manufacturing OH budget No.of lab.hrs.reqd. 2800 800 1500 5100 MOHRate/hr. 5 5 5 5 Total MOH cost 14000 4000 7500 25500 Payment for MOH 7500 14000 4000 25500 Cost of goods manufactured Raw materials used in prodn 182000 52000 97500 331500 Direct labor 56000 16000 30000 102000 MOH cost 14000 4000 7500 25500 Total mfg. cost 252000 72000 135000 459000 Payment for selling expense: 1st 50% payment 15000 13500 10500 39000 2nd 50% payment 13500 15000 13500 42000 Cash paid --Selling expense 28500 28500 24000 81000 Cash budget Opening balance 30000 55000 71000 30000 Total sales collections 267000 304500 262500 834000 Total cash available 297000 359500 333500 864000 Less: Disbursements : Yarn purchases 90000 130000 97500 317500 Payment for OH 7500 14000 4000 25500 Cash paid --Selling expense 28500 28500 24000 81000 Payment for admn.exp. 45,000 50,000 45,000 140,000 Total labor cost payment 41000 36000 23000 100000 Rent 10000 10000 10000 30000 Sales salaries 20000 20000 20000 60000 Total disbursements 242000 288500 223500 754000 Surplus/Deficit 55000 71000 110000 110000 Add: Borrowings Less: Repayments Less: Interest Ending balance of cash 55000 71000 110000 110000

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

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