In: Finance
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,300 per month, and the rent is $3,000 per month. In addition, she must make a tax payment of $11,000 in December. The current cash on hand (on December 1) is $700, 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 $100,000.
Sales | Purchases | |||
December | $180,000 | $35,000 | ||
January | 30,000 | 35,000 | ||
February | 50,000 | 35,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 | $ | $ | $ |
A.
Following is the Cash Budget for in case I
Note: Payment for Purchases are made on 1 month lag basis and Sales are done on a cash basis only.
In this case, Surplus Cash or loan is not required in any case
B.
In the second case, payment for sales worth $ 180,000 made in December will be received in January.
Attaching the resulting cash budget in Excel :-
Company would require a loan amount of $ (1,17,600 + 4,000) = $ 1,21,600 in this case at the end of December