I need help with e), f), g) and h) Please do it on excel and show formulas as well if possible. Thank you

4 5 5 7 8 3 (e) Prepare a manufacturing overhead budget. (Round intermediate amounts to the nearest dollar.) This is similar to Illustration 22-12 in your textbook. (f) Prepare a selling and administrative budget. This is similar to Illustration 22-11 in your textbook. (8) Prepare a budgeted income statement. (Round intermediate calculations to the nearest dollar.) This is similar to illustration 22-15 in your textbook. (h) Prepare a cash budget. This is similar to Illustration 22-19 in your textbook. You will need to prepare schedules for expected collections from customers and expected payments to vendors first. See Illustrations 22-17 and 22-18 in your textbook for guidance. You will also need to prepare schedules to support your calculation of amounts borrowed, interest expense, and loan repayments. These should be well labeled and formatted so that I may follow your work. Rules: Points are lost for not following these rules. * Use Excel's functionality to your benefit. Use formulas. * Use proper formats for schedules, following the referenced textbook examples (including titles). 4 * Use correct number formats ($, %, general) and underscoring where appropriate. Double-check your work! Verify your formulas and logic! The company is preparing its budget for the coming year, 2022. The first step is to plan for the first quarter of that coming year. The 2 following information has been gathered from their managers. nmin 4 Sales Information 5 Period Units November, 2021 102,000 Actual December, 2021 91,000 Actual 8 January, 2022 100,000 Planned 9 February, 2022 101,000 Planned 10 March, 2022 103,000 Planned Grading guidelines are on the instructions tab. 11 April, 2022 112,000 Planned 12 May, 2022 123,000 Planned 13 Unit selling price $ 30.70 14 15 Finished Goods Inventory Planning 16 The company likes to keep 15% of the next quarter's unit sales in finished goods ending inventory. 17 18 Accounts Receivable & Collections 19 Sales on Account 100% 20 Collections Activity 21 Month of Sale 80% 22 Month after Sale 20% 23 Balance at 12/31/21 $ 185,000.00 24 25 Materials Inventory Costs & Planning 26 Direct Materials Amount Used per Unit Cost 25 Materials Inventory Costs & Planning 26 Direct Materials Amount Used per Unit Cost 27 Ingredient 2 lb $ 1.18 lb 28 The company likes to keep 12% of the material needed for the next month's production in raw materials ending inventory. 29 30 Accounts Payable & Disbursements 31 Purchases on Account 100% 32 Payment Activity 33 Month of Purchase 40% 34 Month after Purchase 60% 35 Balance at 12/31/21 $ 120,000 36 37 Direct Labor & Costs 38 Time per Unit Production 30 minutes 39 Pay Rate/Hour $ 20.00 40 41 Manufacturing Overhead Costs 42 Variable costs per direct labor hour 43 Indirect materials $ 0.25 44 Indirect labor 0.35 45 Utilities 0.45 46 Maintenance 0.25 47 Fixed costs per month 48 Salaries $ 42,000 49 Depreciation 16,800 50 Property taxes 2,675 51 Insurance 1,200 9 51 Insurance 1,200 52 Janitorial 1,300 53 54 Selling and Administrative Costs 55 Variable costs per unit sold $ 1.20 56 Fixed costs per month 57 Advertising $ 15,000 58 Insurance 14,000 59 Salaries 72,000 60 Depreciation 25,000 61 Other fixed costs 3,000 62 63 Income Taxes 64 Accrued on Monthly Net Income 45% rounded to nearest dollar 65 Amounts Accrued Q4 2021 paid January 2022 $ 200,000 66 67 Cash and Financing Matters 68 Cash Balance, 12/31/2021 $ 145,000 69 2022 Minimum Balance Required 795,000 70 71 Monthly Dividends $ 1.80 per share 72 Outstanding Shares 5,000 73 74 Line of Credit 75 Limit $ 1,200,000 76 Borrowing Increment Required $ 1,000 77 Annual Interest Rate 10% 78 Borrowings occur on the First of Month 79 Repayments occur on the Last of Month Interest accrues on the loan balance from the date of borrowing. Interest accrued is paid first with each repayment. 81 82 Additional Item 83 Fixed Asset Purchase $ 361,000 84 Month January 80 4 5 5 7 8 3 (e) Prepare a manufacturing overhead budget. (Round intermediate amounts to the nearest dollar.) This is similar to Illustration 22-12 in your textbook. (f) Prepare a selling and administrative budget. This is similar to Illustration 22-11 in your textbook. (8) Prepare a budgeted income statement. (Round intermediate calculations to the nearest dollar.) This is similar to illustration 22-15 in your textbook. (h) Prepare a cash budget. This is similar to Illustration 22-19 in your textbook. You will need to prepare schedules for expected collections from customers and expected payments to vendors first. See Illustrations 22-17 and 22-18 in your textbook for guidance. You will also need to prepare schedules to support your calculation of amounts borrowed, interest expense, and loan repayments. These should be well labeled and formatted so that I may follow your work. Rules: Points are lost for not following these rules. * Use Excel's functionality to your benefit. Use formulas. * Use proper formats for schedules, following the referenced textbook examples (including titles). 4 * Use correct number formats ($, %, general) and underscoring where appropriate. Double-check your work! Verify your formulas and logic! The company is preparing its budget for the coming year, 2022. The first step is to plan for the first quarter of that coming year. The 2 following information has been gathered from their managers. nmin 4 Sales Information 5 Period Units November, 2021 102,000 Actual December, 2021 91,000 Actual 8 January, 2022 100,000 Planned 9 February, 2022 101,000 Planned 10 March, 2022 103,000 Planned Grading guidelines are on the instructions tab. 11 April, 2022 112,000 Planned 12 May, 2022 123,000 Planned 13 Unit selling price $ 30.70 14 15 Finished Goods Inventory Planning 16 The company likes to keep 15% of the next quarter's unit sales in finished goods ending inventory. 17 18 Accounts Receivable & Collections 19 Sales on Account 100% 20 Collections Activity 21 Month of Sale 80% 22 Month after Sale 20% 23 Balance at 12/31/21 $ 185,000.00 24 25 Materials Inventory Costs & Planning 26 Direct Materials Amount Used per Unit Cost 25 Materials Inventory Costs & Planning 26 Direct Materials Amount Used per Unit Cost 27 Ingredient 2 lb $ 1.18 lb 28 The company likes to keep 12% of the material needed for the next month's production in raw materials ending inventory. 29 30 Accounts Payable & Disbursements 31 Purchases on Account 100% 32 Payment Activity 33 Month of Purchase 40% 34 Month after Purchase 60% 35 Balance at 12/31/21 $ 120,000 36 37 Direct Labor & Costs 38 Time per Unit Production 30 minutes 39 Pay Rate/Hour $ 20.00 40 41 Manufacturing Overhead Costs 42 Variable costs per direct labor hour 43 Indirect materials $ 0.25 44 Indirect labor 0.35 45 Utilities 0.45 46 Maintenance 0.25 47 Fixed costs per month 48 Salaries $ 42,000 49 Depreciation 16,800 50 Property taxes 2,675 51 Insurance 1,200 9 51 Insurance 1,200 52 Janitorial 1,300 53 54 Selling and Administrative Costs 55 Variable costs per unit sold $ 1.20 56 Fixed costs per month 57 Advertising $ 15,000 58 Insurance 14,000 59 Salaries 72,000 60 Depreciation 25,000 61 Other fixed costs 3,000 62 63 Income Taxes 64 Accrued on Monthly Net Income 45% rounded to nearest dollar 65 Amounts Accrued Q4 2021 paid January 2022 $ 200,000 66 67 Cash and Financing Matters 68 Cash Balance, 12/31/2021 $ 145,000 69 2022 Minimum Balance Required 795,000 70 71 Monthly Dividends $ 1.80 per share 72 Outstanding Shares 5,000 73 74 Line of Credit 75 Limit $ 1,200,000 76 Borrowing Increment Required $ 1,000 77 Annual Interest Rate 10% 78 Borrowings occur on the First of Month 79 Repayments occur on the Last of Month Interest accrues on the loan balance from the date of borrowing. Interest accrued is paid first with each repayment. 81 82 Additional Item 83 Fixed Asset Purchase $ 361,000 84 Month January 80