Objective: The purpose of this assignment is to let you see the complexity of budgeting and develop your spreadsheet skills. Case: ChocoLasting Chocolate Company is a small manufacturin 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 o 75 o 25 % of sales are collected in the month of sale; % are collected in the month following The company desires to have finished goods inventory on hand the 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 S0.75 per pound. The company's payment pattern is o 70% of the month's purchases are paid for in the month of purchase; .30% is paid for in the following month. Each unit requires 6 minutes of labor time to make (0.1 hours) a ages are paid in the the hourly employees are paid S15/hour. W month incurred Each unit requires 6 minutes of labor time to make (0.1 hours) and the hourly employees are paid S1S/hour. Wages are paid in the month incurred. Variable manufacturing overhead is S3.00 per labor hour. Fixed manufacturing overhead is $28,000 per month including All cash disbursements for manufacturing overhead are paid in th Variable selling and administrative expenses are S1.50 per unit so $8,000 in depreciation that is not a current cash outflow. month incurred. Fixed selling and administrative expense is $25,000 per month including $7,000 in depreciation that is not a cash outflow of the - All cash disbursements for selling and administrative costs are paid ChocoLasting purchased a new piece of equipment at the end of ChocoLasting borrowed S100,000 on September 30th as they The Company uses variable costing in its budgeted income statement Additionally, ChocoLasting has the following balance sheet as of current month. in the month incurred December on the 31. The cquipment cost $35,700 normally need a lot of working capital to support the winter sales. Interest payments of S625 are made on the last day of each month. and its balance sheet. September 30, 2019: ChocoLasting Chocolate Company Balance Sheet As of September 30, 2019 LLABILI I IES & EQ ITIES Roceivable s Payable Raw Malcrials Invenly $2160C Finished Goods Inventory $17.550 TOTAL LIABILITIES P&E, ne Payable 190.000 Common Siock 580 IOIALLIABILITES & TOTAL ASSETS 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. 1:23 ACCT 285 Excel Project Instructions-... 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 ravw 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 INPUTS FG Inventory Cost per Unit Sales Budget Expected Cash Collections Production Bu Spring 2019 Excel Spreadsheet Project INPUTS Name: PROJECTED BUDGETING DATA 2019 2020 ChocoLasting Choco October November December January February Balance S Sales: As of September Unit 130,000 31.800 21.600 17,550 190,000 Selling Pric sh Accounts Receivable Raw Materials Invenlory Finished Goods Invent Collections: Collected in Following Month Finished Goods Inventory: Ending FG Inventory Roquiremet of next month's unit sales TOTAL ASSETS 5390,750 Raw Materials Inventory: pounds pounds $0.75 per pound aw Materials Cost per Poand Ending RM Inventory Requirement Paid in Month of Purchase of next month's production needs Direct Labor: hours per hour per labor hour per month per unit sold per month Manfacturing Overhead: 28,000 per month Selling & Administrative Expenses: ed SKA Cash Borrowings: Other: 25,000 per mont per mont rchased on December 31, 2019 ed Cash Collections Production Budget RM Budget DL Budget OH Budget S&A Budget Cash B ChocoLasting Chocolate Company Balance Sheet As of September 30, 2019 LIARILITIES &EQUITIES Accounts Payable ash Accounts Receivable 31,600 Notes Payable $21,600 $17,550 $190,000 Interest Payable TOTAL LIABILITIES Common Stock RetainedE TOTAL EQUITIES Finished Goods Inventory S262.5 TOTAL LIARILITIES EQUITIES TOTAL ASSETS 5390,750 $390,750 Objective: The purpose of this assignment is to let you see the complexity of budgeting and to develop your spreadsheet skills. Case: ChocoLasting Chocolate Company is a small manufacturing company that manufactur 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 c ocoa powder is sold in five pound containers, alternatively referred to as units. Budgeted sales in units are as follows: 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 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 . 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 orn 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, 30% is paid for in the following month. o 0 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 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 TOTAL LIABILITIES Common Stock Retained Earnings TOTAL EQUITIES TOTAL LIABILITIES & EQUITIES $28,200 $100,000 S0 $128,200 $80,000 182,550 $262,550 $390,750 TOTAL ASSETS $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 Expese 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. 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 manual antarad inte the cell eins the raferanced valae or t ly 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. Il 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 ses/58012/assignments/756713 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 INPUTS Name: PROJECTED BUDGETING DATA 2019 2020 ChocoLasting Chocolate Company Balance Sheet As of September 30, 2019 October November December January February Sales: Selling Price Cash Accounts Receivable Raw Materials Inventory Finished Goods Inventory 0,000Accounts Payable 331,400 Notes Puyaible 21,600 Interest Payable 528, 100,000 $17,550 TOTAL LUABILITIES $190,000 Common Stock Cellected in Following E, net 580, 262 -Smnd of mext month's unit sales TOTAL ASSETS 300,750 TOTAL LIABILITIES & EQUITIES G Inventory, October 1, 2019 pounds pounds Raw Materials Required per Unit of F Raw Materials Cost per Pound Ending RM inventory Requirement Paid in Month of Purchase Paid in Following Month $0.75 per pound 20% of next month's production needs ect Labor: hours Cost per Hour 28,000 per month per month Noncash Fixed OH (included in above) g& Administrative unit sold Fised 5&4 oncsh Fised S&A included in above) per menth ash Borrowings nterest Payment per month 2 per Objective: The purpose of this assignment is to let you see the complexity of budgeting and develop your spreadsheet skills. Case: ChocoLasting Chocolate Company is a small manufacturin 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 o 75 o 25 % of sales are collected in the month of sale; % are collected in the month following The company desires to have finished goods inventory on hand the 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 S0.75 per pound. The company's payment pattern is o 70% of the month's purchases are paid for in the month of purchase; .30% is paid for in the following month. Each unit requires 6 minutes of labor time to make (0.1 hours) a ages are paid in the the hourly employees are paid S15/hour. W month incurred Each unit requires 6 minutes of labor time to make (0.1 hours) and the hourly employees are paid S1S/hour. Wages are paid in the month incurred. Variable manufacturing overhead is S3.00 per labor hour. Fixed manufacturing overhead is $28,000 per month including All cash disbursements for manufacturing overhead are paid in th Variable selling and administrative expenses are S1.50 per unit so $8,000 in depreciation that is not a current cash outflow. month incurred. Fixed selling and administrative expense is $25,000 per month including $7,000 in depreciation that is not a cash outflow of the - All cash disbursements for selling and administrative costs are paid ChocoLasting purchased a new piece of equipment at the end of ChocoLasting borrowed S100,000 on September 30th as they The Company uses variable costing in its budgeted income statement Additionally, ChocoLasting has the following balance sheet as of current month. in the month incurred December on the 31. The cquipment cost $35,700 normally need a lot of working capital to support the winter sales. Interest payments of S625 are made on the last day of each month. and its balance sheet. September 30, 2019: ChocoLasting Chocolate Company Balance Sheet As of September 30, 2019 LLABILI I IES & EQ ITIES Roceivable s Payable Raw Malcrials Invenly $2160C Finished Goods Inventory $17.550 TOTAL LIABILITIES P&E, ne Payable 190.000 Common Siock 580 IOIALLIABILITES & TOTAL ASSETS 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. 1:23 ACCT 285 Excel Project Instructions-... 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 ravw 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 INPUTS FG Inventory Cost per Unit Sales Budget Expected Cash Collections Production Bu Spring 2019 Excel Spreadsheet Project INPUTS Name: PROJECTED BUDGETING DATA 2019 2020 ChocoLasting Choco October November December January February Balance S Sales: As of September Unit 130,000 31.800 21.600 17,550 190,000 Selling Pric sh Accounts Receivable Raw Materials Invenlory Finished Goods Invent Collections: Collected in Following Month Finished Goods Inventory: Ending FG Inventory Roquiremet of next month's unit sales TOTAL ASSETS 5390,750 Raw Materials Inventory: pounds pounds $0.75 per pound aw Materials Cost per Poand Ending RM Inventory Requirement Paid in Month of Purchase of next month's production needs Direct Labor: hours per hour per labor hour per month per unit sold per month Manfacturing Overhead: 28,000 per month Selling & Administrative Expenses: ed SKA Cash Borrowings: Other: 25,000 per mont per mont rchased on December 31, 2019 ed Cash Collections Production Budget RM Budget DL Budget OH Budget S&A Budget Cash B ChocoLasting Chocolate Company Balance Sheet As of September 30, 2019 LIARILITIES &EQUITIES Accounts Payable ash Accounts Receivable 31,600 Notes Payable $21,600 $17,550 $190,000 Interest Payable TOTAL LIABILITIES Common Stock RetainedE TOTAL EQUITIES Finished Goods Inventory S262.5 TOTAL LIARILITIES EQUITIES TOTAL ASSETS 5390,750 $390,750 Objective: The purpose of this assignment is to let you see the complexity of budgeting and to develop your spreadsheet skills. Case: ChocoLasting Chocolate Company is a small manufacturing company that manufactur 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 c ocoa powder is sold in five pound containers, alternatively referred to as units. Budgeted sales in units are as follows: 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 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 . 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 orn 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, 30% is paid for in the following month. o 0 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 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 TOTAL LIABILITIES Common Stock Retained Earnings TOTAL EQUITIES TOTAL LIABILITIES & EQUITIES $28,200 $100,000 S0 $128,200 $80,000 182,550 $262,550 $390,750 TOTAL ASSETS $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 Expese 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. 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 manual antarad inte the cell eins the raferanced valae or t ly 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. Il 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 ses/58012/assignments/756713 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 INPUTS Name: PROJECTED BUDGETING DATA 2019 2020 ChocoLasting Chocolate Company Balance Sheet As of September 30, 2019 October November December January February Sales: Selling Price Cash Accounts Receivable Raw Materials Inventory Finished Goods Inventory 0,000Accounts Payable 331,400 Notes Puyaible 21,600 Interest Payable 528, 100,000 $17,550 TOTAL LUABILITIES $190,000 Common Stock Cellected in Following E, net 580, 262 -Smnd of mext month's unit sales TOTAL ASSETS 300,750 TOTAL LIABILITIES & EQUITIES G Inventory, October 1, 2019 pounds pounds Raw Materials Required per Unit of F Raw Materials Cost per Pound Ending RM inventory Requirement Paid in Month of Purchase Paid in Following Month $0.75 per pound 20% of next month's production needs ect Labor: hours Cost per Hour 28,000 per month per month Noncash Fixed OH (included in above) g& Administrative unit sold Fised 5&4 oncsh Fised S&A included in above) per menth ash Borrowings nterest Payment per month 2 per