Declare in the program module a number of public

Assignment Help Visual Basic Programming

Reference no: EM132957847

Retirement Savings Plan (RSP) Model VBA/Excel Program

1. Introduction
Consider a simple savings plan (RSP) for a working age individual to accumulate a net target (after tax) amount of retirement money. The individual invests a fix amount of (tax free) lump sum into a retirement fund at the start and in addition will make increasing annual instalments (at each anniversary) until the retirement age. The savings fund is expected to return annual dividends in the form of interest and capital gains as a percentage of the current holding at the start of each year, which are added to the fund at each anniversary. All the returns from the retirement fund will be taxable at the time of earnings. Thus, the income tax will be payable annually on the amount of interest earned and the capital gains at the time of maturity when it is expected that the entire holding will be cashed in. Other than the income tax payments, there will be no other withdrawals from the fund during the entire RSP term.

Coursework Tasks

You therefore need to build an Excel macro-enabled workbook with a VBA User Form (UF) designed such that at the start up (e.g. UserForm_Initialize) it reads in the parameter values given in the relevant input WS cells3 and then allows the user to make changes within the RSP parameter constraints given in section 2. The UF should have a minimum of three command buttons (CB), corresponding to ‘CFT', ‘Done' and ‘Cancel' actions, as described below:

- The 'CFT' action CB should check the parameters of the RSP and, if necessary, alert the user of any potential errors. Then, if all the input parameters are given and are correct, it executes the main RSP macro to output the CFT together with the current parameters to a NEW worksheet.

- The 'Done' action CB should be disabled until at least one complete run of the CFT CB action (i.e. with confirmed parameters and output of CFT in a new worksheet). Thus, following a successful run of the CFT the CB should be enabled. The CB action should display a final (brief) summary information about the last executed RSP (e.g. amount of CGT, current CFT worksheet name, or anything else you find useful) and show some closing/goodbye message to the user and exit the UF program.

- The ‘Cancel' CB action should request a confirmation from the user that indeed wishes to cancel the program. Then, in case of a ‘Yes' response, it resets the (input) WS parameter values to their last correct settings used for the CFT (i.e. either the start-up values or those used in the last CFT run) and exits the UF. Alternatively, in the case of a ‘No' response, it returns to the current (active) UF and waits for further user inputs.

Furthermore, the UF should satisfy the following (minimum) specifications:

1. Declare in the program module a number of Public (i.e. global) variables corresponding to the main RSP parameters, which will be used as the current values at CFT execution time.

2. In addition, declare in the program module a number of Public (i.e. global) variables corresponding to the main RSP parameters, which will be used as temporary values in-between CFT runs (i.e. a new set of variables that act as copies of the current RSP parameters, which are used at Cancel). Thus, after every successful CFT run these temporary variables will be overwritten by the current CFT variables.

3. Include on the UF a label control with caption made up by the concatenated text of:
"First Instalment: £" & R
for example, resulting: First Instalment: £2,500.70
where R is the re-calculated first instalment amount (rounded down to 2 decimal places), respectively. Thus, at the UF start up (e.g. Initialize event) the caption could be based on the current input values (i.e. calculated) or display "unknown" for missing parameters. However, during run time of the UF, any changes of the parameter controls should trigger the recalculation of the ?? value and, in turn, the re-evaluation of the caption text of this label.4
Finally, note that it would look good to apply greater font settings to this label control in order to emphasize the return value to the user.

4. All the other parameters (i.e. controls) on the UF should be linked to their corresponding cells in the input WS (e.g. as located in the input WS).
5. There should be at least 3 parameters that have multiple controls on the UF that are linked up (e.g. spin button and textbox, or spin button and scroll bar, etc).

The UF should be designed such that it is aesthetically appealing (i.e. well-proportioned and ordered layout) and also it is easily understandable by a user. Additional good/helpful functionalities (like tab order, pointer tips, extra error traps, help button, etc.) might attract some bonus marks.

Attachment:- Retirement Savings Plan.rar

Reference no: EM132957847

Questions Cloud

Implementing human resources strategies or solutions : How can Amazon improve the low retention strategy by implementing Human Resources strategies or solutions?

Focus groups and other forms of data collection : Question: Read the assigned chapters and the lecture materials for module 5 then answer the following questions:

What benefits do you see from a peer group experience : What benefits do you see from a peer group experience for social workers at work? Find an assessment on assessing outcomes in a group setting or evaluating the

What are the trade-offs involved in the decision : What are the trade-offs involved in the decision of how much inventory the firm should carry? In what way does the cash manager face a similar trade-off

Declare in the program module a number of public : Declare in the program module a number of Public (i.e. global) variables corresponding to the main RSP parameters, which will be used as the current values

Succession management model : How can Ford integrate diversity and inclusion into its succession management model? Kindly provide the brief answer.

Balance between meeting professional expectations : While there are definitely expected conventions for writing resumes, you will want to strike a balance between meeting professional expectations and providing n

What is Sharp gain from the distribution : A captial loss of $10,000. Sharp received a cash distribution of $40,000 on 11/1/x1. What is Sharp's gain from the distribution

Understanding of union membership today in us : Question 1: Labor Unions 1a) Summarize your understanding of union membership today in the US. What is the current status of union membership? Share some statis

User Account

All Pages