Question

PLEASE SHOW FORMULAS SOLVER AND KEEP IT IN THIS FORMAT!!

112. Based on Robichek et al. (1965). The Korvair Department Store has $100,000 in available cash. At the beginning of each of the next six months, Korvair will receive revenues and pay bills as listed in the file P04_112.xlsx. It is clear that Korvair will have a short-term cash flow problem until the store receives revenues from the Christmas shopping season. To solve this problem, Korvair must borrow money. At the beginning of July, the company takes out a six-month loan. Any money borrowed for a six-month period must be paid back at the end of December along with 9% interest (early payback does not reduce the total interest of the loan). Korvair can also meet cash needs through month-to- month borrowing. Any money borrowed for a one-month period incurs an interest cost of 2.5% per month. Determine how Korvair can minimize the cost of paying its bills on time.

Answer 1

1. Opening cash (cash at the beginning of Six Month)>>>>>$ 1,00,000/-

2. Christmas Shopping Season Comes In December

3. Money Borrowed for Six Month @ 9% (non-Reducing Interest Rate)

4. Money Borrowed for One Month @ 2.5%PM

5. Also Closing cash Will be Cash at Beginning of Next Six Months Hence Has to be minimum $1,00,000/-

Assumptions

1. Revenue from Sales & Bills are also in Thousands

2. One Month Loan Has been Considered to be take on 1st day of Month & To be Pay on 1st day of next month

Now

1. As stated cash flows of company will be poor until Christmas Shopping season which is in sixth Month

2. One Month interest Rate is 5%PM & Six Months Interest Rate is 9%

Hence any money required in the duration has to be borrowed for 6 months as there is less rate of interest

Hence Total Of Sales from July to Nov (100+200+300+600+700=1900) is $ 19,00,000/-

And Bills Payable of July to Nov (700+600+600+400+200=2500) is $ 25,00,000/-

Hence difference $ 6,00,000/- has to be borrowed for 6 Months/(which is highest negative difference)

And Above Balance required to borrowed monthly

Start of Month	JULY	AUG	SEPT	OCT	NOV	DEC
BEGINNING CASH BALANCE CARRIED OVER	$ 100,000.00	$       100,000.00	$                         -	$                         -	$                       -	$        66,745.31
REVENUE FROM SALES	$      100,000.00	$       200,000.00	$       300,000.00	$       600,000.00	$      700,000.00	$      900,000.00
BILLS TO BE PAID	$      700,000.00	$       600,000.00	$       600,000.00	$       400,000.00	$      200,000.00	$      100,000.00
Additional Funds Require for Bill Payments (-)/ Surplus Fund	$   (500,000.00)	$     (300,000.00)	$     (300,000.00)	$       200,000.00	$      500,000.00	$      866,745.31
SIX MONTH LOAN	$      600,000.00	$                         -	$                         -	$                         -	$                       -	$                       -
Monthly Loan Borrowing	$                       -	$       300,000.00	$       607,500.00	$       422,687.50	$                       -
INTEREST DUE FOR THE ONE MONTH LOAN	$                       -	$                         -	$            7,500.00	$          15,187.50	$        10,567.19	$                       -
ONE MONTH LOAN REPAYMENT DUE	$                       -	$                         -	$       300,000.00	$       607,500.00	$      422,687.50	$                       -
INTEREST DUE FOR THE SIX MONTH LOAN						$        54,000.00
SIX MONTH LOAN REPAYMENT DUE						$      600,000.00
Closing Cash	$      100,000.00	$                         -	$                         -	$                         -	$        66,745.31	$      212,745.31

Hence Answer For Various Questions Are

Available Cash At the beginning	$ 100,000.00
Interest rate on Six Month Loan	9%
Interest rate on One Month Loan	2.5%
					All Fig Are in $
Start of Month	JULY	AUG	SEPT	OCT	NOV	DEC
BEGINNING CASH BALANCE CARRIED OVER	100,000.00	100,000.00	-	-	-	66,745.31
SIX MONTH LOAN	600,000.00	-	-	-	-	-
ONE MONTH LOAN	-	300,000.00	607,500.00	422,687.50	-	-
CASH ON HAND AFTER TAKING OUT LOAN	700,000.00	400,000.00	607,500.00	422,687.50	-	66,745.31
INTEREST DUE FOR THE ONE MONTH LOAN	-	-	7,500.00	15,187.50	10,567.19	-
ONE MONTH LOAN REPAYMENT DUE	-	-	300,000.00	607,500.00	422,687.50	-