IF1201 Developing Financial and Utility Tools Using VBA in Excel Assignment Brief

Assignment Guidelines

The mark that you score on this coursework constitutes 70% of the final assessment for this module. This is an individual assignment.

Assessment

This assignment is marked out of 100 marks. The assessment will be based on the level of basic comprehension and coding skills 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.
• Present carefully and indent your code.
• Avoid overusing Worksheet Function calls to Excel built-in functions (the less you use them the better).
• Comment sufficiently throughout to explain your code.

Questions

Make the necessary assumptions to complete your VBA models in a clear and easy to follow structure. When documenting your models, assume that you are in a professional finance role such as investment analyst, portfolio manager or fund manager and that you are introducing a new approach to analysing your client’s reports by using VBA programming. You want to persuade them to appreciate the advantages of VBA modelling.

Question 1

• Create and document a model to produce an asset depreciation table for a fixed asset using both the SLN (Straight line depreciation method) and DDB (Double declining balance depreciation method) functions within VBA. Use dialog boxes to get the user inputs. The program should validate the user input for reasonableness and ask the user to modify any input that is not appropriate. For output, use a predesigned worksheet with labels, table headings, formatting and so on.
• Explain and demonstrate an alternative method which you could have used to solve the problem in 1.i. instead of using the SLN and DDB functions in VBA.
• Use the above VBA exercise in 1.i. and 1.ii. to compare VBA modelling and Excel modelling. (5 marks)
  Question Total: 35 marks

Question 2

• Develop and document a VBA driven multicurrency converter for various currencies. A multicurrency converter allows the user to query exchange rates across different currencies. Prepare a spreadsheet that stores a table of exchange rates across at least four currencies. It is assumed that the user can complete this table before the subroutine runs.
• Write a subroutine with 4 arguments (e.g. source currency, destination currency, exchange rates table range, output position range) that converts the given source currency into the destination currency and outputs all three values to the output range. The macro should validate (check) the inputs and make sure that the output range is empty.
• Explain and demonstrate 2 methods that can be used to ‘call’ the VBA subroutine defined in part 2.i.
• Create an interactive program that collects the relevant user inputs through Excel InputBox methods. Thus, at run time, the user is first asked to select (input) the exchange rate table range, followed by the name of source and destination currencies. All user inputs should be validated by the program and, thereafter (if all good), it should call with the given user input the macro from 2.i. to complete the conversion and output the values to the spreadsheet.
  Question Total: 35 marks

Question 3

• Design an interactive macro that prompts the user to input a date of birth (in any format). Then the macro determines and displays the weekday of the given input date. Next, the program should ask the user whether wishes to continue (i.e. enter another date) by making use of a MsgBox with only Yes and No buttons and a question mark icon. Thus, the program should be executed in a loop until the user presses the Cancel button of the InputBox or the No button of the MsgBox with the query. In addition, in case of a wrong input (i.e. that
• cannot be transformed into a date), the program should display a simple error message with an exclamation mark icon. (15 marks)
• Suggest and demonstrate an alternative method that could be used in VBA to achieve the same looping program flow. (5 marks)
  Question Total: 20 marks