Student Information You Must Enter The Information In The Ye

Student Informationyou Must Enter The Information In The Yellow Fiel

Student Informationyou Must Enter The Information In The Yellow Fiel

Student Information You must enter the information in the "yellow" fields below before starting the assignment. Fall 2020 Question Rate Maturity Price YTM Name Bond 1 8.25% 10 $1,.49% 10 digit student ID # Bond 2 6.50% 20 $.49% Date Bond 3 4.50% 30 $.49%

Class Instructions: 1. You must enter the information requested above. 2. Include your entire 10 (ten) digit student ID number. 3. Do not change the questions as I have typed them. 4. Fill in all yellow boxes with the requested information and/or answer. 5. To show your cash flow drawings, you must copy the drawings onto the Question Worksheet. 6. Please show your Excel work in the same worksheet as the Question. 7. Data Analysis TookPak results such as Covariance Matrices or Regression Analysis can be inserted in a new worksheet. 8. I do give some partial credit where enough detail is presented. Question Maximum Points Available Grade 1a b c a b c d a b c d e f 1 0 Total 30 0 Comments: Question 1 Your company has an obligation to pay a single payment debt in 12 years and would like to find a low risk bond portfolio to meet this obligation. Debt (obligation to be paid in 12 years) $ 12,878,500.00 Rate Maturity (years) Price YTM Coupon Frequency Bond 1 8.25% .07485 Semi-annual Bond 2 6.50% .07485 Semi-annual Bond 3 4.50% .07485 Semi-annual

a. Calculate the Macaulay Duration for each of the three bonds. Bond 1 Points 3 Bond 2 Grade Bond 3

b. Calculate the PV of the obligation. PV oblg Points 3 Grade

c. Choose two bonds and apply the immunization process using the provided YTM for the calculations and determine the quantity of each of the two bonds to achieve the obligation. Points 6 Bond # Quantity Grade Please show all of your work in this Excel Worksheet.

Question 2 Find today's date US Treasury Yield Curve data provided in this Excel Workbook, and use it to work the problems below. Yield Curve Data a. Enter the most recent yield curve data from the provided worksheet into the corresponding yellow boxes. Use the Maturity 1 Month 3 Month 6 Month 1 Year 2 Year 3 Year 5 Year 7 Year 10 Year 20 Year 30 Year yield curve data as the spot rates for years 1 through 10. Use interpolation to calculate the missing years. Yield Points 2 Grade Year Spot Rate b. Determine the forward rates for the first 10 years. Fill in the yellow fields in the table to the right. Forward Rates Points 4 + Years Grade Spot Rate i=1 i=2 i=3 i=4 i=5 i=6 i=7 i=8 i=9 c. Determine the short rates for the first 10 years. Fill in the yellow fields in the table to the right. Short Rates Points 3 Year Grade Spot Rate d. Calculate the ten running PVs for the cash flow shown in the table to the right. Fill in the yellow fields in the table to the right. Running PV Points 3 Year Grade Cash Flow Short Rate Please show all of your work in this Excel Worksheet.

