Measurement Analysis with an Electronic Spreadsheet / 321
Calculate the accrued interest on a security that pays periodic interest. This formula is used to accrue the amount of interest earned from a stream of regu-lar interest payments from an investment. For this calculation, use the AC-CRINT formula. The details of the formula are ACCRINT(Issue Date, First Interest Date, Settlement Date, Annual Coupon Rate, Par Value, No. of Pay-ments per Year). For example, if the issue date is 3/31/01, the first interest date is 4/15/01, the settlement date is 4/10/01, the annual coupon rate is 11%, the par value is $1,000, and there are four payments per year, then the formula will be ACCRINT(“3/31/01”,“4/15/01”,“4/10/01”,11%,1000,4).
Calculate the annual yield for a discounted security. If a company purchases a security at a discounted rate (usually because the stated interest rate is lower than the prevailing market rate), one should use the YIELDDISC formula to determine its annual yield. The details of the formula are YIELDDISC(Settle-ment Date, Maturity Date, Price per $100 Face Value, Redemption Value). For example, if the settlement date is 9/9/05, the maturity date is 12/31/15, the price is $101, and the redemption value is $100, then the formula will be YIELDDISC(“9/9/05”,“12/31/15”,101,100).
Calculate the yield for a Treasury bill. To calculate this yield, use the TBIL-LYIELD formula. The details of the formula are TBILLYIELD(Settlement Date, Maturity Date, Price Per $100 Face Value). For example, if the settle-ment date is April 13, 2005, the maturity date is June 15, 2012, and the price per $100 face value is $94.30, then the formula will be TBILLYIELD (“4/14/05”,“6/15/12”,94.3).
Calculate the yield on a security that has a short or long first period. A secu-rity that was purchased in between its coupon payment dates will still earn the owner the full amount of the next coupon, even though the security was not held for the full period, which results in a higher than normal interest rate earned for the first period. To calculate the full-term yield with the odd-length first period, use the ODDFYIELD formula. The details of the formula are ODDFYIELD (Settlement Date, Maturity Date, Issue Date, First Coupon Date, Annual Coupon Rate, Price per $100 Face Value, Redemption Value, No. of Payments per Year). For example, if the settlement date is May 2, 2003, the maturity date is November 11, 2008, the issue date is June 6, 2001, the first coupon date is July 7, 2003, the interest rate is 8.5%, the price is $98.25, the re-demption value is $100, and four coupon payments are made per year, then the formula will be ODDFYIELD(“5/2/03”,“11/11/08”,“6/6/01”,“7/7/03”,8.5%, 98.25,100,4).
Calculate the yield on a security that has a short or long last period. This is the same type of situation as just described in the preceding scenario, except that we are now selling a security prior to the next scheduled coupon date. The formula now changes to ODDLYIELD, for which the formula detail is ODD-LYIELD(Settlement Date, Maturity Date, Last Coupon Date, Annual Coupon Rate, Price per $100 Face Value, Redemption Value, No. of Payments per Year). To use part of the preceding example, if the settlement date is May 2,
322 / Business Ratios and Formulas
2003, the maturity date is November 11, 2008, the last coupon date is August 11, 2008, the interest rate is 8.5%, the price is $98.25, the redemption value is $100, and four coupon payments are made per year, then the formula will be ODDFYIELD(“5/2/03”,“11/11/08”,“8/11/08”,8.5%,98.25,100,4).
Calculate the yield on a security that pays interest at maturity. Some securities pay all interest at the redemption date, rather than as regular coupon payments. To calculate the yield on these types of securities, use the YIELDMAT for-mula. The detail for this formula is YIELDMAT(Settlement Date, Maturity Date, Issue Date, Annual Coupon Rate, Price per $100 Face Value). For ex-ample, if the settlement date is February 15, 2002, the maturity date is April 15, 2011, the issue date is October 5, 2001, the interest rate is 8.2%, and the price is $101.125, then the formula will be YIELDMAT(“2/15/02”,“4/15/11”, “10/5/01”,8.2%,101.125).
Calculate the yield on a security that pays periodic interest. This is the standard formula for a basic bond purchase that has no unusual variations in terms of purchase or sale dates, and for which coupon payments are made in standard amounts and on regularly scheduled dates. For this situation, use the YIELD formula. The detail of the formula is YIELD(Settlement Date, Maturity Date, Annual Coupon Rate, Price per $100 Face Value, Redemption Value, No. of Payments per Year). For example, if the settlement date is January 8, 2001, the maturity date is May 15, 2007, the annual coupon rate is 7.5%, the price is $100.50, the redemption value is $100, and there are two coupon payments per year, then the formula will be YIELD(“1/8/01”,“5/15/07”,7.5%,100.50,100,2).
Most of the components of the above formulas are identical. To keep from re-peating the definitions of each component for every formula listed above, they are summarized below:
Annual coupon rate. The listed coupon rate on a security.
First interest date. The first date on which interest is earned on a security. Issue date. The date on which a security is issued.
Last coupon date. The last coupon date for a security prior to its redemption date.
Maturity date. The date on which a security expires.
No. of payments per year. The number of coupon payments per year. Par value. The listed price on a security.
Price per $100 face value. The actual price paid for a security can be higher or lower than the face value, depending on the discount or premium paid to ac-quire the stated interest to be paid on the security.
Redemption value. The amount paid at the termination date of the security per $100 of face value.
Settlement date. The date when the security is issued to the buyer.
Measurement Analysis with an Electronic Spreadsheet / 323
The formulas described here should be sufficient for calculating the interest rates or accrued interest for the majority of investment situations for which one will need to calculate interest earnings.
RISK ANALYSIS
When constructing a financial analysis of the likely results of a set of projected cash flows, one must always remember that these cash flows are projected—they are not facts, and may vary considerably from reality. Given the level of uncer-tainty involved, it may be useful to determine the spread of possible outcomes. By doing so, one can see if all expected outcomes are grouped tightly about a single estimate, which relates to a low level of risk, or if there is a significant spread of possible outcomes, which greatly increases the risk of meeting the targeted out-come. Excel provides a wide array of statistical tools for determining the level of risk, of which this section describes six that are easy to understand and use.
The first step when using the following statistical tools is to generate a list of possible outcomes for whatever the analysis may be. For example, if there is a cap-ital project under discussion, try to obtain a number of possible outcomes, either by polling several experts in the company or industry, or by personal knowledge of previous actual outcomes for similar types of projects. Then, the first step in the risk analysis work is to determine the highest, lowest, and median values in the list of possible outcomes. These are shown in Exhibit 15.11, where we have itemized a dozen possible annual cash inflows from a project. All possible variations are noted at the top of the worksheet. Below them are the Excel formulas to find the minimum, maximum, and median values from amongst the list, alongside a list-ing of the formulas used. However, these are not very precise measures, and do not give a sufficiently accurate view of the level of risk.
To provide us with a more detailed idea of the spread of possible outcomes, we can use the Excel QUARTILE formula to generate the average outcome for the first, second, third, and fourth quartiles of all possible outcomes, which we have also converted into a graph with the Excel Chart Wizard icon. Yet another formula that tells us if there is a “lean” in the data toward the lower or higher end of the spectrum of possible results is the SKEW formula. This formula determines the presence of skew toward the higher end of the possible outcomes (which is posi-tive skew) or a skew toward the lower end of the outcomes (which is a negative skew). A skew of zero indicates no skew in either direction. Finally, the standard deviation is an extremely useful tool for determining the dispersion of possible outcomes about the median of all outcomes. The larger the standard deviation of the sample, the larger the dispersion about the median, and the greater the degree of risk that the average outcome will not be attained.
The detail of the formulas shown in Exhibit 15.11 are very simple. All of the formulas reference the range of projected cash inflows, which are noted in cells C5 through C16. The only variation from this pattern is for the quartile formulas, which also require the addition of the quartile number at the end of the formula.
324 / Business Ratios and Formulas
Risk Analysis for a Capital Project
Projected Projected Outcome Cash Inflow
1 $5,400 Minimum value of all Outcomes 2 $3,200 Maximum value of all Outcomes 3 $1,700 Median value of all Outcomes
4 $6,100
5 $2,900 Minimum value
6 $4,700 Value of 25th percentile 7 $5,800 Value of 50th percentile 8 $8,000 Value of 75th percentile 9 $3,900 Maximum value
10 $4,250
11 $5,950 Degree of skew
12 $2,500 Standard deviation
Result Text of Formula $1,700 = MIN($C$5:$C$16) $8,000 = MAX($C$5:$C$16) $4,475 = MEDIAN($C$5:$C$16)
$1,700 = QUARTILE($C$5:$C$16,0) $3,125 = QUARTILE($C$5:$C$16,1) $4,475 = QUARTILE($C$5:$C$16,2) $5,838 = QUARTILE($C$5:$C$16,3) $8,000 = QUARTILE($C$5:$C$16,4)
0.24 = SKEW($C$5:$C$16) $1,808 = STDEV($C$5:$C$16)
Quartile Values
$10,000
$8,000
$8,000
$5,838 $6,000 $4,475
$4,000 $3,125
$1,700 $2,000
$0
1 2 3 4 5
Exhibit 15.11 Risk Analysis for a Capital Project
The analysis shown in Exhibit 15.11 tells us that the project has an extremely wide range of possible outcomes, with a maximum value that is more than four times higher than the minimum value. The range of possible outcomes has a pos-itive skew of 0.24, which tells us that those people providing the estimates have generally guessed that the actual outcome should be higher than the projected av-erage. Finally, the standard deviation from the mean is $1,808, which is slightly more than a 40% variation from the median. With such a large dispersion of pos-sible outcomes, the underlying data requires a great deal more validation before the project can be approved. Also, given the higher degree of risk, the cost of cap-ital used to discount the cash flows from the project may be set higher, thereby making it more difficult to obtain approval for the project.
APPENDIX
Measurement Summary
his appendix contains all the measurements described in Chapters 2 through 14. It is intended to be a quick reference for the reader who needs to find a for-mula as soon as possible. However, refer back to the related discussion in the main body of the book, since there may be comments on alternative formula derivations
or cautions on their use that may be pertinent.
The following measurements are listed in the same order as they are found within each chapter, and the name of each measurement and its derivation are shown. In some cases, multiple variations on the same measurement are shown.
Asset Utilization Measurements
Name
Sales to working capital ratio
Sales to fixed assets ratio
Sales to administrative expenses ratio
Sales to equity ratio
Sales per person
Formula
——Annualized net sales—— (Accounts receivable +
Inventory – Accounts payable)
Annualized net sales Total fixed assets
—Annualized net sales— Total fixed assets prior to
accumulated depreciation
Annualized net sales
Total general and administrative expenses
Annual net sales Total equity
— Annualized revenue — Total full-time equivalents
325
...
- tailieumienphi.vn

nguon tai.lieu . vn