Question

TO SOLVE USE THE EXCEL FORMAT PROVIDED IN THE PICTURES.

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.

LCD E F G H Available cash at the beginning Interest rate on six-month loan Interest rate on one-month loan Jul Aug Sep Oct Nov Dec Start of month Beginning cash balance carried over Six-month loan One-month loan Cash on hand after taking out loans Interest due for the one-month loan One-month loan repayment due One-month loan and interest to be paid Cash available after loan payments Revenues from sales $100.00 Bills to be paid $700.00 Ending cash after revenues and bills $200.00 $600.00 $300.00 $600.00 $600.00 $400.00 $700.00 $200.00 $900.00 $100.00

Edit: I don't have any more information. This is what I was given. The data from the file in question is given in the pictures.

Edit AGAIN: Tell me what information is missing.

Answer 1

What Is Given in Que.

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
Clsoing 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
Interest rate on One Month Loan
					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	-
ONE MONTH LOAN & INTEREST TO BE PAID	-	-	307,500.00	622,687.50	433,254.69	-
CASH AVAILABLE AFTER LOAN PAYMENTS	700,000.00	400,000.00	300,000.00	(200,000.00)	(433,254.69)	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
ENDING CASH AFTER REVENUE & BILLS	100,000.00	-	-	-	66,745.31	866,745.31
END DECEMBER REQUIREMENTS
SIX MONTHS LOAN PRINCIPAL DUE	$ 600,000.00
INTEREST ON SIX MONTH LOAN DUE	$    54,000.00
LAST ONE MONTH LOAN DUE	$                   -
INTEREST ON LAST ONE MONTH LOAN DUE	$                   -
CASH NEEDED AT THE END OF DECEMBER	$ 654,000.00
ENDING CASH OF DECEMBER	$ 212,745.31
TOTAL INTEREST PAYMENTS	$    87,254.69