Runing PV Question 3 Based on the most recent date in the income statement, cashflow statement, and balance sheet provided in this Excel Workbook, calculate the following: a. Debt Ratio Points 1 Grade b. Current Ratio Points 1 Grade c. Quick Ratio Points 1 Grade d. Inventory Turnover Ratio Points 1 Grade e. Total Asset Turnover Ratio Points 1 Grade f. Profit Margin on Sales Points 1 Grade Please show all of your work in this Excel Worksheet. Income Statement Income Statement All numbers in thousands Period Ending 12/28//29//30/17 Revenue Total Revenue 275,466,,826,,543,000 Cost of Revenue 140,089,,258,,606,000 Gross Profit 135,377,,568,,937,000 Operating Expenses Research Development 8,067,,041,,475,000 Selling General and Administrative 14,329,,993,,830,000 Non Recurring - - - Others - - - Total Operating Expenses - - - Operating Income or Loss 112,981,,534,,632,000 Income from Continuing Operations Total Other Income/Expenses Net 1,285,,,156,000 Earnings Before Interest and Taxes 114,266,,514,,788,000 Interest Expense - - - Income Before Tax 114,266,,514,,788,000 Income Tax Expense 19,121,,973,,118,000 Minority Interest - - - Net Income From Continuing Ops 95,145,,541,,670,000 Non-recurring Events Discontinued Operations - - - Extraordinary Items - - - Effect Of Accounting Changes - - - Other Items - - - Net Income Net Income 95,145,,541,,670,000 Preferred Stock And Other Adjustments - - - Net Income Applicable To Common Shares 95,145,,541,,670,000 Cashflow Statement Cash Flow All numbers in thousands Period Ending 12/28//29//30/17 Net Income 95,145,,541,,670,000 Operating Activities, Cash Flows Provided By or Used In Depreciation 11,257,,946,,757,000 Adjustments To Net Income 4,968,,210,,394,000 Changes In Accounts Receivables -3,124,,452,,949,000 Changes In Liabilities 15,188,,408,,320,000 Changes In Inventories -238,,,000 Changes In Other Operating Activities -179,,,080,000 Total Cash Flow From Operating Activities 123,017,,744,,299,000 Investing Activities, Cash Flows Provided By or Used In Capital Expenditures -11,247,,571,,165,000 Investments -44,417,,017,,042,000 Other Cash flows from Investing Activities -610,,991,,567,000 Total Cash Flows From Investing Activities -56,274,,579,,774,000 Financing Activities, Cash Flows Provided By or Used In Dividends Paid -11,561,,126,,564,000 Sale Purchase of Stock -34,710,,270,,330,000 Net Borrowings 29,305,,266,,896,000 Other Cash Flows from Financing Activities -1,499,,158,,082,000 Total Cash Flows From Financing Activities -17,716,,549,,379,000 Effect Of Exchange Rate Changes - - - Change In Cash and Cash Equivalents 49,027,,616,,146,000 Balance Sheet Balance Sheet All numbers in thousands Period Ending 12/28//29//30/17 Current Assets Cash And Cash Equivalents 21,120,,844,,259,000 Short Term Investments 20,481,,233,,287,000 Net Receivables 85,757,,606,,885,000 Inventory 2,349,,111,,764,000 Other Current Assets 9,539,,806,,882,000 Total Current Assets 139,246,,600,,077,000 Long Term Investments 164,065,,162,,215,000 Property Plant and Equipment 22,471,,624,,597,000 Goodwill 5,116,,616,,577,000 Intangible Assets 3,893,,142,,179,000 Accumulated Amortization - - - Other Assets 5,556,,764,,146,000 Deferred Long Term Asset Charges - - - Total Assets 340,347,,908,,791,000 Current Liabilities Accounts Payable 81,469,,175,,140,000 Short/Current Long Term Debt 10,999,,308,,308,000 Other Current Liabilities 8,940,,491,,435,000 Total Current Liabilities 101,408,,974,,883,000 Long Term Debt 53,463,,987,,960,000 Other Liabilities 33,427,,826,,208,000 Deferred Long Term Liability Charges 3,624,,031,,625,000 Total Liabilities 191,922,,818,,676,000 Stockholders' Equity Misc. Stocks Options Warrants - - - Redeemable Preferred Stock - - - Preferred Stock - - - Common Stock 27,416,,313,,764,000 Retained Earnings 92,284,,152,,256,000 Treasury Stock - - - Capital Surplus - - - Other Stockholder Equity -345,,082,,000 Total Stockholder Equity 119,355,,547,,549,000 Net Tangible Assets 110,346,,789,,793,000 US Treasury Yield Curve Data Date 1 mo 3 mo 6 mo 1 yr 2 yr 3 yr 5 yr 7 yr 10 yr 20 yr 30 yr 1 mo 3 mo 6 mo 1 yr 2 yr 3 yr 5 yr 7 yr 10 yr 20 yr 30 yr 8/4/20 0.57 0.59 0.73 0.84 0.98 1.09 1.38 1.66 1.86 2.19 2.60 0.00 0.02 0.16 0.27 0.41 0.52 0.81 1.09 1.29 1.62 2./5/20 0.55 0.57 0.72 0.82 0.95 1.07 1.34 1.62 1.82 2.15 2.02 0.00 0.15 0.25 0.38 0.50 0.77 1.05 1.25 1.58 1./6/20 0.54 0.59 0.76 0.87 1.03 1.17 1.44 1.72 1.90 2.22 2.03 0.02 0.19 0.30 0.46 0.60 0.87 1.15 1.33 1.65 2./7/20 0.58 0.62 0.76 0.88 1.05 1.17 1.46 1.73 1.90 2.22 2.61 0.01 0.05 0.19 0.31 0.48 0.60 0.89 1.16 1.33 1.65 2./10/20 0.58 0.60 0.75 0.86 1.02 1.15 1.42 1.69 1.86 2.17 2.56 0.01 0.03 0.18 0.29 0.45 0.58 0.85 1.12 1.29 1.60 1./11/20 0.58 0.59 0.74 0.86 1.00 1.11 1.38 1.65 1.81 2.14 2.54 0.01 0.02 0.17 0.29 0.43 0.54 0.81 1.08 1.24 1.57 1./12/20 0.58 0.59 0.76 0.86 1.07 1.19 1.47 1.73 1.88 2.20 2.59 0.01 0.02 0.19 0.29 0.50 0.62 0.90 1.16 1.31 1.63 2./13/20 0.58 0.60 0.74 0.87 1.02 1.13 1.41 1.67 1.82 2.16 2.54 0.01 0.03 0.17 0.30 0.45 0.56 0.84 1.10 1.25 1.59 1./14/20 0.57 0.62 0.77 0.87 1.03 1.16 1.45 1.71 1.86 2.21 2.58 0.00 0.05 0.20 0.30 0.46 0.59 0.88 1.14 1.29 1.64 2./17/20 0.58 0.58 0.76 0.88 1.07 1.18 1.47 1.73 1.88 2.23 2.60 0.01 0.01 0.19 0.31 0.50 0.61 0.90 1.16 1.31 1.66 2./18/20 0.58 0.61 0.77 0.89 1.05 1.17 1.46 1.72 1.87 2.21 2.58 0.01 0.04 0.20 0.32 0.48 0.60 0.89 1.15 1.30 1.64 2./19/20 0.58 0.61 0.75 0.89 1.02 1.12 1.43 1.69 1.84 2.20 2.57 0.01 0.04 0.18 0.32 0.45 0.55 0.86 1.12 1.27 1.63 2./20/20 0.58 0.61 0.75 0.90 1.07 1.19 1.48 1.74 1.89 2.24 2.60 0.01 0.04 0.18 0.33 0.50 0.62 0.91 1.17 1.32 1.67 2./21/20 0.55 0.60 0.76 0.89 1.07 1.17 1.46 1.71 1.86 2.19 2..02 0.03 0.19 0.32 0.50 0.60 0.89 1.14 1.29 1.62 1./24/20 0.59 0.61 0.76 0.89 1.05 1.17 1.46 1.71 1.86 2.19 2.55 0.02 0.04 0.19 0.32 0.48 0.60 0.89 1.14 1.29 1.62 1./25/20 0.59 0.62 0.77 0.90 1.07 1.18 1.44 1.71 1.87 2.20 2.55 0.02 0.05 0.20 0.33 0.50 0.61 0.87 1.14 1.30 1.63 1./26/20 0.59 0.64 0.77 0.91 1.09 1.20 1.47 1.74 1.89 2.22 2.58 0.02 0.07 0.20 0.34 0.52 0.63 0.90 1.17 1.32 1.65 2./27/20 0.59 0.65 0.78 0.93 1.15 1.27 1.54 1.80 1.93 2.27 2.60 0.02 0.08 0.21 0.36 0.58 0.70 0.97 1.23 1.36 1.70 2./28/20 0.56 0.64 0.80 0.93 1.12 1.23 1.49 1.74 1.88 2.21 2..01 0.07 0.23 0.36 0.55 0.66 0.92 1.17 1.31 1.64 1./31/20 0.54 0.64 0.78 0.92 1.11 1.23 1.49 1.75 1.88 2.22 2..03 0.07 0.21 0.35 0.54 0.66 0.92 1.18 1.31 1.65 1./1/20 0.57 0.64 0.78 0.92 1.11 1.23 1.50 1.76 1.89 2.21 2.54 0.00 0.07 0.21 0.35 0.54 0.66 0.93 1.19 1.32 1.64 1./2/20 0.58 0.64 0.78 0.91 1.09 1.22 1.49 1.75 1.88 2.21 2.54 0.01 0.07 0.21 0.34 0.52 0.65 0.92 1.18 1.31 1.64 1./3/20 0.56 0.64 0.76 0.90 1.11 1.23 1.51 1.78 1.91 2.26 2..01 0.07 0.19 0.33 0.54 0.66 0.94 1.21 1.34 1.69 2./4/20 0.55 0.