Tab1 Tab 3 10 11 12 13 14 15 16 17 18 1,047.01 0,00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0,00 0,00 0,00 57.44 57.44 1,057.44 D.DO 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 45.41 45.41 45.41 15.41 45.41 15.41 45.41 45.41 45.41 1,045.41 0.00 10.00 0.00 43.34 43.34 43.34 43.34 43.34 43.34 43.34 43.34 43.34 1,043.34 0.00 0.00 0.00 0.00 44.22 44.22 44.22 14.22 14.12 14.22 44 22 14 22 14 22 14 22 14.22 4.22 14.22 14.22 54.91 54.91 54.91 $4.91 54.91 54.91 1.054.91 0.00 0.00 0.00 0.00 0.00 0.00 54.46 54.46 54.46 54.46 54.46 54 46 $4 46 54 46 54 46 54.46 14.46 54.46 54.46 54.46 56.75 56.75 56.75 56.75 56.75 $6.75 56.75 56 75 56.75 16.75 56.75 1,056.75 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0 00 0.00 0.00 0.00 0.00 0,00 0.00 0.00 0.00 0.00 0.00 1.00 0.00 0.00 100 0.00 50.94 10.94 50,94 50.94 1,050.94 0.00 0.00 0.00 0.00 0.OD 0.00 0.00 50.36 50.36 50.36 1,050.36 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 46.91 46.91 16,91 46.91 46.91 16.91 46.91 16.91 469 1691 46.91 16.91 46.91 1,046.9 0.00 42.29 1,042.29 D.DO D.DO D.CO 0.00 0.00 0.00 0.00 0,00 0.00 0.OD 13.58 13.58 43.58 43 58 13.58 1,043.5 0.00 0.00 0.00 1,00 43.58 43.58 43.58 13.58 53.47 53.47 53.47 53.47 53.47 $3.47 53.47 1.053.47 0.00 0.00 0.00 0.00 0.00 0.00 48.3M 48.18 48.38 48 38 48 78 48.38 18.38 55.07 55.07 55.07 55.07 55.07 55.07 $5.07 $5-07 $5.07 55.07 55.07 55.07 55.07 1,055.07 51.14 $1.14 51.14 51.14 51.14 $1.14 51.14 51.14 $1.14 $1.14 51.14 1.14 1,05 0,00 0,00 D.DO 0.00 0,00 0.00 0,00 0,00 0,00 0.00 0.00 0.00 O.00 0.00 0.00 0.00 0.00 10.09 40.03 40.03 10.03 40.03 40.03 40.03 40703 1,040.0 0.00 0.00 0.00 0.00 49.15 49.15 19.15 19.15 19.15 19.15 19.15 19.15 19.15 49.15 19.15 9.15 19.15 19,15 1,045 41.39 41.39 41.39 1.39 41.39 139 11.39 1,041.39 0.00 0.00 0.00 0.00 45.71 15.71 1.045.71 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0,00 0.00 0.00 0,00 0,00 D.DO D.00 0.00 0.00 0.00 0.00 0.00 0,00 0,00 0,00 54.78 54.78 54.78 54.78 54.78 $4.78 54.78 $4.78 54.78 54.78 54.78 4.78 54.78 14.78 0.00 0.00 0.00 0.00 10 00 0.00 0.00 0.00 0.00 0,00 0,00 1.26 1.26 51.26 51.26 51.26 $1.26 51.26 $1.26 01.26 1,051.2 0,00 0,00 0.00 0.00 D.00 D.DO 0.00 0.00 0200 0.00 0.00 0.00 0.OD 1,00 34.54 54.54 $4.54 54.54 $4.54 54.54 -454 34.54 54.34 1.054.54 0.00 0.00 0.00 46. 36 46,36 46,36 16.26 16.16 16 36 16 36 16 36 16 36 16.36 1,046.36 0.00 0,00 0.00 1,059.18 0.00 0.00 D.DO 0.00 0200 0.0 0.00 0.OD 0.00 0,00 56.95 56.95 56.95 56.95 56.9S 56.95 56 95 $6.95 56 95 1.056.95 0.00 0.00 0.00 0.00 46,85 46,85 46,85 16.85 46,85 16,85 46.85 46 85 46.85 6.8 46.85 16.85 16,85 16.85 56.28 56.28 56.28 56.28 56.28 16.28 56.28 56 28 56.28 56.28 16. 28 56.28 56.28 58.40 18.40 58.40 1.058.40 0.00 0.00 0.00 0.00 1.00 56, 18 56.18 1.056.18 0.CO 0.00 0.00 0.00 0700 0.00 0.00 0,00 0,00 0,00 0.00 53.95 53.95 51.95 53.95 53.95 53.95 53.95 1.053.95 0.00 0.00 0.00 0.OD 0.00 40.83 40.83 10.83 40.83 40.83 40.83 40.83 10.83 10.83 10.83 40.83 10.83 10.83 16 22 46, 22 46 22 46 22 1.046 22 0.00 0.00 0.00 0.00 0,00 0,00 16. 22 55.00 $5.00 55.00 55.00 55.00 55.10 55.00 $5-00 1.055.00 0.00 0.00 0.OD 0.00 0.00 45.01 45.01 45.01 45.01 15.01 45.01 45.01 45.01 5.01 45.01 45.01 45.01 45.01 45.01 43.56 43,56 43.56 13,56 1.043.56 0.00 0.00 0.06 0,00 0,00 10,00 0.00 42.77 42.77 42.77 12.77 42.17 12.77 42.77 12 77 42.77 12.77 12.77 2.77 12.77 12.77 49.69 19.69 49.69 19.69 19269 10 69 1.049.69 0.00 10.00 0.00 0.00 46.36 46.36 1.046.36 0.00 0.00 0.00 0.00 0.0 0.00 0,00 0.00 0,00 0.00 0.00 D.DO D.DO D.CO 1.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 51.41 $1.41 51.41 51.41 $1.41 $1.41 51.41 $1.41 $1.41 $1.41 $1.41 $1.41 51.41 51.41 40,06 40,06 40,06 10.06 40.06 40 06 40-06 40.06 40.06 40,06 10.06 1,040.06 0.00 0.78 0.75 0.71 D.68 D.64 0.61 0.58 0.56 0.53 0.51 0.48 0.46 0.44 0.42Problem 3 (5 points extra credit) In this problem we will further investigate the Bond Investment problem from problem 3 by performing a parameter analysis. First. we will look at how decreasing risk through diversication impacts the total return. We will do this by evaluating different settings on the Max per Bend setting. I Convert the Max per Bond cell into a parameter that varies from 1.500 to 5.00-8- I Generate a Parameter report with 8 steps that tracks the Discounted Cash Flow as an output. I Paste the report into your template. Now we will look at how smoothing out the cash ow effects the total return. I Keep the Mexpar Bond setting as a parameter. ' Convert the Initial Cash Required setting to a parameter that varies from 5i],ilil to mean. a Generate a parameter report that includes 8 major axis points, and 11 minor axis points, and tracks the Disc Cashow. Select Vary Two Selected Parameters Independently. Paste the report into your template. 0 Generate a parameter graph for the same settings and paste the graph into the template. Consider this analysis and I Comment on how you think the time to solve would ehange if we increased the number of bonds to choose from? How could the analyst address this issue? :- Comment on what observations you can make from this sensitivity analysis. Tab1 Tab 3 Bond Cash Flows Investment Bond Termi Coupon Yield Rating Price 5 4.70% 5.00% AAA $987.01 47.01 47.01 47.01 47.01 1.047 2 7 5.74% 5.46% AAA $1,016.25 57.44 57.44 57.44 57.44 14 4.54% 5.80% AAA $881.71 45.41 45.41 45.41 45.41 00 14 4.33% 5.37% AAA $899.45 43.34 43,34 43,34 43.34 27 4.42% 5.59% AAA $854.57 44.22 44.22 44.22 14.22 6 5.49% 5.36% AAA $1,010.34 54.91 54.91 54.91 54.91 00 23 5.45% 4.52% AAA $1.130.47 54.46 54.40 $4.46 54.46 0.0 16 5.68% 4.61%% AAA $1,118,71 46.75 56.75 56.75 16.75 0.0 9 4.08% 4.24% AAA $997.08 40.81 1.040.81 0.00 0.00 0,00 0.0 10 4.87% 4.63% AAA $1,002.28 1,048.68 0.00 0.00 0.0 11 5.09% 5.63% AAA $963.01 50.94 50.94 50.94 50.94 12 5.04% 5.30% AAA $983.30 50.36 50.36 50.36 50.36 0.0 13 18 4.69% 5.46% AAA 8912.91 46.91 46.91 46.91 16.91 14 AAA $915,47 42.29 42.29 42.29 42.29 0.0 4.23% 5.95% 0.0 15 14 4.36% 4.97% AAA $939.54 43.58 43.58 43.58 43.58 0.0 16 12 5.35% 5.87% AAA $956.10 53.47 53.47 53.47 53.47 17 4.84% 5.83% AAA $891.29 48.38 48.38 48.38 48.38 0.0 18 5.51% 4.85% AAA $1,077.56 55.07 $5.07 55.07 55.07 0.0 19 5.11%% 5.96% $905.54 51.14 51.14 $1.14 11.14 AAA 0.0 20 5.78% 5.75% AAA $1,000.61 57.77 57.77 1,057.77 0,00 0.0 21 5.65% 4.28% AAA $1,025.80 56.53 1,056.53 0.0D 0.00 0.0 22 4.00% 5.93% AAA $828.79 40.03 40.03 40.03 40,03 0.0 23 4.91% 5.22% AAA 8963.21 49.15 49,15 49.15 49,15 24 4.14% 5.36% AAA $882.17 41.39 11.30 41.39 41.39 25 45.71 4.57% 5.26% AAA $960.67 45.71 45.71 45.71 0.0 26 4.04% 4.89% AAA $969.61 40.37 40.37 40.37 1,040.37 0.0 27 23 5.48% 4.42% AAA $1,150.81 54.78 54.78 54.78 54.78 0.0 28 3 4.11% 4.42% AAA $991.57 41.13 41.13 1,041.13 0.00 0.0 29 14 5.13% 5.080% AAA $1,004.16 51.26 51,26 51.26 1.26 0.0 30 4.95% 4.34% AAA $1,016.64 19.48 19.48 ,049.48 0.00 0.0 31 5.45% 5.98% $949.14 $4 54 54.54 AAA 14.54 0.0 32 4.64% 5.59% AAA $904.81 46.36 16.36 46,36 16,36 33 5.924 4.97%% AAA $1,041.16 59.18 19.18 0.0 59.18 59.18 1,059 0.0 34 5.70% 5.85% AAA $985.41 56.95 0.0 35 4.68% 5.93% AAA $840.02 46,85 46,85 46,85 46,85 0.0 36 5.63% 5.03% AAA $790.98 56.28 56.28 56.28 56.28 0.0 37 5.84% 5.98% AAA $991.38 58.40 58.40 58.40 58.40 0.0 38 5.62% 5.36% AAA $1.014.91 56. 18 56,18 56, 18 56.18 0.0 39 5.39% 4.93% AAA $1,041.55 53.95 53.95 53.95 53.95 0.0 40 4.08% 5.41% AAA $827.29 40.83 40 83 40.83 40,83 0.0 41 4.62% 5.57% 16.22 AAA 46 22 46. 22 46.22 0.0 42 5.50% 5.83% AAA $970.11 55.00 $5.00 $5.00 65.00 0.0 4.50% 4.02% AAA 1,074.35 45.01 45.01 45.01 45.01 0.0 44 4.36% 4.56% AAA $985.17 43,56 43,56 43.56 43,56 0.0 45 25 4.28% 5.39% AAA $849.24 42.77 42.77 42.77 42.77 0.0 46 13 .97% 4.83% AAA $1,012.95 49 69 49.69 49.69 49.69 0.0 47 4.64% 5.41% AAA $955.92 46.36 46.36 46,36 46.36 48 4.86% 5.00% AAA $995.09 48.60 48.60 1,D48.60 49 22 5.14% 5.17% AAA $996.25 $1.41 $1.41 51.41 51.41 4.01% 4.41% AAA $952.41 40.06 40,06 40,06 40,06 Avail Funds 10,000,000 Cash Flow 50.000 Investment Cash Required Discount Factor 0.86 0.82 Max per Bond 0.95 0.91 Discount Rate Dise Cash Flow Initial Cash Required 50.000 Annual Increase 3% Bonds Invested in 0 Max Bonds 0.0Tab1 Tab 3 11 12 13 14 16 17 18 19 20 21 22 23 24 0.00 0.00 0,00 0,00 0.00 0,00 0,00 9,00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 D.DO D.DO D.00 0.00 0.00 45.41 45.41 45.41 1,045.41 0.00 1.00 0.00 0.00 0,00 0.00 0.00 0.00 0.00 0-00 43.34 43 34 43.34 1.043.34 10.00 0,00 0.00 0,DO 0.00 D.DO 0.00 0.00 44 22 44 22 44.22 14.22 44.22 4.22 14.22 44.22 14.22 14.22 14.22 1,044.22 0-00 .054.91 0.00 0.00 0.00 0,00 0.00 0.00 0.00 0.00 0200 0200 54 46 $4.46 34.46 14.46 54.46 54.46 54.46 54.46 54.46 54.46 54.46 54.46 1,054 4 0.00 56.75 16.75 36 75 6.75 56.75 1,D56.75 0.00 0.00 D.DO D.DO D.00 0.00 0.00 0200 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 1,00 0,00 0.00 0,00 0.00 0 00 10O 0.00 .0D 1.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.DO 0.00 0.00 0.00 0.00 16.91 4691 16.91 16.91 1691 6.91 1.046.91 9.00 0.00 0.00 0.00 0.00 0.00 0,00 1.00 0.00 0,00 D.DO 2.00 0.00 13.58 43.58 43.58 1,043.58 1.00 1.00 0.00 D.D0 3.00 0.00 0.00 53.47 1.053.47 0.00 0.0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0200 48 38 18.38 18.38 18,38 18,38 8,3H 1.048.38 D,DO D.CO 12.00 0-00 $5.07 $5 07 $5.07 $5.07 55.07 55.07 $5.07 1,055.07 D.DI 0.00 0.00 0.00 $1.14 $1.14 $1.14 $1.14 51.14 51.14 1.14 51.14 1.051.14 0.00 D.00 D.00 0.00 0.00 0.00 0.00 0.00 0.00 D.DI 5,00 100 0.00 0.00 0.00 0.00 1.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 10.03 1.040.03 0.00 0.00 0.00 0.00 900 0.00 1.00 49.15 49.15 49.15 19.15 19.15 9,15 19.15 49.15 1,049.15 0.00 0.00 0-00 11.39 41 39 41.39 1,041.39 0.00 1.00 0.00 0.00 0.00 0.00 0.00 0.00 0 00 0.00 0.00 0.00 0.00 TOOTH 0.00 0.00 0.00 0.00 0.DO 0.00 0200 0.00 0.00 0.00 0,00 0,00 0,00 0,00 D.DO 0.00 100 -4.78 54.78 54.78 4.78 14.78 54.78 14.TH 54.78 1.054.78 0.00 0.00 0.00 0.00 0.00 100 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0 00 $1.26 $1.26 1.26 1,051.26 0.00 1.00 0,00 100 0.DO 5,00 0.00 0.00 0.00 0.00 0.00 1.00 0.00 7.00 0.00 0.00 0.00 (.00 14.54 4.54 54.54 4.54 1,054.34 0.00 0.00 0.00 9.00 0.00 0.00 0 00 46.36 16.36 46.36 46.36 ,046.36 10,00 0.00 0,00 0,D0 9.00 0.00 0-00 0200 0.00 0.00 0.00 0,00 0.00 0.00 0.00 36.95 56.95 36.95 1,056.9 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0200 46.85 16 85 46.85 46.85 46,85 16,85 46,85 46,85 46,85 46.85 16,85 46.85 16.85 46 85 1,046 6 28 16.25 56.28 16.28 56.28 56.28 56.28 16.28 56.28 56 28 0.00 0.00 0.00 0.00 0.00 1,00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0 00 0.00 0.00 0.00 0.00 0,00 1,00 0,90 0,DO 0.00 0.00 53.95 1.053.95 0.00 0.00 0.00 0.0D 0.00 0.00 D.DO 0.00 0.00 1200 40-83 40.83 10.83 .83 10.83 10.83 1.040.83 46 23 1.046 22 0.00 0.00 0,00 0.OD 0 01 0,00 0,00 0.DO 0.00 0.00 0 00 55.00 55-00 1,055.00 0.00 0.00 0.OD 0.00 0.00 D.DO D.DO D.CO 0.00 0.00 0.00 45-01 4501 45.01 15.01 15.01 15.01 45.01 13.01 45.01 15.01 15.01 15.01 45.01 45.01 1,045. 0.00 0.00 0.00 0,00 0,00 0,00 0,0 0,00 0,00 D.DO 0.00 0 00 0 00 12.77 12.77 42.77 12.77 12.77 2.77 12.77 12.77 12.77 12.77 12.17 12.77 12.77 42.77 1,042 19.69 49.69 1.049.69 0.00 0.00 0.00 0,00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0 00 0.00 0.00 0.00 0.00 1.00 0,00 0.00 0,00 D.00 0.00 0 00 0-00 0.00 0.00 0.00 0.00 0.0D 0.00 0.00 D.D0 D.DO D.00 0.00 0.00 0.00 $1.41 $1.41 $1.41 $1.41 $1.41 51.41 51.41 1.41 $1.41 1.41 51.41 1,051.41 0 00 40-06 40-06 40 06 40.06 40,06 40,06 1,040,06 0,00 0,00 0.DO 0.00 0 00 0.58 0.56 0.53 0.51 0.48 0,46 0,44 0,42 D,40 D.38 0.36 0.34 0.33 0.31