Chapter Exercises

Exercise 1

In this exercise, you will complete the Time Cost Worksheet to predict and calculate the cost of each team member. Microsoft Excel is required to use the formulas to automatically predict the cost of each task. If you do not have Microsoft Excel, you can use the alternate worksheet called Manual Time Cost Worksheet to enter the values manually.

Scenario: You are the IT project manager for Harding Enterprises. The project you are managing is an installation of new network cable, network cards, servers, and workstations throughout the entire company. In this first part of the budget planning exercises, you need to calculate the hourly rate of each worker.

Follow these steps to complete Exercise 1:

1. Insert the CD-ROM included with this book into your CD-ROM drive.

2. Open Windows Explorer and navigate to the drive that represents your CD-ROM.

3. Within the CD-ROM, open the folder called Chapter 4.

4. Within the Chapter 4 folder, open the Microsoft Excel file called Time Cost Worksheet by double-clicking it.

5. The Excel document has two spreadsheets: Instructions and Time cost analysis. On the Instructions spreadsheet, hover your mouse over the red marker in cell A5. You ll see some general directions on how this spreadsheet works in case you want to use it in production. Click the spreadsheet titled Time cost analysis to move to the second sheet.

6. Hover your mouse over the comment marker in cell A1 and read the comments. Click in cell A2, enter Rick Gordon , and then press TAB to move to cell B2.

7. Hover your mouse over cell B1 to read the comment. In cell B2, enter Rick Gordon s yearly salary, 73500 , and press TAB to move to cell C2.

8. Rick Gordon s hourly rate is calculated for you based on his annual salary, divided by 52 weeks, and then divided again by 40 hours. Press TAB again to move to cell D2.

9. For this first task, enter 4.5 to represent four-and-a-half hours for the best value time. Press TAB to move to cell E2.

10. Note that 4.5 hours equates to $159.01 for Rick s time. Press TAB again to move to F2. For the worst time, enter 7 to represent seven hours and press TAB. The cost for seven hours is calculated. Press TAB to move onto cell H2.

11. Notice that cell H2 has already calculated the average time for Rick Gordon and the average cost for Rick to complete the assigned task.

12. Complete the remainder of the spreadsheet with the following information:

    Team Member	Yearly Salary	Best Time (Hours)	Worst Time (Hours)
    Samantha Murray	67500	5	9
    Bradley Kiser	43200	9	15
    Harriet Sutherland	37600	12	19
    Fred Stephens	57600	8	16

1. Based on your entries, answer the following questions:

   1. What is the average cost of Samantha Murray s time on the assigned task?

   2. What is the cost of Bradley Kiser s time if he takes the worst amount of predicted time?

   3. What is the cost of Harriet Sutherland s time if she beats the best time estimate by two hours?

   4. What is the average cost of Fred Stephens time?

2. Review your work and then close the document. You can save the spreadsheet to your hard disk if you would like to review your work again later.

Exercise 2

In this exercise you will create a budget for phase 1 of a hypothetical project. You will be using the Microsoft Excel spreadsheet called Budget to complete the exercise. If you do not have Excel, you can use the alternative spreadsheet, Manual Budget, to complete the exercise.

Scenario: You are the IT project manager for Harding Enterprises. The project you are managing is an installation of new network cable, network cards, servers, and workstations throughout the entire company. In this exercise, you will be calculating ongoing expenses related to the purchase and installation of Category 5 UTP cable, switches, patch panels, and the servers. Follow these steps to complete the exercise:

1. Insert the CD-ROM included with this book into your CD-ROM drive.

2. Open Windows Explorer and navigate to the drive that represents your CD-ROM.

3. Within the CD-ROM, open the folder called Chapter 4.

4. Within the Chapter 4 folder, open the Microsoft Excel file called Budget by double-clicking it.

5. Hover your mouse over cell A1 to see the comment that has been added.

6. Hover your mouse over cell B2 to see the comment that has been added.

7. Navigate to cell E6, the third week s hours for Steve Ledbetter. Weeks 1 and 2 have been completed for you. Enter 27 for Steve s hours and press ENTER.

8. Notice that several things have happened : The hours to date and the cost to date have increased. Also, the amount in cell F2, the amount spent to date, has increased. Finally, the figure in cell F3, the amount until the budget has been reached, has decreased.

9. Move back to cell E6 and enter 37 for Steve s work hours and press ENTER. Notice the changes throughout the spreadsheet.

10. Enter the following figures for the rest of the team members third week hours on the project:

    Team Member	Week 3 Hours Worked
    Sally Dehority	28
    Jane Chambers	39
    John Maxwell	21
    Fred Hoffman	37

11. Navigate to cell B14. Enter 7854 as the amount of the server that will be purchased and press TAB.

12. In cell C14, enter 2 , and press TAB. The amount of the servers has been calculated and the spreadsheet has been updated to reflect your changes.

13. Navigate to cell B15, enter 1800 for the amount of the network cable, and press TAB to move to cell C15.

14. In cell C15, enter 3 and press TAB. Again, the spreadsheet is updated to reflect the changes.

15. Navigate to cell B16, enter 21.34 , and press TAB.

16. For the number of cards purchased, enter 227 and press TAB to see the spreadsheet updated.

17. Navigate to cell B20. Enter 27.80 as the amount of the PC tool kit and press TAB.

18. In cell C20, enter 5 as the number of tool kits purchased and press TAB.

19. In cell B21, enter 98.78 as the cost of the RJ-45 connectors and press TAB.

20. In cell C21, enter 2 and press TAB.

21. In cell B22, enter 49 and press TAB.

22. In cell C22, enter 4 and press TAB.

23. In cell B23, enter 150 and press TAB.

24. In cell C23, enter 3 and press TAB.

25. Review your work and then close the document. You can save the spreadsheet to your hard disk if you would like to review your work later.