i just need the raw material budget table. (with formulas)

INPUTS Name PROJECTED BUDGETING DATA ChocoLasting Chocolate Company Balance Sheet As of September 30, 2019 2019 2020 October November December January February 328,200 $100,000 130,000 $31,600 521,600 $17,550 $190,000 Accounts Payable Notes Payable Interest Payable TOTAL LABI Common Stock Retained Earning TOTAL EQUITIES TOTAL LIABILITIES&EQUITIES Selling Price per Unit ccounts Receivable Raw Materials Inventory Finished Goods Inventory PPSE, net 128 $80,000 Collected in Following Month $182,550 of next month's unit sales $390,750 Beginning FG Inventory, October 1, 2019 pounds pounds Materials Required per Unit of FG 0.75 per pound 20% of next month's production needs Materials Cost per Pound Ending RM Inventory Requirement Paid in Month of Purchae aid in Follewing Month Cost per Hour per hour per labor hour per month per month Noncash Fued Oncluded in above) per unit sold par month per month ioncash Fixed S&.A (incladed in above) 625 per month Raw Materials Purchases Budget 2019 November December 2020 January TOTAL October Units Produced Pounds of RM Required Per Unit of FG Total Pounds Used in Production Plus: Desired Pounds in Ending Inventory Total Pounds Required Less: Pounds in Beginning Inventory RAW MATERIALS PURCHASES (POUNDS) Cost per Pound RAW MATERIALS PURCHASES (COST) Case: ChocoLasting Chocolate Company is a small manufacturing company that manufactures the cocoa powder used by hot chocolate vendors. The information below pertains to the company's budgeting process during their busiest time of year The cocoa powder is sold in five pound containers, alternatively referred to as units. Budgeted sales in units are as follows: . . o October 15,000 units o November-35,000 units o December-40,000 units o January- 50,000 units o February-45,000 units Each unit sells for $15.00. All sales are on account. The company's collection pattern is: 75% of sales are collected in the month of sale; 25% are collected in the month following. o The company desires to have finished goods inventory on hand at the end of each month equal to 15% of the following month's budgeted sales in units. On September 30, the company had 2,250 units on hand. . . 8 pounds of cacao beans are required for each unit produced. The company desires to have materials on hand at the end of each month equal to 20% of the following month's production needs. On September 30, the company had 28,800 pounds of cacao beans on hand The cacao beans used in production cost $0.75 per pound. The company's payment pattern is Each unit sells for $15.00. All sales are on account. The company's collection pattern is: 75% of sales are collected in the month of sale; o 25% are collected in the month following o The company desires to have finished goods inventory on hand at the end of each month equal to 15% of the following month's budgeted sales in units. On September 30, the company had 2,250 units on hand. . 8 pounds of cacao beans are required for each unit produced. The company desires to have materials on hand at the end of each month equal to 20% of the following month's production needs. On September 30, the company had 28,800 pounds of cacao beans on hand The cacao beans used in production cost $0.75 per pound. The company's payment pattern is: 70% of the month's purchases are paid for in the month of purchase; o o 30% is paid for in the following month. Each unit requires 6 minutes of labor time to make (0.1 hours) and the hourly employees are paid $15/hour. Wages are paid in the month incurred Variable manufacturing overhead is $3.00 per labor hour. Fixed manufacturing overhead is $28,000 per month including $8,000 in depreciation that is not a current cash outflow . All cash disbursements for manufacturing overhead are paid in the month incurred. Variable selling and administrative expenses are $1.50 per unit sold Fixed selling and administrative expense is $25,000 per month including $7,000 in depreciation that is not a cash outflow of the current month Chocolasting purchased a new piece of equipment at the end of December on the 31st The equipment cost $35,700 ChocoLasting borrowed $100,000 on September 30th as they normally need a lot of working capital to support the winter sales. Interest payments of $625 are made on the last day of each month. The Company uses variable costing in its budgeted income statement and its balance sheet Additionally, ChocoLasting has the following balance sheet as of September 30, 2019: ChocoLasting Chocolate Company Balance Sheet As of September 30, 2019 ASSETS LIABILITIES&EQUITIES Cash Accounts Receivable Raw Materials Inventory Finished Goods Inventory PP&E, net 130,000 Accounts Payable $31,600 Notes Payable $21,600 Interest Payable $17,550 $190,000 $28,200 $100,000 $0 128,200 $80,000 $182,550 $262.550 TOTAL LIABILITIES Common Stock Retained Earnings TOTAL EQUITIES ChocoLasting Chocolate Company Balance Sheet As of September 30, 2019 ASSETS LIABILITIES &EQUITIES Accounts Payable Cash Accounts Receivable Raw Materials Inventory Finished Goods Inventory PP&E, net $130,000 $28,200 $100,000 $0 $128,200 80,000 182,550 $262,550 $31,600 Notes Payable $21,600 Interest Payable $17,550 TOTAL LIABILITIES $190,000 Common Stock Retained Earnings TOTAL EQUITIES TOTAL LIABILITIES& EQUITIES TOTAL ASSETS $390,750 390,750 Requirements 1. Enter your name at the top of the INPUTS tab, 2. Prepare a master budget for the quarter ended December 31, 2019 that includes: Finished Goods Inventory Cost per Unit, Sales Budget, Schedule of Expected Cash Collections, Production Budget, Raw Materials Purchases Budget, Direct Labor Budget, Manufacturing Overhead Budget, Selling& Administrative Expense Budget, Cash Budget, Budgeted Income Statement,& Budgeted Balance Sheet (a total of 11 schedules). Each schedule should be on a separate worksheet as appears in your template. There is a template provided to you in the assignments link in the Excel Project folder with an input page you must use. Complete the shaded areas of the template only the shaded areas of the template only. All of your spreadsheets must be formula driven from the input tab! This means that EVERY cell should contain either a value referenced from the input worksheet or a formula manually entered into the cell using the referenced values or the input values. 3. ChocoLasting Chocolate Company borrowed $100,000 so they would have adequate working capital to get through the busy season. The company would like to be able to repay the $100,000 at the end of December. However, a large, new competitor has emerged in the cocoa powder market, and ChocoLasting is concerned that the increased market demand for cacao beans will drive up the cost of their raw materials and hurt their profitability and cash flow. Use Goal Seek to see how far the cost per pound of cacao beans could rise and still keep the company with a cash balance of $300,000 at the end of December. This will allow them to repay the loan and still have $200,000 cash on hand. HINT: To find goal seek, click on the Data tab and under Data Tools click on the What-If Analysis. If you are using an old version of Excel, you can find goal seek by going to the Tools menu. Enter your answer on the Goal Seek tab and then change the cost per pound of raw materials back to $0.75 on the INPUTS tab. see how far the cost per pound of cacao beans could rise and still keep the company with a cash balance of $300,000 at the end of December. This will allow them to repay the loan and still have $200,000 cash on hand. HINT: To find goal seek, click on the Data tab and under Data Tools click on the What-If Analysis. If you are using an old version of Excel, you can find goal seek by going to the Tools menu. Enter your answer on the Goal Seek tab and then change the cost per pound of raw materials back to $0.75 on the INPUTS tab. 4. After comparing your file against the project rubric below, upload your completed Excel file to Canvas via the project link to submit. You may only submit once so make sure this is your final version