Create an ending finished goods schedule per unit, sales and administrative budget, Cash budget, income statement and balance sheet for the quarter ended. (Previous schedules answered on a separate question)

Remaining requirements as follows and clearer screenshots of original information.

I do NOT have any additional information!

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 55 The company pays a cash dividend on 549,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 Requirement Sales Budget Particulars Units Sales Price Sales Revenue April 20,000 10 2,00,000 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 Partiolars April May June Sales Revenue $ 2,00,000 $ 5,00,000 $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 June 29,000 Quarter 1,01,000 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 1,30,000 23,000 1,53,000 13,000 1,40,000 0.40 56,000 May 46,000 5 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 5,05,000 11,500 5,16,500 13,000 5,03,500 0.40 2,01,400 $ $ $ $ $ $ May Requirement Direct Labor Budget Particulars Production Hours per Unit Hours required 46,000 April 26,000 0.05 1,300 June 29,000 0.05 1,450 Quarter 1,01,000 0.05 5,050 2.300 1,500 1,500 4.500 Minimum Hours to be paid Overtime Hours Total Hours to be paid 1.500 800 2,300 1,500 1,500 5,300 Rate per Hour Direct Labor Cost 15.000 $ 23,00 $ 15,00 $ 53,00 May May June Quarter Ended 5 Direct Labor Budget April Required Units of Production Direct labor per unit Total Direct labor hours needed Larbor contract minimium hours Labor hours paid (Use the Large Function) Rate per hour Total direct labor costs - =large 7 8 MOH Budget April May June Quarter Ended 15 16 17 18 19 Variable MOH Rate Total Variable MOH Rate Fixed Mfg MOH Less Depreciation for MOH Cash Disbursements for MOH Total Valuation of Ending Finished Goods Inventory - Per Unit Quantity Cost Direct Materials Direct Labor MOH (use the round function here) Total Cost Per Unit =round 25 26 Ending Inventory in Units (See the Production Schedule) 30 Value of Finished Good Inventory Cash SG&A Budget April May June Quarter Ended 4 Budgeted units Sales Variable SG&A Expenses Total Variable Total Fixed SG&A Total SG&A Less: Depreciation related SG&A Cash Disbursements for SG&A 9 Cash Budget April May June Quarter Ended 16 17 18 19 20 21 Beginning Cash Balance Add Cash collections Less DM Disbursements Less DL Disbursements Less cash MOH Disbursements Less cash SGA Less: Equipment Purchase Less Dividend payments Cash balance before borrowing Borrowing Repayments Interest Ending Cash Balance 22 23 24 25 26 Income Statement Quarter Ended 4 bales 5 COGS 6 Gross Margin 7 G&A Include Depreciation 8 Pperating Income 9 nterest Expense 10 Net Income Balance Sheet Quarter Ended 16 Cash 17 Accounts Receivable 18 Raw Materials 19 Finished Goods Inventory 20 and 21 Equipment 22 fotal Assets 25 Accounts Payable 26 Common Stock 27 Retained Earnings 28 total Liability and SHE These should balance 29 1 Instructions: Prepare all schedules on the following tabs for the below problem. If you do this correctly, your balance sheet will balance. You must crea input tab for this project, and then link all numbers on the other schedules. 3 5 Geddes Company is preparing budgets for the quarter ending June 30. 7 8 9 Budgeted sales for the next 5 months are: April 20,000 Units May 50,000 Units June 30,000 Units July 25,000 Units August 15,000 Units 10 11 14 The selling price is $10 per unit. Accounts receivable balance is $30,000 on April 1, 2014. 15 16 17 18 All sales are on account and the collection pattern is: 70% collect in the month of sale 25% collect in the month following the sale 5% uncollected 21 Management wants ending inventory (of completed goods) to be equal to 20% of the following month's budgeted sales in units. 24 On March 31, 4,000 completed goods were on hand. 26 There is only one direct material in the product, but 5 pounds are required to complete each unit of the product. Management wants raw materials on hand at the end of each month equal to 10% of the following month's production. 30 31 32 On March 31, 13,000 pounds of material were on hand and the materials cost $.40 per pound. 1/2 of a month's purchases is paid for in the month of purchase; the other 1/2 is paid in the following month. 34 The March 31 accounts payable balance is $12,000. The March 31 accounts payable balance is $12,000. 34 35 36 37 38 39 40 41 Each unit of production requires .05 hours of direct labor. The company has a "no layoff" policy so all employees will be paid for 40 hours of work each week. Workers are paid $10 per hour regardless of the hours worked. For the next three months, the direct labor workforce will be paid for a minimum of 1,500 hours per month. Manufacturing overhead is applied to units of product on the basis of direct labor hours actually worked. The VMOH rate is $20 per direct labor hour actually worked. FMOH is $50,000 per month, which includes $20,000 of depreciation expense. The variable selling and administrative expenses are $.50 per unit sold. The fixed selling and administrative expenses are $70,000 per month, but includes $10,000 of depreciation expense. 49 50 52 53 The company maintains a 16% line of credit. Management requires that a minimum cash balance of $30,000 be maintained at all times. Draws on the line of credit must be made on the first day of the month and repaid on the last day of the month. The company pays a cash dividend on $49,000 in April, purchases equipment of $143,700 in May, and additional equipment of $48,300 in June (all of which are paid in cash on date of purchase). 56 On April 1, the balance sheet shows $40,000 of cash, land with a value of $50,000, Common Stock of $200,000, Retained Earnings of $146,150 and equipment of $175,000. 59