Show what formulas are used in excel to solve problems please. What role does ca
ID: 2617234 • Letter: S
Question
Show what formulas are used in excel to solve problems please.
What role does capital budgeting play in the firm's overall financial planning program? answer must be at least 300 words.
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)Explanation / Answer
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:
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.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.