Systems Control has periodically had issues with cash flow due to the contract terms it has with
Question:
Systems Control has periodically had issues with cash flow due to the contract terms it has with some of their major customers. Management would like you to create a cash flow model to help them understand when they should borrow money and for how long. The next 6-month cash flow projection is as follows:
Month 1 2 3 4 5 6 Cash inflow $340 $760 $1,450 $650 $830 $1,300 Cash outflow $650 $1,200 $650 $1,320 $600 $750
Systems Control would like to keep a cash balance of at least $100,000 and currently has $150,000 on hand. The company can use their one-month credit line or borrow for 2 to 5 months. The table below describes the loan options, rates and fixed fees. The fixed fee is incurred each time a loan option is used, regardless of the loan amount.
Term Fixed Loan (Months) Rate Fee Credit line 1 1.30% $0 B 2 1.80% $500 C 3 2.67% $800 D 4 3.06% $1,000 E 5 4.17% $1,100
Create an Excel model to determine the optimum borrowing strategy.