Question

Need Selling & Administrative Expense Budget, Cash Budget, Budgeted Income Statement for EXCEL.

Schedules to include: a. Sales Budget, with a Schedule of Expected Cash Collections b. Production Budget c. Ending Finished Goods Inventory Budget d. Direct Materials Budget, with a Schedule of Cash Disbursement e. Direct Labor Budget f. Manufacturing Overhead Budget g. Selling & Administrative Expense Budget h. Cash Budget i Budgeted Income Statement

Answer 1

Budget a Sales Budget Q1 Q2 Q3 24 Total 26000 30000 Budgeted Sales (units) Selling Price p.u Budgeted Sales (in $) 23 23 28000 23 $ 6,44,000 12000 23 2,76,000 96000 23 22,08,000 $ 5,98,000 $ 6,90,000 $ $ Collections from credit sales From Current quarter sales (78%) From Previous quarter Sales (22%) Total Collections from account receivables $ $ $ 4,66,440 1,30,000 5,96,440 $ $ $ 5,38,200 1,31,560 6,69,760 $ 5,02,320 $ 1,51,800 $ 6,54,120 $ $ $ 2,15,280 $ 1,41,680 $ 3,56,960 $ 17,22,240 5,55,040 22,77,280 Budget b Production Budget Q1 Q2 Q3 Q4 Total Budgeted Sales (units) (+) Closing Inventory (30% of next quarter Budg.Sale) (-) Opening Inventory Budgeted Production (units) 26000 9000 -2000 33000 30000 8400 -9000 29400 28000 3600 -8400 23200 12000 11000 -3600 19400 96000 11000 -2000 105000 Budgetc Amount 3.75 7.50 Inventory Budget Direct Material cost per case (15*0.25) Direct Labor per case (0.5*15) Variable MOH (0.5*5) Fixed MOH (24000*4)/105000 Cost of Production p.u Ending Inventory (units) Ending Inventory ($) 2.50 0.91 14.66 11000 1,61,307 $

Budget d Q1 Q2 04 19400 15 Direct Material Budget Budgeted Production (in %) DM required p.u Total Material needed (in pounds) (+) Ending Inventory (10% of next quarter needs) (-) Beginning Inventory Direct Material Purchases (in pounds) Cost per pound Direct Material Purchases (in $) 33000 15 495000 44100 -21000 518100 0.25 1,29,525 29400 15 441000 34800 -44100 431700 0.25 1,07,925 23 23200 15 348000 29100 -34800 342300 0.25 85,575 291000 23000 -29100 284900 0.25 71,225 Total 105000 15 1575000 23000 -21000 1577000 0.25 3,94,250 $ $ $ $ $ $ $ $ $ Payments made To Current quarter purchases (22%) To Previous quarter purchases (78%) Total Payments made to creditors $ $ $ 28,495.50 68,000.00 96,496 $ 23,743.50 $ 1,01,029.50 $ 1,24,773 $18,826.50 $84,181.50 $ 1,03,008 $ 15,669.50 $ 66,748.50 $ 82,418 $ 86,735.00 $ 3,19,959.50 $ 4,06,695 Budget e Q1.com 22 . 23. 24,00m Total.com Direct Labor Budget Budgeted Production (in $) DL hours p.u Total Direct labor needed in hours) Cost per hour Direct Labor cost (in $) Q1 33000 0.5 16500 15.00 2,47,500 29400 0.5 14700 15.00 2,20,500 03 23200 0.5 11600 $ 15.00 $ 1,74,000 Q4 19400 0.5 9700 15.00 1,45,500 Total 105000 0.5 52500 15.00 7,87,500 $ $ $ $ $ $ $ $ Budget f Q2 $ 50 Manufacturing OH Budget Direct labor hours used Variable MOH per labor hour Variable MOH cost (in $) (+) Cash Fixed MOH (24000-17000) Total MOH payments (+) Non Cash MOH (Depreciation) Total MOH for Income Statement Q1 16500 5.00 $ 82,500 $ 7,000 $ 89,500 $ 17,000 $ 1,06,500 $ 14700 5.00 73,500 7,000 80,500 17,000 97,500 $ $ $ $ $ $ Q3 11600 5.00 58,000 7,000 65,000 17,000 82,000 $ $ $ $ $ $ Q4 9700 5.00 48,500 7,000 55,500 17,000 72,500 $ $ $ $ $ $ Total 52500 5.00 2,62,500 28,000 2,90,500 68,000 3,58,500 $

Budget h Q1 92,000 5,96,440 6,88,440 Q2 1,44,145 6,69,760 8,13,905 $ $ $ Q3 $ 2,75,132 $ 6,54,120 $ 9,29,252 $ Q4 4,79,844 3,56,960 8,36,804 $ $ $ Total 92,000 22,77,280 23,69,280 $ $ $ Cash Budget Beginning Cash Balance (+) Collections from Sales (Budget a) Total Cash Available (-) Cash Disbursements For Direct Materials (Budget d) For Direct Labor (Budget e) For MOH (Budget f) For SA (Budget g) For Equipment Purchase For Dividend paid Total Cash Disbursements Excess/(Deficiency) of cash available over disbursements Financing Borrowings Repayments Interest Total Financing Ending Cash Balance 96,496 2,47,500 89,500 1,00,800 5,000 5,000 5,44,296 $ $ $ $ $ $ $ 1,24,773 $ 1,03,008 $ 2,20,500 $ 1,74,000 $ 80,500 $ 65,000 $ 1,04,000 $ 1,02,400 $ 4,000 $ - $ 5,000 $ 5,000 $ 5,38,773 $ 4,49,408 $ 82,418 $ 1,45,500 $ 55,500 $ 89,600 $ 26,000 $ 5,000 $ 4,04,018 $ 4,06,695 7,87,500 2,90,500 3,96,800 35,000 20,000 19,36,495 $ $ 1,44,145 $ 2,75,132 $ 4,79,844 $ 4,32,786 $ 4,32,786 0 OOOO OOOO 0 1,44,145 $ 2,75,132 $ 4,79,844 $ 4,32,786 $ 4,32,786

Budget i - in$ ins in $ 22,08,000 18,300 5,250 3,94,250 -5,750 Budgeted Income Statement Sales (-) Cost of Goods Sold Beginning FG Inventory Beginning Raw Materials Inventory (+) Purchases (Budget d) (-) Ending Raw Materials Inventory (23000*0.25) Cost of Direct Materials consumed (+) Direct Labor (Budget e) (+) Manufacturing OH (Budget f) Cost of Goods Available for Sale (-) Closing Inventory (Budget c) Cost of Goods sold Gross Margin (-) Selling and Administration expenses (Budget g) Net income for the Year 3,93,750 7,87,500 3,58,500 15,58,050 -1,61,307 13,96,743 8,11,257 -4,12,800 3,98,457