Question

Harry's Carryout Stores has eight locations. The firm wishes to expand by two more stores and needs a bank loan to do this. Mr. Wilson, the banker, will finance construction if the firm can present an acceptable three-month financial plan for January through March. The following are actual and forecasted sales figures:

Of the firm's sales, 60 percent are for cash and the remaining 40 percent are on credit. Of credit sales, 20 percent are paid in the month after sale and 80 percent are paid in the second month after the sale. Materials cost 20 percent of sales and are purchased and received each month in an amount sufficient to cover the following month's expected sales. Materials are paid for in the month after they are received. Labor expense is 50 percent of sales and is paid for in the month of sales. Selling and administrative expense is 15 percent of sales and is also paid in the month of sales. Overhead expense is $31,000 in cash per month.

Depreciation expense is $10,600 per month. Taxes of $8,600 will be paid in January, and dividends of $5,000 will be paid in March. Cash at the beginning of January is $92,000, and the minimum desired cash balance is $87,000.

For January, February, and March, prepare a schedule of monthly cash receipts, monthly cash payments, and a complete monthly cash budget with borrowings and repayments.

Answer 1

Bringing together anticipated cash receipt and cash payment in a cash flow statement helps us to find out net cash flow during the month. The main purpose of preparing cash budget is to anticipate the need of external funding at the end of every month.

Prepare cash budget for January, February and March. Before it we need to prepare schedule of cash receipt and schedule of cash payment to know the cash receipt and payment during these months

Consider:

The sales for November is 260,000

The sales for December is 340,000

Month	Projected sales
January	400,000
February	440,000
March	410,000
April	400,000

From the monthly projected sales, 60% are collected as cash and 40% of the projected sales per month are collected as credit (20% are collected in the next month and 80% are collected after 2 months).

Prepare the schedule of cash receipt:

Schedule of cash receipt

Particulars	November	December	January	February	March	April
Total sales	260,000	340,000	400,000	440,000	410,000	400,000
Credit sales (40%) (0.40 × 2,60,000) (0.40 × 3,40,000) (0.40 × 4,00,000) (0.40 × 4,40,000) (0.40 × 4,10,000) (0.40 × 4,00,000)	104,000	136,000	160,000	176,000	164,000	160,000
Collections:
60% cash sales (0.60 × 4,00,000) (0.60 × 4,40,000) (0.60 × 4,10,000)			240,000	264,000	246,000
20% of credit sales collected in next month(20% of previous month's credit sales (0.20 × 1,36,000) (0.20 × 1,60,000) (0.20 × 1,76,000)			27200	32000	35200
80% of credit sale before 2 months (0.80 × 1,04,000) (0.80 × 1,36,000) (0.80 × 1,60,000)			83200	108800	128000
Total cash receipt			350,400	404,800	409,200

Prepare the schedule of cash payment:

Consider:

The material cost is 20% of expected sales of following month, paid in the month after purchase.

The labor cost is 50% of sales paid in the same month of sale

The selling and administrative expense is 15% of sales paid in the month of sale

The overhead expenses is 31,000 per month

The tax amount is 8,600 in January

The depreciation is10, 600 per month

The dividend is 5,000 in the month of March

Schedule of cash payment

Particulars	Jan	Feb	Mar
Payments
Material payment(20% of next month's sale, and paid month after purchase, therefore it is equal to 20% of current month's sale) (0.20 × 4,00,000 = 80,000) (0.20 × 4,40,000 = 88,000) (0.20 × 4,10,000 = 82,000)	80,000	88,000	82,000
Labor(50% of sales)To be paid in the month of production (0.50 × 4,00,000 = 2,00,000)

(0.50 × 4,40,000 = 2,20,000)

(0.50 × 4,10,000 = 2,05,000)

200,000

220,000

205,000

Selling and distribution expense (15% of sales) paid in the same month

(0.15 × 4,00,000 = 60,000)

(0.15 × 4,40,000 = 66,000)

(0.15 × 4,10,000 = 61,500)

60,000

66,000

61,500

overhead expenses

31,000

31,000

31,000

Taxes

8,600

Dividend

5,000

Total Cash Payment

379,600

405,000

384,500

Note: As depreciation is a non cash expense, it will not be included in schedule of cash payment.

Prepare the cash budget:

Consider:

The beginning cash balance of January is $92,000

The minimum desired cash balance is $87,000

Particulars	January	February	March
Cash Receipt	350,400	404,800	409,200
Less: Cash payment	379,600	405,000	384,500
Net cash flow	-29,200	-200	24,700
Add: Beginning cash balance	92,000	87,000	87,000
Cumulative cash balance	62,800	86,800	111,700
Monthly loan(or repayment)	24,200	200	-24,400
Cumulative loan balance	24,200	24,400	0
Ending cash balance	87,000	87,000	87,300

Notes:

Monthly loan (or repayment):

For the month of January: Cumulative cash balance is 62,800, and as minimum balance required is $87,000, to meet the requirement monthly loan of 24,200(87,000 - 62,800) is needed.

For the month of February: Cumulative cash balance is 86,800,and as minimum balance required is $87,000, to meet the requirement monthly loan of 2,00(87,000 -86,800) is needed which will bring cumulative loan balance to 24,400(Jan Loan + Feb Loan).

For the month of March: As the cumulative cash balance is more than the required $87,000, excess balance is used to repay monthly loan taken in the month of January and February and the remaining amount becomes ending balance for the month of December .