Ms. Excel Practice Assignment 5 1. Create a Ms. Excel file using the Project Timeline template and enter the following into the template. Change the dates per your judgement. Delete the extra tasks when done. Insert note & tasks of your choice when applicable. ACTIVITY END Project Start START 2/17/2021 Scope/project charter 2/27/2021 3/2/2021 Develop WBS 2/28/2021 3/3/2021 Identify milestones 3/10/2021 3/12/2021 Identify tasks 3/10/2021 3/12/2021 Identify slacks 3/10/2021 3/12/2021 Identify predecessors 3/15/2021 3/19/2021 Group tasks into work packages 3/22/2021 4/2/2021 Identify Resources 3/22/2021 3/26/2021 Identify resource availability 3/22/2021 3/26/2021 Assign unit costs to resources 4/5/2021 4/16/2021 Assign tasks to resources 4/5/2021 4/9/2021 Calculate project cost 4/12/2021 4/16/2021 Assign time (days/hours) to each task 4/19/2021 4/23/2021 Update project when necessary 4/19/2021 4/23/2021 Step 1 e is not required. wUICK QUIZ ANSW 1.6 2.b: 3.b; 4:5.66:7c8.0;9. d: 10. b Exercises 1 diiven the following information for a one-year project answer the following ques that PV is the planned value. EV is the earned value. AC is the actual cos, and budget at completion PV-$22.000 EV-$20,000 AC - $25,000 BAC-$120,000 a. What is the cost variance, schedule variance, cost performance index (CP) and schedule performance index (SPI) for the project? b. How is the project doing? Is it ahead of schedule or behind schedule? Is it or over budget? c. Use the CPI to calculate the estimate at completion (EAC) for this projects performing better or worse than planned? d. Use the SPI to estimate how long it will take to finish this project. Sketch the earned value chart for this project, using Figure 7-6 as a guide 2. Cheate a cost estimate and model for building a new, state-of-the-art multimedia Com for your organization within the next six months. The classroom should include 20 high-end personal computers with appropriate software for your organization, a network server, Irene access for all machines, an instructor station, and a projection system. Be sure to include personnel costs associated with the project management for this project. Document the assumptions you made in preparing the estimate and provide explanations for key numbers 3. Research using the cost management features of Project 2016 or other software tools. including project portfolio management software. Also review the project cost management section of Appendix A. Ask three people in different IT organizations that use project management software if they use the cost management features of the software and in whe ways. Write a brief report on what you learned from your research. 4. Read the free ebook Cost Estimating in an Agile Development Environment by Avin Alexander, which discusses estimating software costs. Find one or two other references on ways to measure software development costs. In your own words, write a two-page paper that explains how to estimate software development costs using at least two different approaches 5. Create a spreadsheet to calculate your projected total costs, total revenues, and total prolis for giving a seminar on cost estimating. Make the following assumptions: . You will charge $600 per person for a two-day class. You estimate that 30 people will attend the class, but you want to change this input . Postage colleagues. Your fixed costs include $500 totalto rent room for both days to registration, and $300 for designing a postcard or You will not include your labor costs for this time you want spend at least 150 hours developing malls, managing the proving the actual class. You would like to know what your time is worth . You will order 5,000 postcards, mail 4.000, and distribute the route trends Your variable costs include the following: a $5 per person for registration plus 4 percent of the deeper person to handle credit card processing: assume that everyone pays by Credit card $.40 per postcard for printing if you order 5.000 or more $.35 per postcard for mailing and postage d$25 per person for beverages and lunch e. $30 per person for class handouts Be sure to have input cells for any variables that might change, such as the cost of postage and handouts. Calculate your profits based on each of the following numbers of people who might attend: 10, 20, 30, 40, 50, and 80. In addition, calculate what your time would be worth per hour based on the number of students. Try to use the Excel data table feature to show the profits based on the number of students. If you are untamiliar with data tubles, just repeat the calculations for each possibility of 10, 20, 30, 40,50, and 60 students. Print you results on one page, highlighting the profits for each scenario and what your time is worth & Read the study by Lingyueng Song cited in the End Notes or a similar study about the use of EVM. Summarize the findings and your opinions of the study and the use of Evm. Why do you think it is still used mostly on large government or defense projects? Running Case You and your team are continuing your work on the Global Treps Project. Your project sponsor D: K. has asked you to refine the existing cost estimate for the project so you can evaluate supplier bids and have a solid cost baseline for evaluating project performance. Recall that your schedule and cost goals are to complete the project in six months for under $120,000. bu planned to use up to $50,000 total to pay yourself and your team members, and your initial estimates were $30,000 for travel expenses, S20,000 for hardware and software, and $20,000 10 organizing four events, including consultants, legal business fees, etc. Tasks ho alter the project, similar to the model provided in din Chapter 5, and be sure Also not Botal project to se VESL ver 50.300 205 000 100 56.000 2. Fung 8 14.000 ter Bater 10 10.00 19:00 logic 5 59.000 559.000 5202,400 10 1100 $50,000 5.600 $144,000 51.267.700 5251.540 os 17% Real 5253.560 51,521.240 FIGUREN Surveyor Pro project cost estimate FIG M N O Q R S T U V A3 fx Name the cells, use the names in the formula and function. Do not use the cell addresses. B C D E F G H J K L 1 Chapter 7 exercise (Schwalbe book) Step 1, 2, 5 only. Please save the document as "Your Name Last Name Assignment 5" 3 Name the cells, use the names in the formula and function. Do not use the cell addresses. For Step 2: Use figure 7-2 as a guide. Use your own assumptions! 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 Instructions Step 1 Step 2 Step 5 + x fx D18 Name Box A B D D E F G H . I J K L . M N 9 Schedule Variance 10 Cost Performance Index (CPI) 11 Schedule Performance Index (SPI) ) 12 Earned Value-Planned Value Earned Value/Actual Cost Earned Value/Planned Value SO Original total Budget BAC/CPI #DIV/0! B) How is the project doing? Is it ahead of 13 schedule or behind schedule? 14 Is it under budget or over budget? 15 C) Use the CPI to calculate the estimate at completion (EAC) for this project. Is the project performaing better or worse than 16 planned 17 Budget at Completion 18 Estimate at Completion 19 D) Use the SPI to estimate how long it will 20 take to finish this project 21 E) Sketch the earned value chart for this 22 project, using Figure 7-5 as a guide. 23 24 25 26 27 28 29 30 31 32 33 34 35 36 Instructions Step 1 Step 2 Step 5 + B5 fx B D E F G H I J L M M N 0 P O 1 2) Cost Estimate for Making a classroom #Units/Hrs. Cost/Unit/Hr. Subtotals WBS Level 2 Totals % of Total 2 3 WBS Items 4 1. Project Management 5 Project manager 6 Project team members Contractors (builders, plumbers, 7 electricions) 8 2. Hardware 9 Student workstation 10 Teacher workstation 11 Computer 12 Server 13 Projector 14 3. Software 15 Licensed software 16 4. Support 17 Teacher 18 Technology Specialist 19 Janitor 20 6. Reserves (20% of total estimate) 21 Total project cost estimate 22 23 24 25 26 27 28 29 30 31 32 33 34 Instructions Step 1 Step 2 Step 5 + B21 x fx B C D E F G H 1 J K L M N 0 P P Q Total 1 5) 2 Costs 3 Variable costs 4 Registration 5 Postcard printing 6 Postcard for mailing and postage 7 Beverages and Foods 8 Class Handout 9 Total Variable 10 Fixed costs 11 Rent room 12 Set up fee 13 Postcard design 14 Total Fixed 15 Total Costs 16 17 Revenue 18 Class fee 19 Number of Students Attending Profit Estimates Time Worth (Per Hour) 20 21 22 23 24 25 26 27 28 29 30 31 32 33 Instructions Step 1 Step 2 Step 5 + Ms. Excel Practice Assignment 5 1. Create a Ms. Excel file using the Project Timeline template and enter the following into the template. Change the dates per your judgement. Delete the extra tasks when done. Insert note & tasks of your choice when applicable. ACTIVITY END Project Start START 2/17/2021 Scope/project charter 2/27/2021 3/2/2021 Develop WBS 2/28/2021 3/3/2021 Identify milestones 3/10/2021 3/12/2021 Identify tasks 3/10/2021 3/12/2021 Identify slacks 3/10/2021 3/12/2021 Identify predecessors 3/15/2021 3/19/2021 Group tasks into work packages 3/22/2021 4/2/2021 Identify Resources 3/22/2021 3/26/2021 Identify resource availability 3/22/2021 3/26/2021 Assign unit costs to resources 4/5/2021 4/16/2021 Assign tasks to resources 4/5/2021 4/9/2021 Calculate project cost 4/12/2021 4/16/2021 Assign time (days/hours) to each task 4/19/2021 4/23/2021 Update project when necessary 4/19/2021 4/23/2021 Step 1 e is not required. wUICK QUIZ ANSW 1.6 2.b: 3.b; 4:5.66:7c8.0;9. d: 10. b Exercises 1 diiven the following information for a one-year project answer the following ques that PV is the planned value. EV is the earned value. AC is the actual cos, and budget at completion PV-$22.000 EV-$20,000 AC - $25,000 BAC-$120,000 a. What is the cost variance, schedule variance, cost performance index (CP) and schedule performance index (SPI) for the project? b. How is the project doing? Is it ahead of schedule or behind schedule? Is it or over budget? c. Use the CPI to calculate the estimate at completion (EAC) for this projects performing better or worse than planned? d. Use the SPI to estimate how long it will take to finish this project. Sketch the earned value chart for this project, using Figure 7-6 as a guide 2. Cheate a cost estimate and model for building a new, state-of-the-art multimedia Com for your organization within the next six months. The classroom should include 20 high-end personal computers with appropriate software for your organization, a network server, Irene access for all machines, an instructor station, and a projection system. Be sure to include personnel costs associated with the project management for this project. Document the assumptions you made in preparing the estimate and provide explanations for key numbers 3. Research using the cost management features of Project 2016 or other software tools. including project portfolio management software. Also review the project cost management section of Appendix A. Ask three people in different IT organizations that use project management software if they use the cost management features of the software and in whe ways. Write a brief report on what you learned from your research. 4. Read the free ebook Cost Estimating in an Agile Development Environment by Avin Alexander, which discusses estimating software costs. Find one or two other references on ways to measure software development costs. In your own words, write a two-page paper that explains how to estimate software development costs using at least two different approaches 5. Create a spreadsheet to calculate your projected total costs, total revenues, and total prolis for giving a seminar on cost estimating. Make the following assumptions: . You will charge $600 per person for a two-day class. You estimate that 30 people will attend the class, but you want to change this input . Postage colleagues. Your fixed costs include $500 totalto rent room for both days to registration, and $300 for designing a postcard or You will not include your labor costs for this time you want spend at least 150 hours developing malls, managing the proving the actual class. You would like to know what your time is worth . You will order 5,000 postcards, mail 4.000, and distribute the route trends Your variable costs include the following: a $5 per person for registration plus 4 percent of the deeper person to handle credit card processing: assume that everyone pays by Credit card $.40 per postcard for printing if you order 5.000 or more $.35 per postcard for mailing and postage d$25 per person for beverages and lunch e. $30 per person for class handouts Be sure to have input cells for any variables that might change, such as the cost of postage and handouts. Calculate your profits based on each of the following numbers of people who might attend: 10, 20, 30, 40, 50, and 80. In addition, calculate what your time would be worth per hour based on the number of students. Try to use the Excel data table feature to show the profits based on the number of students. If you are untamiliar with data tubles, just repeat the calculations for each possibility of 10, 20, 30, 40,50, and 60 students. Print you results on one page, highlighting the profits for each scenario and what your time is worth & Read the study by Lingyueng Song cited in the End Notes or a similar study about the use of EVM. Summarize the findings and your opinions of the study and the use of Evm. Why do you think it is still used mostly on large government or defense projects? Running Case You and your team are continuing your work on the Global Treps Project. Your project sponsor D: K. has asked you to refine the existing cost estimate for the project so you can evaluate supplier bids and have a solid cost baseline for evaluating project performance. Recall that your schedule and cost goals are to complete the project in six months for under $120,000. bu planned to use up to $50,000 total to pay yourself and your team members, and your initial estimates were $30,000 for travel expenses, S20,000 for hardware and software, and $20,000 10 organizing four events, including consultants, legal business fees, etc. Tasks ho alter the project, similar to the model provided in din Chapter 5, and be sure Also not Botal project to se VESL ver 50.300 205 000 100 56.000 2. Fung 8 14.000 ter Bater 10 10.00 19:00 logic 5 59.000 559.000 5202,400 10 1100 $50,000 5.600 $144,000 51.267.700 5251.540 os 17% Real 5253.560 51,521.240 FIGUREN Surveyor Pro project cost estimate FIG M N O Q R S T U V A3 fx Name the cells, use the names in the formula and function. Do not use the cell addresses. B C D E F G H J K L 1 Chapter 7 exercise (Schwalbe book) Step 1, 2, 5 only. Please save the document as "Your Name Last Name Assignment 5" 3 Name the cells, use the names in the formula and function. Do not use the cell addresses. For Step 2: Use figure 7-2 as a guide. Use your own assumptions! 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 Instructions Step 1 Step 2 Step 5 + x fx D18 Name Box A B D D E F G H . I J K L . M N 9 Schedule Variance 10 Cost Performance Index (CPI) 11 Schedule Performance Index (SPI) ) 12 Earned Value-Planned Value Earned Value/Actual Cost Earned Value/Planned Value SO Original total Budget BAC/CPI #DIV/0! B) How is the project doing? Is it ahead of 13 schedule or behind schedule? 14 Is it under budget or over budget? 15 C) Use the CPI to calculate the estimate at completion (EAC) for this project. Is the project performaing better or worse than 16 planned 17 Budget at Completion 18 Estimate at Completion 19 D) Use the SPI to estimate how long it will 20 take to finish this project 21 E) Sketch the earned value chart for this 22 project, using Figure 7-5 as a guide. 23 24 25 26 27 28 29 30 31 32 33 34 35 36 Instructions Step 1 Step 2 Step 5 + B5 fx B D E F G H I J L M M N 0 P O 1 2) Cost Estimate for Making a classroom #Units/Hrs. Cost/Unit/Hr. Subtotals WBS Level 2 Totals % of Total 2 3 WBS Items 4 1. Project Management 5 Project manager 6 Project team members Contractors (builders, plumbers, 7 electricions) 8 2. Hardware 9 Student workstation 10 Teacher workstation 11 Computer 12 Server 13 Projector 14 3. Software 15 Licensed software 16 4. Support 17 Teacher 18 Technology Specialist 19 Janitor 20 6. Reserves (20% of total estimate) 21 Total project cost estimate 22 23 24 25 26 27 28 29 30 31 32 33 34 Instructions Step 1 Step 2 Step 5 + B21 x fx B C D E F G H 1 J K L M N 0 P P Q Total 1 5) 2 Costs 3 Variable costs 4 Registration 5 Postcard printing 6 Postcard for mailing and postage 7 Beverages and Foods 8 Class Handout 9 Total Variable 10 Fixed costs 11 Rent room 12 Set up fee 13 Postcard design 14 Total Fixed 15 Total Costs 16 17 Revenue 18 Class fee 19 Number of Students Attending Profit Estimates Time Worth (Per Hour) 20 21 22 23 24 25 26 27 28 29 30 31 32 33 Instructions Step 1 Step 2 Step 5 +