401k DSS Development

B3: up to 4% (company contribution)

Data >> Validation >> Settings tab >> Decimal >> “less than or equal to” from the Data list arrow >> Type “0.04” in the Maximum text box

Input message tab (make sure the check box is checked) >> “Title” text box >> Type “Valid Data” >> “Input Message” text box >> Type “Percent of salary invested by employer cannot exceed 4%.”

Error Alert tab (make sure the check box is checked) >> Style list arrow >> Stop >> type “Invalid Data” in the Title text box >> type “You entered a value above 4%. A Valid percentage is 4% or less” in the Error message text box

B9: from 0% to 20% (employee contribution)

Data >> Validation >> Settings tab >> Decimal >> “less than or equal” to from the Data list arrow >> Type “0.2 (employee contribution)” in the Maximum text box.

Input message tab (make sure the check box is checked) >> Title text box >> Type “Valid Data” >> Input Message text box >> Type “Percent of salary invested by employee cannot exceed 20%.”

Error Alert tab (make sure the check box is checked) >> Style list arrow >> Stop >> type “Invalid Data” in the Title text box >> type “You entered a value above 20%. A Valid percentage is 20% or less” in the Error message text box.

E8: Insert >> Function >> select Logical >> IF

type “Invested>MaxMatch” in the Logical_test text box
type “Salary*MaxMatch/12” in the Value_if_true text box
type “Salary*Invested/12” in the Value_if_false text box

E13: Insert >> Function >> select Financial >> FV

type “Return/12” in the Rate(interest rate per period) text box
type “D13*12” in the Nper(number of periods) text box
type “TotContribution” in the Pmt(payments made each period) text box

Recording a Macro Action

Make cell A1 the active cell
Tools >> Macro >> Record New Macro >> type “ClearInputs” in the Macro name box >> select “This Workbook” in the Store macro in list box >> assign a “m” in the Shortcut key box >> click OK
Select the range B3:B10 >> press the delete key >> click the Stop Recording >> Save