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 companys 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 companys 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 companys 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 companys 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