Question

Create an Assumptions page, Sales budget, cash collections schedule, production budget, direct materials budget, direct labor budget, ending finished goods schedule per unit, sales and administrative budget, Cash budget, income statement and balance sheet for the quarter ended.

1 2 3 Instructions: Prepare all schedules on the following tabs for the below problem If you do this correctly, your balance sheet will balance. You must create an input tab for this project, and the link all numbers on the other schedules 5 Geddes Company is preparing budgets for the quarter ending June 30. 7 Budgeted sales for the next 5 months are 8 April 20,000 Units 9 May 50,000 Units 10 June 30,000 Units 11 July 25.000 Units 12 August 15.000 Units 13 14 The selling price is $10 per unit. Accounts receivable balance is $30,000 on April 1, 2014 15 16 All sales are on account and the collection pattern is: 17 70% collect in the month of sale 18 25% collect in the month following the sale 19 S% uncollected 20 21 Management wants ending inventory (of completed goods) to be equal to 20% of the 22 following month's budgeted sales in units. 23 24 On March 31, 4,000 completed goods were on hand. 25 26 There is only one direct material in the product, but 5 pounds are required to complete 27 each unit of the product Management wants raw materials on hand at the end of each 28 month equal to 10% of the following month's production. 30 On March 31, 13,000 pounds of material were on hand and the materials cost $.40 per 31 pound. 1/2 of a month's purchases is paid for in the month of purchase; the other 1/2 32 is paid in the following month 34 The March 31 accounts payable balance is $12.000 36 Each unit of production requires .05 hours of direct labor. The company has a 'no layoff policy 37 so all employees will be paid for 40 hours of work each week Workers are paid $10 per hour 38 regardless of the hours worked. For the next three months, the direct labor workforce will be paid 39 for a minimum of 1.500 hours per month 40 41 Manufacturing overhead is applied to units of product on the basis of direct labor hours actually 42 worked. The VMOH rate is $20 per direct labor hour actually worked. FMOH is $50,000 per month, 43 which includes $20,000 of depreciation expense

Answer 1

Requirement Sales Budget Particulars Units April 20,000 10 $ 2,00,000 10 Sales Price Sales Revenue May 50,000 $ 10 $ 5,00,000 June 30,000 10 3,00,000 Quarter 1,00,000 $ 10 $ 10,00,000 $ $ Requirement Schedule of Expected Cash Collections Particulars April Sales Revenue $ 2,00,000 May $ 5,00,000 June 3,00,000 Quarter $ 10,00,000 $ Accounts Receivables April Sales May Sales June Sales Total Collections $ 30,000 $ 1,40,000 $ $ 50,000 3,50,000 $ $ $ 1,25,000 2,10,000 3,35,000 $ 30,000 $ 1,90,000 $ 4,75,000 $ 2,10,000 $ 9,05,000 $ 1,70,000 $ 4,00,000 Requirement Production Budget Particulars Budgeted Sales Add : Desired Ending Inventory Total Needs Less : Beginning Inventory Production Budget April 20,000 10,000 30,000 4,000 26,000 May 50,000 6,000 56,000 10,000 46,000 June 30,000 5,000 35,000 6,000 29,000 Quarter 1,00,000 5,000 1,05,000 4,000 1,01,000

April 26,000 May 46,000 June 29,000 5 5 Requirement Direct Materials Budget Particulars Production Pounds per Unit Direct Materials Required for Production Add : Desired Ending Inventory Total Needs Less : Beginning Inventory Direct Materials Required Raw Material cost per Pound Direct Material Purchases Cost 23,000 1,53,000 13,000 1,40,000 0.40 56,000 2,30,000 14,500 2,44,500 23,000 2,21,500 0.40 88,600 1,45,000 11,500 1,56,500 14,500 1,42,000 0.40 56,800 Quarter 1,01,000 5 5,05,000 11,500 5,16,500 13,000 5,03,500 0.40 2,01,400 $ $ $ $ $ $ $ $ April Requirement Direct Labor Budget Particulars Production Hours per Unit Hours required 26,000 0.05 1,300 May 46,000 0.05 2,300 June 29,000 0.05 1,450 Quarter 1,01,000 0.05 5,050 1,500 1,500 Minimum Hours to be paid Overtime Hours Total Hours to be paid 1,500 800 2,300 4,500 800 1,500 1,500 5,300 Rate per Hour Direct Labor Cost 10 15,000 $ $ 10 23,000 $ $ 10 15,000 $ $ $ 10 53,000