Using excel and solver
Find the new minimum cost
Jan Feb Mar Apr 25,000 230,000 87,984 150,000 May 25,000 200,000 Financing Cost Summary Interest Earned Beg Cash Balance Accounts Receivable Borrowed Paid Acct Rec Int Paid 57,944 100,000 27,246 0 June 144,645 100,000 0 0 0 0 25,000 140,000 105,000 27,655 220,000 3,010 $1,828 $1,984 $2,879 $60 $3,095 0 0 Loan Intrst Paid Discounts Lost Net Financing Cost 106,575 120,000 Payments Owed 80,000 120,000 180,000 0 160,000 0 0 0 Deferred Deferrments Paid 0 0 0 0 3,070 125 440 290 125 723 Problem 5 Interest Earned Interest Paid, ST Loan Ending Balance 480 480 480 480 25,000 480 125 480 144,645 57,944 25,000 25,000 76,905 This spreadsheet has the model and solution to Problem 2 from Problem Set 3. Modify the model with the following new condition: If any money is borrowed against Accounts Receivable in any month, the amount must be at least $50,000, or none at all. Accounts Receivable Loan rate 1.5% per month Add the appropriate modifications and find the new optimal solution using Solver. Max Loan Amnt 0.75 Check figure: The new minimum cost is $3,235 (about $140 more than the original solution). Amnt Available to borrow 112,500 75,000 105,000 172,500 150,000 2.0% On-Time Payment Discount Six-Month Loan Amnt Hints & Comments: None 47,984 Rate 1.0% per month Investment Return 0.5% per month Min. Req'd Ending Balance 25,000 Jan Feb Mar Apr 25,000 230,000 87,984 150,000 May 25,000 200,000 Financing Cost Summary Interest Earned Beg Cash Balance Accounts Receivable Borrowed Paid Acct Rec Int Paid 57,944 100,000 27,246 0 June 144,645 100,000 0 0 0 0 25,000 140,000 105,000 27,655 220,000 3,010 $1,828 $1,984 $2,879 $60 $3,095 0 0 Loan Intrst Paid Discounts Lost Net Financing Cost 106,575 120,000 Payments Owed 80,000 120,000 180,000 0 160,000 0 0 0 Deferred Deferrments Paid 0 0 0 0 3,070 125 440 290 125 723 Problem 5 Interest Earned Interest Paid, ST Loan Ending Balance 480 480 480 480 25,000 480 125 480 144,645 57,944 25,000 25,000 76,905 This spreadsheet has the model and solution to Problem 2 from Problem Set 3. Modify the model with the following new condition: If any money is borrowed against Accounts Receivable in any month, the amount must be at least $50,000, or none at all. Accounts Receivable Loan rate 1.5% per month Add the appropriate modifications and find the new optimal solution using Solver. Max Loan Amnt 0.75 Check figure: The new minimum cost is $3,235 (about $140 more than the original solution). Amnt Available to borrow 112,500 75,000 105,000 172,500 150,000 2.0% On-Time Payment Discount Six-Month Loan Amnt Hints & Comments: None 47,984 Rate 1.0% per month Investment Return 0.5% per month Min. Req'd Ending Balance 25,000
