e. 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.
Answer:
	Change	MAXIMUM Loan Required
	in Sales	% Collections in 2nd Month
	$67,800	0%	15%	30%	45%	60%	75%	90%
	-100%
	-75%
	-50%
	-25%
	0%
	25%
	50%
	75%
	100%

Required information to answer the question above:

Input Data
Collections during month of sale		20%
Collections during month after sale		60%
Collections during second month after sale		25%
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%

			May	June	July	August	September	October	November	December
Original sales estimates		$75,000	$115,000	$145,000	$125,000	$120,000	$95,000	$75,000	$55,000
Original labor and raw mat. estimates	$80,000	$75,000	$105,000	$85,000	$65,000	$70,000	$30,000	$35,000
Forecasted Sales
Sales (gross)			$75,000	$115,000	$145,000	$125,000	$120,000	$95,000	$75,000	$55,000
Collections
During month of sale				29000	25000	24000	5000	15000	11000
During 1st month after sale				69000	87000	75000	78000	63700	48750
During 2nd month after sale				18750	28750	36250	31250	30000	23750
Total collections				$116,750	$140,750	$135,250	$114,250	$108,700	$83,500
Purchases
Labor and raw materials		$80,000	$75,000	$105,000	$85,000	$65,000	$70,000	$30,000	$35,000
Payments for labor and raw materials			$75,000	$105,000	$85,000	$65,000	$70,000	$30,000
Payments
Payments for labor and raw materials			$75,000	$105,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	0		30,000
Design studio payment							95,000
Total payments					$112,000	$142,000	$152,000	$197,000	$107,000	$97,000
Net Cash Flows
Cash on hand at start of forecast period			$70,000	$74,750	$73,500	$56,750	($26,000)	($24,300)
Net cash flow (NCF): Total collections - Total payments	$4,750	($1,250)	($16,750)	($82,750)	$1,700	($13,500)
Cumulative NCF: Prior month cumulative + this month's NCF	$74,750	$73,500	$56,750	($26,000)	($24,300)	($37,800)
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		$44,750	$43,500	$26,750	($56,000)	($54,300)	($67,800)
		Max. Loan		$67,800
b. Prepare an estimate of the required financing (or excess funds)that is, the amount of money
Marvel's Renovations will need to borrow (or will have available to invest)for each month during that period.
TIME FRAME	May	June	July	August	September	October	November	December	January
$ BORROWED			$44,750	$43,500	$26,750	$56,000	$54,300	$67,800