You need to generate the following on the Input worksheet:

1. Calculate the current age of the client (cell B9)as always, must use Excel functions anywhere you can. Format to one decimal. The current date is given in cell B3.

2. Create a drop-down box for cell B10 that allows the agent to select the ethnicity of the client (choices are White or Other). Note that for uniformity, I have specified cells in columns O through P of the Data worksheet as your dropdown boxs input range (O4-P5, O9-P10, respectively) and cell links (R4 and R9, respectively).

3. Create a drop-down box for cell B11 that allows the agent to select the gender of the client (male/female).

4. Calculate the remaining life expectancy of the client in cell B13 using the life expectancy tables on the Data worksheet. You will need use VLOOKUP in conjunction with the dataset in cells B4:L105 (there are other ways to do this, including a function that we have not covered in class). Inside the VLOOKUP function, you will need to embed several IF statements based upon the ethnicity and gender that is input with the drop-down boxes (you can get some ideas of how to do it by reviewing your old homeworks/quizzes and my suggested answers).

5. Calculate the cost of the proposed policy amount using the insurance rates on the Data worksheet for cell B15 on the Input worksheet. Be sure to rounddown the policy amount to the nearest $10,000 of desired policy amount since that is the basic unit size for which prices are quoted. Also, if the age of the client is greater than 100, Lone Star Insurance Agency will not underwrite a life insurance policy for the individual. So, make sure your dummy client is not that old when you select Date of birth for him/her.

please what are the formulas for excel how do you type them in ?

7 Policy Amount: \begin{tabular}{c|l|l} 9 & & Age: \\ 10 & Ethnicity: 22.0 & years \\ \hline \end{tabular} 11 Gender: 7 Policy Amount: \begin{tabular}{c|l|l} 9 & & Age: \\ 10 & Ethnicity: 22.0 & years \\ \hline \end{tabular} 11 Gender