PLEASE SHOW FORMULAS AND SOLVER IN EXCEL AND KEEP MY 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. B D E F G H ! 3 4 Jul Aug Sep Oct Nov Dec 5 #2. Problem #112 6 Note: All cash amounts are in $10005 7 8 Available cash at the beginning 9 Interest rate on six-month loan 10 Interest rate on one-month loan 11 12 Start of month 13 Beginning cash balance carried over 14 15 Six- month loan 16 One-month loan 17 18 Cash on hand after taking out loans 19 20 Interest due for the one-month loan 21 One-month loan repayment due 22 One-month loan and interest to be paid 23 Cash available after loan payments 24 25 Revenues from sales 26 Bills to be paid 27 Ending cash after revenues and bills 100 700 200 600 300 600 600 400 700 200 900 100 E F H End of December requirements Six-month loan principal due Interest on six-month loan due Last one month loan due Interest on last one-month loan due Cash needed at the end of December 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 Ending cash for December Total interest payments Answer key: 99 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. B D E F G H ! 3 4 Jul Aug Sep Oct Nov Dec 5 #2. Problem #112 6 Note: All cash amounts are in $10005 7 8 Available cash at the beginning 9 Interest rate on six-month loan 10 Interest rate on one-month loan 11 12 Start of month 13 Beginning cash balance carried over 14 15 Six- month loan 16 One-month loan 17 18 Cash on hand after taking out loans 19 20 Interest due for the one-month loan 21 One-month loan repayment due 22 One-month loan and interest to be paid 23 Cash available after loan payments 24 25 Revenues from sales 26 Bills to be paid 27 Ending cash after revenues and bills 100 700 200 600 300 600 600 400 700 200 900 100 E F H End of December requirements Six-month loan principal due Interest on six-month loan due Last one month loan due Interest on last one-month loan due Cash needed at the end of December 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 Ending cash for December Total interest payments Answer key: 99