In addition to spinners and scroll bars, there are numerous other controls in Excel. For this assignment, you need to build a Black-Scholes Option Pricing Model spreadsheet using several of these controls.
a. Buttons are always used in sets. Using buttons permits you to check an option, and the spreadsheet will use that input. In this case, you need to create two buttons, one for a call option and one for a put option. When using the spreadsheet, if you click the call option, the spreadsheet will calculate a call price, and if you click the put option, it will calculate the price of a put. Notice on the next spreadsheet that cell B20 is empty. This cell should change names. The names should be “Call option price” and “Put option price.” In the price cell, only the price for the call option or put option is displayed depending on which button is selected. For the button, use the button under Form Controls.
b. A Combo Box uses a drop-down menu with values entered by the spreadsheet developer. One advantage of a Combo Box is that the user can choose values from the drop-down menu or enter another value if they choose. In this case, you want to create a Combo Box for the stock price and a separate Combo Box for the strike price. On the right-hand side of the spreadsheet, we have values for the drop-down menu. These values should be created in an array before the Combo Box is inserted. To create an ActiveX Combo Box, go to Developer, Insert, and select Combo Box from the ActiveX Controls menu. After you draw the Combo Box, right-click on the box, select Properties, and enter the LinkedCell, which is the cell where you want the output displayed, and the ListFillRange, which is the range that contains the list of values you want displayed in the drop-down menu.
c. In contrast to a Combo Box, a List Box permits the user to scroll through a list of possible values that are predetermined by the spreadsheet developer. No other values can be entered. You need to create a List Box for the interest rate using the interest rate array on the right-hand side of the spreadsheet. To insert a List Box, go to Developer, Insert, and choose the List Box from the ActiveX Controls. To enter the linked cell and array of values, you will need to go to the Properties for the List Box. To do this, right-click on the List Box and select Properties from the menu. We should note here that to edit both the Combo Box and List box you will need to make sure that Design Mode is checked on the Developer tab.