MFIN6002 Session ABCDE
Assignment 1
(Due by the end of December 23, 2022)
(100 points in total) Download Excel file “HW1.xlsm” and finish all the problem sets in it. Submit your Excel file to the “Assignment1” session on Moodle.
Q1. (15 points) Worksheet “Q1” stores the sales price and the unitsold in three market states (“Scenarios”) of a product. Follow the steps and record a macro.
1. Select cell A1 before you start recording.
2. Start recording and name the macro as ProjectRecord and store the macro in This Workbook.
3. Type “Total Sales” in the cell D1.
4. Total Sales = Price * Unit Sold. Type the formula in D2 to calculate the total sales in Normal scenario.
5. Copy cell D2 to range D3:D4.
6. Change the font of range A1:D1 to Bold.
7. Select range A1:D4 and name the range as “Data” .
8. Stop recording the macro.
Put the recorded sub procedure ProjectRecord in Module Q1.
Then write a sub procedure named ProjectRecord_mod that modify the recorded macro ProjectRecord. The sub procedure should do the followings:
1. Clear column D using Clear method.
2. Change cell D1 value to “Total Sales”, enter formula in cell D2 that calculates the sales.
3. Copy cell D2 to range D3:D4 using Copy method and specify Destination argument.
4. Change the font of range A1:D1 to Bold.
5. Change Name property of range A1:D4 and name the range as “Data” .
Q2. (15 points) In Module Q2, write a sub procedure called Project that calculates total revenue of a product under various scenarios.
(1) Variable Scenario, defined as String type. It represents the state of the market.
Variable Price is the price of the product.
Variable Unit is the number of products sold.
Variable Revenue = Price * Unit is the total revenue of the firm’s project.
Variables Price, Unit and Revenue have Variant data type.
Price and Unit under each scenario of the market is given worksheet “Q1” . Use an input box as below to assign value to Scenario
The sub is terminated immediately, if the user clicks Cancel or Exit (X) button.
Scenario variable only accepts three values: Normal, Best, and Worst. If the user enters wrong value of Scenario, the calculation is cancelled and a dialog box as below
should be displayed. You should use statement Exit Sub to terminate the sub procedure. Note that the input value is case sensitive,e.g., “normal” and “NORMAL” are both invalid inputs.
If the user enters an acceptable value of Scenario, use a message box as below to display the
calculated Revenue. Note that in the message box, “Best” is the value of Scenario and “550” is the result of Revenue.
Q3. (15 points) Worksheet “Q3” stores the daily data of 4-week Treasury Bill rates from September 1, 2022 to October 31, 2022. The interest rate is reported as percentage per annum. The link to the data source is given in the spreadsheet.
Write a sub procedure called InterestData in Module Q3.
1. Create two-dimensional array variables Time and IntAnnual. Time is used to store the dates in range A4:A44. IntAnnual stores the interest rate data in range B4:B44.
2. Create a two-dimensional array variable named IntDaily which stores the interest rate at daily frequency, calculated by the IntAnnual/252/100. (We assume 252 trading days in a year.)
3. Calculate IntDaily on each day and write the results in range C4:C44.
Q4. (20 points) Worksheet “Q4” stores the monthly adjusted close price of AAPL stock from
December 2012 to December 2014. Write a sub procedure named YearMonth in Module Q4.
Create an array variables DateData and read the dates in range A2:A26 into it.
VBA built-in functions Month and Year are used to return month and year from a given date. The online documents are listed below:
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/month- function
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/year-function
Now in the sub procedure, use these VBA functions to get month and year values from the date data in column A, and write the results in column C and D, respectively.
The outputs of the sub procedure should look like this:
Q5. (15 points) In Module Q5, write a function procedure named SumIntegerFn. It has one argument named num with Double data type. The argument value must be positive. This function returns a summation of all the positive integers up to the argument value.
If the argument is non-positive, the function returns an error message “ Please enter a positive number.” .
For example, if the user enters formula “=SumIntegerFn(4.6)” in a cell, the returned value is 1 + 2 + 3 + 4 = 10.
Q6. (20 points) In Module Q6, write a function procedure named BSCall. This function returns the Black-Scholes price of a European call written on a non-dividend paying stock. The expression of call price (C) is given by
The arguments of the function are the current stock price S0, strike price of the option K, time-to- maturity T, risk-free interest rate r, volatility of the stock returns sig. There is no requirement in the datatype of these arguments.
Note: In Excel, built-in function NORMSDIST is to return the cumulative distribution function (CDF) of a standard normal distribution, which is N(⋅) function. You can call this function in VBA to return N(d1) and N(d2). The online document of this Excel function is
https://support.microsoft.com/en-us/office/normsdist-function-463369ea-0345-445d-802a- 4ff0d6ce7cac
VBA built-in function Exp is to calculate exponential of a number.
https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/exp-function
Worksheet “Q6” gives the inputs values of a European call, calculate the call price using BSCall function in cell B10.