Question

You (Steven Rohrbacher) are a relatively recent hire to the Hartz & Co., a local manufacturer of plumbing supply products. You have been asked to prepare for a presentation to the company’s management a condensed cash-flow statement for the months of November and December, 2013.

The cash balance at November 1st was $38,700. It is the company’s policy to maintain a minimum cash balance of $38,700 at the end of each month. Cash receipts (from cash sales and collection of accounts receivable) are projected to be $484,400 for November and $403,000 for December. Cash disbursements (sales commissions, advertising, delivery expense, wages, utilities, etc.), prior to financing activity, are scheduled to be $467,000 in November and $431,300 in December.

Borrowing, when needed, is done at the beginning of the month - in increments of $900. The annual interest rate on any such loans is estimated to be 11.00%. Interest on any outstanding loans is paid in cash at the end of the month. Interest on any outstanding loans is paid in cash at the end of the month. Repayments of principal (if any) are assumed to occur at the end of the month. As of November 1st, the company has a $38,700 short-term loan from the local bank.

Required:   Use the preceding information to prepare the cash budget for November and December.

Data Input:

Cash balance, November 1st

$38,700

Minimum required cash balance

$38,700

Budgeted cash receipts:

November

$484,400

December

$403,000

Budgeted cash disbursements:

November

$467,000

December

$431,300

Interest rate on borrowings

11.0%

per year

Short-term loan payable, as of November 1st

$38,700

Borrowings in increments of

$900

Solution:

Cash Budget

For November and December, 2013

November

December

Cash balance, beginning

$38,700

0

Plus: Cash receipts

$484,400

$403,000

Total Cash Available

$523,100

$403,000

Cash disbursements, prior to financing

$467,000

$431,300

Plus: Minimum cash balance (given)

$38,700

$38,700

Total Cash Needed

$505,700

$470,000

Excess (deficiency of) cash, before financing effects

$17,400

-$67,000

Financing:

Balance Short term loan beginning of month

$38,700

Short-term borrowing, beginning of the month

0

Repayments (loan principal), end of the month

38700

Balance Short term loan end of month

Interest (@11.00%), paid in cash @ end of the month

Total effects of financing

Ending cash balance

Most of the points are on cells I39, I40 and L39, L40

You (Steven Rohrbacher) are a relatively recent hire to the Hartz & Co., a local manufacturer of plumbing supply products. You have been asked to prepare for a presentation to the company’s management a condensed cash-flow statement for the months of November and December, 2013.
The cash balance at November 1st was $38,700. It is the company’s policy to maintain a minimum cash balance of $38,700 at the end of each month. Cash receipts (from cash sales and collection of accounts receivable) are projected to be $484,400 for November and $403,000 for December. Cash disbursements (sales commissions, advertising, delivery expense, wages, utilities, etc.), prior to financing activity, are scheduled to be $467,000 in November and $431,300 in December.
Borrowing, when needed, is done at the beginning of the month - in increments of $900. The annual interest rate on any such loans is estimated to be 11.00%. Interest on any outstanding loans is paid in cash at the end of the month. Interest on any outstanding loans is paid in cash at the end of the month. Repayments of principal (if any) are assumed to occur at the end of the month. As of November 1st, the company has a $38,700 short-term loan from the local bank.
Required:   Use the preceding information to prepare the cash budget for November and December.
Data Input:
	Cash balance, November 1st						$38,700
	Minimum required cash balance						$38,700
	Budgeted cash receipts:
	November						$484,400
	December						$403,000
	Budgeted cash disbursements:
	November						$467,000
	December						$431,300
	Interest rate on borrowings						11.0%	per year
	Short-term loan payable, as of November 1st						$38,700
	Borrowings in increments of						$900
Solution:
					Cash Budget
				For November and December, 2013
								November			December
	Cash balance, beginning							$38,700			0
	Plus: Cash receipts							$484,400			$403,000
	Total Cash Available							$523,100			$403,000
	Cash disbursements, prior to financing							$467,000			$431,300
	Plus: Minimum cash balance (given)							$38,700			$38,700
	Total Cash Needed							$505,700			$470,000
	Excess (deficiency of) cash, before financing effects							$17,400			-$67,000
	Financing:
	Balance Short term loan beginning of month						$38,700
	Short-term borrowing, beginning of the month							0
	Repayments (loan principal), end of the month							38700
	Balance Short term loan end of month
	Interest (@11.00%), paid in cash @ end of the month
	Total effects of financing
	Ending cash balance
Most of the points are on cells I39, I40 and L39, L40

Answer 1

		November	December
Cash Balance, Beginning		38700	845
add: Cash Receipts		484400	403000
Total Cash Available		523100	403845
Less:
Cash Disbursment prior to financing		467000	431300
add: Minimum Cash Balance		38700	38700
Total Cash Needed		505700	470000
Excess/(Deficiency)		17400	-66155
Financing:
Short Term Loand Beginning		38700	22500
Short Term Borrowing, beginning of the month		0	106200
Repayment of Loand, end of the month		16200	0
Balance Short term Loand, end of the month		22500	128700
Interest Payd 11% for one month		355	1180
Total Effect of Financing		16555	1180
Ending Cash Balance		845	38866