Question

Show what formulas are used in excel to solve problems please.

2 COMMON STOCK VALUATION Crosby Corporation common stock paid $1.32 in dividends last year and is expected to grow indefinitely at an annual 7 percent rate. What is the value of the stock if you require an 11 percent return? 4 6 DATA 7 Growth rate 8 Dividend (Do) ?? Required rate 10 11 SOLUTION 12 Value 13 14 Requirements: 70% S1.32 1 1 .0% 1. Start Excel. Download and open the workbook named: Keown Martin Petty Problem 8-8 Start. Important note: All calculations must be shown using cell references. Do NOT enter absolute numbers in the cells. 15 2. Calculate the value of the common stock by using the dividend 16 valuation model in cell B12. (1 point)

What role does capital budgeting play in the firm's overall financial planning program? answer must be at least 300 words.

Answer 1

Because the worksheet has not been provided, I have answered these questions only by mentioning what formulas or functions are to be used in Excel. Also I have given cell references for each formula or function that is applicable for Excel.

Crossby Corporation question:

Price= Dividend(1+growth)/required rate-growth

Given, dividend=$1.32(B8)

Growth rate=7%(B7)

Required rate=11%(B9)

So,price=B8*(1+B7)/(B9-B7)

=1.32(1+7%)/(11%-7%)

=$35.31

Salte Corporation question:

The formula is derived from the previous question formula as follows:

cost of equity=dividend(1+growth)/(price-floatation cost)+growth

Given, dividend=$1.45(B8)

Growth rate=6%(B9)

Floatation cost=6%(B10)

Market price=$27(B7)

So, cost of equity=(B8*(1+B9))/(B7*(1-B10))+B9

=(1.45(1+6%))/(27*(1-6%))+6%

=12.06%

Belton question:

Annual interest payment=par value*annual interest

=B7*B8

=1,000*7%

=$70

Net proceeds per bond=Market price-floatation cost

=B10*(1-B11)

=958*(1-11%)

=$852.62

Cost of debt: In excel rate function requires the following arguments:

Nper that is years to maturity=15(B9)

Pmt that is interest amount=70(B7*B8)

Pv that is price=852.62(B10*(1-B11)) but since it is an outflow we will enter -852.62.

Fv that is par value of the bond=$1,000(B7)

Type that is which will be 0 since interest is paid annually.

Cost of debt=RATE(B9,B7*B8,-B10*(1-B11),B7,0)

=RATE(15,1000*7%,-958(1-11%),1000,0)

=8.81%

Tax rate=18%(B12)

So after tax cost of debt=cost of debt(1-tax rate)

=8.81(1-18%)

=7.22%

Capital budgeting is useful taking investment decisions for a business. Decision of whether to make or not make an investment depends on the rate of return that the investment is expected to generate. But the acceptable rate is very much dependent on the type of company and also on the kind of investment so it is largely variable.

It is an important decision making tool because it involves accountability as well as responsibility. Because if resources are sought to be invested in an investment without any idea of risks and returns a business can be held responsible for wrong financial planning. It also measures the long term viability of an investment and its profitability.

It helps in financial planning by:

1. Setting long term goals needed for growth
2. Seeking new investments
3. Estimate and forecasting future cash flow for an investment
4. Helps in transfer of information at various levels to ultimate decision makers
5. Monitors and controls expenditure which is a very important step of the process without which a good project can easily fail
6. Taking decisions as to acceptability or unacceptability of investments which helps a business to efficiently determine whether it should continue with the investment or abandon the same early which will save both time and money.

If capital budgeting decisions are not taken correctly, it shows a lack of proper financial planning which may result in decision makers losing their jobs. In fact the entire process of running a business is all about taking multiple capital budgeting decisions.Most importantly, unlike other business processes, capital budgeting involves taking two important decisions at the same time- a financial decision and an investment decision.

Financially company is committing itself which involves certain risks such as overrun costs, time delays and regualtory problems. From investment point of view, it is making an investment in future direction and growth. So we need a good balance of a two for successful implementation of financial planning.