lengthy but please help me!!

X V 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 en Data Scenario: 10 11 You have just been hired into a management position which requires the application of your budgeting skills 12 You find out that budgeting has not been a priority of the company 13 You have contacted various areas on the organization and have accumulated the information below to assist you 14 in preparing a comprehensive budget 15 16 Manufacturing Inc. produces a part used in the production of engines 17 Actual Sales and Projected sales in units 18 March (Actual) 38,000 19 April 44,000 45,000 50,000 52,000 21 June July 23 55% Cash sales collected in month of sale 45% Credit sales collected in the following month of sale 241 Sales are the following type: 27 The following data pertains to the manufacturing process 28 1. Finished goods inventory 29 Desired ending finished goods for each month $148.71 budgeted cost to make a unit March 31st 35,200 units 80% of next month's sales volume 31 2. Direct materials used: Per-Unit Usage Cost per Pound 10 pounds S8 33 Metal 50% of that month's estimated sales volume 220,000 34 The beginning balance of each month needs to be able to produce Beginning material in pounds as of April 1st 36 Direct materials paid in month purchased. Parti Search the web and Windows hp $13.00 per hour 8 3. The direct labor used per unit Direct labor paid in month incurred. 4 hours All costs that use cash are paid in month incurred. 14. Overhead each month is estimated based on direct labor hours per variable cost. Fixed costVariable cost $1.00 0.50 0.40 Supplies 4 Power s Maintenance 6 Supervision Depreciation 8 Taxes 9 Other $28,000 16,000 20,000 12,000 80,000 $156,000 1.10 o Total $3.00 2 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 $30,000 Variable cost 5 Commissions 6 Depreciation 7 Shipping 10,000 0.6 0.4 $2.00 20,000 $60,000 8 Other 9 Total 6. Unit selling price $165 per unit 3 7. Cash balance as of April $120,000 Required: You must use cell references on the BudgetSolution worksheet, by referencing this worksheet that contains the data. arch the web and r the questions listed on the template provided on the the budget model to meet the needs of this company 69 1 72 4 turing Cost budget per month and quarter ignore income tax) for the quarter 8 What is the New Net income(Loss) for the quarter? - Table X f W1 Sales Budget Part 1 Solution #1 Sales Budget Manufacturing Inc. Sales Budget For quarter ended June 30, 20xX cel references or forrmuls your budgets. Quarter 44,000 $165 7.260,000 #2 Production Budget Manufacturing Inc Production Budget For quarter ended June 30, 20xX May April Quarter 18 Plus desired ending inventory 19 Total Inventory requirements ) Less: Beginning Inventory 21 Units to be produced Manufacturing Inc. Direct Material Purchases Budget For quarter ended June 30, 20XX May June Quarter April Units to be produced (Reference Budget 2) Direct Materials per unit (pounds) 0 Production needs (pounds) 1 Desired ending inventory (pounds) Total needs (pounds) Less: Be ginning invent s needed of Direct materials (pounds) Cost per pound Total purchases of direct materials 38 #4 Manufacturing Cost Budget Manufacturing Inc. Manufacturing Cost Budget For quarter ended June 30, 20XX May Quarter April June 44 Production needs(pounds)--found on Budget 3 15 Cost per pound 6 Total Cost of material issued to production 8 Direct Labor: o Unts to be produced (Reference Budget 2)) 50 Direct labor time per unit (hours) 51 Total hours needed 52 Cost per hour 53 Total cost of Direct Labor Manufacturing Overhead s6 Budgeted direct labor hours needed (Reference Row 51 above 57 Variable overhead rate 58 Budgeted variable overhead Budgeted Fixed overhead 60 Total Manufacturing Overhead 61 Total Manufacturing Cost 63#5 Selling and Administrative Expenses Budget Manufacturing Inc. Selling and Administrative Expenses Budget For quarter ended June 30, 20XX May 65 June Apri 68 Budgeted Sales in units (Reference Budget 1) 69 Variable Selling and Administrative expenses per unit 70 Total variable expenses 71 Fixed Selling and Administrative expenses 72 Total selling and administrative expenses Cash Budget #6 Manufacturing Inc For quarter ended June 30, 20xX au May June April 9Beginning cash 0 Add Cash Collections of Sales: 31 Cash sales collected in month sale 2 Credit sales Collected in following month 3 Total Cash receipts s Cash Available Purchases (Budget 3) 8 Direct Labor (Budget 4) Selling and administrative expenses that use cash 2 Ending Cash 6 7 Based on the quarterly cash budget you prepared, provide recommendations on cash management. Your comments should be directed at management 1 #8 Budgeted Income statement for Second quarter Manufacturing Inc For the Secord Quarter ended June 30,20xX Sales (Budget #1) Cost of Goods Sold: Beginning Finis 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 #9 What if the company decides to lay off one of the administrative staff. The monthly salaries will be reduced by $5,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