You have almost completed analyzing the project after incorporating uncertainty from the market, inflation, and autocorrelation in growth. To begin, run a Monte Carlo simulation with 10,000 observations of project NPV to get a feel for the parameters of this project. Use Excel’s “Data Analysis” tool to calculate the projects summary statistics, and then create a graph of the projects possible outcomes. At this point, you should have Data Analysis added onto your computer, as we used regression from that package before. You will now use the “Descriptive Statistics” and “Histogram” options. Note the mean and median values for NPV, as well as the maximum and minimum values for your sample. Also, note the distribution of your outcomes (it should look like a normal curve) as you can make inferences regarding the likelihood of profitability given that distribution.
During your first year of operations, you are approached by a local bank that wants to lease that space from you for $18,000 per year. You don’t want to do it right now, as you have already invested in the bakery and want to see whether it is well received. However, you would like to keep that option available in case the bakery performs poorly, and you would like to mitigate losses. So you and the bank’s president are negotiating a price for an option, which if you pay right now, would obligate the bank to enter into a lease agreement and locate a branch in your store. This would allow you to analyze results after the first year, and then decide whether to abandon the bakery and put the bank in instead. So after some analysis, you expect the combined market value of your delivery van and equipment at the end of each year to be as follows:
2014- 32,000
2015- 23,000
2016- 15,000
2017- 10,000
2018- 7,000
Based on these numbers, you can calculate the net salvage value of your assets and inventory at the end of each of the next five years. You have decided that at the end of each year, you will analyze whether the Net Salvage Value is higher than the present value of all the remaining future cash flows. If it is, you will abandon the project by selling off the assets and liquidating the working capital, and you will then enter into the lease agreement and receive $18,000. If the present value of future cash flows is higher, you will allow the project to continue another year, at which point you will analyze it again. For example, if you abandon at the end of 2014, you will get 2014’s OCF plus the NSV of inventory and assets. And in each of the next four years, you would get $18,000. If you don’t abandon, you will go to 2015, and then analyze whether it would be more profitable to abandon again.
You will now program that decision, and its effect on the project’s cash flows, and re-run the Monte Carlo simulation. Using your new sample with the option, calculate the histogram and descriptive statistics in order to estimate the value of this option. Submit a recommendation to me (no more than one page) as to what you feel we would need to pay the bank in order to retain this option over the next five years, and what impact this option has on the risk/return profile of the project.