Input Data
Collections during month of sale		20%
Collections during month after sale		60%
Collections during second month after sale	20%
Lease payments			$7,000
Target cash balance			$30,000
General and administrative salaries		$25,000
Depreciation charges			$8,000
Income tax payments (Sep & Dec)		$30,000
Miscellaneous expenses			$5,000
New office suite payment (Oct)		$95,000
Cash on hand July 1			$70,000
Sales, labor, and RM adjustment factor	0%
a. Prepare a monthly cash budget for the last six months of the year.
			May	June	July	August	September	October	November	December
Original sales estimates		$75,000	$1,15,000	$1,45,000	$1,25,000	$1,20,000	$95,000	$75,000	$55,000
Original labor and raw mat. estimates	$80,000	$75,000	$1,05,000	$85,000	$65,000	$70,000	$30,000	$35,000
Forecasted Sales
Sales (gross)			$75,000	$1,15,000	$1,45,000	$1,25,000	$1,20,000	$95,000	$75,000	$55,000
Collections
During month of sale				1,45,000 *20 = 29,000	125000*20 = 25,000	120000*20= 24,000	95000*20=19,000	75000*20= 15,000	55000*20=11,000
During 1st month after sale			1,15,000* 60= 69,000	145000*60 = 87,000	125000*60=75,000	120000*60=72,000	95000*60= 57,000	75000*60=45,000
During 2nd month after sale			75,000 * 20 = 15,000	115000*20 = 23,000	145000*20= 29,000	125000*20=25,000	120000*20= 24,000	95000*20=19,000
Total collections				$1,13,000	$135,000	$1,28,000	$1,16,000	$96,000	$75,000
Purchases
Labor and raw materials		$80,000	$75,000	$1,05,000	$85,000	$65,000	$70,000	$30,000	$35,000
Payments for labor and raw materials			$75,000	$1,05,000	$85,000	$65,000	$70,000	$30,000
Payments
Payments for labor and raw materials			$75,000	$1,05,000	$85,000	$65,000	$70,000	$30,000
General and administrative salaries			25,000	25,000	25,000	25,000	25,000	25,000
Lease payments				7,000	7,000	7,000	7,000	7,000	7,000
Miscellaneous expenses				5,000	5,000	5,000	5,000	5,000	5,000
Income tax payments						30,000			30,000
Design studio payment							95,000
Total payments				$1,12,000	$1,42,000	$1,52,000	$1,97,000	$1,07,000	$97,000
Net Cash Flows
Cash on hand at start of forecast period		$70,000	$71,000	$64,000	$40,000
Net cash flow (NCF): Total collections - Total payments	$1,000	($7,000)	($24,000)	($81,000)	($11,000)	($22,000)
Cumulative NCF: Prior month cumulative + this month's NCF	$71,000	$64,000	$40,000	($41,000)	($52,000)	($74,000)
Cash Surplus (or Loan Requirement)
Target cash balance				$30,000	$30,000	$30,000	$30,000	$30,000	$30,000
Surplus cash or loan needed: Cum NCF - Target cash		$41000	$34,000	$10,000	($71,000)	($82,000)	($1,04,000)
		Max. Loan		($1,04,000)

d. If its customers began to pay late, this would slow down collections and thus increase the required loan amount. Also, if sales dropped off, this would have an effect on the required loan. Do a sensitivity analysis that shows the effects of these two factors on the max loan requirement. Assume the purchases of labor and raw material also vary by the sales adjustment factor.