60.1K

Verified Solution

Link Copied!

Please assume all data is correct. Need questions 6, 8 & 9 (highlighted). Please show formulas .

Part 1 I have a template set up on the Budgetsolution worksheet that you should use to complete the required budgets and requirements stated below. You need to use cell references in the development of your budgets. If you type in any numbers in the solution, I will take off 10 pts., since we use Excel so that we can update budgets or do what if analysis without retyping numbers. You should use this worksheet as your data field and only use cell references and formulas in your budgets. Your grade will be based on accuracy of your solution and correct usage of excel. The budget worksheet has formatted budgets for you to complete. The beauty behind excel is that managers can perform what-if analysis just by changing the data, so you do not need to retype the budgets if you have used cell references and formulas throughout. Data Scenario: You have just been hired into a management position which requires the application of your budgeting skills. You find out that budgeting has not been a priority of the company. You have contacted various areas on the organization and have accumulated the information below to assist you in preparing a comprehensive budget. Manufacturing Inc. produces a part used in the production of engines. Actual Sales and Projected sales in units: March (Actual) 38,000 April 44,000 May 45,000 June 50,000 July 52,000 Sales are the following type: 56% Cash sales collected in month of sale 44% Credit sales collected in the following month of sale The following data pertains to the manufacturing process. 1. Finished goods inventory March 31st Desired ending finished goods for each month $148.71 budgeted cost to make a unit 35,200 units 80% of next month's sales volume $6 2. Direct materials used: Direct Material Per-Unit Usage Cost per Pound Metal 8 pounds The beginning balance of each month needs to be able to produce Beginning material in pounds as of April 1st Direct materials paid in month purchased. 50% of that month's estimated sales volume 176,000 4 hours $12.00 per hour 3. The direct labor used per unit Direct labor paid in month incurred. 4. Overhead each month is estimated based on direct labor hours per variable cost. All costs that use cash are paid in month incurred. Fixed cost Variable cost Supplies $1.00 Power 0.60 Maintenance $27,000 0.40 Supervision 15,000 Depreciation 19,000 Taxes 11,000 60,000 1.10 Total $132,000 $3.10 Other 5. Monthly selling and administrative expenses are based on units sold per variable cost. All costs that use cash are paid in month incurred. Fixed cost Variable cost Salaries $40,000 Commissions $1 Depreciation 15,000 Shipping 0.7 Other 10,000 0.4 Total $65,000 $2.10 6. Unit selling price $124 per unit 7. Cash balance as of April 1st $150,000 7. Cash balance as of April 1st $150,000 Required: You must use cell references on the BudgetSolution worksheet, by referencing this worksheet that contains the data. Prepare the following second quarter budgets and answer the questions listed on the template provided on the Budget Solution Worksheet. I have adapted the budget model to meet the needs of this company. If I bolded a line item, that is a header and does not need computation on that row. Please note the quarter column is for the quarter so not all lines should be added across in the quarter column. When you have beginning and ending inventory or cash balances this is for the quarter and should be brought over to the quarter column. 1. Sales Budget per month and quarter. 2. Production Budget per month and quarter. 3. Direct materials purchase budget per month and quarter. 4. Manufacturing Cost budget per month and quarter. 5. Selling and administrative expenses budget per month and quarter. 6. Cash budget per month and quarter. 7. Based on the quarterly cash budget you prepared, provide recommendations on cash management. Your comments should be directed at management. 8. Budgeted income statement (ignore income tax) for the quarter. #9 What if the company decides to lay off one of the part-time administrative staff. The monthly salaries will be reduced by $6,000, what budgets are effected? Why? What is the New Net income(Loss) for the quarter? Part 1 Solution #1 Sales Budget Guidance: Make sure you are using cell references or formulas throughout your budgets. April Manufacturing Inc. Sales Budget For quarter ended June 30, 20XX Units Selling Price Sales May 44.000 $124 5,456,000 $ June 45,000 $124 5,580,000 $ Quarter 50.000 139,000 $124 $124 6,200,000 $ 17,236,000 $ #2 Production Budget April Sales Budget (Reference Budget 1) Plus desired ending inventory Total Inventory requirements Less: Beginning Inventory Units to be produced Manufacturing Inc. Production Budget For quarter ended June 30, 20XX | May June 44,000 45,000 36,000 40,000 80,000 85,000 35,200 36,000 44,800 49,000 Quarter 50,000 41,600 91,600 40,000 51,600 139,000 117,600 256.600 111,200 145,400 #3 Direct Material Purchases Budget April Units to be produced (Reference Budget 2) Direct Materials per unit (pounds) Production needs (pounds) Desired ending inventory (pounds) Total needs (pounds) Less: Beginning inventory (pounds) Purchases needed of Direct materials (pounds) Cost per pound Total purchases of direct materials Manufacturing Inc. Direct Material Purchases Budget For quarter ended June 30, 20XX May June 44,800 49,000 8 8 358,400 392,000 180,000 200,000 538,400 592,000 176,000 180,000 362,400 412,000 $6 $6 $2,174,400 $2,472,000 Quarter 51,600 145,400 8 8 412,800 1,163,200 208,000 588,000 620,800 1,751,200 200,000 556,000 420,800 1,195,200 $6 $6 $2,524,800 $7,171,200 #4 Manufacturing Cost Budget Manufacturing Inc. Manufacturing Cost Budget For quarter ended June 30, 20XX May June April Quarter Direct Materials: Production needs(pounds)--found on Budget 3 Cost per pound Total Cost of material issued to production 362,400 $6 $2,174,400 412,000 $6 $2,472,000 420,800 $6 $2,524,800 1,195,200 $6 $7,171,200 51,600 4 Direct Labor: Units to be produced (Reference Budget 2) Direct labor time per unit (hours) Total hours needed Cost per hour Total cost of Direct Labor 44,800 4 179,200 $12 $2,150,400 49,000 4 196.000 $12 $2,352,000 206,400 $12 $2,476,800 145,400 4 581,600 $12 $6,979,200 Manufacturing Overhead: Budgeted direct labor hours needed (Reference Row 51 above) Variable overhead rate Budgeted variable overhead Budgeted Fixed overhead Total Manufacturing Overhead Total Manufacturing Cost 179,200 3.10 $555,520 132,000 $687,520 $2,837,920 196,000 3.10 $607,600 132,000 $739,600 $3,091,600 206,400 3.10 $639,840 132,000 $771,840 $3,248,640 581,600 3.10 $1,802,960 396,000 $2,198,960 $9,178,160 #5 Selling and Administrative Expenses Budget April Quarter Budgeted Sales in units (Reference Budget 1) Variable Selling and Administrative expenses per unit Total variable expenses Fixed Selling and Administrative expenses Total selling and administrative expenses Manufacturing Inc. Selling and Administrative Expenses Budget For quarter ended June 30, 20XX May June 44,000 45,000 50,000 $2.10 $2.10 $2.10 $92,400 $94,500 $105,000 65,000 65,000 65,000 $157,400 $159,500 $170,000 139,000 $2.10 $291,900 195,000 $486,900 #6 Cash Budget Manufacturing Inc. Cash Budget For quarter ended June 30, 20XX May June $150,000 April Quarter Beginning cash Add Cash Collections of Sales: Cash sales collected in month sale Credit sales Collected in following month Total Cash receipts 56% 44% Cash Available Less Cash Disbursements: Purchases (Budget 3) Direct Labor (Budget 4) Overhead that uses cash Selling and administrative expenses that use cash Total Cash Disbursements Ending Cash #7 Based on the quarterly cash budget you prepared, provide recommendations on cash management. Your comments should be directed at management As we can see, the the cash is comming to be negative in the months of May and June, which is not a good indication. The management should follow the following steps (i). Modify the payment terms. The management should try to collect the sales faster and should try to shorten the payment terms. m). The management should try to cut expenses. The management should find new ways to operate the business, with fewer business. Find out ways to reduce the expenses and by contacting new suppliers which can give better deals. (iii). The management should try to increase sales. (iv). The management can take working capital loan, in order to infuse cash in the business. #8 Budgeted Income statement for Second quarter Manufacturing Inc. Budgeted Income Statement For the Secord Quarter ended June 30, 20XX $ 17,236,000 $9,178,160 Sales (Budget #1) Cost of Goods Sold: Beginning Finished Goods Total Manufacturing Costs (Budget #4) Cost of Goods Available for Sale Ending finished goods Budgeted Cost of Goods Sold Gross Profit Less: Selling and administrative expenses (Budget 5) Income before income taxes 486,900 #9 What if the company decides to lay off one of the part-time administrative staff. The monthly salaries will be reduced by $6,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 data sheet. Please change the salary back to the original amount of before you submit. Part 1 I have a template set up on the Budgetsolution worksheet that you should use to complete the required budgets and requirements stated below. You need to use cell references in the development of your budgets. If you type in any numbers in the solution, I will take off 10 pts., since we use Excel so that we can update budgets or do what if analysis without retyping numbers. You should use this worksheet as your data field and only use cell references and formulas in your budgets. Your grade will be based on accuracy of your solution and correct usage of excel. The budget worksheet has formatted budgets for you to complete. The beauty behind excel is that managers can perform what-if analysis just by changing the data, so you do not need to retype the budgets if you have used cell references and formulas throughout. Data Scenario: You have just been hired into a management position which requires the application of your budgeting skills. You find out that budgeting has not been a priority of the company. You have contacted various areas on the organization and have accumulated the information below to assist you in preparing a comprehensive budget. Manufacturing Inc. produces a part used in the production of engines. Actual Sales and Projected sales in units: March (Actual) 38,000 April 44,000 May 45,000 June 50,000 July 52,000 Sales are the following type: 56% Cash sales collected in month of sale 44% Credit sales collected in the following month of sale The following data pertains to the manufacturing process. 1. Finished goods inventory March 31st Desired ending finished goods for each month $148.71 budgeted cost to make a unit 35,200 units 80% of next month's sales volume $6 2. Direct materials used: Direct Material Per-Unit Usage Cost per Pound Metal 8 pounds The beginning balance of each month needs to be able to produce Beginning material in pounds as of April 1st Direct materials paid in month purchased. 50% of that month's estimated sales volume 176,000 4 hours $12.00 per hour 3. The direct labor used per unit Direct labor paid in month incurred. 4. Overhead each month is estimated based on direct labor hours per variable cost. All costs that use cash are paid in month incurred. Fixed cost Variable cost Supplies $1.00 Power 0.60 Maintenance $27,000 0.40 Supervision 15,000 Depreciation 19,000 Taxes 11,000 60,000 1.10 Total $132,000 $3.10 Other 5. Monthly selling and administrative expenses are based on units sold per variable cost. All costs that use cash are paid in month incurred. Fixed cost Variable cost Salaries $40,000 Commissions $1 Depreciation 15,000 Shipping 0.7 Other 10,000 0.4 Total $65,000 $2.10 6. Unit selling price $124 per unit 7. Cash balance as of April 1st $150,000 7. Cash balance as of April 1st $150,000 Required: You must use cell references on the BudgetSolution worksheet, by referencing this worksheet that contains the data. Prepare the following second quarter budgets and answer the questions listed on the template provided on the Budget Solution Worksheet. I have adapted the budget model to meet the needs of this company. If I bolded a line item, that is a header and does not need computation on that row. Please note the quarter column is for the quarter so not all lines should be added across in the quarter column. When you have beginning and ending inventory or cash balances this is for the quarter and should be brought over to the quarter column. 1. Sales Budget per month and quarter. 2. Production Budget per month and quarter. 3. Direct materials purchase budget per month and quarter. 4. Manufacturing Cost budget per month and quarter. 5. Selling and administrative expenses budget per month and quarter. 6. Cash budget per month and quarter. 7. Based on the quarterly cash budget you prepared, provide recommendations on cash management. Your comments should be directed at management. 8. Budgeted income statement (ignore income tax) for the quarter. #9 What if the company decides to lay off one of the part-time administrative staff. The monthly salaries will be reduced by $6,000, what budgets are effected? Why? What is the New Net income(Loss) for the quarter? Part 1 Solution #1 Sales Budget Guidance: Make sure you are using cell references or formulas throughout your budgets. April Manufacturing Inc. Sales Budget For quarter ended June 30, 20XX Units Selling Price Sales May 44.000 $124 5,456,000 $ June 45,000 $124 5,580,000 $ Quarter 50.000 139,000 $124 $124 6,200,000 $ 17,236,000 $ #2 Production Budget April Sales Budget (Reference Budget 1) Plus desired ending inventory Total Inventory requirements Less: Beginning Inventory Units to be produced Manufacturing Inc. Production Budget For quarter ended June 30, 20XX | May June 44,000 45,000 36,000 40,000 80,000 85,000 35,200 36,000 44,800 49,000 Quarter 50,000 41,600 91,600 40,000 51,600 139,000 117,600 256.600 111,200 145,400 #3 Direct Material Purchases Budget April Units to be produced (Reference Budget 2) Direct Materials per unit (pounds) Production needs (pounds) Desired ending inventory (pounds) Total needs (pounds) Less: Beginning inventory (pounds) Purchases needed of Direct materials (pounds) Cost per pound Total purchases of direct materials Manufacturing Inc. Direct Material Purchases Budget For quarter ended June 30, 20XX May June 44,800 49,000 8 8 358,400 392,000 180,000 200,000 538,400 592,000 176,000 180,000 362,400 412,000 $6 $6 $2,174,400 $2,472,000 Quarter 51,600 145,400 8 8 412,800 1,163,200 208,000 588,000 620,800 1,751,200 200,000 556,000 420,800 1,195,200 $6 $6 $2,524,800 $7,171,200 #4 Manufacturing Cost Budget Manufacturing Inc. Manufacturing Cost Budget For quarter ended June 30, 20XX May June April Quarter Direct Materials: Production needs(pounds)--found on Budget 3 Cost per pound Total Cost of material issued to production 362,400 $6 $2,174,400 412,000 $6 $2,472,000 420,800 $6 $2,524,800 1,195,200 $6 $7,171,200 51,600 4 Direct Labor: Units to be produced (Reference Budget 2) Direct labor time per unit (hours) Total hours needed Cost per hour Total cost of Direct Labor 44,800 4 179,200 $12 $2,150,400 49,000 4 196.000 $12 $2,352,000 206,400 $12 $2,476,800 145,400 4 581,600 $12 $6,979,200 Manufacturing Overhead: Budgeted direct labor hours needed (Reference Row 51 above) Variable overhead rate Budgeted variable overhead Budgeted Fixed overhead Total Manufacturing Overhead Total Manufacturing Cost 179,200 3.10 $555,520 132,000 $687,520 $2,837,920 196,000 3.10 $607,600 132,000 $739,600 $3,091,600 206,400 3.10 $639,840 132,000 $771,840 $3,248,640 581,600 3.10 $1,802,960 396,000 $2,198,960 $9,178,160 #5 Selling and Administrative Expenses Budget April Quarter Budgeted Sales in units (Reference Budget 1) Variable Selling and Administrative expenses per unit Total variable expenses Fixed Selling and Administrative expenses Total selling and administrative expenses Manufacturing Inc. Selling and Administrative Expenses Budget For quarter ended June 30, 20XX May June 44,000 45,000 50,000 $2.10 $2.10 $2.10 $92,400 $94,500 $105,000 65,000 65,000 65,000 $157,400 $159,500 $170,000 139,000 $2.10 $291,900 195,000 $486,900 #6 Cash Budget Manufacturing Inc. Cash Budget For quarter ended June 30, 20XX May June $150,000 April Quarter Beginning cash Add Cash Collections of Sales: Cash sales collected in month sale Credit sales Collected in following month Total Cash receipts 56% 44% Cash Available Less Cash Disbursements: Purchases (Budget 3) Direct Labor (Budget 4) Overhead that uses cash Selling and administrative expenses that use cash Total Cash Disbursements Ending Cash #7 Based on the quarterly cash budget you prepared, provide recommendations on cash management. Your comments should be directed at management As we can see, the the cash is comming to be negative in the months of May and June, which is not a good indication. The management should follow the following steps (i). Modify the payment terms. The management should try to collect the sales faster and should try to shorten the payment terms. m). The management should try to cut expenses. The management should find new ways to operate the business, with fewer business. Find out ways to reduce the expenses and by contacting new suppliers which can give better deals. (iii). The management should try to increase sales. (iv). The management can take working capital loan, in order to infuse cash in the business. #8 Budgeted Income statement for Second quarter Manufacturing Inc. Budgeted Income Statement For the Secord Quarter ended June 30, 20XX $ 17,236,000 $9,178,160 Sales (Budget #1) Cost of Goods Sold: Beginning Finished Goods Total Manufacturing Costs (Budget #4) Cost of Goods Available for Sale Ending finished goods Budgeted Cost of Goods Sold Gross Profit Less: Selling and administrative expenses (Budget 5) Income before income taxes 486,900 #9 What if the company decides to lay off one of the part-time administrative staff. The monthly salaries will be reduced by $6,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 data sheet. Please change the salary back to the original amount of before you submit

Answer & Explanation Solved by verified expert

See Answer