Bond Calculation Formulas by ltd59884

VIEWS: 0 PAGES: 7

More Info
									                                        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.

Each spreadsheet is graded in five areas as described below.

Answers (A):
    Correct Answers will be the most significant part of the grade for virtually all spreadsheets. No answer key
      is available for spreadsheets.

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.
   2) Spreadsheet exhibits second
   3) Diagnostic sheets last.

    Note: Important information from spreadsheets should be presented in the Narrative. Do not force the reader to
    hunt through spreadsheets to find the basis for your discussion. In addition, when referencing the spreadsheet,
    use the specific exhibit number and title.




                                                      Page 2 of 7
BA 303                                   Spreadsheet Assignment Guide                            Spring 2006



Individual Spreadsheet Assignments
Follow the instructions above for all Spreadsheet Assignments.
Follow the instructions below for individual Spreadsheet Assignments.
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.
     b. Your findings
     c. Your assessment of Taco Swell’s strengths and weaknesses
     d. Your recommendation for how Taco Swell can improve its weaknesses

Grading (maximum points possible for each part of the spreadsheet):
     Narrative: 20
     Correct Answers: 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.
Quantify your narrative with figures from your spreadsheets!!
Grading (maximum points for each part of the spreadsheet):
     Narrative: 25
     Correct Answers: 30
     Diagnostic: 15
     Formulas and Cell References: 15
       Presentation: 15


3. Stock and Bond Values
Calculate the returns on stocks and bonds. Start with the template provided for Spreadsheet #3.

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?
Quantify your narrative with figures from your spreadsheets!!

Grading (maximum points for each aspect of the spreadsheet):
     Narrative: 33
     Correct Answers: 37
     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.
Quantify your answers with figures from your spreadsheets!!

Grading will be as follows:
     Narrative: 30
     Correct Answers: 40
     Diagnostic: 10
     Formulas and Cell References: 10
     Presentation: 10




                                                       Page 7 of 7

								
To top