Solve using Excel show all formulas and constraints and equations in solver.
Problem 1 Problem 1 (based on textbook Prablern 3.47) Jan Feb Mar Apr May June The CFO for Eagle Beach Wear and Gift Shop is in the process of planning for the company's cash flows for the next six months. The template on the spreadsheet shows the expected accounts receivables and planned payments Inet of the on- time discount for each of these months. 150,000 100,000 140.000) 230.X 200.000 100,000 The company currently has a beginning cash balance of $40,000 and desires to maintain a balance of at least $25,000 in cash at the end of each month. To accomplish this, the company has a number of ways of obtaining short-term funds: Beg Cash Balance Accounts Receivable Borrowed Paid Planned Payments Deferred Deferrments Paid Interest Earned Interest Paid, ST Loan Ending Balance 180,000 160,000 220,000 ) 120.000 80,000 120,000 1. Delay payments. In any month, the company's suppliers permit it to delay any or all payments for one month. However, for this consideration, the company forfeits a 2% discount that normally applies when payments are made on time. (Loss of this 2% discount is, in effect, a financing cost.) 2. Borrow against accounts receivables. In any month, the company's bank will loan it up to 75% of the accounts receivable balances due that month. These loans must be regaid in the following month and incur an interest charge of 1.5%. 3. Take a short-term loan. At the beginning of January, the company's bank will also give it a six-month loan to be repaid in a lump sum at the end of June. Interest on this loan is 1% per month and is payable at the end of each month. Six-Month Loun Amnt Assume the company ears 0.5% interest each month on cash held at the beginning of the month. Create a spreadsheet model the company can use to determine the least costly cash management plan (ie, minimal net financing costs) for this six-month period. What is the optimal salution? Formula Bar Check figure: Min Net Financing Cast = $3,095 Hints & Comments: The recommended template tracks cash flows but you must add a few details, below or to the side, ta account for everything involved in modeling the problem. If any money is borrowed on the short-term loan, be sure to add it to January's besinning balance. If you either borrow or defer payments in any one month, then assume the full amount must be paid back with interest or penalties in the next month (note that lost discounts are essentially the same things as an interest expense). Assume interest is earned on the beginning cash balance and is deducted from financing costs to get net financing costs. Problem 1 Problem 1 (based on textbook Prablern 3.47) Jan Feb Mar Apr May June The CFO for Eagle Beach Wear and Gift Shop is in the process of planning for the company's cash flows for the next six months. The template on the spreadsheet shows the expected accounts receivables and planned payments Inet of the on- time discount for each of these months. 150,000 100,000 140.000) 230.X 200.000 100,000 The company currently has a beginning cash balance of $40,000 and desires to maintain a balance of at least $25,000 in cash at the end of each month. To accomplish this, the company has a number of ways of obtaining short-term funds: Beg Cash Balance Accounts Receivable Borrowed Paid Planned Payments Deferred Deferrments Paid Interest Earned Interest Paid, ST Loan Ending Balance 180,000 160,000 220,000 ) 120.000 80,000 120,000 1. Delay payments. In any month, the company's suppliers permit it to delay any or all payments for one month. However, for this consideration, the company forfeits a 2% discount that normally applies when payments are made on time. (Loss of this 2% discount is, in effect, a financing cost.) 2. Borrow against accounts receivables. In any month, the company's bank will loan it up to 75% of the accounts receivable balances due that month. These loans must be regaid in the following month and incur an interest charge of 1.5%. 3. Take a short-term loan. At the beginning of January, the company's bank will also give it a six-month loan to be repaid in a lump sum at the end of June. Interest on this loan is 1% per month and is payable at the end of each month. Six-Month Loun Amnt Assume the company ears 0.5% interest each month on cash held at the beginning of the month. Create a spreadsheet model the company can use to determine the least costly cash management plan (ie, minimal net financing costs) for this six-month period. What is the optimal salution? Formula Bar Check figure: Min Net Financing Cast = $3,095 Hints & Comments: The recommended template tracks cash flows but you must add a few details, below or to the side, ta account for everything involved in modeling the problem. If any money is borrowed on the short-term loan, be sure to add it to January's besinning balance. If you either borrow or defer payments in any one month, then assume the full amount must be paid back with interest or penalties in the next month (note that lost discounts are essentially the same things as an interest expense). Assume interest is earned on the beginning cash balance and is deducted from financing costs to get net financing costs
