Case $3$ Financial Options A put option in finance allows you to sell a share of stock at a given price in the future. There are different types of put options. A European put option allows you to sell a share of stock at a given price, called the exercise price, at a particular point in time after the purchase of the option. For example, suppose you purchase a six$-$month European put option for a share of stock with an exercise price of $\backslash (\backslash$$ $26\backslash ).$ If six months later, the stock price per share is $\backslash (\backslash$$ $26\backslash )$ or more, the option has no value. If in six months the stock price is lower than $\backslash (\backslash$$ $26\backslash )$ per share, then you can purchase the stock and immediately sell it at the higher exercise price of $\backslash (\backslash$$ $26\backslash ).$ If the price per share in six months is $\backslash (\backslash$$ $22\mathrm{.}50\backslash ),$ you can purchase a share of the stock for $\backslash (\backslash$$ $22\mathrm{.}50\backslash )$ and then use the put option to immediately sell the share for $\backslash (\backslash$$ $26\backslash ).$ Your profit would be the difference, $\backslash (\backslash$$ $26-\backslash$$ $22\mathrm{.}50=\backslash$$ $3\mathrm{.}50\backslash )$ per share, less the cost of the option. If you paid $\backslash (\backslash$$ $1\mathrm{.}00\backslash )$ per put option, then your profit would be $\backslash (\backslash$$ $3\mathrm{.}50-\backslash$$ $1\mathrm{.}00=\backslash$$ $2\mathrm{.}50\backslash )$ per share. Managerial Report $1.$ Build a $($spreadsheet$)$ model to calculate the profit of this European put option. $(20\backslash \%)($Hint: Follow the above to create the parameters and the formulas for the model. It is recommended to have the same cell positions for all numbers.$)2.$ Construct a data table that shows the profit per share for a share price in six months between $\backslash (\backslash$$ $10\backslash )$ and $\backslash (\backslash$$ $30\backslash )$ per share in increments of $\backslash (\backslash$$ $1\mathrm{.}00\backslash ).(25\backslash \%)($Hint: Please use Excel "Data Table" function.$)$ The point of purchasing a European option is to limit the risk of a decrease in the pershare price of the stock. Suppose you purchased $200$ shares of the stock at $\backslash (\backslash$$ $28\backslash )$ per share and $75$ six$-$month European put options with an exercise price of $\backslash (\backslash$$ $26\backslash ).$ Each put option costs $\backslash (\backslash$$ $1\backslash ).$

$3.$ Build a $($spreadsheet$)$ model to show the value of the portfolio with options and without options. $(20\backslash \%)($Hint: Follow the above to create the parameters and the formulas for the model. It is recommended to have the same cell positions for all numbers.$)$

$4.$ Use data tables to shows the value of the portfolio with options and without options for a share price in six months between $\backslash (\backslash$$ $15\backslash )$ and $\backslash (\backslash$$ $35\backslash )$ per share in increments of $\backslash$$$1\mathrm{.}00.(25\backslash \%)($Hint: Please use Excel "Data Table" function to create two tables.$)$

$5.$ Discuss the portfolio profit with and without the European put options. Which one $($with or without$)$ is more profitable? $(\backslash (10\backslash \%\backslash ))$