Numbers on current excel might be wrong so start fresh

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. 12. Problem 1112 (25 points) Note: All cash amounts are in $1000s $ 100,000.00 9% Available cash at the beginning Interest rate on six-month loan Interest rate on one-month loan Oct NON Start of month Beginning cash balance carried over Six-month loan One- month loan Cash on hand after taking out loans Interest on one-month loan Loan and interest payments Cash available after loan payments Revenues from sales Bills to be paid Ending cash after revenues and bills $ $ 100.00 $ 200.00 $ 300.00 $ 600.00 $ 700.00 $ 900.00 700.00 S 600.00 $ 600.00 $ 400.00 $ 200.00 $ 100.00 End of December requirements Six-month loan principal due Interest on six-month loan due One month loan due One month loan interest due Sheet1 Sheet2 Available cash (December) Sheet3 Revenues from sales Bills to be paid Ending cash after revenues and bills $ $ D 100.00 $ 200.00 $ 300.00 $ 600.00 $ 700.00 $ 900.00 700.00 $ 600.00 $ 600.00 $ 400.00 $ 200.00 $ 100.00 End of December requirements Six-month loan principal due Interest on six-month loan due One month loan due One month loan interest due Total cash required (December) Available cash (December) Total interest payments Sheet1 Sheet2 Sheet3 + Assumptions: At the beginning of each month (Aug-Dec), the company pays back previous month's loans with interest and takes out new loans large enough to cover these paybacks. 2. At the end of December the six-moth loan with interest must be paid back. 3. (1) and (2) occur at the beginning of each month. 4. Then, during the month, the company receives revenues and pays bills-it must have enough on hand to have nonnegative ending month balance Hints: 1. There is only one six-month loan and it is due with interest at the end of December 2. Loan and interest payment for each 1-month loans are due at the beginning of the next month. 3. Cash available after loan payments due cannot be negative. Ending cash after revenues and bills cannot be negative. At the end of December the six-month loan and the 1-month lean taken out at the beginning of December are both due with interest. 6. Total interest payments interest on 6-month loan +interest on all the 1-month loans. Answer key Cell C35 -99