Kellys Boutique is contemplating several alternative means of financing an expansion One alternative is to borrow $300,000 from a local bank another alternative is to borrow this amount from investors by issuing bonds Both alternatives involve a 5 year debt period Modify the workbook file ch5 05 to compute a loan and bond analysis Name and format cell ranges as appropriate Assume an initial loan rate of 3 percent, an initial bond stated rate of 3 percent, and a market interest rate of 2 9 percent a Print the newly completed loan and bond worksheets in Value view, with your name and date printed in the lower left footer and the file name in the lower right footer b Print the worksheets from part a (above) in Formula view, with your name and date printed in the lower left footer and the file name in the lower right footer c Use the Scenario Manager to create two loan scenarios called Best Case and Worst Case In the Best Case, the rate would be 2 8 percent and the loan amount would be $325,000 in the Worst Case, the rate would be 3 2 percent and the loan amount would be $280,000 (Hint Youll need to place two cell references, separated by a comma, in the Changing cells text box ) The resulting comparison values youre trying to predict are Payment, Total Payments, and Total Interest Print the resulting summary worksheet d Use the Scenario Manager to create two bond scenarios called Best Case and Worst Case In the Best Case, the market rate would be 2 5 percent and the stated rate would be 2 percent in the Worst Case, the market rate would be 3 2 percent and the stated rate would be 3 1 percent (Again, youll need to place two cell references, separated by a comma, in the Changing cells text box ) The resulting comparison values youre trying to predict are Proceeds, Total Interest Payments, and Total Interest Expense Print the resulting summary worksheet e Use Excels goal seek feature to calculate the interest rate that the company would have to negotiate under the original loan analysis (in part a) to achieve a payment of $63,000 Round the interest rate to two decimal places Print the resulting worksheet in Value view, with your name and date printed in the lower left footer and the file name in the lower right footer f Use Excels goal seek feature to calculate the market rate necessary to achieve bond proceeds of $310,000 Round the interest rate to two decimal places

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jun 22, 2024

Kellys Boutique is contemplating several alternative means of financing an expansion. One alternative is to borrow $300,000 from a local bank; another alternative is to

Kellys Boutique is contemplating several alternative means of financing an expansion. One alternative is to borrow $300,000 from a local bank; another alternative is to borrow this amount from investors by issuing bonds. Both alternatives involve a 5-year debt period. Modify the workbook file ch5-05 to compute a loan and bond analysis. Name and format cell ranges as appropriate. Assume an initial loan rate of 3 percent, an initial bond stated rate of 3 percent, and a market interest rate of 2.9 percent.

a.Print the newly completed loan and bond worksheets in Value view, with your name and date printed in the lower left footer and the file name in the lower right footer.
b.Print the worksheets from part a (above) in Formula view, with your name and date printed in the lower left footer and the file name in the lower right footer.
c.Use the Scenario Manager to create two loan scenarios called Best Case and Worst Case. In the Best Case, the rate would be 2.8 percent and the loan amount would be $325,000; in the Worst Case, the rate would be 3.2 percent and the loan amount would be $280,000.(Hint:Youll need to place two cell references, separated by a comma, in the Changing cells: text box.) The resulting comparison values youre trying to predict are Payment, Total Payments, and Total Interest. Print the resulting summary worksheet.
d.Use the Scenario Manager to create two bond scenarios called Best Case and Worst Case. In the Best Case, the market rate would be 2.5 percent and the stated rate would be 2 percent; in the Worst Case, the market rate would be 3.2 percent and the stated rate would be 3.1 percent. (Again, youll need to place two cell references, separated by a comma, in the Changing cells: text box.) The resulting comparison values youre trying to predict are Proceeds, Total Interest Payments, and Total Interest Expense. Print the resulting summary worksheet.
e.Use Excels goal seek feature to calculate the interest rate that the company would have to negotiate under the original loan analysis (in part a) to achieve a payment of $63,000. Round the interest rate to two decimal places. Print the resulting worksheet in Value view, with your name and date printed in the lower left footer and the file name in the lower right footer.
f.Use Excels goal seek feature to calculate the market rate necessary to achieve bond proceeds of $310,000. Round the interest rate to two decimal places.