The assessment will be based on the level of basic comprehension or understanding that you demonstrate, including your ability to apply the relevant VBA concepts

Subject:SociologyPrice: Bought3

Share With

The assessment will be based on the level of basic comprehension or understanding that you demonstrate, including your ability to apply the relevant VBA concepts.

The efficiency, elegance and clarity of the code will be credited. Please remember to:

• Use the most efficient commands and structures.
• Choose your variable names and data types carefully.
• Indent your code.
• Comment sufficiently to explain your code.

Question 1

i. Nike Inc, has reported dividends of $1.20 per share this year. These dividends are expected to grow at 5% per year (g) for the foreseeable future. Nike's future dividend payments at time t will be equal to:
D_t = D₀(1 + g)^t
D₀ = Current dividend
D_t = Future dividend at time or year t
g = Dividend growth rate
t = time or years

The total present value of a stream of future cash flows (or dividends) that last n years is given by:

PV = _t=1∑ⁿD_tω^t = D₀ x [1 - ((1 + g)/(1+i))ⁿ] x ((1+g)/(i-g))

ω^t = Discount factor = 1/(1 + i)^t

i = Discount rate

Create a subroutine that undertakes the following tasks:

1. Reads the assumptions of D₀, i, g, and n from an input range of cells on a worksheet. For Nike Inc, these will be

D₀ = 1.20
i = 10%
g = 5%
n = 20

2. Assigns these values to corresponding VBA variables.

3. Uses a loop control structure to create a table that contains the year (t), the dividends (D_t), the discount factor (ω_t) and the present value of each payment (D_tω^t) for each year from year 1 to year n.

4. Once the final year's data has been produced, the subroutine should enter a total row, labelled as such, with the sum of the total of all of the dividends and of their respective present values.

5. Format the total row, so that it is well presented.

6. Below the total row use the formula below to cross check your total present value of the future dividends paid.

PV = D₀ x [1 - ((1 + g)/(1 + i))ⁿ] x ((1+g)/(i - g))

7. Remember that the subroutine should ‘clear contents' and ‘clear formats' of the output range so that any previous data is cleared as subroutine executes.

ii. Use the above VBA exercise in 1.i. to explain why the declaration of variables within a subroutine, module or project is seen as good coding practice.

iii. Use the above VBA exercise in 1.i. to explain why careful consideration of a variable's ‘data-type' is important. With reference to the variables you have used, document which data type(s) you have used and why.

Question 2

i. Create a workbook containing VBA User Defined Functions (UDFs).

Create two UDFs that:
a. Converts Fahrenheit to Celsius
b. Converts Celsius to Fahrenheit

On a worksheet, and using your UDFs, produce a table that converts a range of temperatures, say from 0°C to 100°C in 5°C increments into °F and then back into °C.

Suggest and demonstrate an alternative method that could be used in Excel to achieve the same calculation and operation.