Chapter 5 Case Problem 1:

KELLY'S BOUTIQUE

Kelly's 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.

126 Part 1 Excel for Accounting

Copyright 2014 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has

deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

(Hint: You'll need to place two cell references, separated by a comma,

in the Changing cells: text box.) The resulting comparison values

you're 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, you'll need to place two cell references, separated by a

comma, in the Changing cells: text box.) The resulting comparison

values you're trying to predict are Proceeds, Total Interest Payments,

and Total Interest Expense. Print the resulting summary worksheet.

e. Use Excel's 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 Excel's goal seek feature to calculate the market rate necessary to

achieve bond proceeds of $310,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.

Kelly's Boutique Loan Amortization Schedule Loan Amount Loan Term Rate Payment Payment # 1 2 3 4 5 Total Payment Interest Principal Balance Kelly's Boutique Bond Amortization Schedule Market Rate Stated Rate Interest Payment Bond Term Face Value Proceeds (Discount) / Premium Payment # 1 2 3 4 5 Totals Interest Payment Interest Expense Amortization (Discount) Premium Carrying Value