Start with the partial model in the file IFM9 Ch22 P12 Build a Model.xls, from the ThomsonNOW Web site. Rework parts a through d of given Problem using a spreadsheet model. Then answer the following related question.

Do a sensitivity analysis that shows how the size of the bank loan would vary with changes in the interest rate and the compensating balance percentage, using interest rates in the range of 5 to 25 percent and compensating balances in the range of 0 to 30 percent.

Problem :
Malone Feed and Supply Company buys on terms of 1/10, net 30, but it has not been taking discounts and has actually been paying in 60 rather than 30 days. Assume that the accounts payable are recorded at full cost, not net of discounts. Malone's balance sheet follows (thousands of dollars):

Now, Malone's suppliers are threatening to stop shipments unless the company begins making prompt payments (that is, paying in 30 days or less). The firm can borrow on a 1-year note (call this a current liability) from its bank at a rate of 15 percent, discount interest, with a 20 percent compensating balance required. (Malone's $50,000 of cash is needed for transactions; it cannot be used as part of the compensating balance.)

a. How large would the accounts payable balance be if Malone takes discounts? If it does not take discounts and pays in 30 days?

b. How large must the bank loan be if Malone takes discounts? If Malone doesn't take discounts?

c. What are the nominal and effective costs of nonfree trade credit? What is the effective cost of the bank loan? Based on these costs, what should Malone do?

d. Assume that Malone forgoes the discount and borrows the amount needed to become current on its payables. Construct a pro forma balance sheet based on this decision. (Hint: You will need to include an account called "prepaid interest" under current assets.)

e. Now assume that the $500,000 shown on the balance sheet is recorded net of discounts. How much would Malone have to pay its suppliers to reduce its accounts payables to $250,000? If Malone's tax rate is 40 percent, what is the effect on its net income due to the lost discount when it reduces its accounts payable to $250,000? How much would Malone have to borrow? (Hint: Malone will receive a tax deduction due to the lost discount, which will affect the amount it must borrow.) If Malone's tax rate is 40 percent, what is the effect on its net income due to the lost discount when it reduces its accounts payable to $250,000? Construct a pro forma balance sheet based on this scenario. (Hint: You will need to include an account called "prepaid interest" under current assets and adjust retained earnings by the after-tax amount of the lost discount.)