Financial Engineering Assignment 2

profileshoomoosh
IEE512-412Assignment2.xlsx
Home>Business & Finance homework help>Financial markets homework help>Financial Engineering Assignment 2

Student Information

You must enter the information in the "yellow" fields below before starting the assignment. Fall 2020 Question 1 12878500 Rate Maturity Price YTM
Name Bond 1 8.25% 10 $1,053 7.49%
10 digit student ID # 1220885757 Bond 2 6.50% 20 $899 7.49%
Date Bond 3 4.50% 30 $645 7.49%
Class
By downloading this assignment you agree to not share this exam with anyone. You also agree to completing this assignment without the assistance or of anyone unless you have received written approval from the instructor. You agree to not collaborate with anyone with this assignment You agree to not use another student's assignment or materials to aid you with this assignment You understand that not following these rules, or any other rules identified in the ASU Academic Integrity Policy will result in a zero grade for this assignment a violation report to the Dean's office, and likely suspension from ASU. Please teach each other the Excel functions and financial engineering functions. However, do not provide answers or assist others with their answers. Engineers must learn how to solve problems! This assignment is the property of Daniel R. McCarville and Arizona State University. It is copyright protected.
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 3 0
1b 3 0
1c 6 0
2a 2 0
2b 4 0
2c 3 0
2d 3 0
3a 1 0
3b 1 0
3c 1 0
3d 1 0
3e 1 0
3f 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% 10 1053 0.07485 Semi-annual
Bond 2 6.50% 20 899 0.07485 Semi-annual
Bond 3 4.50% 30 645 0.07485 Semi-annual
a. Calculate the Macauly 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 1 2 3 4 5 6 7 8 9 10
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 1 2 3 4 5 6 7 8 9 10
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 1 2 3 4 5 6 7 8 9
Grade Spot Rate
1
2
3
4
5
6
7
8
9
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 1 2 3 4 5 6 7 8 9 10
Grade Cash Flow 2000 4500 4000 5500 800 500 200 3000 4000 9000
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/19 12/29/18 12/30/17
Revenue
Total Revenue 275,466,000 192,826,000 173,543,000
Cost of Revenue 140,089,000 112,258,000 106,606,000
Gross Profit 135,377,000 80,568,000 66,937,000
Operating Expenses
Research Development 8,067,000 6,041,000 4,475,000
Selling General and Administrative 14,329,000 11,993,000 10,830,000
Non Recurring - - -
Others - - -
Total Operating Expenses - - -
Operating Income or Loss 112,981,000 62,534,000 51,632,000
Income from Continuing Operations
Total Other Income/Expenses Net 1,285,000 980,000 1,156,000
Earnings Before Interest and Taxes 114,266,000 63,514,000 52,788,000
Interest Expense - - -
Income Before Tax 114,266,000 63,514,000 52,788,000
Income Tax Expense 19,121,000 13,973,000 13,118,000
Minority Interest - - -
Net Income From Continuing Ops 95,145,000 49,541,000 39,670,000
Non-recurring Events
Discontinued Operations - - -
Extraordinary Items - - -
Effect Of Accounting Changes - - -
Other Items - - -
Net Income
Net Income 95,145,000 49,541,000 39,670,000
Preferred Stock And Other Adjustments - - -
Net Income Applicable To Common Shares 95,145,000 49,541,000 39,670,000

Cashflow Statement

Cash Flow All numbers in thousands
Period Ending 12/28/19 12/29/18 12/30/17
Net Income 95,145,000 49,541,000 39,670,000
Operating Activities, Cash Flows Provided By or Used In
Depreciation 11,257,000 7,946,000 6,757,000
Adjustments To Net Income 4,968,000 5,210,000 3,394,000
Changes In Accounts Receivables -3,124,000 -6,452,000 -1,949,000
Changes In Liabilities 15,188,000 13,408,000 8,320,000
Changes In Inventories -238,000 -76,000 -973,000
Changes In Other Operating Activities -179,000 167,000 1,080,000
Total Cash Flow From Operating Activities 123,017,000 69,744,000 56,299,000
Investing Activities, Cash Flows Provided By or Used In
Capital Expenditures -11,247,000 -9,571,000 -8,165,000
Investments -44,417,000 -9,017,000 -24,042,000
Other Cash flows from Investing Activities -610,000 -3,991,000 -1,567,000
Total Cash Flows From Investing Activities -56,274,000 -22,579,000 -33,774,000
Financing Activities, Cash Flows Provided By or Used In
Dividends Paid -11,561,000 -11,126,000 -10,564,000
Sale Purchase of Stock -34,710,000 -44,270,000 -22,330,000
Net Borrowings 29,305,000 18,266,000 16,896,000
Other Cash Flows from Financing Activities -1,499,000 -1,158,000 -1,082,000
Total Cash Flows From Financing Activities -17,716,000 -37,549,000 -16,379,000
Effect Of Exchange Rate Changes - - -
Change In Cash and Cash Equivalents 49,027,000 9,616,000 6,146,000

