Financial Modeling Project

User Generated

Avxynhf

Business Finance

Description

  • Use the table provided to create a pivot table and find the inputs needed for the stock valuation section
  • Stock Valuation
  • Stock Portfolio
  • Stock Growth
  • Questions
    • How did you estimate the growth rate for GE’s free cash flow(i.e. what criteria and method did you use)?
    • Does it appear that GE was appropriately valued at the beginning of 2018? Explain.
    • How did you estimate the growth rate for GE’s dividends (i.e. what criteria and method did you use)?
    • How did you select a stock to add to your portfolio (i.e. why did you think the stock would be beneficial and what did you do to determine if this was true)?How beneficial was the stock you selected?
    • Compare the risk and return of your stock to the portfolio you created.
  • Visual Basic
    • Include macros (with buttons) where appropriate to automatically update your model.
      • Make sure there are no pop-ups that show up after your macro is created.
    • Include at least one scroll bar or spin button.
  • Use the database provided to create three pivot tables showing information relevant to finding the firm’s free cash flow and WACC. Format the table and make sure the values make sense.
    • You might need to copy the pivot tables, paste them as text, and make a few additional edits to get the final table.
  • Find the firm’s free cash flow for 2016 and 2017.
  • Find the firm’s WACC (for the weights use book value for debt and market value for equity). Do not include preferred stock.

Note: This is the only part of the final project that does not require all of the numbers to be directly linked, but you must show all of your work.

  • Everything from this part of the project on should be directly linked and either automatically update when the inputs are changed or when a macro button is pressed.
  • For the WACC use the share price at the beginning of January. You will need to download the historical prices for the portfolio section.
  • Ideally, we would use market value for the firm’s debt, but it takes longer to find/calculate and is outside the scope of the project.

Note:

Notes

  • All the numbers except the actual share price are shown in thousands.
  • Refer to projects 3 and 7 for additional examples. The calculations will be exactly the same as they were for project 7, but you will need to have them in a database format. Suggested column headers.
  • Hint: The FCF growth rates should be randomly generated for each year and the remaining columns should just have equations.


Please strictly follow the instruction step by step and answer all question with around 2-5 sentences. I also upload the project 3 and 7 for you as additional example. Please make this project nice and beautiful. All works should in the excel.

Unformatted Attachment Preview

