Betty Best owner of Best Ideas Inc Betty Best owner of Best

Betty Best, owner of Best Ideas, Inc. is planning to request a line of credit from her bank. She has estimated the following forecasts for the company for parts of 2011 and 2012; Labor and Raw Materials Sales May 2011 June August September October November December January 2012 $370,000 S370,000 $730,000 $1,090,000 $1,450,000 $730,000 $730,000 $190,000 $370,000 $200,000 $190,000 $262,000 $1,774,000 $622,000 $478,000 $334,000 $190,000 N/A The credit and collections department estimates that collections obtained from ongoing operations will match the following credit policy. 1) Collections within the month of sales are l 5% of that month\'s sales; 2) collections the month following the sale are estimated at 70% of the prior month\'s sales, and 3) collections the second month following sales are estimated at 15% of sales from two months prior. Total sales figures are estimated to be incurred for each month as shown above Payments for labor and raw materials are typically made during the month following the one in which these costs were incurred. Total labor and raw materials cost are estimated to be incurred for each month as shown above. The company currently leases their work space and payments under long-term lease contracts are $18,000 a month. Salaries for labor and administration employees are approximately $54,000 a month. Current depreciation charges total $72,000 a month on existing equipment. They estimate that miscellaneous expenses will be $5,000 a month. The company makes quarterly estimated income tax payments of $126,000 in both September and December. The company plans to make a progress payment of $460,000 on a new manufacturing facility that must be paid in October. Cash on hand on July 1 is orecasted at $264,000, and a minimum cash balance of $180,000 will be maintained throughout the cash budget period. Required: a. Prepare a monthly cash budget for the last 6 months of 2011 for Best Ideas, Inc. b. Prepare an estimate of the required financing (or excess funds)- that is, the amount of money Best Ideas, Inc. will need to borrow (or will have available to invest) -for each month during that period.

Solution

MAY JUN JULY AUG SEP OCT NOV DEC JAN Sales 370000 370000 730000 1090000 1450000 730000 730000 190000 370000 CASH IN Cash receipts 0.15*current month 109500 (0.15*july) 163500 217500 109500 109500 28500 55500 Cash receipts - 0.70* previous one month sales 259000 259000 (0.70*jun sales) 511000 763000 1015000 511000 511000 133000 Cash receipts -0.15* two month prior sales 55500 (0.15*may sales) 55500 109500 163500 217500 109500 109500 Miscellaneous - cash in TOTAL CASH-IN 0 259000 424000 730000 1090000 1288000 838000 649000 298000 PURCHASES LABOUR & R.M 200000 190000 262000 1774000 622000 478000 334000 190000 Total Purchases 262000 1774000 622000 478000 334000 190000 0 CASH OUT Payables - after delay 262000 1774000 622000 478000 334000 190000 add SALARY 54000 54000 54000 54000 54000 54000 no effect Depreciation - not a cash item 72000 72000 72000 72000 72000 72000 add LEASE 18000 18000 18000 18000 18000 18000 add TAXES 126000 126000 add Miscellaneous - cash out 5000 5000 5000 5000 5000 5000 add NEW MANUF 460000 total TOTAL CASH OUT 0 0 149000 411000 2049000 1231000 627000 609000 190000 Opening balance 264000 539000 858000 -101000 -44000 167000 207000 add CASH IN 424000 730000 1090000 1288000 838000 649000 298000 less CASH OUT 149000 411000 2049000 1231000 627000 609000 190000 Closing balance 539000 858000 -101000 -44000 167000 207000 315000 Cash Shorage - Financing Required FALSE FALSE 281000 224000 13000 FALSE FALSE SPARE CASH 359000 678000 FALSE FALSE FALSE 27000 135000