Question

Problem 16-14

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,600 per month, and the rent is $2,400 per month. In addition, she must make a tax payment of $14,000 in December. The current cash on hand (on December 1) is $650, 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	$150,000	$40,000
January	50,000	40,000
February	50,000	40,000

Prepare a cash budget for December, January, and February.

I. Collections and Purchases: December January February Sales $150,000 $50,000 $50,000 Purchases $40,000 $40,000 $40,000 Payments for purchases $100,000 $40,000 $40,000 Salaries $4,600 $4,600 $4,600 Rent $2,400 $2,400 $2,400 Taxes $14,000 Total payments $121,000 $47,000 $47,000 Cash at start of forecast $650 $? $? Net cash flow $29,000 $3,000 $3,000 Cumulative NCF $29,650 $? $? Target cash balance $4,000 $4,000 $4,000 Surplus cash or loans needed $25,650 $? $?

Please, finish and show me all the steps of the boxes with the sign ?

Answer 1

Question :-

There can be three ways of solving this Question:-

Way 1:-

If we consider that the surplus cash will not form part of opening cash (Cash at start of forecast)
				Particulars	December	January	February
Sales	$150,000	$50,000	$50,000
Purchases	$40,000	$40,000	$40,000
Payments for purchases	$100,000	$40,000	$40,000
Salaries	$4,600	$4,600	$4,600
Rent	$2,400	$2,400	$2,400
Taxes	$14,000
Total payments	$121,000	$47,000	$47,000
Cash at start of forecast	$650	$4,000	$4,000
Net cash flow	$29,000	$3,000	$3,000
Cumulative NCF	$29,650	$7,000	$7,000
Target cash balance	$4,000	$4,000	$4,000
Surplus cash or (loans needed)	$25,650	$3,000	$3,000

Way 2:-

If we consider that the surplus cash will also form part of opening cash (Cash at start of forecast)
				Particulars	December	January	February
Sales	$150,000	$50,000	$50,000
Purchases	$40,000	$40,000	$40,000
Payments for purchases	$100,000	$40,000	$40,000
Salaries	$4,600	$4,600	$4,600
Rent	$2,400	$2,400	$2,400
Taxes	$14,000
Total payments	$121,000	$47,000	$47,000
Cash at start of forecast	$650	$29,650	$32,650
Net cash flow	$29,000	$3,000	$3,000
Cumulative NCF	$29,650	$32,650	$35,650
Target cash balance	$4,000	$4,000	$4,000
Surplus cash or (loans needed)	$25,650	$28,650	$31,650

Way 3:-

If we consider Cash at start of forecast as $650 in all three months
				Particulars	December	January	February
Sales	$150,000	$50,000	$50,000
Purchases	$40,000	$40,000	$40,000
Payments for purchases	$100,000	$40,000	$40,000
Salaries	$4,600	$4,600	$4,600
Rent	$2,400	$2,400	$2,400
Taxes	$14,000
Total payments	$121,000	$47,000	$47,000
Cash at start of forecast	$650	$650	$650
Net cash flow	$29,000	$3,000	$3,000
Cumulative NCF	$29,650	$3,650	$3,650
Target cash balance	$4,000	$4,000	$4,000
Surplus cash or (loans needed)	$25,650	($350)	($350)

Steps of calculating the amount of the boxes with sign are:-

1. Sales are made on a cash basis therefore the same would be considered as cash incoming.

2. Net cash flow is calculated by subtracting the total payments from the Total Income (Sales).

3. Cumulative net cash flow (NCF) is calculated by adding Net cash flow and Cash at the start of Forecast.

4. Target cash will always be $4000 as asked in the Question.

5. Surplus cash will be calculated by subtracting Target cash balance from Cumulative net cash flow.

6. In "Way 3" We have assumed that the Cash at the start of forecast is $650 in all three months and rest is calculated as per the above steps.

Thanks and Regards