Could you help me with this Directions, please?

Directions

Data Validation

Use theData Validationfeatures to set up the following:

Create a drop-down list inB4to show the term years. Use the information located on the Assumption Tab. Include an Input Message:

• Title:Terms
• Input Message:Select your investment term (in years)

InD2ensure that clients enter a date. The date should be today or a future date. You can use a function in the criteria box for the current date. Be sure to include an:

• Input Message that indicates they should enter today's date
• Error Alert thatwarnsthem if they input a date beforetoday. They should be able to put in a datebeforethe current dateafterthey receive the warning.
• Be sure to include the title for both the Input Message and Error Alert.

InD4ensure that the Initial Investment is equal to or greater than the minimum deposit inB6. Be sure to include an:

• Input Message that indicates they should enter their investment amount
• Error Alert thatpreventsthe client or representative from entering a value if it isless thanthe minimum deposit. They should be provided instructions in the Error message that let's them know the reason for the error message. Once they receive the error message, they will have another opportunity to reenter their Initial Investment.
• Be sure to include the title for both the Input Message and Error Alert.

Lookup Values with Index & Match

Use the Index and Match functions together to lookup data from the Assumptions Tab. Display the following:

1. B5should show theInterest Rate (annual). This is listed under the Annual Percentage Yield on the assumptions tab. The rate will change based on the selection of Term in Years inB4.
3. B6should show the minimum deposit amount based on the selection of Term in Years inB4
5. A8should show the CD Savings Plan based on the selection of Term in Years inB4

• 1 year: Short Term Saver
• 3 year: Mid Term Saver
• 5 year: Long Term Saver

Display Annual Earnings

We will calculate the compound interest earned each year. In reality, CD compound interest is calculated daily or monthly. For simplicity of example, we will calculate the compound interest yearly. The basic formula is

1. Ending Balance = Beginning balance * (1+InterestRate)
3. The following year's beginning balance is the previous year's ending balance.
5. The Interest Earned is calculated by subtracting the Beginning Balance from the Ending Balance

INSERTING FORMULAE

1. Set the term in years to5inB4and create the following formulas

• B10The beginning balance is the Initial Investment, use a cell reference
• C10is the year 1 Beginning balance * (1+InterestRate)
• D10is the difference between the Ending Balance for year 1 and the Beginning balance for year 1.
• B11, the beginning balance is the ending balance for year 1, use a cell reference.

1. Complete the formulae for all five years.
3. InD6use theFVfunction to determine the future value of the investment. Use0for thePMTargument in the PV function, the PV argument should be negative, and set theTypeargument to1(payment at the end of period).
5. Use theSUMfunction inD5to calculate the total interest earned over all five years.

AutoSave . Off InvestmentCalculator_DataValidation_Mulyar.xisx O Search Silvia Mulyar SM X File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments La From Text/CSV To Recent Sources Queries & Connections > Clear 100 Group Get Le From Web [a Existing Connections Refresh Properties Reapply 2B Ungroup -= Stocks Data From Table/Range Geography Sort Filter Text to What-If Forecast All ~ Edit Links Advanced Columns EO Analysis Sheet Subtotal Get & Transform Data Queries & Connections Data Types Sort & Filter Data Tools Forecast Outline A1 X Acme Bank CD Calculator A B C D E F G H K M N O P Acme Bank CD Calculator 2 Client Name Date 3/21/2021 W 4 Term in Years Initial Investment Interest Rate (annual) #N/A Total Interest Earned Minimum Deposit CD Future Value 7 8 CD Investment Package Annual Projection 9 Beginning Balance Ending Balance Interest Earned 10 11 12 13 14 16 17 18 20 21 22 23 24 Documentation Sheet CD Investment Calculator Assumption + Ready + 100% LO O 9 W X ENG 9:54 PM 3/21/2021AutoSave . Off InvestmentCalculator_DataValidation_Mulyar.xIsx O Search Silvia Mulyar SM X File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments La From Text/CSV To Recent Sources Queries & Connections Clear Group > Le From Web -= Get [a Existing Connections Refresh Properties 2B Ungroup Stocks Geography Sort Filter Reapply Text to What-If Forecast Data From Table/Range All Edit Links Advanced Columns EO Analysis Sheet Subtotal Get & Transform Data Queries & Connections Data Types Sort & Filter Data Tools Forecast Outline C2 X Y 0.7% B D E F G H K M N O P Q R S T 1 CD Savings Plan Term Annual Percentage Yield Minimum Balance Short Term Saver 1 0.70% 500 3 Mid Term Saver 3 1.10% 750 4 Long Term Saver 5 1.30% 1000 IO UT 10 11 20 21 22 23 24 25 26 Documentation Sheet | CD Investment Calculator Assumption + Ready EO Average: 1.03% Count: 3 Sum: 3.10% + 100% O SP W x ENG 9:54 PM 3/21/2021