Answer Requirements 1-7 please, all the information is given

Requirements:

\begin{tabular}{|c|c|c|c|} \hline \multicolumn{4}{|c|}{ A. Budget information assembled at the start of 2022} \\ \hline & \multicolumn{2}{|c|}{ Plain chair } & Customized chair \\ \hline Budgeted production & & 50,000 & 10,000 \\ \hline Number of chairs per batch & & 50 & 8 \\ \hline Setup labor-hours per batch & & 2.5 hours & 5 hours \\ \hline Budgeted machine hours & & 60,000hrs & 40,000hrs \\ \hline Budgeted direct manufacturing labor & & 00,000hrs & 60,000hrs \\ \hline Budgeted feet of lumber per chair & & 18ft & 25ft \\ \hline \multicolumn{4}{|c|}{ Budgeted Manufacturing Operations Overhead Costs } \\ \hline \multicolumn{4}{|l|}{ Variable costs } \\ \hline Supplies & $40,000 & & \\ \hline Indirect manuf. Labor & 95,000 & & \\ \hline Power & 135,000 & & \\ \hline Maintenance & 125,000 & $395.000 & \\ \hline \multicolumn{4}{|l|}{ Fixed costs } \\ \hline Depreciation & 100,000 & & \\ \hline Supervision & 75,000 & & \\ \hline Power & 20,000 & & \\ \hline Maintenance & 30,000 & $225,000 & \\ \hline \multicolumn{4}{|l|}{ Budgeted Setup Overhead Costs } \\ \hline \multicolumn{4}{|l|}{ Variable costs } \\ \hline Supplies & $85,000 & & \\ \hline Indirect manuf. Labor & 185,000 & & \\ \hline Power & 155,000 & & \\ \hline Inspection costs & 50,000 & $475,000 & \\ \hline \multicolumn{4}{|l|}{ Fixed costs } \\ \hline Depreciation & 65,000 & & \\ \hline Supervision & 80,000 & & \\ \hline Power & 50,000 & & \\ \hline Engineering costs & 30,000 & $225.000 & \\ \hline \end{tabular} - Machine-hours is the cost driver for the variable portion of manufacturing operations overhead. Machine-hours is also used to allocate the fixed portion of manufacturing operations overhead. - Setup labor-hours is the cost driver for the variable portion of machine setup overhead. Setup laborhours is also used to allocate the fixed portion of machine setup overhead. - Projected sales for the plain chair are 51,250 in 2023,54,000 in 2024, and 58,000 in 2025. - Projected sales for the customized chair are 10,250 in 2023, 12,000 in 2024, and 14,000 in 2025. - Sales commission is paid as a rate per chair sold. - 2023 input cost and quantity standards for direct material, direct manufacturing labor and manufacturing overhead are the same as 2023 budgeted input costs and quantities. - You require an ending inventory of plain chairs of 10% of next year's total sales needs. Customized chairs are made to order and no inventory of finished goods is planned. - You require an ending raw material inventory of 8% of next year's production needs (for both product lines). - Your beginning 2023 cash balance is $200,000. The company requires a minimum cash balance of $150,000. - Assume a FIFO cost flow - Assume no WIP inventory. 1. What were the 2022 budgeted indirect cost rates for the manufacturing operations activity and the machine setup activity? 2. Was overhead over- or under-applied for the year? By how much? Use @IF statements to indicate under-/over-applied MOH. 3. What was the unit product cost for the plain and customized Chairs under normal costing in 2022 ? 4. What was the Chair Division's operating income for 2022 ? Use normal costing and adjust for misapplied MOH. 5. Use the 2022 actual monthly MOH information to calculate annual estimates of cost behavior (fixed and variable components) for each MOH activity. a. Use the High-Low method to separate variable and fixed costs. For this exercise use @VLOOKUP,@MAX, and @MIN excel functions. b. Use the Regression function found under Data: Data Analysis in Excel. Place output on a separate sheet. These estimates will be used for the 2023 budget. 6. For the Manufacturing Operations overhead activity, evaluate the selected cost driver (MHrs) and compare it to the alternative of Direct Labor Hours as the cost driver. Refer to Exhibit 10-19. i.e., run a regression for DLHrs as the cost driver for the Manufacturing Operations activity and compare the results according to the criteria in Ex 10-19. You can ignore the "specification analysis of estimation assumptions' criterion. 7. Calculate the break-even point in units (calculate the number of units of each product required), and the number of units of both products that must be sold to earn an operating income of $200,000. Use 2023 budgeted information - refer to section D bullet point 2 and your results from requirement 5 . C. Additionaly, the Chair division had the following actual costs in 2022 that were not directly traced to either product: Actual Manufacturing Overhead Costs - 2022 D. Based on the information you have gathered you also make the following assumptions: - Because of past financial trouble in the division, all expenses must be paid in cash. No sales are made on account (all sales are cash sales). - Information for the 2023 budget is as follows: - The budgeted input quantities for materials, labor and MOH (machine hours and setup labor hours) used in 2022 will also be used in the 2023 budget, except for budgeted DLHr/unit for the custom chair, which is revised up to 6.2DLHrs/ chair. - The cost of Poplar is expected to increase by 8% and Red Oak is expected to increase by 10% in 2023 over 2022 actual costs. - Budgeted labor rates in 2023 are expected to be the same as actual rates in 2022. - Calculate 2023 budgeted variable moh rates and total fixed moh costs from 2022 actual monthly cost and activity data. Use Regression Analysis to determine estimates for variable and monthly fixed costs. Convert monthly estimates of fixed costs into annual costs. - Assume non-cash (e.g., depreciation) MOH item amounts remain unchanged from budgeted 2022 data. - Production and sales forecasts are within the relevant range. - Budgeted period costs: variable costs per unit and fixed costs in total for 2023 are assumed to be the same as actual period costs in 2022 . - A 2% increase in selling price for 2023 is expected for the Plain model and a 3% increase is expected for the Customized model. - Normal costing is used. - There are two cost drivers for manufacturing overhead costs - machine-hours and setup labor-hours. B. Actual information for 2022 that can be traced directly to each product: \begin{tabular}{lrr} & Plain Chair & Customized Chair \\ \hline Direct materials used cost & $810,000 & $550,000 \\ Feet of lumber per chair & 18 Feet & 25 Feet \\ Lumber in inventory (12/31/2022) & 60,000 Feet & 25,000 Feet \\ Direct labor cost & $1,800,000 & $1,344,000 \\ Direct labor hours & 100,000 hours & 64,000 \\ Direct labor hours per chair & 2 hours & 6.4 hours \\ Machine hours used in total & 62,500 hours & 42,000 hours \\ Setup labor hours & 2,500 setup lab hrs & 6,500 setup lab hrs \\ Number of batches & 1,000 & 1,250 \\ Chairs per batch & 50 & 8 \\ Total sales commissions & $192,000 & $80,000 \\ Units produced & 50,000 & 10,000 \\ Units sold & 48,000 & 10,000 \\ Chairs in inventory (01/01/2022) & $,000@$57.50 each & 0 \\ Sales revenue & $3,840,000 & $2,850,000 \end{tabular}