Have Some questions on Excel that i would like answered, i am attaching the file.

Question 1 Given the followng data, Calculate the YTM, YTC for year 4 and YTW between YTM and YTC(4) Insurance Date= Settlement Data= Maturity Date= Coupon Rate= Current Market Price= Redemption Value %= Coupon Pmts per year= 6/12/2013 11/12/2015 6/12/2023 8.75% 98 100 2 Call Provision Year Year 1 from insurance date= Year 2 from insurance date= Year 3 from insurance date= Year 4 from insurance date= Year 5 from insurance date= Call Price 105 104 103 102 101 YTM= YTC (4)= YTW= Question 2 Given the following data, calculate the price, Duration, and Convexity of the Bond Face Value= 1000 Coupon Rate= 6.75% Yield= 8.13% Remaining Years to Maturity= 8 Redemption Price= 100 Frequency (Pmts per year)= 1 Time Until Payments Payments PV of Pmt 1 2 3 4 5 6 7 8 9 % Weight Duration (years) PV of FV (CF) 10 11 12 Price= Duration= Question 3 Prepare a DCF Analysis given the followng assumptions: Revenue Growth Cost of Revenue 25% of Revenue Operating Expense 25% Depreciation Expense as 25% of Tax Rate Working Capital Expense Capex 25% of Revenue Amortization Fees 7.00% 55.00% 17.00% 4.00% 36.00% 1.00% 3.00% 0.00% 7 Year Amortization Risk Free Historical Total Market Return Beta 2.00% 12.60% 1.75x Sources Amount Interest/Expected Retur % Capital Interest/Expected return After TaWACC Bank Loss 180,000 5.00% Corp Bonds 45,000 10.00% Equity 125,000 Total Sourc 350,000 0 Bank Loan Information Amount Outstanding Schedule Payments Interest Payment Total Financing Payment Corporate Bond Information Account Outstanding Schedule Payments Interest Payments Total Financing Payments Total Total Financing 1 2 3 4 5 18,000 20,000 25,000 20,000 97,000 0 0 0 0 45,000 Total Debts Outstanding 0 230,000 1 2 3 Revenues Cost of Revenues Operating Expense EBITDA 45,000 Depreciation Fees EBIT Taxes Depreciation and Amortization of fees Working Capital expense Capital Expenditures Free Cash Flow Before Financing Financing Expense Net Cash Flow Terminal Value EBITDA Multiple Method using initial EBITDA multiple (Zero Year) Perpetuity Method (using WACC) with no growth Average of two valuaion Methods Debt Outstanding Equity Value Equity Cash Flows PV Values (for $1) PV1 PV 2 PV3 Total Less Initial Equity Investment Equity NPV Equity IRR Question 4 Calculate the values of the Call Option using the Black Scholes Model 4 Factor Years Convexity calc Total= Convexity= Zero Year's EBITDA Multiple based on Total Sources Exit 3 Question 1 Given the followng data, Calculate the YTM, YTC for year 4 and YTW between YTM and YTC(4) Insurance Date= Settlement Data= Maturity Date= Coupon Rate= Current Market Price= Redemption Value %= Coupon Pmts per year= 6/12/2013 11/12/2015 6/12/2023 8.75% 98 100 2 Call Provision Year Year 1 from insurance date= Year 2 from insurance date= Year 3 from insurance date= Year 4 from insurance date= Year 5 from insurance date= YTM= YTC (4)= YTW= Call Price 105 104 103 102 101 Question 2 Given the following data, calculate the price, Duration, and Convexity of the Bond Face Value= 1000 Coupon Rate= 6.75% Yield= 8.13% Remaining Years to Maturity= 8 Redemption Price= 100 Frequency (Pmts per year)= 1 Time Until Payments 1 2 3 4 5 6 7 8 9 10 11 12 Payments PV of Pmt Price= % Weight Duration (years) Duration= PV of FV (CF) Factor Years Convexity calc Total= Convexity= Question 3 Prepare a DCF Analysis given the followng assumptions: Revenue Growth Cost of Revenue 25% of Revenue Operating Expense 25% Depreciation Expense as 25% of Revenue Tax Rate Working Capital Expense Capex 25% of Revenue Amortization Fees 7.00% 55.00% 17.00% 4.00% 36.00% 1.00% 3.00% 0.00% 7 Year Amortization Risk Free Historical Total Market Return Beta 2.00% 12.60% 1.75x Sources Amount Interest/Expected Return % Capital Bank Loss 180,000 5.00% Corp Bonds 45,000 10.00% Equity 125,000 Total Sources 350,000 0 Interest/Expected return After Tax WACC 3 4 5 20,000 25,000 20,000 97,000 0 Corporate Bond Information Account Outstanding Schedule Payments Interest Payments Total Financing Payments 2 18,000 Bank Loan Information Amount Outstanding Schedule Payments Interest Payment Total Financing Payment 1 0 0 0 45,000 1 2 3 Zero Year's EBITDA Multiple based on Total Sources 4 Total Total Financing Total Debts Outstanding 0 230,000 Revenues Cost of Revenues Operating Expense EBITDA 45,000 Depreciation Fees EBIT Taxes Depreciation and Amortization of fees Working Capital expense Capital Expenditures Free Cash Flow Before Financing Financing Expense Net Cash Flow Exit Terminal Value EBITDA Multiple Method using initial EBITDA multiple (Zero Year) Perpetuity Method (using WACC) with no growth Average of two valuaion Methods Debt Outstanding Equity Value Equity Cash Flows PV Values (for $1) PV1 PV 2 PV3 Total Less Initial Equity Investment Equity NPV Equity IRR 3 Question 4 Calculate the values of the Call Option using the Black Scholes Model Input Standard Deviation Expectation (in years)(T) Risk-Free amount(i) Stock Price(S) Expense Price (x) Dividend Yield (annual) Output d1 d2 n(d1) n(d2) C 0.3 100.00% 1 105 110 0 Question 5 Input Current Stock Price Exercise Stock Price Stock Up Factor Stock Down Factor Interest Rate Exercise time Premium Break Even Stock Distance to Break Even ($) Distance to Break Even (%) 90 105 1.3 0.8 10% 1 Question 6 Prepare Cash Flow Statement and Ratio Analysis Balance Sheet Income Statement Current Assets Property and Equipment Land Building Furniture and Equipement Total Gross P&E Less Accumilated Depreciation Net P&E Long Term Investments Total Assets 2014 86,100 87,000 65,000 13,000 251,100 3,750,000 675,000 75,000 4,500,000 450,000 4,050,000 3,750,000 800,000 100,000 4,650,000 550,000 4,100,000 300,000 4,751,100 52,500 18,000 15,000 30,000 115,500 65,000 15,000 10,000 30,000 120,000 Liabilities and Owners Equity Current Liabilities Accounts Payable Accrued Income Taxes Accrud Expense Current Portion of Long Term Debt Total Current Liabilities Long Term Debt Differed Income Taxes Total Liabilities Owners Equity Common Stock Paid in Capital Retained Earnings Total Owners Equity Total Liabilities and Owners Equity 1,800,000 1,700,000 18,000 22,000 1,933,500 1,842,000 1,500,00 1,119,000 2,619,000 1,500,00 30,000 1,379,100 2,509,100 4,532,500 4,751,100 Cash Flow Statement 2014 Net Income Plus Depreciation Plus Differed Taxes Cash Income Working Capital Activities Change in Accounts Recievable Change in Inventory Change in Prepaid Expense Change in Accounts Payable Change in Accrued Income Taxes Change in Accrued Expenses Total change in working capital Investment Activities Capital Expenditures Investments (change) Total Investment Activities Cash Available Before Financing Activities Beginning Cash Ending Cash Ratio Analysis Trend Analysis Ratio Revenue Growth 1,200,000 180,000 60,000 1,440,000 1,400,000 210,000 75,000 1,685,000 Cost Of Revenue by Geography U.S Europe Asia Total Cost of Revnue 330,000 150,000 37,500 517,500 405,000 172,000 52,500 630,000 Gross Profit 922,500 1,055,000 Operatng Expense Administrative &General Marketing Expense Other Operating Expense Total Operating Expense 217,500 112,500 15,000 345,000 247,500 120,000 18,000 385,500 577,500 669,500 90,000 100,000 EBIT 343,500 433,500 144,000 136,000 EBIT 343,500 433,500 Taxes 137,400 173,400 Net Income 206,100 260,100 Revenues by Geography U.S Europe Asia TOTAL REVENUE 400,000 4,552,500 2014 EBITDA 67,500 67,500 62,500 15,000 202,500 2013 Interest Expense cash Accounts Recieveable Inventories Pre-Paid Expenses Total Current Assets 2013 2013 2014 Depreciation Solvency Ratios LTD/Total Capitalization EBITDA/Interest (Coverage Ratio) LTD/EBITDA (Leverage Ratio) Profibility Ratios Gross Margins EBITDA Margin Return on Assets (ROA) Return on Equity (ROE) Question 1 Given the followng data, Calculate the YTM, YTC for year 4 and YTW between YTM and YTC(4) Insurance Date= Settlement Data= Maturity Date= Coupon Rate= Current Market Price= Redemption Value %= Coupon Pmts per year= 6/12/2013 11/12/2015 6/12/2023 8.75% 98 100 2 Call Provision Year Year 1 from insurance date= Year 2 from insurance date= Year 3 from insurance date= Year 4 from insurance date= Year 5 from insurance date= Year 4 Date YTM= YTC (4)= YTW= Call Price 105 104 103 102 101 6/12/2017 9.12% 8.32% 9.06% Question 2 Given the following data, calculate the price, Duration, and Convexity of the Bond Face Value= 1000 Date Coupon Rate= 6.75% Yield= 8.13% Remaining Years to Maturity= 8 Redemption Price= 1000 Frequency (Pmts per year)= 1 Time Until Payments Date 1 2 3 4 5 6 7 8 01.01.2015 Duration Payments PV of Pmt % Weight (years) 67.5 $ 62.4 6.2% 62.428 67.5 $ 57.7 5.8% 115.473 67.5 $ 53.4 5.3% 160.194 67.5 $ 49.4 4.9% 197.542 67.5 $ 45.7 4.6% 228.372 67.5 $ 42.2 4.2% 253.454 67.5 $ 39.1 3.9% 273.476 167.5 $ 571.4 57.1% 4571.397 Price= $ 921.36 Duration= PV of FV (CF) $67.50 $67.50 $67.50 $67.50 $67.50 $67.50 $67.50 $1,067.50 6.36 Convexity calc 106.80 296.31 548.09 844.85 1,172.04 1,517.55 1,871.36 35,191.61 Factor Years 0.924855491 0.85535768 0.791082247 0.73163676 0.676658276 0.625811122 0.578784853 0.535292349 Total= Convexity= 45.0950102 Question 3 Prepare a DCF Analysis given the followng assumptions: Revenue Growth Cost of Revenue 25% of Revenue Operating Expense 25% Depreciation Expense as 25% of Revenue Tax Rate Working Capital Expense Capex 25% of Revenue Amortization Fees 7.00% 55.00% 17.00% 4.00% 36.00% 1.00% 3.00% 0.00% 7 Year Amortization Risk Free Historical Total Market Return Beta 2.00% 12.60% 1.75x Interest/Expe cted return % Capital After Tax 0.514285714 3.20% 0.128571429 6.40% 0.357142857 20.55% Interest/Expe Sources Amount cted Return Bank Loan 180,000 5.00% Corp Bonds 45,000 10.00% Equity 125,000 20.55% Total Sources 350,000 0 WACC 1.65% 0.82% 7.34% 9.81% 0 230,000 Revenues Cost of Revenues Operating Expense EBITDA 45,000 Depreciation Fees EBIT Taxes Depreciation and Amortization of fees Working Capital expense Capital Expenditures Free Cash Flow Before Financing Financing Expense Net Cash Flow 3 4 162,000 20,000 8100 28,100 142,000 25,000 7100 32,100 117,000 20,000 5850 25,850 97,000 97,000 4850 101,850 45000 0 4500 4500 45000 0 4500 4500 45000 0 4500 4500 45,000 0 4500 4500 32,600 187,000 36,600 162,000 30,350 142,000 7.7778 5 31,500 207,000 Total Total Financing Total Debts Outstanding 2 45000 0 4500 4500 Corporate Bond Information Account Outstanding Schedule Payments Interest Payments Total Financing Payments 1 180000 18,000 9000 27,000 Bank Loan Information Amount Outstanding Schedule Payments Interest Payment Total Financing Payment Zero Year's EBITDA Multiple based on Total Sources 106,350 45,000 1 246100 135355 41837 68908 9844 0 59064 21263 9844 2461 7383 21263 13500 7763 2 3 4 263327 281760 301483 144829.85 154967.9395 165815.6953 44765.59 47899.1813 51252.12399 73731.56 78892.7692 84415.26304 10533.08 11270.3956 12059.32329 0 0 0 63198.48 67622.3736 72355.93975 22751 24344 26048 10533.08 11270.3956 12059.32329 2633.27 2817.5989 3014.830823 7900 8453 9044 22751 24344 26048 12600 11600 10350 10151 12744 15698 Revenue Growth Cost of Revenue 25% of Revenue Operating Expense 25% Depreciation Expense as 25% of Revenue Tax Rate Working Capital Expense Capex 25% of Revenue Amortization Fees Exit Terminal Value EBITDA Multiple Method using initial EBITDA multiple (Zero Year) Perpetuity Method (using WACC) with no growth Average of two valuaion Methods Debt Outstanding Equity Value Equity Cash Flows 3 656563 160057 408310 45,000 363310 46357 PV Values (for $1) PV1 PV 2 PV3 7069.658039 8419.016615 9625.146985 7.00% 55.00% 17.00% 4.00% 36.00% 1.00% 3.00% 0.00% 7 Year Amortization Total Less Initial Equity Investment Equity NPV 25113.82164 0 388424 Question 4 Calculate the values of the Call Option using the Black Scholes Model Input Standard Deviation Expectation (in years)(T) Risk-Free amount(i) Stock Price(S) Expense Price (x) Dividend Yield (annual) 0.3 100.00% 1 105 110 0 Output d1 d2 n(d1) n(d2) C $ 0.0283 -0.2717 0.5113 0.3929 10.89 Question 5 Input Current Stock Price Exercise Stock Price Stock Up Factor Stock Down Factor Interest Rate Exercise time Premium Break Even Stock Distance to Break Even ($) Distance to Break Even (%) 90 105 1.3 0.8 10% 1 15 120 30 33.33% Question 6 Prepare Cash Flow Statement and Ratio Analysis Balance Sheet Income Statement Current Assets Property and Equipment Land Building Furniture and Equipement Total Gross P&E Less Accumilated Depreciation Net P&E Long Term Investments Total Assets 2014 86,100 87,000 65,000 13,000 251,100 3,750,000 675,000 75,000 4,500,000 450,000 4,050,000 3,750,000 800,000 100,000 4,650,000 550,000 4,100,000 300,000 Long Term Debt Differed Income Taxes Total Liabilities Owners Equity Common Stock Paid in Capital Retained Earnings Total Owners Equity Total Liabilities and Owners Equity Cash Flow Statement Net Income Plus Depreciation Plus Differed Taxes Cash Income 4,552,500 4,751,100 52,500 18,000 15,000 30,000 115,500 65,000 15,000 10,000 30,000 120,000 1,800,000 1,700,000 18,000 22,000 1,933,500 1,842,000 1,500,00 1,119,000 2,619,000 1,500,00 30,000 1,379,100 2,509,100 4,532,500 4,751,100 2014 260,100 100,000 173,400 533,500 Working Capital Activities Change in Accounts Recievable Change in Inventory Change in Prepaid Expense Change in Accounts Payable Change in Accrued Income Taxes Change in Accrued Expenses Total change in working capital -19500 -2,500 2,000 12,500 -3,000 -5,000 -15500 Investment Activities Capital Expenditures Investments (change) Total Investment Activities 1,200,000 180,000 60,000 1,440,000 1,400,000 210,000 75,000 1,685,000 Cost Of Revenue by Geography U.S Europe Asia Total Cost of Revnue 330,000 150,000 37,500 517,500 405,000 172,000 52,500 630,000 Gross Profit 922,500 1,055,000 Operatng Expense Administrative &General Marketing Expense Other Operating Expense Total Operating Expense 217,500 112,500 15,000 345,000 247,500 120,000 18,000 385,500 577,500 669,500 90,000 100,000 EBIT 343,500 433,500 144,000 136,000 EBIT 343,500 433,500 Taxes 137,400 173,400 Net Income 206,100 260,100 Revenues by Geography U.S Europe Asia TOTAL REVENUE 400,000 Liabilities and Owners Equity Current Liabilities Accounts Payable Accrued Income Taxes Accrud Expense Current Portion of Long Term Debt Total Current Liabilities 2014 EBITDA 67,500 67,500 62,500 15,000 202,500 2013 Interest Expense cash Accounts Recieveable Inventories Pre-Paid Expenses Total Current Assets 2013 -150,000 -100,000 -250,000 Cash Available Before Financing Activities 30,000 Beginning Cash Ending Cash 67,500 86,100 Depreciation Ratio Analysis 2013 Trend Analysis Ratio Revenue Growth 2014 17.01% Solvency Ratios LTD/Total Capitalization EBITDA/Interest (Coverage Ratio) LTD/EBITDA (Leverage Ratio) 0.40 4.01 3.12 0.36 4.92 2.54 Profibility Ratios Gross Margins EBITDA Margin Return on Assets (ROA) Return on Equity (ROE) 0.64 0.40 0.05 0.08 0.63 0.40 0.05 0.10 Option Value based on the Black-Scholes Model: Black-Scholes Option Pricing Model Inputs: Stock Price (S) $105.00 Strike Price (X) $110.00 Volatility (s) 30.00% Black-Scholes Option Pricing Model (with dilution) Inputs (with dilution effects): Stock Price (S) $105.00 Strike Price (X) $110.00 Volatility (s) 30.00% Risk-free Rate Time to expiration (T) Dividend Yield # of Options (000) # Shares Outstanding (000) Tax Rate Output: Risk-free Rate Time to expiration (T) Dividend Yield # of Options (000) # Shares Outstanding (000) Tax Rate \\ Adjusted S (dilution) D1 D2 N(D1) N(D2) Call Price Put Price Value of Call Options (000) After-tax Option Value (000) D1 D2 N(D1) N(D2) Call Price Put Price Value of Call Options (000) After-tax Option Value (000) 1.00% 1 yrs 0.00% 0 0 0.00% 0.02827 -0.27173 0.51128 0.39291 $10.89347 $14.79895 $0 $0 *Note: This spreadsheet requires iterative calculations which may result in circular references. To correct this problem, go to "Tools" - "Options" - Calculation and check the "iteration" box. 1.00% 1 yrs 0.00% 0 0 0.00% $9.59 0.93696 -0.32795 0.82561 0.37148 $5.49418 $2.42844 $54,942 $32,965 Standard Deviation Expectation (in years)(T) Risk-Free amount(i) Stock Price(S) Expense Price (x) Dividend Yield (annual) 0.3 100.00% 1 105 110 0 Bond Price Face Value Coupon Rate Life in Years Yield Frequency Macaulay Duration Modified Duration Convexity Period 0 1 2 3 4 5 6 7 8 $921.36 1,000 6.75% 8 8.13% 1 $869.16 If Yield Changes By 1000 Bond Price Will Change By 7% 8 Modified Duration Predicts 9% Convexity Adjustment 1 Total Predicted Change 6.36 5.88 45.10 Cash Flow ($921.36) 67.50 67.50 67.50 67.50 67.50 67.50 67.50 1,067.50 1.00% -52.20 -54.22 2.08 -52.14 Actual New Price Predicted New Price Difference PV Cash Flow Duration Calc -5.67% -5.88% 0.23% -5.66% $869.16 $869.22 $0.06 N Convexity Calc 62.43 57.74 53.40 49.39 45.67 42.24 39.07 571.42 62.43 115.47 160.19 197.54 228.37 253.45 273.48 4,571.40 106.80 296.31 548.09 844.85 1,172.04 1,517.55 1,871.36 35,191.61 Total 5,862.34 41,548.61 DMac 1 C 1 i 2 N CF 1 i t t t 1 VB t 2 t CFt 1 i t 1 VB t t