please help with question 4 and 5, please show cell reference

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. 4 10 11 12 11 14 15 16 17 18 19 20 21 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 23 M 2s Sales are the following type: 56% Cash sales collected in month of sale 44% Credit sales collected in the following month of sale 27 29 29 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 31 32 31 S6 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 36 38 3. The direct labor used per unit 4 hours $12.00 per hour 39 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. 42 Fixed cost Variable cost 43 Supplies $1.00 44 Power 0.60 45 Maintenance $27,000 0.40 16 Supervision 15,000 47 Depreciation 19,000 Taxes 11,000 49 Other 60,000 1.10 Total $132.000 $3.10 51 52 5. Monthly selling and administrative expenses are based on units sold per variable cost. All costs that use cash are paid in month incurred. 53 Fixed cost Variable cost 54 Salaries $40,000 55 Commissions S1 56 Depreciation 15,000 57 Shipping 0.7 Other 10,000 0.4 Total $65.000 $2.10 59 51 6. Unit selling price $124 per unit 7. Cash balance as of April 1st $150,000 55 57 50 70 71 72 73 Required: You must use cell references on the Budget Solution 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 38 #4 Manufacturing Cost Budget 39 40 Manufacturing Inc. Manufacturing Cost Budget For quarter ended June 30, 20XX May June 41 42 April Quarter 43 44 Direct Materials: Production needs(pounds)--found on Budget 3 Cost per pound Total Cost of material issued to production 45 46 47 48 49 50 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 51 52 53 54 55 56 57 58 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 59 60 61 62 63 64 #5 Selling and Administrative Expenses Budget 65 Manufacturing Inc. Selling and Administrative Expenses Budget For quarter ended June 30, 20XX May June 66 67 April Quarter 68 69 70 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 71 72 73