F G H 1 3 2 Name Box into an accounting position which requires the application of your budgeting skils. www.ing has not been a priority of the company and that they have been experiencing cash shortages. 4 You have contacted various areas on the organization and have accumulated the information below to assist you 5 in preparing a master budget. 6 7 Manufacturing Inc. produces a part used in the production of engines. 8 Actual Sales and Projected sales in units: 9 March (Actual) 38,000 10 April 40,000 11 May 50,000 12 June 60,000 13 July 65,000 14 15 Sales are the following type: 60% Cash sales colected in month of sale 16 40% Credit sales collected in the following month of sale 17 18 The folowing data pertains to the manufacturing process 19 1. Finished goods inventory March 31st 32,000 units $148.71 budgeted cost to make a unit 20 Desired ending finished goods for each month 80% of next month's sales volume 21 22 2. Direct materials used: 23 Direct Material Per-Unit Usage Cost per Pound 24 Metal 11 pounds $8 25 The beginning balance of each month needs to be able to produce 50% of that month's estimated sales volume 26 Beginning material in pounds as of April 1st 220,000 27 28 3. The direct labor used per unit 4 hours $9.25 per hour $ 29 30 4. Overhead each month is estimated based on direct labor hours per variable cost. All costs that use cash are paid in month incurred. . 31 Fixed cost Variable cost 32 Supplies $1.00 33 Power 0.60 34 Maintenance $28,000 0.50 35 Supervision 40.000 36 Depreciation 200,000 37 Taxes 12,000 38 Total $280,000 $2.10 39 40 5. Monthly selling and administrative expenses are based on units sold per variable cost. Al costs that use cash are paid in month incurred 41 Fixed cost Variable cost 42 Salaries $70,000 43 Commissions $2 44 Depreciation 30,000 45 Shipping $1 46 Total $100,000 $3.00 47 48 6. Unit seling price $174 per unit 49 50 7. Cash balance as of April 1st $175,000 51 Required: Prepare the following 2nd quarter budgets on the budget worksheet, which contains a template of budgets you should use. I 52 have adapted the budget model to meet the needs of this company, so the format might vary from the textbook. 53 Please remember the textbook is a wonderful aid, but real-Ife is not a textbook. so vou_ust be able to adapt. 54 Use the instructions worksheet as your reference worksheet. The budget worksheet must only be cell references and formulas. 55 1. Sales Budget and cash collection of sales for month and quarter. 56 2. Production Budget for month and quarter. 57 3. Direct materials purchase budget for month and quarter. 58 4. Direct labor budget for month and quarter. 59 5. Overhead budget for month and quarter, 60 6. Seling and administrative expenses budget for month and quarter. 61 7. Cash budget for month and quarter. 62 8. Based on the quarterly cash budget you prepared, do you have any recommendations 63 on cash management. 64 9. Cost of goods sold budget for the quarter. 65 10. Budgeted income statement (ignore income tax) for the quarter. 66 11. What the company decides to lay off one of the administrative staff. The salaries wil be reduced by $10,000, what budgets are effected? Why? 67 What is the New Net income(Loss) for the quarter? 68 69 70 71 72 references or formulas throughout your budgets. For quarter ended June 30, 20XX #1 Schedule 1: Sales Budget April June Quarter May 40,000 Units Selling Price Sales #2 Schedule 2: Production Budget April May June Quarter Sales(Schedule 1) Desired ending inventory Total Needs Less: Beginning Inventory Units to be produced #3 Schedule 3: Direct Material Purchases Budget April May June Quarter Units to be produced (Schedule 2) Direct Materials per unit (pounds) Production needs Desired ending inventory Total needs Less: Beginning inventory Direct materials to be ased Cost per pound Total Cost #4 Schedule 4: Direct Labor Budget April May June Quarter Units to be produced (from Schedule 2) Direct labor time per unit (hours) Total hours needed Cost per hour Total cost #5 Schedule 5: Overhead Budget April May June Quarter Budgeted direct labor hours needed (Schedule 4) Variable overhead rate Budgeted variable overhead Budgeted Fixed overhead Total overhead #6 Schedule 6: Selling and Administrative Expenses Budget April May June Quarter Planned Sales (Schedule 1) Variable Selling and Administrative expenses per unit Total variable expenses Fixed Selling and Administrative expenses Total selling and administrative expenses #7 Schedule 7: Cash Collections April May June Quarter Cash sales collected in month sale Credit sales Collected in following month Total Cash receipts #8 Schedule 8: Cash Budget April | May June Quarter Beginning cash Cash Receipts Cash Available Less Cash Disbursements: Purchases (Schedule 3) Direct Labor (Schedule 4) Overhead that uses cash Selling and administrative expenses that use cash Total Cash Disbursements Ending Cash 9. Based on the quarterly cash budget you prepared, do you have any recommendations on cash management. #10 Schedule 10: Quarterly Cost of Goods Sold Budget Direct material used (Schedule 3-production needs cost of material) Direct labor used (Schedule 4) Overhead (Schedule 5) Budgeted manufacturing costs Add: Beginning Finished Goods Goods available for sale Less: Ending finished goods Budgeted Cost of Goods Sold #11 Schedule 11: Quarterly Budgeted Income statement Sales (Schedule 1) Less: Cost of Goods Sold (Schedule 10) Gross Margin Less: Selling and administrative expenses (Schedule 6) Income before income taxes 12. What if the company decides to lay off one of the administrative staff. The salaries will be reduced by $10,000, what budgets are effected? Why? What is the New Net income(Loss) for the quarter? If you have linked everything correctly, you should only have to change the monthly salary on the Instructions worksheet. Please change the salary back to the original amount of before you submit. F G H 1 3 2 Name Box into an accounting position which requires the application of your budgeting skils. www.ing has not been a priority of the company and that they have been experiencing cash shortages. 4 You have contacted various areas on the organization and have accumulated the information below to assist you 5 in preparing a master budget. 6 7 Manufacturing Inc. produces a part used in the production of engines. 8 Actual Sales and Projected sales in units: 9 March (Actual) 38,000 10 April 40,000 11 May 50,000 12 June 60,000 13 July 65,000 14 15 Sales are the following type: 60% Cash sales colected in month of sale 16 40% Credit sales collected in the following month of sale 17 18 The folowing data pertains to the manufacturing process 19 1. Finished goods inventory March 31st 32,000 units $148.71 budgeted cost to make a unit 20 Desired ending finished goods for each month 80% of next month's sales volume 21 22 2. Direct materials used: 23 Direct Material Per-Unit Usage Cost per Pound 24 Metal 11 pounds $8 25 The beginning balance of each month needs to be able to produce 50% of that month's estimated sales volume 26 Beginning material in pounds as of April 1st 220,000 27 28 3. The direct labor used per unit 4 hours $9.25 per hour $ 29 30 4. Overhead each month is estimated based on direct labor hours per variable cost. All costs that use cash are paid in month incurred. . 31 Fixed cost Variable cost 32 Supplies $1.00 33 Power 0.60 34 Maintenance $28,000 0.50 35 Supervision 40.000 36 Depreciation 200,000 37 Taxes 12,000 38 Total $280,000 $2.10 39 40 5. Monthly selling and administrative expenses are based on units sold per variable cost. Al costs that use cash are paid in month incurred 41 Fixed cost Variable cost 42 Salaries $70,000 43 Commissions $2 44 Depreciation 30,000 45 Shipping $1 46 Total $100,000 $3.00 47 48 6. Unit seling price $174 per unit 49 50 7. Cash balance as of April 1st $175,000 51 Required: Prepare the following 2nd quarter budgets on the budget worksheet, which contains a template of budgets you should use. I 52 have adapted the budget model to meet the needs of this company, so the format might vary from the textbook. 53 Please remember the textbook is a wonderful aid, but real-Ife is not a textbook. so vou_ust be able to adapt. 54 Use the instructions worksheet as your reference worksheet. The budget worksheet must only be cell references and formulas. 55 1. Sales Budget and cash collection of sales for month and quarter. 56 2. Production Budget for month and quarter. 57 3. Direct materials purchase budget for month and quarter. 58 4. Direct labor budget for month and quarter. 59 5. Overhead budget for month and quarter, 60 6. Seling and administrative expenses budget for month and quarter. 61 7. Cash budget for month and quarter. 62 8. Based on the quarterly cash budget you prepared, do you have any recommendations 63 on cash management. 64 9. Cost of goods sold budget for the quarter. 65 10. Budgeted income statement (ignore income tax) for the quarter. 66 11. What the company decides to lay off one of the administrative staff. The salaries wil be reduced by $10,000, what budgets are effected? Why? 67 What is the New Net income(Loss) for the quarter? 68 69 70 71 72 references or formulas throughout your budgets. For quarter ended June 30, 20XX #1 Schedule 1: Sales Budget April June Quarter May 40,000 Units Selling Price Sales #2 Schedule 2: Production Budget April May June Quarter Sales(Schedule 1) Desired ending inventory Total Needs Less: Beginning Inventory Units to be produced #3 Schedule 3: Direct Material Purchases Budget April May June Quarter Units to be produced (Schedule 2) Direct Materials per unit (pounds) Production needs Desired ending inventory Total needs Less: Beginning inventory Direct materials to be ased Cost per pound Total Cost #4 Schedule 4: Direct Labor Budget April May June Quarter Units to be produced (from Schedule 2) Direct labor time per unit (hours) Total hours needed Cost per hour Total cost #5 Schedule 5: Overhead Budget April May June Quarter Budgeted direct labor hours needed (Schedule 4) Variable overhead rate Budgeted variable overhead Budgeted Fixed overhead Total overhead #6 Schedule 6: Selling and Administrative Expenses Budget April May June Quarter Planned Sales (Schedule 1) Variable Selling and Administrative expenses per unit Total variable expenses Fixed Selling and Administrative expenses Total selling and administrative expenses #7 Schedule 7: Cash Collections April May June Quarter Cash sales collected in month sale Credit sales Collected in following month Total Cash receipts #8 Schedule 8: Cash Budget April | May June Quarter Beginning cash Cash Receipts Cash Available Less Cash Disbursements: Purchases (Schedule 3) Direct Labor (Schedule 4) Overhead that uses cash Selling and administrative expenses that use cash Total Cash Disbursements Ending Cash 9. Based on the quarterly cash budget you prepared, do you have any recommendations on cash management. #10 Schedule 10: Quarterly Cost of Goods Sold Budget Direct material used (Schedule 3-production needs cost of material) Direct labor used (Schedule 4) Overhead (Schedule 5) Budgeted manufacturing costs Add: Beginning Finished Goods Goods available for sale Less: Ending finished goods Budgeted Cost of Goods Sold #11 Schedule 11: Quarterly Budgeted Income statement Sales (Schedule 1) Less: Cost of Goods Sold (Schedule 10) Gross Margin Less: Selling and administrative expenses (Schedule 6) Income before income taxes 12. What if the company decides to lay off one of the administrative staff. The salaries will be reduced by $10,000, what budgets are effected? Why? What is the New Net income(Loss) for the quarter? If you have linked everything correctly, you should only have to change the monthly salary on the Instructions worksheet. Please change the salary back to the original amount of before you submit