Question

The Cash Budget

ASNWER THE FOLLING USING EXCELL FORMAT

1.       The Right Target, Inc., a marketing research and consulting firm, is working on a cash budget for July to December 2017. The staff has projected the following cash collections and payments:

Month	Collections		Payments
	Cash Sales	Credit Sales	Purchases	Wages	Miscellaneous Expenses
July	$10,000	$20,000	$15,000	$6000	$1,500
August	$12,000	$24,000	$18,000	$7,200	$2,000
September	$14,000	$28,000	$21,000	$8,400	$2,500
October	$16,000	$32,000	$24,000	$9,600	$3,000
November	$20,000	$40,000	$30,000	$12,000	$4,000
December	$25,000	$50,000	$37,500	$15,000	$4,500

a)If the ending cash balance as of June 30, 2017 was $10,000, determine the firm’s forecasted monthly cash balance.

b)The staff at The Right Target, Inc. wants to know how much they would need to borrow each month if the minimum ending cash balance is $30,000 and the annual interest rate is 7%.

c)       Determine the impact on the ending cash balance if the firm uses any cash surplus above the required minimum cash balance to pay off its short-term borrowing monthly.

**please add the formulas for each cell
** each letter is a different tab

Answer 1

(A)

	Month		Collections		Payments
		Opening balance	Cash Sales	Credit Sales	Purchases	Wages	Miscellaneous Expenses	closing balance
	July	10000	$10,000	$20,000	$15,000	$6000	$1,500	17500
	August	17500	$12,000	$24,000	$18,000	$7,200	$2,000	26300
	September	26300	$14,000	$28,000	$21,000	$8,400	$2,500	36400
	October	36400	$16,000	$32,000	$24,000	$9,600	$3,000	47800
	November	47800	$20,000	$40,000	$30,000	$12,000	$4,000	61800
	December	61800	$25,000	$50,000	$37,500	$15,000	$4,500	79800