# Bond Calculation Formulas by ltd59884

VIEWS: 0 PAGES: 7

• pg 1
```									                                        Spreadsheet Assignment Guide

Spreadsheet assignments provide the opportunity to perform in-depth financial analysis that resembles the work
performed by financial professionals. They also provide an essential, hands-on learning opportunity that increases
understanding of course materials.

Spreadsheet assignments may be completed individually or in groups of up to three members (no larger). Hand in a
single paper for the group. Do not simply allocate the work. All members of the group should work together on
each assignment to assure all group members have an opportunity to learn course material.

Spreadsheet assignments are due at the beginning of class. Assignments received in the middle or end of the class
will be penalized one-third of a grade (e.g., A- will become B+), and an additional one-third of a grade for each day
late.

Spreadsheet assignments require comprehensive application of course material. They can be time-consuming and
challenging. Grading will recognize demonstrated effort (even if you don’t get the right answer, you get points for
presenting a well thought-out, carefully prepared project). Spreadsheet Assignments must meet the following
expectations to receive full credit. Failure to meet these requirements will result in loss of points.

Formulas and Cell References (F):
 Do not hard code numbers in your spreadsheet. Hard coding is defined as directly typing numbers on the
keyboard. In virtually all spreadsheets the beginning numerical data will already be entered for you. Do
not re-enter the numbers provided.
(Exception: you may hard code numbers in formulas that are not variables, such as the number 1 in the
following formula: Future Value = (1+r)n.)
 Create formulas with cell references, not numbers. For example, if you are multiplying 3 by 5 do not enter
"15", the result, and do not enter "=3*5"; instead, in cell C8, enter 3; in cell C9, enter 5; and in cell C10
enter "=C8*C9". (as mentioned above, in this example cells C8 and C9 will already be prepared for you,
you need only create cell C10).
 Do not use preset formulas in Excel. The only exception to this is that you may use the SUM formula.

Narrative(N):
 One page description of your work (approximately 500 words per page, or the average 12 point font, single
spaced, normal margins). The narrative is addressed to the president of the company in question, as if a
company analyst wrote it. Do not repeat the questions. Do not # your responses to questions. Each
narrative includes three sections:
o What you began with (1/4 page max)
o The formulas and processes you used to analyze the information provided (1/4 page max)
o The conclusions drawn from your analysis (1/2 page minimum)
 The narrative must quantify your analysis, particularly in the discussion of your conclusions
o Not – “Acme, Inc.’s expenses increased significantly.”
o Rather – “Acme, Inc.’s expenses increased by 20% (from \$1,000 to \$1,200) in 2002, reducing its
ability to operating at a profit.”
 Avoid using “I”, “we”, “you”.
o Not – “I arranged the financial information into a standard income statement.”
Rather – “The financial information was arranged into a standard income statement.”
o Not – “I think this analysis shows that the company is profitable.”
Rather – “This analysis shows the company is profitable.”
BA 303                                   Spreadsheet Assignment Guide                             Spring 2006

o    Not – “You can see from this analysis.”
Rather “It can be seen from this analysis...” Or “This analysis shows...”
    Refer to a company as “it”, not “they”.
o Not – “They operate profitably” (not – The company operate profitably)
Rather – “It operates profitably” (rather – The company operates profitably)

Diagnostic(D):
 In addition to the spreadsheet itself, you must print a diagnostic sheet. A diagnostic sheet is a printout of
the cell-formulas themselves rather than the results of those formulas. Diagnostic sheets are created as
follows:
1. Save the spreadsheet as a separate file. (avoids the problem of switching back)
2. Go to the Tools menu and select Options.
3. Click on the View tab and select Formulas.
4. Widen columns to show entire formulas.
5. Go to the File menu and select Page Setup.
6. Click on the Sheet tab and click in the box “Row and column headings”.

Presentation (P):
 All materials must be neat and well laid out.
 Section the spreadsheet into logical components that are easy to follow
 Good separation between pages: if the exhibit is too big to fit on one page, you must assure that:
o The page breaks at a logical breaking point, not in the middle of a table.
o Row and column headings are repeated on the second page.
 Each exhibit contains an exhibit number followed by a title, centered at the top of the page.
 Each exhibit must have a different title. (i.e. Exhibit 1, Acme, Inc. Balance Sheet; Exhibit 2, Acme, Inc.
Income Statement).
 Formatting as follows
o Your name in the upper right hand header of each page.
o Staple in the upper left hand of pages printed in “portrait”; in the upper right hand corner of pages
printed in landscape format
o Page numbers at the bottom of each page. Page numbers must be consistent (avoid “Page 1” on
all documents).
o Consistent formatting: numbers representing like quantities should be in the same font, size, and
color, and use the same number of decimal places (maximum of two)
o Underline totals and subtotals in the manner of financial statements (Use Excel row borders: Go
to Format menu, select Cells, click on the Border tab).

Order of documents:
1) Narrative first.
3) Diagnostic sheets last.

Note: Important information from spreadsheets should be presented in the Narrative. Do not force the reader to
use the specific exhibit number and title.

Page 2 of 7
BA 303                                   Spreadsheet Assignment Guide                            Spring 2006

Diagnostic Sheets must accompany all Spreadsheet Assignments!!

1. Financial Statements, Cash Flow, Ratios
 Begin with template on website
 Arrange the financial information provided into an Income Statement and a Balance Sheet
 Calculate the following:
o Operating Cash Flow
o Free Cash Flow
o Prove the DuPont Identity
 Calculate Return on Equity
 Calculate Profitability, Asset Turnover, and Equity Multiplier and multiply the three
numbers
 Show that the two lines above are equal

Your narrative description should be brief (one page max, single spaced, 12 point font). The narrative should
indicate the following:
a. What you started with.
c. Your assessment of Taco Swell’s strengths and weaknesses
d. Your recommendation for how Taco Swell can improve its weaknesses

 Narrative: 20
 Diagnostic: 20
 Formulas and Cell References: 20
 Presentation: 20

2. Forecasting
Calculate 2005 financial statements and external funds needed for Betsen Boutique by following the directions and
answering the questions below in parts one to four. Bentsen’s 2003 and 2004 income statement and balance sheet
are provided in Template 2. Begin with the template. Do not hard code numbers in your calculation, simply
reference the numbers in the spreadsheet.

Part One: Forecast
1. Given these financial statements, use the percent of sales method to develop pro forma statements for 2005.
This is accomplished through the following steps:
a. Complete the column Means of Forecast. The means of forecast for each line item is described
below in items 2-7. Read carefully and record information for each line item in brief, descriptive
terms, such as “given”, “constant”, “% of sales”, “% of other line item” (specify exactly which
line item), or one of the formulas covered in class that allow you to calculate specific line items.
b. Complete the columns for Historical %. Each Historical % is expressed as a percentage (usually
as a percentage of sales).
c. Complete the column for Forecast %. The Forecast % will either be given below in points 2-7, or
it will be the average for the line item in the Historical % Statements for 2003 and 2004.
d. Calculate the Forecast numbers for 2005. This is accomplished by referring the Means of Forecast
column. If the Means of Forecast is “% of Sales”, multiply the forecast 2005 sales figure by the
Forecast % for that line item.
2. Sales for 2005 are projected to be 15% higher than 2004.
3. Fixed expenses, depreciation, interest, short term notes payable, and common stock remain constant (they
are a constant number, not a constant percentage).

Page 3 of 7
BA 303                                    Spreadsheet Assignment Guide                              Spring 2006

4.   The following are forecast as a percent of sales: cost of goods sold, selling, G&A expenses, all current
assets, accounts payable, and other current liabilities.
5.   Net fixed assets change only by depreciation (assume there are no sales or purchases of fixed assets).
6.   2004 Dividends = 4,090; keep dividends constant as a percentage of net income.
7.   Project long-term debt as 50% of equity (in the same year).
8.   Determine and record External Funds Needed (EFN) for 2005.
9.   Print a numerical exhibit and a diagnostic sheet for this page. Diagnostic sheets are not necessary for parts
two through four below.

Part Two: Internal Growth
10. Using the sustainable growth formula, find the greatest % sales growth Betsen can support in 2005 without
additional borrowing or additional equity funding (you may hard code numbers for this equation).
11. Input this % sales growth in your spreadsheet (instead of the original 15% growth). Is EFN = \$0? Why or
why not?

Part Three: Sensitivity Analysis
12. Show sensitivity to changes in sales growth and cost of goods sold in the following scenarios (you do not
need to print a pro-forma statement for each scenario, simply record the results in the sensitivity table
described in Part Four):
 Sales Growth
(a) Zero growth: change sales growth for 2005 to 0% and record the impact on
EFN
(b) High growth: change sales growth for 2005 to 20% and record the impact on
EFN
 Cost of Goods Sold (Reset sales growth to the 15% in the original base case forecast
(a) Low COGS: change the Forecast % for Cost of Goods Sold to 65% and
record the impact on EFN
(b) High COGS: change the Forecast % for Cost of Goods Sold to 74% and
record the impact on EFN

Part Four: Sensitivity Table
13. Summarize your findings regarding EFN in a sensitivity table, similar to the one below:
Scenario                        % Growth              COGS as a % of Sales                  EFN \$
Base Case
Zero Growth
High Growth
High COGS
Low COGS

Numbers in the % Growth and COGS as a % of Sales columns are expressed as a %. Numbers in the EFN
Column are expressed in \$. You may hard code numbers in your sensitivity table.

14. Study your sensitivity table. Which independent variable (% Growth or COGS as a % of Sales) causes
greater variation in EFN (the dependent variable)?

Part Five: Narrative Description
15. Describe the data you started with. (2 points)
16. Describe what you did to generate a 2005 forecast for Betsen Boutique. (2 points)
17. Present and explain your findings for Betsen’s internal growth rate. Describe what the internal growth rate
means and why it produces an EFN that does or does not equal \$0. (4 points)
18. Present your sensitivity table (it must appear in your narrative) and discuss your findings. Should the
managers of Betsen Boutique be more concerned about increases in material costs or rapid growth in
regards to increased need for external funds? Why? (5 points)
19. Imagine you are the CFO of Betsen Boutique. The VP of Marketing just informed you that the President
has approved her request to increase the 2005 Marketing budget by an additional \$2,000 (above and beyond
the amount in your forecast for 2005). The VP of Marketing/Advertising goes on to say that the CEO was
reluctant to approve the additional \$2,000, because it would exhaust all existing credit lines. She convinced

Page 4 of 7
BA 303                                    Spreadsheet Assignment Guide                               Spring 2006

the CEO to approve the increase, however, because it would increase projected sales growth for 2005 to
30% (vs. 15% in the base case). Assume that the new marketing plan would increase sales to 30%. As
CFO of Betsen Boutique, what information and/or concerns would you communicate to the President
regarding the forecast impact of this plan? You must make a recommendation based on the information
available. Recommending the issue for further study is not an option. (7 points)
20. Assume Betsen Boutique wants a Total Debt/Total Equity ration of 1. As CFO, how much should Total
Debt and Total Equity be in 2005. Change Common Stock and Long Term Debt to produce a Total
Debt/Total Equity ratio of 1 (5 points).
21. Review your work to assure spreadsheet and written explanations are polished and logical.
 Narrative: 25
 Diagnostic: 15
 Formulas and Cell References: 15
   Presentation: 15

3. Stock and Bond Values

Part One: Bond Analysis
Given the following bond, find its value in each half-year up to maturity. Show its expected return in each year,
divided into the coupon yield and capital gains yield components.
Face value: \$1,000
Semiannual coupon: 7%
Maturity: 6 years
Yield to maturity: 5%, 7%, 9% (3 different scenarios)

Hardcode numbers in the information section in the upper left corner. Use formulas and cell references in your
calculation of bond value.

Definitions:
 Total Bond Value = PV of Face Value + PVA of Coupons
 Period Capital Gain \$ = Dollar increase/decrease in the bond’s value during the period (Or, if you
purchased the bond on the first day of the period and sold it on the last day of the period, what capital gain
return would you recognize?)
 Period Capital Gain Yield % = Percent increase/decrease in the bond’s value during the period
 Period Coupon Yield \$ = Total coupon payment(s) the investor receives during the period (Or, if you
bought the bond on the first day of the period and sold it on the last day of the period, how much money
would you receive in the form of Coupon payments).
 Period Coupon Yield % = Percent return on the bond’s value in coupons during the period
 Period Total Return % = Period Capital Gain % + Period Coupon Yield %
Print results for all three scenarios. Print a diagnostic sheet only for the case in which the YTM = 6%.

Part Two: Stock Analysis
Using the dividend growth model, calculate the price (the intrinsic value) of First American Financial Corporations’
stock (FAF) as of January 1, 2005. For simplicity, assume that all dividends for a given year are paid to the
shareholder on record at the end of the day on the last day of the year, December 31.

First, using the actual information provided for FAF for 1997 to 2005, calculate the following:
 Total dividends in each year
 Annual increase <decrease> in dividends in dollars (\$)
 Annual increase <decrease> in dividends as a percentage (%)
 Average annual dividend growth (%)

Page 5 of 7
BA 303                                     Spreadsheet Assignment Guide                            Spring 2006

Next, using the irregular dividend growth model, calculate the intrinsic value of FAF’s stock using the assumptions
in the following scenarios.

Scenario 1: Use the following assumptions:
 Annual dividend growth for each of the next five years (2005 – 2009) is equal to the average annual
dividend growth from 1997 to 2004
 Annual dividend growth for years six and beyond (2009+) of 5%
 Discount rate is 8%

Scenario 2: Imagine that FAF announces that it will increase dividends at an annual rate of 6% over the next five
years rather than the historical average you calculated (assume FAF’s announcement is credible). Input this new
average annual growth rate in your spreadsheet and record the resulting intrinsic stock value.

Scenario 3: Imagine that FAF announces that it will increase dividends at an annual rate of 30% over the next five
years rather than the historical average you calculated (assume FAF’s claim is credible). Input this new average
annual growth rate in your spreadsheet and record the resulting intrinsic stock value.

Print the results for all three scenarios. Print a diagnostic sheet only for Scenario 1.

Part Three: Narrative Description (3 points per question)
1. Bonds
a. Briefly describe how you analyzed the bond data.
b. How did the price of each bond evolve over time?
c. How is the price of the bond impacted by a change in yield to maturity?
d. How did the investor’s return on the bond change over time? How did it change with a change in
yield to maturity?
e. What is the greatest unknown in predicting an investor’s return on a bond.
2. Stocks
a. Briefly describe how you analyzed the stock data.
b. How does the intrinsic value of FAF’s stock change if dividends are projected to increase at 6%
per year instead of the historical average?
c. How does the intrinsic value of FAF’s stock change if dividends are projected to increase at 30%
per year instead of the historical average?
d. What is the greatest unknown in predicting an investor’s return on a stock?
3. Comparison
a. Which return is easier to predict? The return on a stock or a bond?
b. If you were to invest \$1,000 today, would you invest in stocks or bonds? Why?

 Narrative: 33
 Diagnostic: 10
 Formulas and Cell References: 10
 Presentation: 10

4. Capital Budgeting
This assignment is based on Problem 9-16, Fusion Chips. Using the template provided, do the problem, parts a
through f (skip part g). Comment on the results. The spreadsheet that you produce should be labeled as Exhibit 1
under Base Case Conditions, and should be carefully printed on two pages so that the acquisition scenario appears
on a page by itself. After doing this base case problem, vary the assumptions in each of the following ways:

1.   After acquiring Fusion Chips, TGI is unable to increase sales as hoped, and sales stay the same as
projected for Fusion Chips as a stand-alone company.
2.   After the acquisition, margins remain at 40% instead of increasing to 45%.

Page 6 of 7
BA 303                                    Spreadsheet Assignment Guide                              Spring 2006

3.   Both of the conditions above in 1) and 2) apply
4.   Reverting to the original predictions for sales and margins in the acquired company, now assume
that the terminal growth rate of cash flows is only 3%.

For each of these alternative scenarios, you should have an exhibit. You don’t need to reprint the part of the exhibit
that applies to the stand-alone value of Fusion Chips, but you do need to print the assumptions and the projections
and valuation for the value of Fusion Chips as acquired.

Don’t forget to print one diagnostic sheet for Exhibit 1.

In the verbal section of your report, comment on the effects of your sensitivity analysis in the above 4 cases
compared to the base case, and comment on the NPV profile and its effect on your final judgment as to whether TGI
should acquire Fusion Chips.