As you prepare your solution on the budget worksheet make sure you are reviewing Chapter 8 that provides excellent exhibits, the MBC videos, and the videos in the Chapter 8 support folder. Data Scenario: You have just been hired into an accounting position which requires the application of your budgeting skills. You find out that budgeting has not been a priority of the company and that they have been experiencing cash shortages. You have contacted various areas on the organization and have accumulated the information below to assist you in preparing a master budget. Manufacturing Inc. produces a part used in the production of engines. Actual Sales and Projected sales in units: March (Actual) 38,000 April 40,000 May 50,000 June 60,000 July 65,000 Sales are the following type: 59% Cash sales collected in month of sale 41% Credit sales collected in the following month of sale The following data pertains to the manufacturing process. 1. Finished goods inventory Desired ending finished goods for each month 2. Direct materials used: March 31st Direct Material Metal Per-Unit Usage 10 pounds 32,000 units $148.00 budgeted cost to make a unit 80% of next month's sales volume Cost per Pound $8 50% of that month's estimated sales volume 200,000 4 hours $15.00 per hour The beginning balance of each month needs to be able to produce Beginning material in pounds as of April 1st 3. The direct labor used per unit 4. Overhead each month is estimated based on direct labor hours per variable cost. All costs that use cash are paid in month incurred. Variable cost Fixed cost Supplies Power Maintenance Supervision $25,000 35,000 $1.00 0.60 0.40 Supervision Depreciation Taxes Total 35,000 80,000 11,000 $151,000 $2.00 5. Monthly selling and administrative expenses are based on units sold per variable cost. All costs that use cash are paid in month incurred. Salaries Commissions Depreciation Shipping Total 6. Unit selling price Fixed cost Variable cost $50,000 30,000 $80,000 $2 $1 $3.00 $190 per unit 7. Cash balance as of April $200,000 Required: Prepare the following 2nd quarter budgets on the budget worksheet, which contains a template of budgets you should use. I have adapted the budget model to meet the needs of this company, so the format might vary from the textbook. Please remember the textbook is a wonderful aid, but real-life is not a textbook, so you must be able to adapt. Use the instructions worksheet as your reference worksheet. The budget worksheet must only be cell references and formulas. 1. Sales Budget by month and quarter. 2. Production Budget by month and quarter. 3. Direct materials purchase budget by month and quarter. 4. Direct labor budget by month and quarter. 5. Overhead budget by month and quarter. 6. Selling and administrative expenses budget by month and quarter. 7. Cash collections budget by month and quarter. 8. Cash budget by month and quarter. 9. Based on the quarterly cash budget you prepared, do you have any recommendations on cash management? 10. Cost of goods sold budget for the quarter. 11. Budgeted income statement (ignore income tax) for the quarter. 12. What if the company decides to lay off one of the administrative staff. The salaries will be reduced by $6,000 per month, what budgets are effected? Why? What is the New Income before income taxes for the quarter? Make sure you are reviewing the exhibits in Chapter 8, the review videos in MBC, and the videos in the chapter 8 support folder. Manufacturing Inc. For quarter ended June 30, 20XX Guidance: Make sure you are using cell references or formulas throughout your budgets. # 1 Schedule 1: Sales Budget April May Units Selling Price Sales $ 40,000 $190 7,600,000 #2 Schedule 2: Production Budget April Sales in units (Schedule 1-row 6) Desired ending inventory Total Needs Less: Beginning Inventory Units to be produced #3 Schedule 3: Direct Material Purchases Budget Units to be produced (Schedule 2-row 16) Direct Materials per unit (pounds) Production needs Desired ending inventory Total needs Less: Beginning inventory Direct materials to be purchased Cost per pound Total Cost June Quarter May June Quarter April May June Quarter #4 Schedule 4: Direct Labor Budget April May June Quarter Units to be produced (from Schedule 2-row 16) Direct labor time per unit (hours) Total hours needed Cost per hour Total cost #5 Schedule 5: Overhead Budget Budgeted direct labor hours needed (Schedule 4-row 34) Variable overhead rate Budgeted variable overhead Budgeted Fixed overhead Total overhead April May June Quarter #6 Schedule 6: Selling and Administrative Expenses Budget April May June Quarter Planned Sales in units (Schedule 1-row 6) 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 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 in dollars (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 $6,000 per month. What budgets are effected? Why? What is the New Income before income taxes 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