In: Finance
1. Cash Budgeting
Dorothy Koehl recently leased space in the Southside Mall and opened a new business, Koehl's Doll Shop. Business has been good, but Koehl frequently run out of cash. This has necessitated late payment on certain orders, which is beginning to cause a problem with suppliers. Koehl plans to borrow from the bank to have cash ready as needed, but first she needs a forecast of how much she should borrow. Accordingly, she has asked you to prepare a cash budget for the critical period around Christmas, when needs will be especially high.
Sales are made on a cash basis only. Koehl's purchases must be paid for during the following month. Koehl pays herself a salary of $4,100 per month, and the rent is $2,300 per month. In addition, she must make a tax payment of $12,000 in December. The current cash on hand (on December 1) is $250, but Koehl has agreed to maintain an average bank balance of $4,000 - this is her target cash balance. (Disregard the amount in the cash register, which is insignificant because Koehl keeps only a small amount on hand in order to lessen the chances of robbery.)
The estimated sales and purchases for December, January, and February are shown below. Purchases during November amounted to $120,000.
Sales | Purchases | |||
December | $150,000 | $50,000 | ||
January | 34,000 | 50,000 | ||
February | 64,000 | 50,000 |
I. Collections and Purchases: | ||||||
|
|
|
||||
Sales | $ | $ | $ | |||
Purchases | $ | $ | $ | |||
Payments for purchases | $ | $ | $ | |||
Salaries | $ | $ | $ | |||
Rent | $ | $ | $ | |||
Taxes | $ | --- | --- | |||
Total payments | $ | $ | $ | |||
Cash at start of forecast | $ | --- | --- | |||
Net cash flow | $ | $ | $ | |||
Cumulative NCF | $ | $ | $ | |||
Target cash balance | $ | $ | $ | |||
Surplus cash or loans needed | $ | $ | $ |
Part A - CASH BUDGET FOR DECEMBER TO FEBRUARY
Loan to be taken for January = $14,550.00
Cash Budget | December | January | February | Notes |
Sales Receipts (A) | $150,000 | $34,000 | $64,000 | Cash Sales |
Less: | ||||
Payments for Purchases | $120,000 | $50,000 | $50,000 | 1 month credit. November Purchases = $120,000. |
Salaries | $4,100 | $4,100 | $4,100 | Salary for Koehl |
Rent | $2,300 | $2,300 | $2,300 | |
Taxes | $12,000 | - | - | One-time tax payment in December |
Total Payment (B) | $138,400 | $56,400 | $56,400 | |
Net Cash Received (A-B) | $11,600 | -$22,400 | $7,600 | |
Cash at beginning of forecast | $250 | - | - | |
Net Cash Flow | $11,850 | -$22,400 | $7,600 | |
Cumulative Net Cash Flow | $11,850 | -$10,550 | $11,600 | Feb = Target Cash + Feb NCF. Assumed no part of loan taken in January was paid back |
Target Cash Balance | $4,000 | $4,000 | $4,000 | |
Surplus Cash or (-) loans needed | $7,850 | -$14,550 | $7,600 | Cumulative NCF - Target Cash Flow |
Part B - CASH BUDGET FOR DECEMBER TO FEBRUARY
Loan to be taken for December = $142,150.00
Cash Budget | December | January | February | Notes |
Sales Receipts (A) | $0 | $150,000 | $34,000 | Cash Sales till November. 30 days Credit Sales from December. |
Less: | ||||
Payments for Purchases | $120,000 | $50,000 | $50,000 | 1 month credit. November Purchases = $120,000. |
Salaries | $4,100 | $4,100 | $4,100 | Salary for Koehl |
Rent | $2,300 | $2,300 | $2,300 | |
Taxes | $12,000 | - | - | One-time tax payment in December |
Total Payment (B) | $138,400 | $56,400 | $56,400 | |
Net Cash Received (A-B) | -$138,400 | $93,600 | -$22,400 | |
Cash at beginning of forecast | $250 | - | - | |
Net Cash Flow | -$138,150 | $93,600 | -$22,400 | |
Cumulative Net Cash Flow | -$138,150 | $97,600 | $75,200 | Jan = Target Cash after loan + Jan NCF. Assumed no part of loan taken in December was paid |
Target Cash Balance | $4,000 | $4,000 | $4,000 | |
Surplus Cash or (-) loans needed | -$142,150 | $93,600 | $71,200 | Cumulative NCF - Target Cash Flow |