help please using excel asap problem 3

Problem S: (17 points - Project Planning Excel Tools) You just landed a job as the project manager for a national research lab. All project and operations use Scrum, an agile project management technique. You are what is known as the Project Owner. This means your main contribution to the project is not the technical aspects of the project but rather you keep track of the teams progress, report the progress to upper management client, and manage the product backlog. This is a list of all of the product backlog items (PBIS that need to be completed for the project. Each project operates in "sprints" lasting 1-3 weeks and the Scrum Master is the engineer on the team that assists with daily obstacles and is your connection to the team. To keep track of all the projects you create an excel worksheet that you will need to update weekly and send to upper management. In the "Problem #" tab there is a table with a list of projects and operations that you are currently the project owner. You will need to apply the following to the table to make it a formal project management tool. All formatting should be applied to all your projects for this year. 1. Include appropriate boarders and shading for the table. There is no RIGHT way to format the table but it should be professional and easy to read. (2 points 2. In the "Security Level" column add a drop-down box. The box should allow users to select from + options. These references should be in a hidden column elsewhere in the sheet (2 points b. Confidential c. Secrete d Top Secrete 3. Dates: Any of the following which do not use a conditional formatting rule to complete the task will not receive points. Use conditional formatting to: (4 points a Public a. In the "Start Date" highlight the cell light blue if the project or operation is starting this month August). You should use conditional formatting so that this updates every month without manual intervention b. In the "Late?" column highlight the cell light green if the answer is "Yes". c. In the "Date Complete" column add data validation to make sure you enter a date between 6/16/2021 and 7/01/2022 d. In the "Date Complete" column add an input and error message. "Enter a date between 6/16/21 and 7/01/22" 4. Current Phase: Add a dropdown list to choose from the current phase of the project. (9 points) a. Product Roadmap Creation b. Prelease Planning c. Sprint Planning d. Sprint in Progress e. Spring Review f Sprint Release g. Project Close Then manually update the column so that any completed project as of today 12/11/2021 is set to "Project Close", and any project operation which has not yet started is set to "Product Roadmap Creation". All projects operations in progress should not have a current phase selected. 5. In the "Scrum Master" column add a drop-drown box which allows users to select the engineer who is the scrum master for the project operation. Names: Jeff Alvarado, Sam Ellis, Jacobe Watson, Barak Obama, Lady Gaga (2 point) 6. You are given the # of initial product backlog items PBIS) planned for the project operation as well as the number that have been completed. In the "+PBIs Left" column include a formula to calculate the # of PBIs left to complete in the project. (2 point 7. In the "Cost Difference" column include an equation to solve to the cost of the project over or under the estimated budget. Include the conditional formatting points) a Green circle: the project/operation came in or under budget b. Yellow triangle the project was less than or equal to $50,000 over budget c. Red diamond: the project was more than $50,000 over budget Sy level Number Tode 1 OPERATION ANAPUNDIT 2 PROJECT ABORTIVE CONDOMINIUM OPERATION MEGAATTRACTION 4 PROJECTENHANCED TEPID PLURALITY 5 PROIECTANT TOXICITY 6 OPERATION TLAKUINEAGE 7 PROIECT UNIREPOSITORY OPERATION BASIC RESIGNATION PROJECT AUTODEGIMATION 10 OPERATION MAINY.POUNDING 11 OPERATION AIR CASSETTE 13 PROIECT IMPISH-INACTOR 13 PROIECT CHIOPHILOSOPHER 16 PROIECT SEMILAKATION WO 15 OPERATION NOCOMMUNE 16 OPERATION SEAPPARATUS 17 OPERATION LOPEDREGULARITY 18 ROJECT ANTIDADBLDCRATE 20 OPERATION DISILLUSIONED MATVEDOM OPERATION STAROCCOU + G Hartie Due Date Current Phone Scrum Master Series Ph Completed Polecamere at 0/6/2010 30 20 7/2001 12 9/1/2021 SLU 31 11 W001 77/2001 W1001 5410 20 WIN 30.000 7/10/2021 W10/1001 5 15 15 1/21/2015 10/20/2021 $$$ 9/10/2021 11/9/2021 11/ 11.11 2/26/2005 11/25/2001 52 11/27 10.00 L 1020112/2001 7 1 10/31/2001 1900 12 22 12 1 11/21/2001 1/30/2022 11/26/2001 1/25/2013 5 JO 991 13/11/2001 9/11/10 2 12/16/2011 9/14/2011 38 SUS 12/20/2012/11/2013 $ 78 11/ 2021 16/2023 145.00 191/2022: 6/2002 21 14.00 2002/2012 No 31 FARO 2012 wa 100 W120 saat 22/1032 w2022 4 30 U R S Cost Difference un un un Master Sprint Initial PBts #PBts Completed Pls Left 20 20 31 31 28 28 27 27 5 42 42 5 34 34 5 52 52 6 74 74 7 27 22 4 75 70 3 70 61 2 59 52 1 45 36 1 37 1 82 1 26 1 85 48 63 68 Date Complete Late? 8/16/2021 Yes 9/2/2021 Yes 9/3/2021 NO 9/21/2021 Yes 10/10/2021 No 11/8/2021 No 11/25/2021 No 12/5/2021 No No No No No NO No Expected Cost Total Cost $ 1,412,999.00 $ 1,844,926 00 $ 4,142,498.00 S 3,861,679,00 $ 4,379,049 00 S 4,580,619.00 $ 1,566,083 00 5 1,521,684,00 $ 529,891.00 $ 5.771,92000 $ 2,941,824.00 5 5 272,89200 $ 1,508,576.00 5 1,04,663.00 $ 175,838.00 S 7,204474.00 $ 2,390,010,00 $ 2,216,254.00 $ 1,182,298.00 $ 4,165,333.00 $ 1,828,655.00 S251,15100 $ 4,516,393 00 $ 3,194,53700 $ 824,675.00 $ 2,372,944.00 $ 1.807,747.00 $ 4,352,972.00 OOOOOOOO 2 2 2 2 2 2 No No No No No Problem S: (17 points - Project Planning Excel Tools) You just landed a job as the project manager for a national research lab. All project and operations use Scrum, an agile project management technique. You are what is known as the Project Owner. This means your main contribution to the project is not the technical aspects of the project but rather you keep track of the teams progress, report the progress to upper management client, and manage the product backlog. This is a list of all of the product backlog items (PBIS that need to be completed for the project. Each project operates in "sprints" lasting 1-3 weeks and the Scrum Master is the engineer on the team that assists with daily obstacles and is your connection to the team. To keep track of all the projects you create an excel worksheet that you will need to update weekly and send to upper management. In the "Problem #" tab there is a table with a list of projects and operations that you are currently the project owner. You will need to apply the following to the table to make it a formal project management tool. All formatting should be applied to all your projects for this year. 1. Include appropriate boarders and shading for the table. There is no RIGHT way to format the table but it should be professional and easy to read. (2 points 2. In the "Security Level" column add a drop-down box. The box should allow users to select from + options. These references should be in a hidden column elsewhere in the sheet (2 points b. Confidential c. Secrete d Top Secrete 3. Dates: Any of the following which do not use a conditional formatting rule to complete the task will not receive points. Use conditional formatting to: (4 points a Public a. In the "Start Date" highlight the cell light blue if the project or operation is starting this month August). You should use conditional formatting so that this updates every month without manual intervention b. In the "Late?" column highlight the cell light green if the answer is "Yes". c. In the "Date Complete" column add data validation to make sure you enter a date between 6/16/2021 and 7/01/2022 d. In the "Date Complete" column add an input and error message. "Enter a date between 6/16/21 and 7/01/22" 4. Current Phase: Add a dropdown list to choose from the current phase of the project. (9 points) a. Product Roadmap Creation b. Prelease Planning c. Sprint Planning d. Sprint in Progress e. Spring Review f Sprint Release g. Project Close Then manually update the column so that any completed project as of today 12/11/2021 is set to "Project Close", and any project operation which has not yet started is set to "Product Roadmap Creation". All projects operations in progress should not have a current phase selected. 5. In the "Scrum Master" column add a drop-drown box which allows users to select the engineer who is the scrum master for the project operation. Names: Jeff Alvarado, Sam Ellis, Jacobe Watson, Barak Obama, Lady Gaga (2 point) 6. You are given the # of initial product backlog items PBIS) planned for the project operation as well as the number that have been completed. In the "+PBIs Left" column include a formula to calculate the # of PBIs left to complete in the project. (2 point 7. In the "Cost Difference" column include an equation to solve to the cost of the project over or under the estimated budget. Include the conditional formatting points) a Green circle: the project/operation came in or under budget b. Yellow triangle the project was less than or equal to $50,000 over budget c. Red diamond: the project was more than $50,000 over budget Sy level Number Tode 1 OPERATION ANAPUNDIT 2 PROJECT ABORTIVE CONDOMINIUM OPERATION MEGAATTRACTION 4 PROJECTENHANCED TEPID PLURALITY 5 PROIECTANT TOXICITY 6 OPERATION TLAKUINEAGE 7 PROIECT UNIREPOSITORY OPERATION BASIC RESIGNATION PROJECT AUTODEGIMATION 10 OPERATION MAINY.POUNDING 11 OPERATION AIR CASSETTE 13 PROIECT IMPISH-INACTOR 13 PROIECT CHIOPHILOSOPHER 16 PROIECT SEMILAKATION WO 15 OPERATION NOCOMMUNE 16 OPERATION SEAPPARATUS 17 OPERATION LOPEDREGULARITY 18 ROJECT ANTIDADBLDCRATE 20 OPERATION DISILLUSIONED MATVEDOM OPERATION STAROCCOU + G Hartie Due Date Current Phone Scrum Master Series Ph Completed Polecamere at 0/6/2010 30 20 7/2001 12 9/1/2021 SLU 31 11 W001 77/2001 W1001 5410 20 WIN 30.000 7/10/2021 W10/1001 5 15 15 1/21/2015 10/20/2021 $$$ 9/10/2021 11/9/2021 11/ 11.11 2/26/2005 11/25/2001 52 11/27 10.00 L 1020112/2001 7 1 10/31/2001 1900 12 22 12 1 11/21/2001 1/30/2022 11/26/2001 1/25/2013 5 JO 991 13/11/2001 9/11/10 2 12/16/2011 9/14/2011 38 SUS 12/20/2012/11/2013 $ 78 11/ 2021 16/2023 145.00 191/2022: 6/2002 21 14.00 2002/2012 No 31 FARO 2012 wa 100 W120 saat 22/1032 w2022 4 30 U R S Cost Difference un un un Master Sprint Initial PBts #PBts Completed Pls Left 20 20 31 31 28 28 27 27 5 42 42 5 34 34 5 52 52 6 74 74 7 27 22 4 75 70 3 70 61 2 59 52 1 45 36 1 37 1 82 1 26 1 85 48 63 68 Date Complete Late? 8/16/2021 Yes 9/2/2021 Yes 9/3/2021 NO 9/21/2021 Yes 10/10/2021 No 11/8/2021 No 11/25/2021 No 12/5/2021 No No No No No NO No Expected Cost Total Cost $ 1,412,999.00 $ 1,844,926 00 $ 4,142,498.00 S 3,861,679,00 $ 4,379,049 00 S 4,580,619.00 $ 1,566,083 00 5 1,521,684,00 $ 529,891.00 $ 5.771,92000 $ 2,941,824.00 5 5 272,89200 $ 1,508,576.00 5 1,04,663.00 $ 175,838.00 S 7,204474.00 $ 2,390,010,00 $ 2,216,254.00 $ 1,182,298.00 $ 4,165,333.00 $ 1,828,655.00 S251,15100 $ 4,516,393 00 $ 3,194,53700 $ 824,675.00 $ 2,372,944.00 $ 1.807,747.00 $ 4,352,972.00 OOOOOOOO 2 2 2 2 2 2 No No No No No