Balance Sheet

Balance Sheet All numbers in thousands
Period Ending 12/28/19 12/29/18 12/30/17
Current Assets
Cash And Cash Equivalents 21,120,000 13,844,000 14,259,000
Short Term Investments 20,481,000 11,233,000 26,287,000
Net Receivables 85,757,000 68,606,000 54,885,000
Inventory 2,349,000 2,111,000 1,764,000
Other Current Assets 9,539,000 9,806,000 6,882,000
Total Current Assets 139,246,000 105,600,000 104,077,000
Long Term Investments 164,065,000 130,162,000 106,215,000
Property Plant and Equipment 22,471,000 20,624,000 16,597,000
Goodwill 5,116,000 4,616,000 1,577,000
Intangible Assets 3,893,000 4,142,000 4,179,000
Accumulated Amortization - - -
Other Assets 5,556,000 3,764,000 5,146,000
Deferred Long Term Asset Charges - - -
Total Assets 340,347,000 268,908,000 237,791,000
Current Liabilities
Accounts Payable 81,469,000 65,175,000 52,140,000
Short/Current Long Term Debt 10,999,000 6,308,000 6,308,000
Other Current Liabilities 8,940,000 8,491,000 7,435,000
Total Current Liabilities 101,408,000 79,974,000 65,883,000
Long Term Debt 53,463,000 28,987,000 16,960,000
Other Liabilities 33,427,000 24,826,000 20,208,000
Deferred Long Term Liability Charges 3,624,000 3,031,000 2,625,000
Minority Interest - - -
Negative Goodwill - - -
Total Liabilities 191,922,000 136,818,000 105,676,000
Stockholders' Equity
Misc. Stocks Options Warrants - - -
Redeemable Preferred Stock - - -
Preferred Stock - - -
Common Stock 27,416,000 23,313,000 19,764,000
Retained Earnings 92,284,000 87,152,000 104,256,000
Treasury Stock - - -
Capital Surplus - - -
Other Stockholder Equity -345,000 1,082,000 -471,000
Total Stockholder Equity 119,355,000 111,547,000 123,549,000
Net Tangible Assets 110,346,000 102,789,000 117,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.03
8/5/20 0.55 0.57 0.72 0.82 0.95 1.07 1.34 1.62 1.82 2.15 2.56 -0.02 0.00 0.15 0.25 0.38 0.50 0.77 1.05 1.25 1.58 1.99
8/6/20 0.54 0.59 0.76 0.87 1.03 1.17 1.44 1.72 1.90 2.22 2.63 -0.03 0.02 0.19 0.30 0.46 0.60 0.87 1.15 1.33 1.65 2.06
8/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.04
8/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.99
8/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.97
8/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.02
8/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.97
8/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.01
8/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.03
8/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.01
8/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.00
8/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.03
8/21/20 0.55 0.60 0.76 0.89 1.07 1.17 1.46 1.71 1.86 2.19 2.55 -0.02 0.03 0.19 0.32 0.50 0.60 0.89 1.14 1.29 1.62 1.98
8/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.98
8/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.98
8/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.01
8/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.03
8/28/20 0.56 0.64 0.80 0.93 1.12 1.23 1.49 1.74 1.88 2.21 2.53 -0.01 0.07 0.23 0.36 0.55 0.66 0.92 1.17 1.31 1.64 1.96
8/31/20 0.54 0.64 0.78 0.92 1.11 1.23 1.49 1.75 1.88 2.22 2.54 -0.03 0.07 0.21 0.35 0.54 0.66 0.92 1.18 1.31 1.65 1.97
9/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.97
9/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.97
9/3/20 0.56 0.64 0.76 0.90 1.11 1.23 1.51 1.78 1.91 2.26 2.59 -0.01 0.07 0.19 0.33 0.54 0.66 0.94 1.21 1.34 1.69 2.02
9/4/20 0.55 0.63 0.76 0.87 1.05 1.17 1.44 1.71 1.86 2.21 2.55 -0.02 0.06 0.19 0.30 0.48 0.60 0.87 1.14 1.29 1.64 1.98
9/7/20 0.56 0.65 0.80 0.88 1.05 1.17 1.43 1.70 1.85 2.20 2.54 -0.01 0.08 0.23 0.31 0.48 0.60 0.86 1.13 1.28 1.63 1.97

Copy, Paste, IP Data