PLEASE TAKE TIME AND ANSWER IT CORRECTLY USE EXCEL IF YOU CAN TO SHOW CALCULATIONS Let's say you are the CEO of a Twitter company that works in the film business. You're thinking of starting a new project that involves writing a script from scratch or buying one. A completed script might run into the millions of dollars and result in a real production. Still, a vast percentage of scripts are dropped. Indeed, Twitter is aware that just a select number scripts will show promise ahead of time, but they are unsure of which ones. To find a few good scripts, they therefore cast a broad net and commissioned numerous of them. Furthermore, Twitter needs to be brutal when it comes to bad scripts because the costs involved are so small compared to the enormous losses that result from making a bad movie.

A few fortunate scripts will then proceed to production, where tens of millions of dollars may be spent. However, the studios are equally brutal in this situation, based on the film's box office performance. $5 million is required for the beginning development of a script and must be paid at date zero. There are three conceivable consequences in the event that this investment is made. Based on previous script developments that were comparable to this one, the probability of each of the scenarios occurring has been assessed. More specifically, there is a 10% chance of finding a medium script, a 5% chance of developing a good script, and an 85% chance that the produced script is too bad to proceed. If the script development process is successful (that is, if you have created a good or medium-length script), you may choose to invest at the outset of the $50 million film's production. Following production, you ought to use the major media to promote the film.

These $70 million worth of advertising expenses must be paid for by date three. The box office earnings are still unknown, though. If the script was successful, you should expect high revenue at the beginning on date 4, which will be equal to an annual FCF of $63 million for perpetuity (based on an estimated 75% probability). Alternatively, you should expect low box office receipts (with a 25% probability) that will only amount to an annual FCF of $10.5 million for perpetuity beginning on date 4. Or your script was a medium one and then you will get high box office revenues starting at date 4, that consists in a yearly FCF of $13.5 million for perpetuity (with an estimated probability of 20%) or you will get low box office revenues (with a 80% probability) that only consist in a yearly FCF of $10.5 million for perpetuity starting at date 4. You have estimated that the cost of capital for this project is 15% and that it will remain constant over all the period. As a consequence, you have the following five potential outcomes if you invest in the project:

Either the developed script is too bad to be continued, Or you have developed a good script that leads to high box office revenues, Or you have developed a good script that leads to low box office revenues, Or you have developed a medium script that leads to high box office revenues, Or you have developed a medium script that leads to low box office revenues. You now have to answer to the following questions.

Questions : 1- Run a sensitivity analysis of the value of the real options for this project with respect to the probability that the developed script is too bad to be continued (this may also impact the probability that development of the script is successful, i.e., that you have developed a medium or a good script). 2- Run Monte Carlo simulations to analyze the distribution of the value of the real options for this project w.r.t. the probability that the developed script is too bad to be continued assuming that if follows a Normal Distribution with mean 0.5 and standard deviation 0.05: N(0.5,0.05). Hint: a probability should belong to [0,1]