Use this Excel Culminating Project Template (SEE SCREENSHOT BELOW) 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:
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 |
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
Actual or Projected Sales | Actual | Actual | Projected | Projected | Projected |
---|---|---|---|---|---|
Month | November | December | January | February | March |
Sales | $240,000 | $270,000 | $300,000 | $270,000 | $210,000 |
Cost of sales | 144,000 | 162,000 | 180,000 | 162,000 | 126,000 |
Gross margin | 96,000 | 108,000 | 120,000 | 108,000 | 84,000 |
Operating Expenses: | |||||
Selling | 24,000 | 27,000 | 30,000 | 27,000 | 21,000 |
Administration | 35,000 | 45,000 | 50,000 | 45,000 | 30,000 |
Rent | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 |
Sales salaries | 20,000 | 20,000 | 20,000 | 20,000 | 20,000 |
Totals | 89,000 | 102,000 | 110,000 | 102,000 | 81,000 |
Operating Income | 7,000 | 6,000 | 10,000 | 6,000 | 3,000 |
Interest Expense | 0 | 0 | ? | ? | ? |
Net Income | $7,000 | $6,000 |
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.
These are for the month of January
Required Production in units 12,000
Required purchases in units 105,000
Required purchases in dollars $136,500
Total Cost of Goods Manufactured $216,000
>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. -DONE
Step 02: Prepare a raw materials budget for each month.-DONE
Step 03: Prepare a raw materials budget in dollars for each month.-DONE
Step 04: Prepare a cost of goods manufactured schedule for each month.-DONE
Step 05: Prepare a cash budget for each month (Cash Flow Projection).--I NEED HELP ON THIS STEP ONLY.
I JUST NEED STEP 5 COMPLETED. I PROVIDED THE WORK TO STEPS 1-4 IN THE SCREENSHOTS.
MY PROFESSOR SAID THE CASH INFLOWS ARE $261,000 AND CASH OUTFLOWS ARE $238,000. PLEASE MAKE SURE THESE ARE CORRECT IN THE ANSWER YOU PROVIDE.
Use this Excel Culminating Project Template (SEE SCREENSHOT BELOW) to help you get started with your...
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: 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 It takes ten skeins of yarn...
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: 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 It takes ten skeins of yarn...
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 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...
Problem: Complete the Purchase Budget (below) using Excel: Provide the formula cell view rather than the numerical answer. Purchase Budget December January February March Desired Ending Inventory Cost of Goods Sold Total Needed Beginning Inventory Purchases The Distribution Center of 123 Oil and Gas Company wants a master budget for the next three months, beginning January 1st. It desires an ending minimum cash balance of $4,000 each month. Sales are forecasted at an average selling price/transfer price of S4 per...
Could you please solve it with the formula in Excel for each step? Thank you 17-12. (Preparing a cash budget) Harrison Printing has projected its sales for the first eight months of 2017 as follows: January $100,000 May $275,000 February 120.000 June 200,000 March 150,000 July 200,000 April 300.000 August 180.000 Harrison collects 20 percent of its sales in the month of the sale, 50 percent in the month following the sale, and the remaining 30 percent two months following...
Kilang Roti Berhad, a manufacturer of traditional breads in Port Dickson, is preparing monthly cash budgets for the month of January, February and March 2018. The following data are available from records of the company: January February March RM RM RM Sales 100,000 150,000 200,000 Raw materials purchases 50,000 70,000 100,000 Wages 20,000 35,000 55,000 Manufacturing overhead costs 10,000 10,000 10,000 Selling and administration 5,000 5,000 5,000 The company has the following business policies: Collections of cash from customers...
Requirement: Complete the various budget schedules using Excel. Submit one hard copy per group and email me your Excel spreadsheet. In Excel use formulas wherever possible. Avoid “hard coding” because I will test the flexibility of your spread sheet by changing certain cells such as sales. To be discussed further in class as well. The Distribution Center of 123 Oil and Gas Company wants a master budget for the next three months, beginning January 1st. It desires an ending minimum...
Cash Budget The controller of Shoe Mart Inc. asks you to prepare a monthly cash budget for the next three months. You are presented with the following budget information: January February March Sales $100,000 $122,000 $166,000 Manufacturing costs 42,000 52,000 60,000 Selling and administrative expenses 29,000 33,000 37,000 Capital expenditures _ _ 40,000 The company expects to sell about 10% of its merchandise for cash. Of sales on account, 65% are expected to be collected in full in the month...
This is all one question. Thank you, I will give you a thumbs up if correct! Waterways Corporation is preparing its budget for the coming year, 2020. The first step is to plan for the first quarter of that coming year. The company has gathered information from its managers in preparation of the budgeting process. Sales Unit sales for November 2019 Unit sales for December 2019 Expected unit sales for January 2020 Expected unit sales for February 2020 Expected unit...
************I AM LOOKING FOR AN EXCEL VERSION + PHOTOS OF THE FORMULAS SHOWN IN THE EXCEL PLEASE. ************** Check figures: cash collections at end of quarter = $814,800 Deficiency in cash at the end of the quarter = $(99,206) I am unsure if my excel spread is correct but it is what I have so far.. PLEASE SHOW YOUR EXCEL + THE FORMULAS SHOWN IN EXCEL (ex: =B13-A12 or =SUM(B17:B37) ETC.) Kline Sisters Company operates a gift shop where peak...