MFIN 6002 : Spreadsheet

(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. (20 points) Corporate finance.

Operating leverage reflects the degree to which fixed costs are embedded in a firm's operations. Thus, if a high percentage of a firm's costs are fixed, then the firm is said to have high operating leverage because these costs are incurred even if sales decline. High operating leverage produces a situation where a small change in sales can result in a large change in operating income. The following example compares two operational plans with different degrees of operating leverage.

Worksheet “Q1.Plan A” provides input data of a low fixed costs and high variable costs operating leverage plan (Plan A). We examine the firm's profitability under this operating plan, in different  states of the economy (probabilities are given).

The calculation of sales, operating costs, EBIT, net income (NI), and return on equity (ROE) are given in the table.

(a) (10 points) Follow the instruction and record a macro.

1.    Start recording and name the macro asQ1_recording.

2.    Select B15:B19 and name the range as “Probability” .

3.    In D20, type formula “=SUMPRODUCT(Probability,D15:D19)” . This calculates the expectation of “ Dollar Sales” using the scenario analysis.

4.    Copy D20 and paste special to E20:H20 by selecting “All Except Borders” .

5.    Change number format of D20:G20 to Currency with integer part only.

6.    Change number format of H20 to Percentage with one decimal number.

7.    Change the font of range D20:H20 to Bold.

8.    Stop recording the macro.

Place the recorded sub procedure Q1_recording in Module   Q1.

The results should look like the above table.

(b) (10 points) Worksheet “Q1.Plan B” provides input data of a high fixed costs and low variable costs operating leverage plan (Plan B). You modify the recorded macro and apply it to plan B table.

Write a sub procedure named Q1_modify that modifies the recorded macro. The sub procedure should do the followings:

should be displayed and the sub procedure is terminated immediately. Note that input values are case sensitive,e.g., “ Normal” and “NORMAL” are both invalid inputs.

If the user enters an acceptable value of Scenario, use a messagebox as below to display the

calculated Revenue. Note that in the messagebox, “normal” is the value of Scenario and “400” is the result of Revenue.

Q3. (15 points) Worksheet “Q3” stores the daily data of 4-week Treasury Bill rates from August 1, 2023 to October 31, 2023. 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.

Create two array variables Time and IntAnnual. Time is used to store the “Date” data in column A. IntAnnual stores the “ Interest Rate (Annual, Percentage)” data in column B.

1.    Create an 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.)

2.    Calculate IntDaily on each day and write the results in column C.

Q4. (15 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 variable 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.