please show in excel format

6. Prepare a cash payments budget for manufacturing overhead costs. 7. Prepare a cash payments budget for operating expenses. 8. Prepare a combined cash budget. 9. Calculate the budgeted manufacturing cost per unit. 10. Prepare a budgeted income statement for the quarter ending March 31. Particulars Cash Sales Credit Sales (Collected from Previous Month) Total Cash Collections $ S January 36,000 $ 73.500$ 1.09.500 8 February 40,500 $ 84,000 $ 1.24.500 8 March Total 38,700 $ 1,15,200 94,500 $ 2,52,000 1.33.200 8 3.67.200 S $ December 1,05,000 $ 31.500 $ 73.500 $ January 1,20,000 $ 36,000 $ 84,000 $ February March 1,35,000 $ 1,29,000 40,500 $ 38,700 94.500 $ 90,300 S Working Note: Particulars Total Sales Cash Sales (30% x Total Sales) Credit Sales (70% x Total Sales) Part 2 : Production Budget Particulars Sales units Add: Closing inventory Less: Opening Inventory Production units January 8,000 1,800 (1,600) 8,200 February 9,000 1,720 (1,800) 8.920 March 8,600 1,880 (1.720) 8,760 Total 25,600 5.400 (5,120) 25,880 Working Note: Particulars Total Sales for the month Selling price p. Sales units (Total Sales - Selling Price p.) Closing Inventory (20% of Sales units of Following month) Opening inventory (Closing inventory of previous month) December S 1,05,000 $ $ 15$ 7,000 January 1,20,000 $ 15$ 8,000 February 1.35.000 $ 15$ 9,000 March 1,29,000 $ 15$ 8,600 April 1,41,000 15 9,400 1,600 1,800 1,720 1,880 1,360 Not required 1,600 1.800 1,720 1,880 4 4 4 4 Part 3 : Direct Materials Budget Particulars January February March Total Production units (from Part 2) 8,200 8.920 8.760 25.880 Raw material required per unit Total raw material required for production (Units x per unit requirement) (In Pounds) 32,800 35.680 35,040 1,03,320 Closing raw material (15% of next month production) 5.352 5.356 5.328 15.996 Opening raw material inventory 4.920 5.352 5.352 15.624 Materials to be purchases (Materials required for production + Closing RM - Opening RM ) 38,232 35.584 95.016 1,03.832 Purchase price per pound Material purchase cost (Total raw material purchased x Purchase price) 33.292 $ 350784 35.016 $ 1,03.832 Working Note: March : Closing inventory: April Production units = 9.400 +1,360 - 1,880 = 8,880 units April Raw material requirement = 8,880 x 4 = 35.520 units Closing raw material units for March = 15%* 35,520 = 5328 Januaray: Opening Inventory Opening inventory of Jan - Closing inventory of Dec - 15% of Jan production requirement =15% x 32800 = 4920 units $ 1 $ 1 1 $ 1 8 March 35,016 S 8,754 $ 26,688$ Total 1,03,832 25,958 79.362 Part 4 : Cash payments for material purchases Particulars January February Raw material Cost required for production $ 33,232 $ 35,584 | $ 25% paid during the month $ 8,308$ 8,896 $ 75% of previous month expense paid now $ 27.750 $ 24.924 $ * Dec's 75% expense paid in Jan Accounts payable of Dec 31 x 75%-$37,000 x 75% Part 5:Labor Cost Particulars January February Production units (from Part 2) 8,200 8,920 Direct labor hours pou 0.10 Direct labor hours (units x hours p.) 820 892 Wage rate p.h 15$ 15$ Wages paid (Hours worked x Ratep.h) 12.300 $ 13.980 $ Total 25,880 0.10 March 8,760 0.10 876 15$ 13.140 $ 2,588 15 38,820 Particulars Cash Sales Credit Sales (Collected from Previous Month) Total Cash Collections $ S January 36,000 $ 73.500$ 1.09.500 8 February 40,500 $ 84,000 $ 1.24.500 8 March Total 38,700 $ 1,15,200 94,500 $ 2,52,000 1.33.200 8 3.67.200 S $ December 1,05,000 $ 31.500 $ 73.500 $ January 1,20,000 $ 36,000 $ 84,000 $ February March 1,35,000 $ 1,29,000 40,500 $ 38,700 94.500 $ 90,300 S Working Note: Particulars Total Sales Cash Sales (30% x Total Sales) Credit Sales (70% x Total Sales) Part 2 : Production Budget Particulars Sales units Add: Closing inventory Less: Opening Inventory Production units January 8,000 1,800 (1,600) 8,200 February 9,000 1,720 (1,800) 8.920 March 8,600 1,880 (1.720) 8,760 Total 25,600 5.400 (5,120) 25,880 Working Note: Particulars Total Sales for the month Selling price p. Sales units (Total Sales - Selling Price p.) Closing Inventory (20% of Sales units of Following month) Opening inventory (Closing inventory of previous month) December S 1,05,000 $ $ 15$ 7,000 January 1,20,000 $ 15$ 8,000 February 1.35.000 $ 15$ 9,000 March 1,29,000 $ 15$ 8,600 April 1,41,000 15 9,400 1,600 1,800 1,720 1,880 1,360 Not required 1,600 1.800 1,720 1,880 4 4 4 4 Part 3 : Direct Materials Budget Particulars January February March Total Production units (from Part 2) 8,200 8.920 8.760 25.880 Raw material required per unit Total raw material required for production (Units x per unit requirement) (In Pounds) 32,800 35.680 35,040 1,03,320 Closing raw material (15% of next month production) 5.352 5.356 5.328 15.996 Opening raw material inventory 4.920 5.352 5.352 15.624 Materials to be purchases (Materials required for production + Closing RM - Opening RM ) 38,232 35.584 95.016 1,03.832 Purchase price per pound Material purchase cost (Total raw material purchased x Purchase price) 33.292 $ 350784 35.016 $ 1,03.832 Working Note: March : Closing inventory: April Production units = 9.400 +1,360 - 1,880 = 8,880 units April Raw material requirement = 8,880 x 4 = 35.520 units Closing raw material units for March = 15%* 35,520 = 5328 Januaray: Opening Inventory Opening inventory of Jan - Closing inventory of Dec - 15% of Jan production requirement =15% x 32800 = 4920 units $ 1 $ 1 1 $ 1 8 March 35,016 S 8,754 $ 26,688$ Total 1,03,832 25,958 79.362 Part 4 : Cash payments for material purchases Particulars January February Raw material Cost required for production $ 33,232 $ 35,584 | $ 25% paid during the month $ 8,308$ 8,896 $ 75% of previous month expense paid now $ 27.750 $ 24.924 $ * Dec's 75% expense paid in Jan Accounts payable of Dec 31 x 75%-$37,000 x 75% Part 5:Labor Cost Particulars January February Production units (from Part 2) 8,200 8,920 Direct labor hours pou 0.10 Direct labor hours (units x hours p.) 820 892 Wage rate p.h 15$ 15$ Wages paid (Hours worked x Ratep.h) 12.300 $ 13.980 $ Total 25,880 0.10 March 8,760 0.10 876 15$ 13.140 $ 2,588 15 38,820