PROJECT REQUIREMENTS Final Project FINA 4630 Financial Modeling Due Friday, Dec 7th at 11:59 P.M Use the spreadsheet file Final Project - Shell as the basis for your model. Due by the end of the day on Thursday, Dec 13th. Your final project should look professional. All tables and graphs should be easy to read and appropriately labeled. The entire workbook should be appropriately formatted (appropriate and consistent decimal places, use of dollar signs, etc.) The components of your model should also be labeled and easy to follow. Remember you are creating a model. Once your model is finished you should be able to change the variables in the input cells, and the information in the output cells should automatically change. This means all of your formulas should be linked (i.e. use =C2+D2 NOT =2+3). Use the homework and in class examples as references but DO NOT COPY directly from them. You are creating your own unique model. This model should look different than the previous projects and examples. It should also look different than your classmates’ projects. Projects that look identical to your classmates or that have chunks copied and pasted from previous material will be considered plagiarism. Note: I will be posting additional tips and examples under the FINAL PROJECT tab of D2L if I notice several people are having problems with the same steps in the project. If you are having problems with the project check here first to see if any new tips have been posted. 1) Use the table provided to create a pivot table and find the inputs needed for the stock valuation section a. Use the database provided to create three pivot tables showing information relevant to finding the firm’s free cash flow and WACC. Format the table and make sure the values make sense. • You might need to copy the pivot tables, paste them as text, and make a few additional edits to get the final table. Note: This is the only part of the final project that does not require all of the numbers to be directly linked, but you must show all of your work. b. Find the firm’s free cash flow for 2016 and 2017. • Everything from this part of the project on should be directly linked and either automatically update when the inputs are changed or when a macro button is pressed. c. Find the firm’s WACC (for the weights use book value for debt and market value for equity). Do not include preferred stock. Note: • For the WACC use the share price at the beginning of January. You will need to download the historical prices for the portfolio section. • Ideally, we would use market value for the firm’s debt, but it takes longer to find/calculate and is outside the scope of the project. 2) Stock Valuation a. Create an input section. This should include important variables that might impact the estimated share price. • Determine what the growth rate for FCF should be for 2018 and 2019 as well as the constant growth rate. This is just an educated guess based on the information from the previous section and any other outside data you think is relevant. b. Use a Monte Carlo model to estimate the free cash flow growth for years 1 and 2 and the constant growth rate. You will need to use the Random Number Generator three times to achieve this. • Use the Random Number Generator for your Monte Carlo model. • The distribution should be normal with the mean equal to your free cash flow growth for the year a, standard deviation of .1, and 50 simulations should be generated for each free cash flow growth estimate. • Create a macro so you can re-run the Monte Carlo simulation multiple times. Make sure to clear your simulations each time you run the macro, so you do not get pop-up messages (you do not need to create a histogram for this project). • Calculate the estimated share price for each Monte Carlo simulation. c. Calculate to probability that the estimated share price from the Monte Carlo model will be higher or lower than the current share price. You will need to know the mean and standard deviation of the estimated share prices to determine the probability. d. Have a neatly labeled inputs and outputs section. If you change the variables in the inputs section, the outputs should change accordingly. Notes • • All the numbers except the actual share price are shown in thousands. Refer to projects 3 and 7 for additional examples. The calculations will be exactly the same as they were for project 7, but you will need to have them in a database format. Suggested column headers. Estimated Share Price PV of FCF PV of Horizon Value FCF yr 1 • FCF yr 2 Horizon Value FCF Growth yr1 FCF Growth yr2 FCF constant growth Hint: The FCF growth rates should be randomly generated for each year and the remaining columns should just have equations. 3) Stock Portfolio a. Download the weekly data for GE and the Vanguard Energy Fund (VGENX) from 01/01/2013 to 01/01/2018. Find another stock that would be beneficial to add to your portfolio (you might have to look at more than one stock). Create an optimal portfolio with at least 20% invested in VGENX. b. Create a graph comparing the historical price changes of each stock and your new portfolio. c. You should be able to replace the historical data for any one of the three stocks and the outputs should change accordingly. 4) Stock Growth a. If you purchased one share of GE and held the stock for thirty years what would the stock be worth? Assume that all dividends the stock pays are reinvested at the end of each year and you can reinvest all the money (i.e. you can buy a fraction of a stock). Assume you purchased the stock at the beginning of 2018. • Estimate the annual growth rate for the dividends. Dividend information can be found in the database for GE. • Use the average return you found from the stock portfolio section. (note that return was a weekly return and you want an annual return). Assume you will make the same return every year for the next 30 years. • Annual return = (weekly return – 1)^52 -1 b. Create a graph showing the stock’s growth rate during the thirty-year period. c. Have a neatly labeled inputs and outputs section. If you change the variables in the inputs section the outputs should change accordingly. 5) Questions a. How did you estimate the growth rate for GE’s free cash flow(i.e. what criteria and method did you use)? b. Does it appear that GE was appropriately valued at the beginning of 2018? Explain. c. How did you estimate the growth rate for GE’s dividends (i.e. what criteria and method did you use)? d. How did you select a stock to add to your portfolio (i.e. why did you think the stock would be beneficial and what did you do to determine if this was true)? How beneficial was the stock you selected? e. Compare the risk and return of your stock to the portfolio you created. 6) Visual Basic a. Include macros (with buttons) where appropriate to automatically update your model. • Make sure there are no pop-ups that show up after your macro is created. • You do not need to include part 1 in your macro. b. Include at least one scroll bar or spin button. Final Project Name: Grade: 0 Comments: Rubric Pivot Tables FCF and WACC Stock Valuation Stock Growth Portfolio Visual Basic Everything is organized, linked, formatted, and looks professional Questions Total Total Points Your Points 5 10 15 15 15 15 15 10 100 0 Final Project Comments GE Financial Statements Year Company Name Financial Statement Subcategory (Financial Statement) 2017 GE Income Statement 3. Income from Continuing Operations 2016 GE Income Statement 3. Income from Continuing Operations 2015 GE 2017 GE Income Statement Income Statement 3. Income from Continuing Operations 3. Income from Continuing Operations 2016 GE 2015 GE 2017 GE Income Statement Income Statement Income Statement 3. Income from Continuing Operations 3. Income from Continuing Operations 3. Income from Continuing Operations 2016 GE 2015 GE Income Statement Income Statement 3. Income from Continuing Operations 3. Income from Continuing Operations 2017 GE Income Statement 3. Income from Continuing Operations 2016 GE Income Statement 3. Income from Continuing Operations 2015 2017 2016 2015 2017 2016 2015 GE GE GE GE GE GE GE Income Statement Income Statement Income Statement Income Statement Income Statement Income Statement Income Statement 3. Income from Continuing Operations 3. Income from Continuing Operations 3. Income from Continuing Operations 3. Income from Continuing Operations 5. Net Income 5. Net Income 5. Net Income 2017 GE Income Statement 5. Net Income 2016 GE Income Statement 5. Net Income 2015 GE Income Statement 5. Net Income 2017 GE Income Statement 5. Net Income 2016 GE Income Statement 5. Net Income 2015 2017 2016 2015 2017 2016 2015 Income Statement Income Statement Income Statement Income Statement Income Statement Income Statement Income Statement 5. Net Income 4. Non-recurring Events 4. Non-recurring Events 4. Non-recurring Events 4. Non-recurring Events 4. Non-recurring Events 4. Non-recurring Events GE GE GE GE GE GE GE 2017 2016 2015 2017 2016 2015 2017 2016 2015 2017 2016 2015 GE GE GE GE GE GE GE GE GE GE GE GE Income Statement Income Statement Income Statement Income Statement Income Statement Income Statement Income Statement Income Statement Income Statement Income Statement Income Statement Income Statement 4. Non-recurring Events 4. Non-recurring Events 4. Non-recurring Events 4. Non-recurring Events 4. Non-recurring Events 4. Non-recurring Events 2. Operating Expenses 2. Operating Expenses 2. Operating Expenses 2. Operating Expenses 2. Operating Expenses 2. Operating Expenses 2017 GE Income Statement 2. Operating Expenses 2016 GE Income Statement 2. Operating Expenses 2015 2017 2016 2015 2017 2016 2015 2017 2016 2015 2017 2016 2015 2017 2016 2015 2017 2016 2015 2017 2016 2015 2017 2016 2015 2017 2016 2015 2017 2016 Income Statement Income Statement Income Statement Income Statement Income Statement Income Statement Income Statement Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet 2. Operating Expenses 2. Operating Expenses 2. Operating Expenses 2. Operating Expenses 1. Revenue 1. Revenue 1. Revenue 1.Current Assets 1.Current Assets 1.Current Assets 1.Current Assets 1.Current Assets 1.Current Assets 1.Current Assets 1.Current Assets 1.Current Assets 1.Current Assets 1.Current Assets 1.Current Assets 1.Current Assets 1.Current Assets 1.Current Assets 1.Current Assets 1.Current Assets 1.Current Assets 3. Current Liabilities 3. Current Liabilities 3. Current Liabilities 3. Current Liabilities 3. Current Liabilities GE GE GE GE GE GE GE GE GE GE GE GE GE GE GE GE GE GE GE GE GE GE GE GE GE GE GE GE GE GE 2015 2017 2016 2015 2017 2016 2015 GE GE GE GE GE GE GE Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet 3. Current Liabilities 3. Current Liabilities 3. Current Liabilities 3. Current Liabilities 2. Long-term Assets 2. Long-term Assets 2. Long-term Assets 2017 GE Balance Sheet 2. Long-term Assets 2016 GE Balance Sheet 2. Long-term Assets 2015 2017 2016 2015 2017 2016 2015 2017 2016 2015 2017 2016 2015 GE GE GE GE GE GE GE GE GE GE GE GE GE Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet 2. Long-term Assets 2. Long-term Assets 2. Long-term Assets 2. Long-term Assets 2. Long-term Assets 2. Long-term Assets 2. Long-term Assets 2. Long-term Assets 2. Long-term Assets 2. Long-term Assets 2. Long-term Assets 2. Long-term Assets 2. Long-term Assets 2017 GE Balance Sheet 4. Long-Term Liabilities 2016 GE Balance Sheet 4. Long-Term Liabilities 2015 2017 2016 2015 2017 2016 2015 2017 2016 2015 2017 2016 2015 2017 2016 2015 2017 Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet 4. Long-Term Liabilities 4. Long-Term Liabilities 4. Long-Term Liabilities 4. Long-Term Liabilities 4. Long-Term Liabilities 4. Long-Term Liabilities 4. Long-Term Liabilities 4. Long-Term Liabilities 4. Long-Term Liabilities 4. Long-Term Liabilities 4. Long-Term Liabilities 4. Long-Term Liabilities 4. Long-Term Liabilities 5. Stockholders' Equity 5. Stockholders' Equity 5. Stockholders' Equity 5. Stockholders' Equity GE GE GE GE GE GE GE GE GE GE GE GE GE GE GE GE GE 2016 2015 2017 2016 2015 2017 2016 2015 2017 2016 2015 2017 2016 2015 2017 2016 2015 2017 2016 2015 GE GE GE GE GE GE GE GE GE GE GE GE GE GE GE GE GE GE GE GE Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet Balance Sheet 5. Stockholders' Equity 5. Stockholders' Equity 5. Stockholders' Equity 5. Stockholders' Equity 5. Stockholders' Equity 5. Stockholders' Equity 5. Stockholders' Equity 5. Stockholders' Equity 5. Stockholders' Equity 5. Stockholders' Equity 5. Stockholders' Equity 5. Stockholders' Equity 5. Stockholders' Equity 5. Stockholders' Equity 5. Stockholders' Equity 5. Stockholders' Equity 5. Stockholders' Equity 5. Stockholders' Equity 5. Stockholders' Equity 5. Stockholders' Equity ments GE Misc Financi Line Item Earnings Before Interest and Taxes Earnings Before Interest and Taxes Earnings Before Interest and Taxes Income Before Tax Income Before Tax Income Before Tax Income Tax Expense Income Tax Expense Income Tax Expense Interest Expense Interest Expense Interest Expense Minority Interest Minority Interest Minority Interest Net Income Net Income Net Income Net Income Applicable To Common Shares Net Income Applicable To Common Shares Net Income Applicable To Common Shares Preferred Stock And Other Adjustments Preferred Stock And Other Adjustments Preferred Stock And Other Adjustments Discontinued Operations Discontinued Operations Discontinued Operations Effect Of Accounting Changes Effect Of Accounting Changes Effect Of Accounting Changes Amount old Year $ (3.922.000) 2015 $ 14.055.000 2015 $ $ 11.649.000 (8.791.000) 2015 2016 $ $ $ 9.030.000 8.186.000 (3.043.000) 2016 2016 2017 $ $ (464.000) 6.485.000 2017 2017 $ 4.869.000 2017 $ 5.025.000 2016 $ $ $ $ $ $ $ 3.463.000 17.723.000 1.663.000 1.864.000 (5.786.000) 8.831.000 (6.126.000) 2015 $ (6.222.000) $ 8.176.000 $ (6.145.000) $ $ $ - (309.000) (954.000) (7.495.000) Extraordinary Items Extraordinary Items Extraordinary Items Other Items Other Items Other Items Non Recurring Non Recurring Non Recurring Research Development Research Development Research Development Selling General and Administrative Selling General and Administrative Selling General and Administrative Cost of Goods Sold Cost of Goods Sold Cost of Goods Sold Total Revenue Total Revenue Total Revenue Cash And Cash Equivalents Cash And Cash Equivalents Cash And Cash Equivalents Inventory Inventory Inventory Net Receivables Net Receivables Net Receivables Other Assets Other Assets Other Assets Other Current Assets Other Current Assets Other Current Assets Short Term Investments Short Term Investments Short Term Investments Accounts Payable Accounts Payable Accounts Payable Other Current Liabilities Other Current Liabilities $ 21.912.000 $ 19.359.000 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 20.439.000 104.102.000 90.280.000 85.298.000 122.092.000 123.693.000 117.386.000 43.299.000 48.129.000 70.483.000 21.923.000 22.354.000 22.515.000 41.076.000 42.263.000 45.856.000 64.385.000 67.153.000 178.904.000 38.696.000 44.313.000 31.973.000 76.297.000 77.082.000 72.110.000 18.697.000 17.564.000 Other Current Liabilities Short/Current Long Term Debt Short/Current Long Term Debt Short/Current Long Term Debt Accumulated Amortization Accumulated Amortization Accumulated Amortization Deferred Long Term Asset Charges Deferred Long Term Asset Charges Deferred Long Term Asset Charges Goodwill Goodwill Goodwill Intangible Assets Intangible Assets Intangible Assets Long Term Investments Long Term Investments Long Term Investments Property Plant and Equipment Property Plant and Equipment Property Plant and Equipment Deferred Long Term Liability Charges Deferred Long Term Liability Charges Deferred Long Term Liability Charges Long Term Debt Long Term Debt Long Term Debt Minority Interest Minority Interest Minority Interest Negative Goodwill Negative Goodwill Negative Goodwill Other Liabilities Other Liabilities Other Liabilities Capital Surplus Capital Surplus Capital Surplus Common Stock $ $ $ $ - 23.597.000 26.016.000 31.131.000 52.943.000 $ 6.207.000 $ 1.833.000 $ $ $ $ $ $ $ $ $ $ 3.105.000 83.968.000 70.438.000 65.526.000 20.273.000 16.436.000 17.797.000 58.117.000 52.263.000 56.913.000 $ $ $ $ $ $ $ $ $ $ 108.575.000 105.080.000 144.659.000 17.723.000 1.663.000 1.864.000 62.976.000 53.812.000 96.651.000 702.000 Common Stock Common Stock Misc. Stocks Options Warrants Misc. Stocks Options Warrants Misc. Stocks Options Warrants Other Stockholder Equity Other Stockholder Equity Other Stockholder Equity Preferred Stock Preferred Stock Preferred Stock Redeemable Preferred Stock Redeemable Preferred Stock Redeemable Preferred Stock Retained Earnings Retained Earnings Retained Earnings Treasury Stock Treasury Stock Treasury Stock $ $ $ $ $ $ $ $ $ $ $ $ $ $ 702.000 702.000 3.399.000 3.025.000 2.972.000 22.776.000 18.625.000 21.084.000 125.682.000 139.532.000 140.020.000 (84.902.000) (83.038.000) (63.539.000) GE Misc Financial Information Company Name Category GE Cost of debt after flotation costs Cost of equity after flotation costs GE GE GE GE GE GE GE GE GE GE GE Amount Shares oustanding Cost of debt after flotation costs Cost of equity after flotation costs Shares oustanding Cost of debt after flotation costs Cost of equity after flotation costs Shares oustanding Annual Dividend Payments Annual Dividend Payments Annual Dividend Payments 3,50% 9% 8.683.512 3,50% 9% 8.683.512 3,50% 9% 8.683.512 $0,84 $0,93 $0,92 Project #5 - Capital Budgeting Monte Carlo Analysis Name: Grade: 0 Comments: Worksheet Key light yellow shading indicates cells that should hold user written formulas pale blue shading indicates cells that should contain Excel functions (perhaps with user written components) rose shading indicates the random number generator should be used to determine values orange shading is for Chart, Histogram, etc. green shading indicates data for histogram purple shading indicates location for macro launch button ABCDEF blue font indicates user supplied values ABCDEF black font indicates calculated values Total Rubric Correct equations/functions used Correct use of the Data Analysis tools Visual Basic (one macro linked to a button does everything explained in step 7) Explanations Formatting including table labels Total Points 10 10 20 5 5 Your Points 2 point reduction for each of the following: 1) if file is named incorrectly, 2) the cells have been moved or work (other than a comment) is shown outside of the specified cells in the model, 3) base case numbers not used Total 0 50 0 Comments A D E F G H I J K L M N O P Q R S T U V Capital Budgeting Monte Carlo Analysis $ Project Cash Flows CF2 CF3 Cost of capital = 60.000,00 $ 60.000,00 11,00% $ CF4 60.000,00 $ Base Case NPV = Simulation Results No. of Simulations = 300 Mean NPV = $ 15.564,78 Std. Dev. = $ 22.498,62 Maximum NPV = $ 78.350,18 Minimum NPV = $ (107.323,15) Sensitivity Analysis Probability NPV is less than Probability NPV is greater than Probability NPV is greater than Probability NPV is greater than Probability NPV is greater than Sim. No. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 CF5 60.000,00 $ 16.753,82 $ $ $ x Value $0,00 $0,00 10.000,00 20.000,00 30.000,00 $ 60.000,00 Probability 24,45% 75,55% 59,77% 42,19% 26,06% Population Values for Simulation CF1 Mean = Std. Dev. = $ $ CF2 60.000,00 9.000,00 $ $ CF3 60.000,00 13.000,00 $ $ 60.000,00 14.000,00 CF4 $ $ CF5 60.000,00 15.000,00 $ $ 60.000,00 19.000,00 Sample Values from Simulation CF1 CF2 CF3 CF4 CF5 Mean = Std. Dev. = $ $ 59.275,44 8.407,23 $ $ 58.686,46 13.515,24 $ $ 60.501,19 12.987,75 $ $ 60.241,33 15.298,46 $ $ 60.007,37 21.714,95 Maxumum = Minimum = $ $ 89.520,02 35.521,24 $ $ 100.513,70 10.057,83 $ $ 94.300,97 12.719,08 $ $ 100.060,70 17.839,46 $ $ 104.503,59 (121.198,12) Macro Launch Button Here Frequency 40 35 30 25 Place NPV Histogram 2 here 20 15 10 5 0 Frequency More Finance 495/795 Financial Modeling Histogram 40 10 Frequency More 93548 80645 67742 (9677) 54839 3226 Bin 41935 0 29032 Frequency 30 Place NPV Histogram 1 here 20 16129 0 0 0 0 0 1 2 4 7 8 11 28 18 34 37 29 32 36 16 21 7 5 1 1 1 0 0 0 0 1 0 0 (22581) 0 (74194) (67742) (61290) (54839) (48387) (41935) (35484) (29032) (22581) (16129) (9677) (3226) 3226 9677 16129 22581 29032 35484 41935 48387 54839 61290 67742 74194 80645 87097 93548 100000 (35484) (80645) (74194) (67742) (61290) (54839) (48387) (41935) (35484) (29032) (22581) (16129) (9677) (3226) 3226 9677 16129 22581 29032 35484 41935 48387 54839 61290 67742 74194 80645 87097 93548 100000 (48387) NPV (100000) (93548) (87097) (80645) 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 (61290) BINS (100000) (93548) (87097) 78 (74194) 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 C CF1 (205.000,00) (87097) 53 B CF0 $ (100000) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 NPV $33.916,80 $78.350,18 $16.968,45 $30.012,26 $14.993,46 $40.484,86 $19.449,81 $25.126,04 -$9.044,76 $28.269,12 $35.717,39 $27.477,86 $26.005,75 $22.483,01 $1.309,64 $16.470,92 $29.978,31 -$2.069,72 $16.604,84 -$21.401,85 $3.695,11 $56.314,61 $28.823,17 $11.794,91 -$20.260,30 -$31.966,48 $22.619,89 $6.561,73 $17.751,96 $31.774,78 $8.237,13 $45.193,15 $34.354,08 $47.763,33 $1.223,37 -$6.875,27 $20.756,74 $34.055,83 $47.684,74 $14.987,16 -$31.582,27 -$4.046,34 $30.234,58 $11.921,37 $21.821,72 $21.543,93 $7.541,78 -$10.904,13 -$367,70 CF1 CF2 CF3 CF4 CF5 89520,015 57812,315 68229,981 60671,555 59824,084 73570,539 55262,935 62119,664 63640,91 67289,35 58335,079 69812,679 63820,212 41967,039 70570,816 56139,812 73159,956 64017,788 51474,831 44373,89 50177,334 67901,806 51819,05 61833,306 55230,48 60745,449 66829,522 57181,002 61440,588 62164,991 61073,481 62051,07 50304,987 68170,534 52582,793 46743,005 44925,098 74218,745 63342,489 54477,961 35521,241 47171,882 53508,2 60307,466 53721,594 57546,82 56412,081 60920,393 60374,986 36796,6347 100146,697 57462,47 60676,049 67967,8694 51589,4614 66820,7453 53203,2119 49265,3 58126,4 48112,2494 49254,0973 46105,754 64512,9741 67330,4977 74188,2947 63503,2383 52866,8501 60706,9229 55440,5155 62448,3961 50998,3194 52676,4929 63535,2059 10057,8269 47458,1476 76560,5184 66539,3033 70323,3037 51682,4231 60598,3838 55009,6253 63255,3305 58392,309 44020,5146 85169,0108 77364,157 74902,3708 43256,514 51654,3425 48292,4089 80500,1743 53888,3502 30842,2365 64015,518 68985,2756 76038,8981 44121,6641 54754,9486 62328,836 60841,758 57082,552 63999,778 55335,038 66069,372 70785,807 70904,35 47701,999 80905,045 66141,217 59151,796 70248,987 85072,914 36735,353 64324,404 56253,941 46641,028 54329,391 59053,068 48154,018 90687,37 52257,076 48532,663 94300,974 58549,974 71370,248 59352,372 48465,147 62768,759 62010,147 64816,93 80781,79 50331,73 72906,667 65453,894 64911,36 52154,528 79363,533 62914,153 61509,25 57811,146 87274,564 61770,211 49146,327 65928,498 61731,36 28756,456 57461,325 87596,9796 93963,3516 40031,4527 67153,3179 59895,0045 79647,473 71757,993 55944,7223 66820,7669 57977,2042 81633,5366 67261,4853 63450,2364 59230,2776 25716,3244 51972,7657 77968,1933 33488,0703 62703,689 49059,4097 48988,9295 59928,275 71584,0521 63134,0392 34861,1117 39303,9706 34667,3893 54607,0488 37556,1719 70257,1562 59915,6557 66764,833 87261,1032 89161,7653 71074,269 23037,4987 65961,2262 57716,327 94023,1054 76740,3414 57671,0512 49420,0641 55147,1523 64553,9252 66321,8636 61320,7227 54425,5023 70049,3821 31458,4192 42338,7839 73472,1881 77098,0479 67528,8756 52267,8853 61602,8866 36065,2645 71251,484 33263,1613 47388,7862 79160,4204 70855,5469 72335,3539 60819,2853 74622,2419 49561,1052 42185,9819 77825,2935 75709,611 38973,4693 76376,0296 89247,4215 99454,435 54676,125 58813,9166 18683,602 50978,2059 44945,9425 84501,2734 78603,1275 40817,8063 98189,3369 44058,0108 81590,8221 38749,4289 40195,3663 53213,9008 59390,1747 68321,7219 54647,3963 32448,6065 30828,4395 72711,0297 84031,4193 78934,5656 50736,6691 32043,0787 61053,5859 72562,3319 50 $3.124,40 53465,246 46410,03 70497,024 49622,1766 64125,9113 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 $6.302,74 $13.255,74 $12.629,47 $12.716,90 $31.917,79 -$14.335,66 $69.335,56 $5.891,16 -$5.897,82 $50.483,73 -$6.143,64 $26.333,37 $33.093,99 $35.612,29 -$27.579,98 -$46.921,88 $20.239,61 -$8.374,61 -$11.752,05 $14.298,50 -$3.237,91 $33.778,11 $12.661,77 $16.592,94 63385,309 65150,205 47286,806 54989,48 53265,583 68459,604 73310,2 78700,812 43912,394 69582,607 48679,233 65771 64890,378 63844,339 54570,753 55263,713 54349,419 75309,579 59116,288 64626,725 67235,96 58868,249 50179,831 48572,126 49203,6408 49426,6898 58332,172 46967,7413 64617,7001 47127,8305 72675,5458 48440,8226 49852,9541 68481,7748 48675,1925 64679,182 70773,8947 65314,1741 41384,9536 54274,4296 92202,297 56707,7213 59151,6097 57127,0984 59616,5661 84346,2182 68932,3066 46901,8257 57775,351 57590,771 69010,009 66861,806 58968,251 25434,181 83966,486 52715,971 62766,578 64674,002 75809,36 53469,601 76460,585 68191,219 56355,964 12719,081 42734,216 40630,864 63695,031 47119,531 44409,551 58894,145 66253,442 60506,15 64493,8815 54924,1328 47930,5506 72767,8049 88019,2126 48554,6231 85986,7647 59835,336 58095,2452 100060,695 52829,9701 56970,2571 63118,7938 46194,2456 43030,9775 63453,7834 45900,6607 51975,4259 59488,6991 49078,8672 62500,9967 71187,6034 50569,7791 71745,5102 49768,4052 69350,1649 76926,5832 56000,4948 59630,0699 67667,4428 52687,583 38272,7491 58835,7763 40463,6445 42570,3412 72391,0399 41632,5339 83905,7908 42320,8987 22144,3476 67334,3199 31690,7314 10482,6701 80760,499 32285,3499 46052,6441 58558,9443 81329,5425 75 $27.222,59 60370,852 73811,7775 75443,957 70392,3867 27624,0486 $11.138,04 $4.215,39 -$602,82 $13.870,64 $10.328,81 $1.188,27 -$32.411,15 $6.116,85 -$7.279,85 $34.176,42 $18.441,06 $57.586,05 $41.994,70 $15.269,03 -$39.670,85 $8.465,36 $42.594,95 $29.482,39 -$25.575,26 $21.031,56 $31.251,66 $24.714,93 $17.399,35 -$22.916,12 $30.229,26 $34.457,46 -$4.261,76 -$8.824,25 $29.272,38 $13.209,31 $23.973,85 $20.357,26 $1.478,35 $2.150,99 $31.192,21 $34.232,37 $22.272,96 $41.929,90 -$14.841,08 -$24.188,67 $39.209,17 $23.889,84 $18.088,10 $4.096,86 $13.742,42 -$107.323,15 $4.561,30 -$2.663,89 $29.368,77 -$4.064,90 $43.513,78 $58.673,28 $467,17 $7.899,97 -$4.380,77 -$19.507,83 $14.411,30 $7.388,39 -$14.939,45 $34.392,05 -$16.649,70 $29.028,71 $9.968,93 $21.998,79 $30.678,50 -$4.683,67 $15.047,42 $36.634,68 $44.057,41 $3.523,31 $21.073,07 $44.088,02 $46.269,11 $36.060,18 $35.136,13 $51.181,62 -$16.177,03 $16.973,49 $28.506,52 $2.697,78 $7.010,03 $11.235,23 $41.934,59 -$8.200,81 $24.231,48 $7.496,63 $44.494,01 $7.882,89 -$4.919,94 $21.390,14 $14.754,14 -$4.256,79 $12.322,10 $51.488,87 $32.986,87 $52.282,06 -$636,36 $15.485,29 $33.276,19 $54.174,85 $53.760,33 $24.919,65 $12.703,54 -$6.484,72 $8.111,90 -$12.600,73 $6.788,06 -$4.486,36 $12.591,31 $23.655,96 -$9.871,30 $38.439,40 $38.357,84 -$22.369,20 $5.309,89 $23.970,93 $16.348,72 $43.216,20 -$23.665,95 $44.057,65 $9.120,79 -$37.971,65 $26.012,12 $10.967,94 $27.332,13 -$4.481,16 $9.946,90 $13.330,85 $7.122,88 $29.743,81 $34.707,56 -$2.850,28 $33.533,72 -$4.809,19 -$4.622,71 $42.638,68 -$13.203,87 $14.655,60 $14.361,72 -$7.946,50 $11.520,63 $24.471,99 $25.303,35 $42.055,85 $35.956,72 $760,85 $9.632,26 $4.389,98 -$2.188,98 $6.817,05 $23.393,67 $18.095,46 $15.439,39 $29.520,86 $25.729,86 $23.369,18 $3.876,20 $23.018,87 $22.506,80 $33.161,07 $58.543,58 -$4.990,83 $7.581,34 $25.826,55 -$4.904,11 $56.503,94 -$13.289,75 $10.764,02 $15.823,96 $12.921,80 $7.999,38 $15.739,55 $27.938,62 $40.791,15 -$26.606,73 $13.501,91 $3.666,58 -$13.675,95 -$24.539,67 $30.194,95 $43.002,66 $63.132,17 $42.610,07 $30.564,75 -$21.245,74 $44.629,12 $24.675,45 $19.059,67 $7.310,17 $35.392,56 $4.638,82 $41.824,83 $47.696,84 $9.757,60 $24.610,13 $3.215,33 -$9.578,47 $9.419,44 -$7.831,50 $6.217,80 $21.259,52 -$3.655,73 $49.835,52 $2.043,66 $30.265,22 $33.268,02 $22.387,34 $26.591,67 $38.165,03 $21.212,39 $42.250,31 $32.101,17 $29.408,17 $19.962,88 -$16.173,93 -$29.829,08 -$11.647,08 $42.279,07 $38.964,65 $5.522,75 $25.404,33 $45.778,71 $23.663,03 $39.589,07 -$8.396,99 64203,486 59596,068 51211,321 58882,133 55003,948 47965,216 36820,764 82994,573 54372,87 72332,55 60714,365 63204,206 86060,516 64939,941 53069,971 53587,425 61632,005 66559,862 42681,711 71824,295 52707,785 53555,43 61767,28 46068,933 54006,509 66983,869 55364,64 63631,947 62549,779 70432,113 63866,982 49408,557 46082,603 60702,619 50356,494 43636,135 55680,22 64546,753 48769,785 48714,083 54567,448 50483,88 54955,674 58175,135 56269,245 50745,937 74168,609 61299,891 53627,298 63553,653 57795,3 61372,291 53111,574 54711,318 51210,195 55521,166 46075,768 75861,401 50802,989 55979,173 66457,033 58724,451 56552,533 61156,737 57465,272 58749,488 57963,766 48005,488 70861,768 66772,725 58610,99 60508,716 56588,815 51652,189 61983,99 65442,466 62856,621 51996,469 62699,931 68557,563 62428,89 66708,94 48116,688 53765,427 53560,771 45200,702 56562,171 57342,532 60230,318 69729,515 72527,303 44053,266 58779,365 59262,839 60414,266 77380,089 58343,49 51902,991 56200,866 66697,112 67553,413 52059,661 64352,256 58843,263 50651,988 55852,493 65855,811 70432,113 51042,291 50505,53 60016,862 60990,317 58896,703 43885,136 62386,786 64343,743 55741,928 62532,579 52800,138 55638,464 66414,346 53771,567 61117,867 51819,05 65537,837 79153,14 55602,806 52572,152 76772,237 62673,287 61530,618 57302,239 57351,167 52404,391 55567,086 65836,268 56325,448 67606,424 56433,691 56262,492 67019,271 66556,281 62871,825 69254,063 61905,768 54434,691 65385,536 66612,966 45981,594 58314,063 62304,88 67114,857 38653,65 67134,645 54788,855 55458,721 53067,188 58486,142 63678,297 65247,008 68949,81 60368,1 43408,047 57399,298 60036,835 59411,957 61370,9 72336,091 63147,051 65726,256 54547,609 65820,982 69736,923 52262,885 67799,079 61015,948 69148,389 52205,935 60561,829 60809,715 62007,278 56920,193 50853,903 50285,301 59429,208 46244,184 62951,633 49393,414 45136,446 63833,032 54285,552 63483,79 71395,582 61419,675 74151,992 57796,006 60977,855 47767,046 59105,218 50209,749 66784,614 51197,999 64132,978 69995,583 55114,41 78724,795 63293,857 48131,483 48859,211 58753,652 56638,317 51901,968 61386,227 64969,572 55025,619 57172,325 58059,72 61374,378 57421,542 59556,778 70684,184 73457,907 62572,729 67066,03 42879,226 25350,2528 57361,0397 53503,5567 62949,3845 54309,4713 62189,7142 54767,8805 59016,0245 59572,7899 58486,4678 56220,6107 79611,2296 50427,0999 42231,9842 40956,2951 63557,9217 62663,3461 50308,0084 46261,4391 64849,3507 57103,6584 68506,3971 40008,115 38098,7491 45624,4222 65783,6974 56099,0658 63091,3986 60000,5025 81041,8057 50736,1052 62723,2909 38831,9201 47469,2321 65203,0191 86676,9712 51577,1946 53817,1732 28663,6511 48311,7698 74154,0659 73571,082 56307,6607 37163,8704 58920,2547 38627,0791 47929,8139 51329,0799 77318,1343 37334,0687 62900,4207 75101,7434 57534,4008 56944,3968 59341,8783 33492,2221 47447,0632 70089,1407 48920,2627 57479,7026 41812,8435 54182,2512 68566,8444 41154,7514 66286,5922 60866,3324 39975,6006 60464,0253 70346,4777 47377,1276 65945,5454 90418,8507 65063,2514 79927,2699 61082,7455 43499,7516 65947,7475 61871,59 51805,3129 44413,289 41983,2783 71256,6454 66385,1405 62791,4234 65663,1723 56837,9711 71977,1653 58429,3754 46240,5707 44182,2887 48000,0155 51799,2386 43130,3876 89849,6707 64760,9706 66175,0382 42677,0181 48554,091 66797,9261 78842,411 83579,7052 49759,6376 52813,6151 53665,3012 72444,0135 36593,3308 42908,6391 61676,8581 72013,6997 84059,2635 60087,0205 80424,6226 44595,4289 68147,4377 40892,0938 46118,0504 52033,3278 63375,5305 68049,6284 65192,245 55714,4792 26518,8807 61690,8984 66314,5399 42577,4353 61931,9043 53294,2524 61501,5166 44119,5654 75251,1325 64906,9752 50447,9387 98432,7723 43354,0573 48948,8458 55293,2103 35665,2505 60135,7478 51291,777 43116,4951 34298,3437 47796,0317 58901,2781 51589,4614 66230,7561 40171,9878 51921,4338 49344,8125 60421,2393 56782,6227 51736,0424 67870,8877 71129,8687 72554,533 58411,3447 61335,4418 69062,453 48799,6933 60093,9815 62656,2225 50928,6646 55083,4035 75884,6319 67768,4081 51165,6653 88332,9609 46678,5106 44378,5281 61256,4908 67969,0813 42638,2964 68000,2951 61872,595 63239,9453 45713,896 55822,4862 43888,4469 40282,6551 67390,0287 67271,1146 72763,8373 68137,7425 74640,6592 61538,5683 39787,8445 100513,696 77936,5588 82716,1217 81646,5742 62761,924 57589,0546 59543,9407 64697,2275 56528,7293 53792,6691 88164,2406 50515,5687 62206,873 53772,5988 54996,7673 53108,388 45523,1545 63459,9498 67787,4438 73689,9962 48858,6626 76544,8819 55906,1813 55726,0366 37196,3849 69129,7875 75162,4272 72813,8504 39877,3479 57310,2581 69748,5372 57057,7687 80037,4643 76060,2099 55202,8497 66256,4277 81134,5014 65494,1711 68399,5133 44524,9613 86394,209 53579,263 54126,508 54112,47 80477,314 68822,437 30369,081 49218,522 59564,582 59704,914 50723,538 62749,102 59101,374 62707,625 48218,224 43620,364 69019,241 86237,594 72764,822 70056,735 64835,122 74490,006 55917,601 69168,693 69990,317 83901,866 61187,536 42044,41 66305,552 44064,361 70099,757 70972,662 65805,737 61945,241 72238,252 47478,313 52512,88 70837,757 69988,917 55061,296 76889,43 85774,625 56143,929 68876,059 59422,052 59315,908 60805,278 62275,629 64493,227 52851,735 79233,403 81156,584 58142,298 53818,658 60910,436 63471,489 53627,998 34683,76 62703,264 56320,487 67876,811 64037,722 53556,646 62731,626 53770,225 36431,736 81237,438 70559,543 53750,107 47293,495 56463,303 81301,994 65542,961 54111,308 73957,069 88607,064 47692,545 58293,47 46632,592 58593,044 76761,528 54060,95 59343,777 32775,349 60277,928 34460,807 40302,036 82893,737 47567,539 73916,642 62191,034 60240,445 55824,746 93669,421 74735,592 60035,875 58590,895 62948,102 63710,566 66961,011 60468,635 67986,218 74780,412 57270,554 63503,528 57441,716 55421,001 50252,722 33586,819 57504,856 62528,886 70091,417 77478,551 47058,891 57202,847 62716,347 58216,833 53349,959 49903,013 59322,338 61724,882 32426,34 70670,892 72934,552 69889,727 51863,358 61912,804 48939,767 53642,259 63928,426 68818,506 57370,905 64507,87 53204,533 62794,973 67722,965 53053,52 68216,653 71154,139 55427,781 68217,926 60346,495 66963,43 74018,824 72513,697 63632,958 66105,868 34289,294 47433,525 66917,417 67866,784 51972,177 56072,688 77926,4 61361,755 65002,576 48395,816 65531,374 45626,596 71063,162 86987,436 49532,739 51803,688 55665,489 68173,44 38674,132 62107,586 41163,355 45933,364 54885,391 79669,187 43282,241 51173,647 82429,367 31209,072 58638,245 63031,359 35132,023 47749,206 69288,478 78660,739 72621,035 70047,042 74677,944 58937,103 57635,337 48024,811 46209,159 54730,51 57932,475 78487,699 66764,144 49918,292 47709,894 49033,163 74761,727 44275,473 48744,062 53193,678 51067,566 52389,021 44291,549 64818,075 44383,671 81610,449 45148,347 62982,051 71749,753 80695,143 62410,311 90230,767 87418,828 41825,245 60726,986 46622,438 41401,588 53914,139 50432,066 44196,72 60000,541 44255,323 44245,233 63827,167 71510,82 68390,33 47547,5396 56930,4213 65234,1193 51007,6326 53648,0378 46448,131 45724,7758 49770,3674 60712,2708 74707,075 38353,4349 61194,0529 80669,2903 46771,1836 60558,3843 53715,7554 50347,8169 57872,925 45863,1099 49734,1468 57353,4386 63559,9214 67071,1394 31070,1559 64978,7673 23800,3828 59682,7114 44518,0355 58279,0428 54665,7795 68129,3649 57353,4386 64487,8789 62570,8289 79483,6275 66227,9355 65097,7292 77254,9676 59515,1143 67891,1683 48344,7003 70842,0068 63687,5122 76725,3688 68442,1345 74633,0308 40839,9366 39160,3841 51433,89 52131,2224 68279,9602 65730,9592 47663,773 51126,8333 69793,7573 39392,7827 96546,9532 60571,0035 62870,6381 79940,785 66540,7392 68508,0956 51975,4259 71770,4758 84468,0905 40956,1018 64206,6404 83063,8079 64595,9951 56658,0674 60765,1181 49760,2379 79590,8228 82464,9739 73173,053 79888,1253 38086,7256 71376,4145 76813,2601 39634,7993 41402,3454 28349,0387 77876,8232 59126,851 67390,7813 77427,3396 82242,0567 43353,4503 51025,4701 63240,8593 20292,9165 61280,4094 62849,5947 40295,5358 59911,0685 49213,0404 46752,5275 51866,6447 46864,0202 36612,7985 67225,4011 77846,5029 50022,3088 46837,0764 81064,8523 62187,3575 52539,1819 41899,4411 75926,6051 66596,315 39956,0101 71952,4316 66855,1458 24739,5271 68056,3609 84799,5331 58888,7907 77203,9108 36108,3018 95463,745 50985,6342 38679,8298 58121,848 50184,9264 69704,4676 35396,4403 80086,0768 49285,7204 49226,41 67745,2626 48996,4329 60427,4852 64586,3771 44991,1887 53151,215 76333,6949 60592,8314 43765,5878 75380,6695 59237,1841 53583,9696 85955,9329 67632,7296 65707,5113 75023,9543 88306,6584 53770,8196 73858,4483 61752,1643 61089,3473 63190,3483 60217,4602 67575,3121 43201,7807 69862,0205 74583,236 43728,4463 80486,9593 72032,9901 50800,7624 78944,138 44281,5782 59169,3812 52576,8179 64786,375 78418,325 31444,3675 64870,9751 75442,9131 58427,9498 53498,3634 61128,4669 46484,3517 76381,5116 50554,3973 45541,4217 54710,97 77353,193 41571,9207 55155,6276 52964,7402 77890,8408 65377,0236 62196,6343 43951,9091 45563,3178 77166,326 62207,0708 44323,2217 72494,6155 34898,4237 75285,8547 77693,7419 70631,5042 74318,6071 20872,4465 52388,1603 60413,7064 52896,7839 77783,7819 90962,2919 62865,9656 74255,4086 53005,7015 59482,9523 55664,3023 33894,0687 77172,9425 79395,9067 92238,1311 59814,6677 56894,0983 63740,7517 70665,4397 17839,4636 36624,3945 43603,891 78372,0772 91010,8589 49875,8233 62019,334 63604,7709 50040,9307 60371,2444 72970,0879 72845,8817 54412,5557 54406,4859 70040,56 46360,9696 53218,5233 71851,2435 33153,1716 28374,3636 56745,0856 102384,672 92406,9697 54227,137 85646,8775 15382,7001 78059,1405 97025,1564 54778,8353 33717,1174 41630,2011 96441,2517 49759,981 82927,3746 65070,3932 93630,422 82051,7768 35848,3955 46466,0776 71364,2602 34968,4775 57497,9062 67844,3725 72202,8564 46213,8702 55147,6639 86324,5715 90943,4927 71470,4699 54896,4501 21453,3044 78855,9397 24118,9788 75613,6616 43172,6063 53407,182 -121198,12 54729,003 57493,4997 71218,5865 68064,892 71584,0453 75471,4871 62367,2851 74753,5297 26637,7698 60545,8446 61642,286 36900,0806 29192,5905 81671,4352 1818,96909 76157,951 58930,4081 76424,5444 59230,1798 74767,3109 56765,7357 72463,056 77967,9864 64053,0097 64763,7741 48805,6277 79413,4486 60271,0863 54664,0287 74994,4617 31526,4812 59435,2333 84719,8705 68979,5219 64525,6718 67571,3422 92703,5013 58109,5675 66084,1694 83314,4556 95163,539 41504,5294 67226,4038 53931,1238 95399,9349 58302,2656 79437,9868 59214,1738 62156,4858 96967,0943 73343,5356 91203,7355 94320,6011 104503,595 70045,5713 70169,4934 49260,6206 49235,0456 14911,2908 49567,8661 71615,5388 38180,299 65019,2 48891,9433 34123,5092 41403,9143 90002,1429 62480,1045 57729,3771 53678,2251 80107,3863 85226,7046 30771,587 66998,7777 51142,1969 80668,308 60528,3914 48991,824 70288,793 29834,9893 39723,8706 88974,7368 59820,4999 43535,2787 82567,2511 46889,8362 29089,3401 82993,7314 49981,1485 73548,9131 56279,4436 52628,0952 42477,1567 53622,8846 72672,8833 50505,1982 54497,4888 76481,6561 48848,8287 32720,2976 50263,9854 59994,9023 62323,3497 40483,3874 65757,6563 50345,1169 71399,0586 50071,2442 72584,062 53863,795 68789,8047 58617,2656 68334,5094 72629,3367 74958,7561 59868,453 59027,9343 82192,0936 20147,9431 94956,1742 54612,5979 70346,0343 52127,1365 41997,8848 60252,1858 57858,1377 87382,0024 62000,0334 40591,4331 76418,1938 48229,888 58899,8434 15007,7155 65408,5922 68544,6607 96286,9378 79083,2202 75392,6669 43600,7714 91384,2293 40037,0347 63461,6096 60940,0537 70489,6344 56641,792 72279,7974 81199,0323 54144,8823 57862,5442 27258,0498 54938,6358 76602,6621 44718,0788 55216,7207 41727,3601 74942,9229 82978,6542 36392,7281 45408,9924 97750,4148 67168,6009 66654,5954 71808,9065 61837,8569 58538,5534 47082,7719 79935,1871 72649,2955 79025,5469 21327,3303 44810,2698 64916,9216 64425,5535 59545,7415 66958,3416 74984,5255 66561,9508 61669,9994 40039,5835 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 File Name: 20181211220425project_3.xlsm You are the project manager and need to determine if the company should invest in this project. Assume the company is fairly risk averse. If the company does not invest in this project their next best alternative is a project with the same initial cost and an NPV of $10,000. The risk level (standard deviation) for the alternative project is very low. Based on the simulation and probability results and the histogram data do you think the company should invest in the current project or pick the alternative project. Explain your reasoning. From my simulation Analysis, I think the company should not invest this project. Accrding to my multi-test the number, The NPV greater than 10,000 has around 60% chance. Because the company is fairly risk averse, 60% probability is still too less. Even though, my data for NPV has average 16,000. Thefore, the company should accept this project only if the project has very low risk and a NPV with greater than 10,000 . Project #7 - Common Stock Valuation Name: Grade: 0 Comments: Worksheet Key light yellow shading indicates cells that should hold user written formulas pale blue shading indicates cells that should contain Excel functions (perhaps with user written components) light gray shading indicates cells that should contain user input data sea green shading indicates cells that make up an Excel Data Table light green shading indicates your estimates aqua shading indicates a direct link to another cell Rubric Correct equations/functions used Data Tables Questions Logical Estimates and explanations Formatting 2 point reduction for each of the following: 1) if file is named incorrectly, 2) the cells have been moved or work (other than a comment) is shown outside of the specified cells in the model, 3) base case numbers not used Total Total Points 25 5 10 5 5 0 50 Your Points 0 Comments The Ale House Actual Income Statement Net Sales Cost Of Goods Sold Selling, general & administrative Depreciation Operating profit Interest income Interest expense Earnings before taxes Taxes Net income Dividends Additions to RE $ $ $ $ $ $ $ $ $ $ $ $ 2015 34,412 21,341 11,747 3,240 (1,916) 38 594 (2,472) (742) (1,730) (1,730) Annual Financial Information (thousand Actual Actual 2016 2017 $ 36,705 $ 37,448 $ 23,557 $ 24,505 $ 11,817 $ 10,909 $ 3,183 $ 3,092 $ (1,852) $ (1,058) $ 33 $ 14 $ 839 $ 1,020 $ (2,658) $ (2,064) $ (797) $ (619) $ (1,861) $ (1,445) $ $ $ (1,861) $ (1,445) The Ale House l Information (thousands of dollars) Balance sheet Cash Short term investments Accounts receivable Inventory Total current assets Net PP&E Total assets Accrued expenses Accounts payable Short-term debt Total current liabilities Long-term debt Total liabilities Common stock Retained earnings Total common equity Total liabilities and equity Actual 2015 $ 7,487 $ 661 $ 1,076 $ 2,726 $ 11,950 $ 73,670 $ 85,620 $ 1,863 $ 2,664 $ 2,241 $ 6,768 $ 8,251 $ 15,019 $ 72,543 $ (1,942) $ 70,601 $ 85,620 - Actual $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Actual 2016 6,364 277 1,406 3,139 11,186 73,671 84,857 2,047 1,940 5,651 9,638 7,100 16,738 71,922 (3,803) 68,119 84,857 - $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 2017 7,007 291 1,491 2,832 11,621 73,672 85,293 1,956 2,270 9,074 13,300 6,544 19,844 70,697 (5,248) 65,449 85,293 - The Ale Hous Operating proft Operating capital 2015 NA 62.02% 34.14% 4.40% 21.76% 3.13% 7.92% 214.08% 5.41% Sales growth rate COGS / Sales SGA / Sales Depreciation / Net PPE Cash / Sales Accts. Rec. / Sales Inventory/ Sales Net PPE / Sales Accruals / Sales Accts. Pay./ Sales 7.74% Operating Taxes Tax Rate (Taxes/EBT) Dividend policy: growth rate Dividend and debt ratios Long-term Debt / operating assets Interest rate on short-term invest. Interest Rates Interest rate on debt 30.02% NA 9.71% NA NA Asset L $ Dividend Growth Rate in 2018 2,817.78 0% 5% 10% 15% 20% 25% 30% The Ale House Projected Parameters 2016 6.66% 64.18% 32.19% 4.32% 17.34% 3.83% 8.55% 200.71% 5.58% 2017 2.02% 65.44% 29.13% 4.20% 18.71% 3.98% 7.56% 196.73% 5.22% 3 Year Average 4.34% 63.88% 31.82% 4.31% 19.27% 3.65% 8.01% 203.84% 5.40% 5.29% 6.06% 6.36% 6.00% 29.98% 0.00% 8.39% 5.0% 8.00% 29.99% 0.00% 7.70% 5.1% 8.00% 30.00% 0.00% 8.60% 5.02% 8.00% 30.00% 0.00% 9.00% 5.00% 8.00% 2018 2.00% 62.00% 29.00% 5.00% 15.00% 4.00% 7.00% 180.00% 5.00% Asset Liability Gap n Sensitivity Analysis Sales Growth Rate in 2018 -10% $ $ $ $ $ $ $ (5,111.91) (5,111.91) (5,111.91) (5,111.91) (5,111.91) (5,111.91) (5,111.91) -5.00% $ $ $ $ $ $ $ (1,807.87) (1,807.87) (1,807.87) (1,807.87) (1,807.87) (1,807.87) (1,807.87) 0% $ $ $ $ $ $ $ 1,496.16 1,496.16 1,496.16 1,496.16 1,496.16 1,496.16 1,496.16 5.00% $ $ $ $ $ $ $ 4,800.20 4,800.20 4,800.20 4,800.20 4,800.20 4,800.20 4,800.20 10% $ $ $ $ $ $ $ 8,104.24 8,104.24 8,104.24 8,104.24 8,104.24 8,104.24 8,104.24 Projected Parameters Projected Parameters Base Case 2% 62% 29% 5% 15% 4% 7% 180% 5% Your Estimates 7.00% 63.88% 31.82% 6.00% 19.27% 3.65% 8.00% 203.84% 5.40% 6% 6.36% 30% 0% 9% 5% 8% 30.00% 0.00% 8.60% 5.02% 8.00% n 2018 15.00% $ $ $ $ $ $ $ 11,408.27 11,408.27 11,408.27 11,408.27 11,408.27 11,408.27 11,408.27 20% $ $ $ $ $ $ $ 14,712.31 14,712.31 14,712.31 14,712.31 14,712.31 14,712.31 14,712.31 The Ale House Actual Income Statement ($ thousands) Net Sales Cost Of Goods Sold Selling, general & administrative Depreciation Operating profit $ $ $ $ $ Projected 2017 37,448.00 24,505.00 10,909.00 3,092.00 (1,058.00) Actual $ $ $ $ $ 2018 38,196.96 23,682.12 11,077.12 3,437.73 - Projected 2017 2018 Balance sheet ($ thousands) Cash Accounts receiveable Inventory Total operating current assets Net PP&E Total net operating assets $ $ $ $ $ $ 7,007.00 1,491.00 2,832.00 11,330.00 73,672.00 85,002.00 $ $ $ $ $ $ 5,729.54 1,527.88 2,673.79 9,931.21 68,754.53 78,685.74 Accrued expenses Accounts payable Total operating current liabilities $ $ $ 1,956.00 2,270.00 4,226.00 $ $ $ 1,909.85 2,291.82 4,201.67 Actual Actual 2015 Free Cash Flow Operating Income NOPAT Total Net Operating Assets Investment in net operating assets Free Cash Flow Growth in FCF ROIC $ (1,916.00) $ $ (1,340.89) $ $ 80,432.00 $ $ $ Actual 2016 (1,852.00) (1,296.68) 80,593.00 161.00 (1,457.68) 2017 $ $ $ $ $ (1,058.00) (740.70) 80,776.00 183.00 (923.70) -36.63% -0.92% Actual 2018 $ $ $ 74,484.07 $ (6,291.93) $ 6,291.93 -781.16% 0.00% The Ale House Income Statement Actual Net Sales Cost Of Goods Sold Selling, general & administrative Depreciation Operating profit Interest income Interest expense Taxes Dividends Additions to RE $ $ $ $ $ $ $ 2017 37,448.00 24,505.00 10,909.00 3,092.00 (1,058.00) 14.00 1,020.00 Projected 2018 $ 38,196.96 $ 23,682.12 $ 11,077.12 $ 3,437.73 $ $ 14.55 $ 1,249.44 Earnings before taxes $ (2,064.00) $ (1,234.89) $ (619.00) $ (370.47) Net income $ (1,445.00) $ (864.42) $ $ $ (1,445.00) $ (864.42) House Balance sheet Actual Cash Short term investments Accounts receiveable Inventory Total current assets Net PP&E Total assets $ $ $ $ $ $ $ 2017 7,007.00 291.00 1,491.00 2,832.00 11,621.00 73,672.00 85,293.00 Accrued expenses Accounts payable Short-term debt Total current liabilities Long-term debt Total liabilities Common stock Retained earnings Total common equity Total liabilities and equity $ $ $ $ $ $ $ $ $ $ 1,956.00 2,270.00 9,074.00 13,300.00 6,544.00 19,844.00 70,697.00 (5,248.00) 65,449.00 85,293.00 Projected 2018 $ 5,729.54 $ $ 1,527.88 $ 2,673.79 $ 9,931.21 $ 68,754.53 $ 78,685.74 $ $ $ $ $ $ $ $ $ $ 1,909.85 2,291.82 2,817.78 7,019.44 7,081.72 14,101.16 70,697.00 (6,112.42) 64,584.58 78,685.74 Balancing the Balance Sheet specified assets specified liabilities asset-liability gap discrepancy Projected 2018 $ 78,685.74 $ 75,867.96 $ 2,817.78 $ - WACC Constant growth rate Current share price Shares oustanding 8% 2% $23.00 5000 The Ale House Valuation free cash flow growth rate in free cash flow Horizon Value 2017 0 $ 2018 2019 1 2 (923.70) $ 6,291.93 $ 8,179.51 -781.16% 30% PV of FCF Horizon Value PV of FCF from 2018 - 2023 (NOT including horizon value) PV of all FCF Value of investments $ 101,786.48 $ $ $ 38,267.97 140,054.44 291.00 Total value of firm $ 140,345.44 Non operating liabilites Value of equity Number of shares (thousands) Estimated price per share, end of 2017 fiscal year $ $ 15,618.00 124,727.44 5000 $ 24.95 Stock Valuation Se $ WACC 24.95 5% 6% 7% 8% 9% 10% 11% 12% 0.5% $ $ $ $ $ $ $ $ 37.05 29.58 24.42 20.63 17.75 15.47 13.63 12.11 House 2020 2021 2022 2023 3 4 5 6 $ 9,815.41 $ 7,852.33 $ 8,637.56 $ 9,501.31 20% -20% 10% 10% $ 161,522.35 Stock Valuation Sensitivity Analysis Constant Growth Rate 1.5% 2.0% 1.0% $ $ $ $ $ $ $ $ 41.18 32.16 26.15 21.87 18.66 16.16 14.17 12.54 $ $ $ $ $ $ $ $ 46.50 35.32 28.21 23.29 19.68 16.93 14.76 13.01 $ $ $ $ $ $ $ $ 53.59 39.26 30.67 24.95 20.86 17.80 15.42 13.52 $ $ $ $ $ $ $ $ 2.5% 63.51 44.33 33.68 26.91 22.22 18.78 16.16 14.09 3.0% $ $ $ $ $ $ $ $ 78.40 51.09 37.44 29.26 23.80 19.91 16.99 14.72 3.5% $ $ $ $ $ $ $ $ 103.22 60.56 42.28 32.13 25.67 21.20 17.93 15.42 4.0% $ $ $ $ $ $ $ $ 152.85 74.76 48.73 35.72 27.92 22.72 19.00 16.22 Use your estimates Do YOUR financial projections for 2018 seem reasonable? Explain: In the sensitivity asset-gap analysis why did changing the payout ratio not change the asset-liability gap? How would you correct this if the company did start paying dividends in 2018? Use base case estimates Interpret the Asset-liability gap data table (hint: temporarily change the dividends paid in 2017 from $0 to a positive value to see how the dividends would impact the assetliability gap). Look at the firm’s historical and projected free cash flow values. What do they tell you about the firm? Interpret the ROIC. Interpret the stock valuation data table. What could you have done to make your FCF model more accurate? Would you invest in The Ale House if the actual share price was $20? Explain. Yes, my estimiation for 2018 is reasonable because I just change some date from company's coming event (Company Additional information). Average is also reasonable in my estimiation. Some times one year do worse and sometimes one year do great. Due to divident in the base year(2017) was 0, therefore for coming year by calculating (multiply 0)with base year will be 0. By correction, I will go back History data and change the base year data to the 2018 data. Sales grothe rate will significantly influence the Asset-liability gap rather than the divident. Increasing sale will increasing the cash flow. In 2017 ROIC is only around -0.92% and 2018 ROIC is 0. Due to the both year ROIC is less than WACC, it is not By caluation, the more value of the constant groth rate can give the more higher stock price. However, the more value of WACC will give the lower value of stock price. For more accurate, I may increase the componding period which change annually to semiannual or quatorly. Also, the year end should not as long as 2023 which mean I will only estimate the 2020. Yes. The estimate price is $24.95 which is lager than $20. I will invest because the Ale House's share price is undervalue.
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

I’m working on the ...


Anonymous
Really useful study material!

Studypool
4.7
Indeed
4.5
Sitejabber
4.4

Similar Content

Related Tags