Modify the Workbook to include all the following items/specifications: • Workbook should consist of five (5) main sheets and a sheet for drop-down options. • You will modify the excel spreadsheet provided to have 5 sheets plus a Drop-down sheet. • The 5 sheets should have the following names: o Player Roster o Contact Information o Uniform Sizes o Medical Information o Parents Information • (5 points) Grid Lines – Each cell should be formatted with outline and inside borders. Sheet Contents & Requirements • (45 points) Player Roster o (5 Points) Replace the title CISA 3358 - Assignment 2 on sheet 1 with San Antonio Soccer Sisters & Brothers. Change the font size to 16 and bold. o (5 points) Merge and Center cells A1 – K1 & Merge and Center cells A3 with B3. o (5 points) Insert a Column between first name and age. Title the column Gender. Create a dropdown with a selection for Male, Female, prefer not to disclose. o (5 points) Create a column titled Position. Create a dropdown box for the Position column that contains the following options: o Goalie/Goalkeeper o Midfielder o Defender CISA 3358 MIS, Spring 2021, Microsoft Excel – Assignment 2, Ms. Carmeshia L. Miller Student Name: o Forward o CenterBack o Sweeper o Winger o (5 points) Create a column and title it Club Dues. Populate the club dues. The dues for real people are $25 and the dues for fake people or cartoon characters are $45. o (5 points) Format the column to only accept Currency values. Set it for two (2) decimal places. o (5 points) Configure the club dues column to have a total at the end. o (5 points) Insert Rows - Insert the following players to the players roster and all other sheets. Make sure all names are in alphabetical order. o OliviaPope o Ronald McDonald o RogerNelson o RogerRabbit o LorenaBobbitt o Boo Boo Da Fool o ChristianKeyes • (20 points) Contact Information o ThefirsttwocolumnsofeachsheetshouldmirrorcolumnsAandBonthePlayerRoster sheet. o Place a merged and centered title in A1 – K1, title it Contact Information. Font size should be 16 and bold. o Each column header/title should be in bold, 14 pt., Baskerville Old Face font. o Thefirstcolumnshouldremainfrozen. o Modify each column on all sheets so that all content can be seen. Use AutoFit Column Width. o Createfakeaddressesforallplayers.Eachplayer’sstateshouldbedifferent. o Create a separate column for street number and name, City, State, and Zip Code. Format all Zip code cells for zip code input only and validate the data so that the criteria allows text lengths equal to 5. o Create an Input Message and Error Alert with the following information: o Input Message Title: Enter Valid Zip Code o Input Message: Please enter a valid 5-digit zip code. o ErrorAlertStyle:Stop o ErrorAlertTitle:ZipCodeError o ErrorMessage:Pleaseenteravalid5-digitzipcode. o Create a dropdown and include the abbreviations for all states. CISA 3358 MIS, Spring 2021, Microsoft Excel – Assignment 2, Ms. Carmeshia L. Miller Student Name: o Create a separate column for a cell phone number. Format the cell phone column for telephone input only. o Format all phone number cells for zip telephone input only and validate the data so that the criteria allow text lengths equal to 10. o Create an Input Message and Error Alert with the following information: o Input Message Title: Enter Valid Phone Number o InputMessage:Pleaseenteravalid10-digitphonenumber. o ErrorAlertStyle:Warning o ErrorAlertTitle:PhoneNumberCodeError o ErrorMessage:Pleaseenteravalid10-digitphonenumber. • (10 points) Uniform Sizes o o o o o o o o • • • • • • • The first two columns of each sheet should mirror columns A and B on the Player Roster sheet. Place a merged and centered title in A1 – K1, title it Uniforms. Font size should be 16 and bold. Each column header/title should be in bold, 14 pt., Baskerville Old Face font. Thefirstcolumnshouldremainfrozen. Modify each column on all sheets so that all content can be seen. Use AutoFit Column Width. Input uniform and shoe sizes for each player. Shoe size column should only allow for 2-digit numbers. Enter fictitious data. There should be a shirt column, shorts column, and shoe size column. Create a separate dropdown box for the shirt and shorts size column that contains the following options: Extra Small Small Medium Large Extra Large XX-Large XXX-Large • (15 points) Medical Information o The first two columns of each sheet should mirror columns A and B on the Player Roster sheet. o Place a merged and centered title in A1 – K1, title it Medical Information. Font size should be 16 and bold. o Each column header/title should be in bold, 14 pt., Baskerville Old Face font. o Thefirstcolumnshouldremainfrozen. o Modify each column on all sheets so that all content can be seen. Use AutoFit Column Width. CISA 3358 MIS, Spring 2021, Microsoft Excel – Assignment 2, Ms. Carmeshia L. Miller Student Name: o Include a Date of Birth (DOB) column formatted to only accept dates. o FormatDOBinputtovalidatethedatasothatitonlyallowscriteriaforDOBsbetween January 1st, 1996 and January 1st, 2010. o Create an Input Message and Error Alert with the following information: o InputMessageTitle:EnterValidDateofBirth o InputMessage:PleaseenteravalidDOBwithintheproperdaterange. o ErrorAlertStyle:Stop o ErrorAlertTitle:DateofBirthCodeError o ErrorMessage:PleaseenteraDOBwithintheallowablerange. o Include a blood type column. Format the blood type column as a dropdown with all the legitimate blood types below: • A RhD positive (A+) • A RhD negative (A-) • B RhD positive (B+) • B RhD negative (B-) • O RhD positive (O+) • O RhD negative (O-) • AB RhD positive (AB+) • AB RhD negative (AB-) • (10 points) Parent’s Information o The first two columns of each sheet should mirror columns A and B on the Player Roster sheet. o Place a merged and centered title in A1 – K1, title it Parent’s Information. Font size should be 16 and bold. o Each column header/title should be in bold, 14 pt., Baskerville Old Face font. o The first column should remain frozen. o Modify each column on all sheets so that all content can be seen. Use AutoFit Column Width. o Create two columns with each players’ mother’s full name. o Createaseparatecolumnforthemother’scellphonenumber.Formatthecellphonecolumn identical to the way it is in Contact Information above. o Create two columns with each players’ father’s full name. o Createaseparatecolumnforthefather’scellphonenumber.Formatthecellphonecolumn identical to the way it is in Contact Information above.