Assignment 3. Financial Calculations and Data Table Models
Overview: Build a workbook to write an Excel user defined function and three unique Data Table examples. The ideas, models and design of this workbook project must be 100% your own as discussed in class. Collaboration with others is strictly prohibited. Begin by downloading the Assignment3Starter workbook. Create the PVGOFA user defined function then use the three sheets supplied to complete the one-, two-, and three-way data tables described below. Unless otherwise instructed, be sure not to copy anything electronically from another workbook, including your own, the instructor’s, or someone else’s into the starter workbook. If you completed a similar assignment in a previous semester do not copy from it and paste it into the starter book. Rather, use the same examples and formulas by entering them without using move/copy/paste type commands.
1. Open the Assignment3Starter.xlsm workbook and complete the instructions given in class to copy the User Defined Functions into the workbook. Apply the steps shown in lecture to record the keystroke macro.
2. Now create the User Defined Function in the starter workbook to calculate the present value of a growing ordinary finite annuity named PVGOFA. The arguments should be C, g, r, and n (in that order). See page 18 of your text, the Chapter 1 workbook, and the class lecture notes if you need help with formulas. The function should be capable of computing a value for any reasonable set of inputs, including the special case where r = g. (Hint: If r is not equal to g then the book formula used in class works. If r = g then you need to derive a different but simple formula to calculate PVGOFA. For help with similar VBA code see the ADDING3 function in the Example Function.xlsm workbook.
For help on creating functions see the textbook, Chapters 0 and 36, and/or the web. Example Function.xlsm shows how to use If-Then-Else in VBA code. Another useful site: http://www.wikihow.com/Create-a-User-Defined-Function-in-Microsoft-Excel
Document your PVGOFA function within the VBA Module copiously and accurately with comments to explain what the function does, the definition of each input parameter, and what key sections of the VBA code accomplish.
3. Construct and explain an example that answers a real-world problem using a one-way Data Table incorporating the new PVGOFA function as part of the model. The applied example should be unique and one of your choosing to include both a model and a Data Table. The values calculated in the body of the Table (the Table array functions) should cover a 10 by 3 array and reflect different parameter input values. The Table should be on the first sheet named One-Way and contain three output variables (in columns). Use a text box to describe in paragraph form the situation in which your model could be used and what question(s) it answers. Precisely define each variable used in your example. Be sure the documentation is accurate and sufficient to unequivocally explain your example. A typical finance student should easily be able to see how and when to use it with a good understanding of the meaning of the inputs and outputs. Use good programming conventions discussed in class where possible. Documentation and formatting will be a significant part of the grading of this assignment. Use Conditional Formatting to highlight what you consider to be key results in your table. The figure below is an example of a well thought out solution. (Please do not use a similar example or one related to salaries!) Ideally, your example should have the same general form as the one below with three panels: left is the model, middle is the data table and a description of the scenario, and right is an explanation of the data table results. Each of the three models in this assignment should be uniquely different examples answering very different real-world questions.
4. Build a unique applied model using a two-way Data Table to demonstrate a concept or relationship you studied in a previous course. This model should not involve the PVGOFA function, an abstract financial math relationship such as PVIF, FVIF, PVIFA, etc., or other similar example used in class so far. The model should help answer a someone’s real-world question as in the above example. The Table function should cover a 10 by 10 array on the second sheet named Two-Way. Use Conditional Formatting to highlight important results in the table. Document/describe/explain the model as discussed above for the One-Way sheet.
5. Similar to the previous question, build a new (unlike One-Way and Two-Way) unique applied model using a three-way Data Table to demonstrate a concept or relationship you learned in a previous course. Again, this model should not involve the PVGOFA function, an abstract financial math relationship such as PVIF, FVIF, PVIFA, etc., or other similar example used in class so far. This example and model should be completely different from your first two. The Table function should cover a 10 by 10 array on the third sheet named Three-Way. Document/describe/explain the model as discussed above for the One-Way sheet and use Conditional Formatting to highlight important table results.
6. Be sure to document (label and carefully explain) the models on each sheet and the special function. Use blue font for all input cells (cells whose values the user may change and still produce correct output values). If you have documented the sheets adequately, you should expect any classmate to be able to use and interpret the models. Each of the three tables should use alternate row shading as in the above example.
7. When you are done with all previous steps copy the Certification sheet from your first assignment into the workbook as the last sheet then complete it. Be sure to compress both pictures on that sheet. Just before saving your final version select cell A1 in each sheet, resize each sheet to fit nicely on your monitor, then select cell A1 in the One-Way sheet.
8. Be sure that you have named the workbook with your class number and last name (ex. 004James.xlsm). Upload the file in Blackboard – ONE submission please! Note that late assignments may be accepted with a significant grade penalty. Grading will reflect how well you followed instructions and achieved each task described above and in lecture. Good luck!
9. Grading of this assignment will consider:
• Explanation of each model with description of a real-world scenario and explanation of its practical significance
• Degree and accuracy of documentation of all sheets and functions
• Uniqueness and complexity of Table examples
• Accuracy of calculations
• Formatting of sheets (they should be clear, neat, readable and follow conventions discussed in class)
• Use of good grammar free from typographical errors