Part 1: Relates to Module 22 and I have support videos in the Module 22 Resources in the applicable weekly folder. 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. You will earn zero credit if you type in the numbers and do not use cell referencing. 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 May 40,000 June 42,000 July 45,000 44,000 Sales are the following type: 52% Cash sales collected in month of sale 48% 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 March 31st 2. Direct materials used: Direct Material Per-Unit Usage Cost per Pound Metal 8 pounds $7 Beginning material in pounds as of April 1st Direct materials paid in month purchased. 3. The direct labor used per unit Direct labor paid in month incurred. 4 hours 31,600 units $125.00 budgeted cost to make a 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. Supplies Power Maintenance Supervision Depreciation Taxes Total \begin{tabular}{|r|r|} \hline Fixed cost & Variable cost \\ \hline & $0.80 \\ \hline$20,000 & 0.60 \\ 15,000 & 0.40 \\ 19,000 & \\ \hline 11,000 & \\ \hline$65,000 \\ \hline \hline \end{tabular} 47% of that month's estimated sales volume 5. 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 Fixed cost Variable cost Commissions 4 Depreciation Shipping Total 6. Unit selling price 150,400 7. Cash balance as of April 1st $60,000 Required: You must use cell references on the BudgetSolution worksheet, by referencing this worksheet that contains the data. If I bolded a line item, that is a header and does not need computation on that row. 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. Budgeted income statement (ignore income tax) for the quarter. 8. What if the company decides to lay off one of the part-time administrative staff. The monthly salaries will be reduced by $4,000, list all budgets that will change? Why? What is the New Net income(Loss) for the quarter