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 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 48 The variable selling and administrative expenses are $.50 per unit sold. The fixed selling and 49 administrative expenses are $70,000 per month, but includes $10,000 of depreciation expense. 51 The company maintains a 16% line of credit. Management requires that a minimum 52 cash balance of $30,000 be maintained at all times. Draws on the line of credit must be made on 53 the first day of the month and repaid on the last day of the month. 54 35 The company pays a cash dividend on $49,000 in April, purchases equipment of $143,700 in May. 56 and additional equipment of $48,300 in June (all of which are paid in cash on date of purchase). 58 On April 1, the balance sheet shows $40,000 of cash, land with a value of $50,000, Common Stock 59 of $200,000, Retained Earnings of $146,150 and equipment of $175,000 50