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 6.25 percent coupon bond with 22 years left to maturity is priced to offer a 5.5 percent yield to maturity. You believe that in one year, the yield to maturity will be 6.0 percent. If this occurs,...
-
A tennis ball is a hollow sphere with a thin wall. It is set rolling without slipping at 4.03 m/s on a horizontal section of a track, as shown in Figure P10.56. It rolls around the inside of a...
-
Sample height averages (in centimeters) for 10 males and 10 females are listed. Find the coefficient of variation for each of the two data sets. Then compare the results. Males 167 165 160 181 190...
-
The market for smoothies is perfectly competitive and the market demand schedule is in the first table. Each of the 100 producers of smoothies has the costs given in the second table when it uses its...
-
Assuming F_hull is 500kN. Can you solve the FBDs for the reaction forces at A, B and C? Bx B By 250 mm 40 400 mm Any Ax 178 mm Ax + 64 A W FR Fhull Fhull
-
A highly viscous liquid discharges from a large container through a small-diameter tube in laminar flow. Disregarding entrance effects and velocity heads, obtain a relation for the variation of fluid...
-
Question 2 Suppose there are 100 households whose demand for electricity is given by Q = 40 - P. The local power company has a constant marginal cost of 2, and a fixed cost of 10,000. (a) Suppose the...
-
Who carries out the role of process improvement and why? What is the role of a Leader in the Coaching Kata mentality
-
Continue with part (B) above. Assume US tax law allowed USA Logistics to elect not to deduct the $350 million of royalties paid to EuroConnect (i.e., USA Logistics could elect to ignore all of this...
-
Why is the information in the notes to the financial statements important to financial statement readers? Choose the best answer. Select answer from the options below It is helpful for determining...
-
How might globalization be a problem for a successful national company that is intent on going international? What advantages would the national company have by going international? Provide an...
-
Do you think there should be some international standard for labor rights? If so, what should that standard be and how should it be enforced?
-
List and describe in detail the five approaches to management theory.
-
Fred Farmer needs to prepare a balance sheet for his bank. He spent the day getting the following information. Fred needs your help to build a balance sheet and evaluate it. The information was...
-
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...
-
Let \[f(x, y)=\frac{1}{2} \sin (x+y), 0 \leq x, y \leq \frac{\pi}{2}\] be the joint probability density of the random vector \((X, Y)\). (1) Determine the marginal densities. (2) Are \(X\) and \(Y\)...
-
A large population of rats had been fed with individually varying mixtures of wholegrain wheat and puffed wheat to see whether the composition of the food has any influence on the lifetimes of the...
-
The random vector \((X, Y)\) is defined as follows: \(X\) is uniformly distributed in the interval \([0,10]\). On condition \(X=x\), the random variable \(Y\) is uniformly distributed in the interval...
Study smarter with the SolutionInn App