Problem S: (17 points - Project Planning Excel Tools) You just landed a job as the...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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 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
Expert Answer:
Answer rating: 100% (QA)
To create a formal project management tool in Excel based on the given requirements follow these steps 1 Formatting the Table Apply appropriate borders and shading to the table to make it professional ... View the full answer
Related Book For
Fundamentals of Financial Management
ISBN: 978-1305635937
Concise 9th Edition
Authors: Eugene F. Brigham
Posted Date:
Students also viewed these business writing questions
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Managing Scope Changes Case Study Scope changes on a project can occur regardless of how well the project is planned or executed. Scope changes can be the result of something that was omitted during...
-
Assume that you recently graduated with a major in finance and that you just landed a job as a financial planner with Barney Smith Inc., a large financial services corporation. Your first assignment...
-
What is the purpose of using case and case type in the process identification stage?
-
A Buffalo, New York, cola distributor is featuring a super-special sale on 12-packs. She wonders where in the grocery store to place the cola for maximum attention. Should it be near the front door...
-
A pulse input to a vessel gives the results shown in Fig. P11.3. (a) Are the results consistent? (b) If the results are consistent, determine the amount of tracer introduced M, and the E curve. v = 4...
-
The common methods for the computational solution of radiative transport equations are summarized briefly below: 1. The discrete ordinate method 2. The Monte-Carlo method 3. Hottel's zonal method 4....
-
1. What steps should Bennie Gordon take to ensure that the accounting matter is adequately addressed by the company? Why do you suggest those steps be taken? What are the ethical obligations of...
-
Your parents set up a trust fund for you 10 years ago that is now worth $42,871.78 If the fund earned 8% per year, how much did your parents invest? PV? What is the relationship between present value...
-
Donovan Ramsey, the Chief Financial Officer of LevelUp Business Consulting, has advised you that the company is considering closing its Calgary, Alberta office and transitioning the staff of that...
-
In March 2018, the India Fashion Forum recognized the brand W as the most admired fashion brand of the year. The W brand was owned by New Delhi-based TCNS Clothing Company Limited (TCNS). In 2017,...
-
Why does the Venture Capital Method use very high discount rates?
-
What criteria should be used to choose an exit multiple?
-
Why are term sheets said to be incomplete? What are the implications?
-
How is the PROFEX method related to the Venture Capital Method?
-
What are the advantages and disadvantages of using contingent terms?
-
What is circuit equivalence of small loop vs electrically smallloop? why there is a difference?1 answer
-
This problem continues the Draper Consulting, Inc., situation from Problem 12-45 of Chapter 12. In October, Draper has the following transactions related to its common shares: Oct 1 Draper...
-
Baxley Brothers has a DSO of 23 days, and its annual sales are $3,650,000. What is its accounts receivable balance? Assume that it uses a 365-day year.
-
A rookie quarterback is negotiating his first NFL contract. His opportunity cost is 7%. He has been offered three possible 4-year contracts. Payments are guaranteed, and they would be made at the end...
-
Barton Industries has operating income for the year of $3,500,000 and a 36% tax rate. Its total invested capital is $20,000,000 and its after-tax percentage cost of capital is 8%. What is the firms...
-
For coordinates \(\left(x^{1}, x^{2} ight)\) and metric \(g=\operatorname{diag}\left(g_{11}, g_{22} ight)\), the Gaussian curvature is For a sphere with coordinates defined in the following figure,...
-
Consider the holonomic basis defined in Box 26.1 . Using that the tangent vector for a curve can be written \(t=t^{\mu} e_{\mu}=\left(d x^{\mu} / d \lambda ight) e_{\mu}\), show that Thus, \(g_{\mu...
-
The Lie bracket of vector fields \(A\) and \(B\) is defined as their commutator, \([A, B]=\) \(A B-B A\). The Lie bracket of two basis vectors vanishes for a coordinate basis but not for a...
Study smarter with the SolutionInn App