Text is in screenshots, very easy to read

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. O P w * 10:27 PM 2/14/2020 Search the Web or type S B AutoSave Of H File Home : Sp20-3560-ETO6-Ch04-2-Shell - Excel Page Layout Formulas Data Review S earch View Help o X Comments Insert Draw Calibri 11 A === General A 12 D E A A Wrap Text 3 Merge & Center - Sign in - Share E' 47 O 5 Sort & Find & Ideas Filter Select Editing Ideas Paste BI U D $ -% -8 Insert Delete Format Conditional Format as Formatting Table Styles Cell Styles Clipboard Font Alignment Number Cells A1 x fc Sheet1!A1 C D E F G H I J K L M N O P Q R S 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 Hints: Six-month loan is taken out just once at the start of the first month. It is due at the en One-month loan is taken out at the start of each of the six months and is due the next 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 Cash on hand after taking out loans is the sum of beginning cash and loan(s) taken out Interest due on one-month loans are payable the following month. E.g. Interest of loa One-month loan repayment due is the one-month loan taken in the previous month. One-month loan and interest to be paid is the sum of the interest due on one-month Cash available after loan payments due is cash on hand after taking out loans minus lo 100 700 200 600 300 600 600 400 700 200 900 100 Revenues from sales Bills to be paid Ending cash after revenues and bills Sheet1 Sheet2 Sheet3 Ending cash after revenues and bills cannot be negative. + Ready NE O P w * 10:27 PM 2/14/2020 Search the Web or type S B AutoSave Of H File Home : Sp20-3560-ETO6-Ch04-2-Shell - Excel Page Layout Formulas Data Review S earch View Help o X Comments Insert Draw Calibri 11 A === General A 12 D E A A Wrap Text 3 Merge & Center - Sign in - Share E' 47 O 5 Sort & Find & Ideas Filter Select Editing Ideas Paste BI U D $ -% -8 Insert Delete Format Conditional Format as Formatting Table Styles Cell Styles Clipboard Font Alignment Number Cells A1 x fc Sheet1!A1 B C D E F G H I J K M N O P Q R S Revenues from sales Bills to be paid Ending cash after revenues and bills 100 700 200 600 300 600 600 400 700 200 900 100 Ending cash after revenues and bills cannot be negative. At the end of December the six-month loan and its interest must be paid off. 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 At the end of December the one-month loan taken out at the beginning of December Ending cash for December The ending cash position for December must be sufficient to pay off the obligations. Total interest payments Answer key: 99 44 Sheet1 Sheet2 Sheet3 + Ready J -_ NE O w TUI 10:28 PM 2/14/2020 * Search the Web or type S B AutoSave Of H File Home : Sp20-3560-ETO6-Ch04-2-Shell - Excel Page Layout Formulas Data Review S earch View Help o X Comments Insert Draw Calibri 11 Sign in - Share E' 47 O 5 Supet e in de A === General A 12 D A A Wrap Text 3 Merge & Center - E Insert Delete Format Paste LG BI U D $ -% -8 Cell Insert Delete Format Ideas Conditional Format as Formatting Table Styles Cell Styles Sort & Find & Filter Select Editing Clipboard Font Alignment Number Cells Ideas A1 : G * v H fc =Sheet1!A1 I J F V W X 14 15 K L M N O P Q R S T U Hints: Six-month loan is taken out just once at the start of the first month. It is due at the end of six months. One-month loan is taken out at the start of each of the six months and is due the next month. Cash on hand after taking out loans is the sum of beginning cash and loan(s) taken out. Interest due on one-month loans are payable the following month. E.g. Interest of loan taken out in Jul is due in Aug. One-month loan repayment due is the one-month loan taken in the previous month. E.g. Loan taken out in Jul is due in Aug. One-month loan and interest to be paid is the sum of the interest due on one-month loan and the one-month loan due. Cash available after loan payments due is cash on hand after taking out loans minus loan and interest payments due, and it cannot be negative. 600 400 700 200 900 100 Ending cash after revenues and bills cannot be negative. At the end of December the six-month loan and its interest must be paid off. At the end of December the one-month loan taken out at the beginning of December and its interest must be paid off. 34 Sheet1 Sheet2 Sheet3 + Ready 0 -- + 100% O P w TUI 10:28 PM 2/14/2020 * Search the Web or type S B Sign in - AutoSave Of H File Home : Sp20-3560-ETO6-Ch04-2-Shell - Excel Page Layout Formulas Data Review S earch View Help o X Comments Insert Draw Calibri A E General 5 11 DA Wrap Text Merge & Center - Share E AY O Sort & Find & Ideas Filter Select Editing Ideas A === F al Paste $ - % -28 BIU - Font Insert Delete Format Conditional Format as Formatting Table Styles Cell Styles Clipboard Alignment Number Cells A41 E F G H I J Q R S T U V W X Y K L M N O P Ending cash after revenues and bills cannot be negative. At the end of December the six-month loan and its interest must be paid off. At the end of December the one-month loan taken out at the beginning of December and its interest must be paid off. The ending cash position for December must be sufficient to pay off the obligations. Answer key: 99 Sheet1 Sheet2 Sheet3 + - Ready @ J - - E 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. O P w * 10:27 PM 2/14/2020 Search the Web or type S B AutoSave Of H File Home : Sp20-3560-ETO6-Ch04-2-Shell - Excel Page Layout Formulas Data Review S earch View Help o X Comments Insert Draw Calibri 11 A === General A 12 D E A A Wrap Text 3 Merge & Center - Sign in - Share E' 47 O 5 Sort & Find & Ideas Filter Select Editing Ideas Paste BI U D $ -% -8 Insert Delete Format Conditional Format as Formatting Table Styles Cell Styles Clipboard Font Alignment Number Cells A1 x fc Sheet1!A1 C D E F G H I J K L M N O P Q R S 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 Hints: Six-month loan is taken out just once at the start of the first month. It is due at the en One-month loan is taken out at the start of each of the six months and is due the next 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 Cash on hand after taking out loans is the sum of beginning cash and loan(s) taken out Interest due on one-month loans are payable the following month. E.g. Interest of loa One-month loan repayment due is the one-month loan taken in the previous month. One-month loan and interest to be paid is the sum of the interest due on one-month Cash available after loan payments due is cash on hand after taking out loans minus lo 100 700 200 600 300 600 600 400 700 200 900 100 Revenues from sales Bills to be paid Ending cash after revenues and bills Sheet1 Sheet2 Sheet3 Ending cash after revenues and bills cannot be negative. + Ready NE O P w * 10:27 PM 2/14/2020 Search the Web or type S B AutoSave Of H File Home : Sp20-3560-ETO6-Ch04-2-Shell - Excel Page Layout Formulas Data Review S earch View Help o X Comments Insert Draw Calibri 11 A === General A 12 D E A A Wrap Text 3 Merge & Center - Sign in - Share E' 47 O 5 Sort & Find & Ideas Filter Select Editing Ideas Paste BI U D $ -% -8 Insert Delete Format Conditional Format as Formatting Table Styles Cell Styles Clipboard Font Alignment Number Cells A1 x fc Sheet1!A1 B C D E F G H I J K M N O P Q R S Revenues from sales Bills to be paid Ending cash after revenues and bills 100 700 200 600 300 600 600 400 700 200 900 100 Ending cash after revenues and bills cannot be negative. At the end of December the six-month loan and its interest must be paid off. 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 At the end of December the one-month loan taken out at the beginning of December Ending cash for December The ending cash position for December must be sufficient to pay off the obligations. Total interest payments Answer key: 99 44 Sheet1 Sheet2 Sheet3 + Ready J -_ NE O w TUI 10:28 PM 2/14/2020 * Search the Web or type S B AutoSave Of H File Home : Sp20-3560-ETO6-Ch04-2-Shell - Excel Page Layout Formulas Data Review S earch View Help o X Comments Insert Draw Calibri 11 Sign in - Share E' 47 O 5 Supet e in de A === General A 12 D A A Wrap Text 3 Merge & Center - E Insert Delete Format Paste LG BI U D $ -% -8 Cell Insert Delete Format Ideas Conditional Format as Formatting Table Styles Cell Styles Sort & Find & Filter Select Editing Clipboard Font Alignment Number Cells Ideas A1 : G * v H fc =Sheet1!A1 I J F V W X 14 15 K L M N O P Q R S T U Hints: Six-month loan is taken out just once at the start of the first month. It is due at the end of six months. One-month loan is taken out at the start of each of the six months and is due the next month. Cash on hand after taking out loans is the sum of beginning cash and loan(s) taken out. Interest due on one-month loans are payable the following month. E.g. Interest of loan taken out in Jul is due in Aug. One-month loan repayment due is the one-month loan taken in the previous month. E.g. Loan taken out in Jul is due in Aug. One-month loan and interest to be paid is the sum of the interest due on one-month loan and the one-month loan due. Cash available after loan payments due is cash on hand after taking out loans minus loan and interest payments due, and it cannot be negative. 600 400 700 200 900 100 Ending cash after revenues and bills cannot be negative. At the end of December the six-month loan and its interest must be paid off. At the end of December the one-month loan taken out at the beginning of December and its interest must be paid off. 34 Sheet1 Sheet2 Sheet3 + Ready 0 -- + 100% O P w TUI 10:28 PM 2/14/2020 * Search the Web or type S B Sign in - AutoSave Of H File Home : Sp20-3560-ETO6-Ch04-2-Shell - Excel Page Layout Formulas Data Review S earch View Help o X Comments Insert Draw Calibri A E General 5 11 DA Wrap Text Merge & Center - Share E AY O Sort & Find & Ideas Filter Select Editing Ideas A === F al Paste $ - % -28 BIU - Font Insert Delete Format Conditional Format as Formatting Table Styles Cell Styles Clipboard Alignment Number Cells A41 E F G H I J Q R S T U V W X Y K L M N O P Ending cash after revenues and bills cannot be negative. At the end of December the six-month loan and its interest must be paid off. At the end of December the one-month loan taken out at the beginning of December and its interest must be paid off. The ending cash position for December must be sufficient to pay off the obligations. Answer key: 99 Sheet1 Sheet2 Sheet3 + - Ready @ J - - E