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 4" 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) a. Public 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: (+ points) 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. In the "Late?" column highlight the cell light green if the answer is "Yes". In the "Date Complete" column add data validation to make sure you enter a date between 6/16/2021 and 7/01/2022. b. c. 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. (3 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 (1 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 (2 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 Number 1 2 7 B 20 21 22 Tack OPERATION ANAPUNDIT PROJECT ABORTIVE-CONDOMINIUM OPERATION MEGAATTRACTION-02 PROJECT ENHANCED-TEPID-PLURALITY 4 5 PROJECT ANTETOXICITY 6 OPERATION FLAKY-LINEAGE 7 PROJECT UNIREPOSITORY 8 OPERATION BASIC RESIGNATION I 9 PROJECT AUTODEGENERATION 10 OPERATION RAINY POUNDING 11 OPERATION AIR-CASSETTE 12 PROIECT IMPISH-INACTION- 13 PROJECT CRYPTOPHILOSOPHER 14 PROJECT SEMIRELAXATION WO 15 OPERATION COMMUNE 16 OPERATION SEMIAPPARATUS 17 OPERATION LOPSIDED-REGULARITY 18 ROJECT ANTIROADBLOCK-MATE OPERATION DISILLUSIONED MARTYRDOM 19 20 OPERATION STARY-BROCCOL Security Level Start Date G Due Date Current Phase Scrum Maider Sprint #nial PPh Completed #PB Leftate Complete Late 8/11/201 0/16/2021 7/3/2021 9/1/2021 7/7/2021 5/5/2021 7/18/2021 9/16/201 3/21/2021 10/20/2021 9/10/2021 11/9/2021 9/26/2021 11/25/2021 10/7/2021 12/6/2021 10/31/2001 12/30/2021 11/21/2021 1/20/2022 11/26/2021 1/25/2022 tappion 12/15/2021 2/13/2022 12/16/2021 2/14/2022 P 12/20/2021 2/14/2022 1/14/2022 /15/2022 1/25/2022 3/24/2022 3/24/2022 4/25/2012 3/4/2012 5/3/2012 5/9/2022 1/10/2012 4/22/2022 6/25/2022 S 3 S 3 s 3 S $ a 12 PT 4 $ H 4 20 31 28 w 31 47 42 34 34 32 12 14 27 25 m 30 35 55 P 45 7 37 2 82 3 29 85 85 48 45 60 18 20 31 38 22 27 a B 24 2 33 74 174 22 M 7090 01 M 32 36 19 M D D n 0 9 D 4/1/201 W/2001 Y VIONIN Yes 34/10/201 11/8/201 11/25/201 12/5/201 P PRO T P Ne N M 20 NO N Sept NA 20 No NO NE NO No No Me Eted Co $141299900L $41424005 ILMA $4.379,043004 Total Co $155 15 $$$ $1341334.00 $1372 OF LYON SOLO $200 $175/410005 UN $2,900100 12 maanings $221,194.00 1381200 $3,300 15.333 $1.574.455.00 $ 75,19 34,506,30300 $LIMAT 1824.675.00 $23724400 1.24700 Master Sprint# # Initial PBIS #PBIs Completed #PBIS Left Date Complete 20 20 31 28 - 5 5 5 6 7 4 3 2 1 1 1 1 1 27 42 34 52 74 N 27 75 70 59 45 37 225435 82 26 85 48 63 68 21272 31 28 42 34 52 74 22 70 61 52 36 0 0 oooooo 0 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 No No No No No No Expected Cost Total Cost $ 1,412,999.00 5 1,844,926 00 $ 4,142,498.00 $3,861,679.00 $ 4,379,049 00 $ 4,580,619.00 $ 1,566,083.00 $ 1,521,684.00 $ 529,891.00 $5,771,920.00 $ 2,941,824.00 $ 5,272,892.00 $ 1,508,576.00 $ 1,094,663.00 $ 175,838.00 $ 7,204.474.00 $ 2,390,010.00 $ 2,216,254.00 $ 1,182,298.00 $ 4,165,333.00 $ 1,328,655.00 $ 751,151.00 $ 4,516,393.00 $ 3,194,537.00 $ 824,675.00 $ 2,372,944.00 $ 1,807,747.00 $4,352,972.00 Cost Difference R