You are a relatively recent hire to the Hartz & Co.

You 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 $51,000. It is the company’s policy to maintain a minimum cash balance of $51,000 at the end of each month. Cash receipts (from cash sales and collection of accounts receivable) are projected to be $563,700 for November and $455,000 for December. Cash disbursements (sales commissions, advertising, delivery expense, wages, utilities, etc.), prior to financing activity, are scheduled to be $554,000 in November and $500,700 in December.

Borrowing, when needed, is done at the beginning of the month - in increments of $1,000. The annual interest rate on any such loans is estimated to be 13.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, in whole dollars) are assumed to occur at the end of the month. As of November 1st, the company has a $51,000 short-term loan from the local bank.

Required Use the preceding information to prepare the cash budget for November and December. (Hint: The December 31st cash balance should be $51,483.)

Data Input

Cash balance, November 1st

$51,000

Minimum required cash balance

$51,000

Budgeted cash receipts:

November

$563,700

December

$455,000

Budgeted cash disbursements:

November

$554,000

December

$500,700

Interest rate on borrowings

13.0%

per year

Short-term loan payable, as of November 1st

$51,000

Borrowings in increments of

$1,000

Solution

Select from dropdown

November

December

Cash balance, beginning

Formula

Plus: Cash receipts

Formula

Total Cash Available

Formula

Cash disbursements, prior to financing

Formula

Plus: Minimum cash balance (given)

Formula

Total Cash Needed

Formula

Excess (deficiency of) cash, before financing effects

Formula

Financing:

Balance Short term loan beginning of month

Formula

Short-term borrowing, beginning of the month^*

Formula

Repayments (loan principal), end of the month

Formula

Balance Short term loan end of month

Formula

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

Formula

Total effects of financing

Formula

Ending cash balance

Formula

^* Hint: the borrowing is at the beginning of the month

therefore your algebra needs the following:

what you borrow needs to include the interest to be paid

interest to be paid needs to be a function of the amount borrowed and any existing balance

therefore a circular reference problem

so, use High School algebra

amount to borrow = AM

amount of interest = I

formulate two linear equations

AM = f()

→

AM =

-I35+I

-I35+(H38+AM)*H18/12

IF(I35-H38*H18/12-(I29-I33)*H18/12<0, Borrow, Not Borrow)

I = f()

→

I =

(H38+I39)*H18/12

Borrow = Roundup in multiples of borrowing amounts

solve for AM and I

Not Borrow = 0

The repayment is at the end of the month

You need two IF statements:

IF (There is a loan balance , IF (Excess cash, Make a repayment , Do not make repayment) , Do not make repayment)

Make a repayment = MIN (Amount borrowed or The Amount of excess cash available)

Make the repayment in whole dollars - use INT

Test your model's algebra by entering various amounts for Cash receipts:

e.g.

100,000

500,000

750,000

12 years ago

Report issue