Question

CASH BUDGETING

Helen Bowers, owner of Helen's Fashion Designs, is planning to request a line of credit from her bank. She has estimated the following sales forecasts for the firm for parts of 2016 and 2017:

	May 2016	$186,000
	June	186,000
	July	372,000
	August	540,000
	September	720,000
	October	360,000
	November	360,000
	December	90,000
	January 2017	180,000

Estimates regarding payments obtained from the credit department are as follows: collected within the month of sale, 10%; collected the month following the sale, 75%; collected the second month following the sale, 15%. Payments for labor and raw materials are made the month after these services were provided. Here are the estimated costs of labor plus raw materials:

	May 2016	$90,000
	June	90,000
	July	126,000
	August	883,000
	September	307,000
	October	234,000
	November	161,000
	December	90,000

General and administrative salaries are approximately $26,000 a month. Lease payments under long-term leases are $9,000 a month. Depreciation charges are $36,000 a month. Miscellaneous expenses are $2,600 a month. Income tax payments of $62,000 are due in September and December. A progress payment of $180,000 on a new design studio must be paid in October. Cash on hand on July 1 will be $132,000, and a minimum cash balance of $90,000 should be maintained throughout the cash budget period.

Prepare a monthly cash budget for the last 6 months of 2016. If no entry required, enter "0". Use minus sign to enter losses, loans outstanding or any other negative amounts.

May

June

July

August

September

October

November

December

January

Collections and purchases worksheet

Sales (gross)

$ ________

$ ________

$ ________

$ ________

$ ________

$ ________

$ ________

$ ________

$ ________

Collections

During month of sale

________

________

________

________

________

________

________

________

During 1st month after sale

________

________

________

________

________

________

________

________

During 2nd month after sale

________

________

________

________

________

________

________

________

Total collections

$ ________

$ ________

$ ________

$ ________

$ ________

$ ________

Purchases

Labor and raw materials

$ ________

$ ________

$ ________

$ ________

$ ________

$ ________

$ ________

$ ________

Payments for labor and raw materials

$ ________

$ ________

$ ________

$ ________

$ ________

$ ________

$ ________

Cash gain or loss for month

Collections

$ ________

$ ________

$ ________

$ ________

$ ________

$ ________

Payments for labor and raw materials

________

________

________

________

________

________

General and administrative salaries

________

________

________

________

________

________

Lease payments

________

________

________

________

________

________

Miscellaneous expenses

________

________

________

________

________

________

Income tax payments

________

________

________

________

________

________

Design studio payment

________

________

________

________

________

________

Total payments

$ ________

$ ________

$ ________

$ ________

$ ________

$ ________

Net cash gain (loss) during month

$ ________

$ ________

$ ________

$ ________

$ ________

$ ________

Loan requirement or cash surplus

Cash at start of month

$ ________

$ ________

$ ________

$ ________

$ ________

$ ________

Cumulative cash

$ ________

$ ________

$ ________

$ ________

$ ________

$ ________

Target cash balance

$ ________

$ ________

$ ________

$ ________

$ ________

$ ________

Cumulative surplus cash or loans outstanding to maintain $90,000 target cash balance

$ ________

$ ________

$ ________

$ ________

$ ________

$ ________

Prepare monthly estimates of the required financing or excess funds - that is, the amount of money Bowers will need to borrow or will have available to invest. Round your answers to the nearest dollar. Enter loans outstanding with minus sign.

July	$ ________
August	$ ________
September	$ ________
October	$ ________
November	$ ________
December	$ ________

Now suppose receipts from sales come in uniformly during the month (that is, cash receipts come in at the rate of 1/30 or 1/31 each day), but all outflows must be paid on the 5th. Will this affect the cash budget? That is, will the cash budget you prepared be valid under these assumptions? If not, what could be done to make a valid estimate of the peak financing requirements? No calculations are required, although if you prefer, you can use calculations to illustrate the effects.

The input in the box below will not be graded, but may be reviewed and considered by your instructor.

_________________

Bowers' sales are seasonal; and her company produces on a seasonal basis, just ahead of sales. Without making any calculations, discuss how the company’s current and debt ratios would vary during the year if all financial requirements were met with short-term bank loans. Could changes in these ratios affect the firm’s ability to obtain bank credit? Explain.

The input in the box below will not be graded, but may be reviewed and considered by your instructor.

_________________

Answer 1

	May		June		July		August		September		October		November		December		January
Collections and Purchases worksheet
Sales (gross)	186000		186000		372000		540000		720000		360000		360000		90000		180000
Collections
During month of sale (10%)	18600		18600		37200		54000		72000		36000		36000		9000		18000
During 1st month of sale (75%)			139500		139500		279000		405000		540000		270000		270000		67500
During 2nd Month of sale (15%)					27900		27900		55800		81000		108000		54000		54000
Total Collections	18600		158100		204600		360900		532800		657000		414000		333000		139500
Purchases
Labor and Raw Materials	90000		90000		126000		883000		307000		234000		161000		90000
Payments for Labor and raw materials			90000		90000		126000		883000		307000		234000		161000		90000
Cash Gain or Loss During the month
Collections					204600		360900		532800		657000		414000		333000		139500
Payments for Labor and raw materials					90000		126000		883000		307000		234000		161000		90000
General and administrative salaries					26000		26000		26000		26000		26000		26000		26000
Lease Payments					9000		9000		9000		9000		9000		9000		9000
Miscellaneous Expenses					2600		2600		2600		2600		2600		2600		2600
Income Tax Payments									62000								62000
Design Studio Payment											180000
Total Payments					127600		163600		982600		524600		271600		198600		189600
Net Cash gain or (loss) during the month					77000		197300		-449800		132400		142400		134400		-50100
Loan requirement or cash surplus
Cash at the start of the month					132000		209000		406300		90000		222400		364800		499200
Cumulative Cash					209000		406300		-43500		222400		364800		499200		449100
Target Cash balance					90000		90000		90000		90000		90000		90000		90000
Cumulative surplus cash or loans outstanding to maintain $90,000 target cash balance					119000		316300		-133500		132400		274800		409200		359100

Funds available for inveting or funds needed to be borrowed

July	119000
August	316300
September	-133500
October	132400
November	274800
December	409200

If receipts are received uniformly throughout the month and the payments have to be done by 5th the above cashflow calculations will be no longer valid. Following calculations show the shortfall that will be faced.

	July		August		September		October		November		December		January
Collections	204600		360900		532800		657000		414000		333000		139500
No of Days	31		31		30		31		30		31		31
Avearge collection per day	6600		11641.94		17760		21193.55		13800		10741.94		4500
Collection till 5th of the month	33000		58209.68		88800		105967.7		69000		53709.68		22500
Payments to be made	127600		163600		982600		524600		271600		198600		189600
Shortfall	-94600		-105390		-893800		-418632		-202600		-144890		-167100

This shortfall will have to be come over by borrowing from the bank and eventually repaying it as the further collections coming in.

Bowers sales are seasonal. This means the collection of sales will increase or decrease as per the season. When the collection is low, Bowers will have to borrow more increasing the debt ratio and lowering the current ratio. Banks are more willinging to provide funds to those companies who have a high current ratio and a low debt to equity ratio. as otherwise it creates a situation for dout if the ompany will be able to generate enough cash to meet its debt obligations.