Project Management Basics Using Excel

By Lynn Holmes,2014-05-17 17:23
14 views 0
Project Management Training Session. Building Budgets Using Excel. June 12, 2002. 3 Components to the Session. 1. EXCEL. 2. PROJECT MANAGEMENT

    Project Management Training Session

    Building Budgets Using Excel

    June 12, 2002

    3 Components to the Session

    1. EXCEL



    1. EXCEL

    Why Excel?

     Budgets impact projects

     Important to let the software do the work

     No need to duplicate efforts

     Never input the same number twice when can just reference it

    - Date the versions of spreadsheet

    - Think while designing the spreadsheet, not when using it


     The sum can add up columns or you can also use the sigma sign in the tool bar

    menu to automatically add

     i.e. =SUM(e45+d12)


     Searches for a value in the leftmost column of a table, and then returns a value in

    the same row from a column you specify in the table

     In the example below we are searching for the job title Accounting


     Lookup_value is the value to be found in the first column of the array.

    Lookup_value can be a value, a reference, or a text string Table_array is the table of information in which data is looked up. Use a

    reference to a range or a range name, such as Database or List If range_lookup is TRUE, the values in the first column of table_array must be

    placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise

    VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array

    does not need to be sorted

     You can put the values in ascending order by choosing the Sort command from

    the Data menu and selecting Ascending

     The values in the first column of table_array can be text, numbers, or logical


     Uppercase and lowercase text are equivalent

     Using the Wizard provides definition reminders for where the data should come

    from that is being evaluated

    - 1 -

    Anchoring a specific cell:

     By inserting a dollar sign ($) you can anchor the data in a cell use the constant

    cell for your formula.



     Range is the range of cells you want evaluated

     Criteria is the criteria in the form of a number, expression, or text that defines

    which cells will be added

     For example, criteria can be expressed as 32, "32", ">32", "apples" Sum_range are the actual cells to sum

     The cells in sum_range are summed only if their corresponding cells in range

    match the criteria

     If sum_range is omitted, the cells in range are summed

    - 2 -

i.e. =sumif(E1:E107,’TR’,G1:G107)

    sum if = what are you going to search ?

    We search from column E to row 1

    We are searching on TR and we want to add all the numbers in G1 to G107

Overhead and Benefits in Excel:

     Annual Expenditures =

     Salary + Benefits %+Overhead%

     Salary x (1+%Benefits+%Overhead)

    i.e. Salary Benefits Overhead

     G I H


    Short Cut Keys:

     Ctrl C to copy

     Ctrl V to paste

     Ctrl R adds in numbers to the right

     Ctrl D drags a formula down

     Ctrl D moves numbers down

     Ctrl Z will undo what you have just inputted

     Ctrl I inserts a row

     Ctrl A to select all

     To format a cell to text wrap - go to alignment and text wrap

    - 3 -


    Project Management Timeline:

     Build a budget




     Approval Process


     Lessons Learned

*We never learn from previous projects.

    A good budget will get you your funding.

Building a Budget:

     The costing memorandum

     Eligible costs


     Work packages

     Minimize partners and work packages

     Too many partners and work packages will drive a project manager mad - too

    much work

     Your eligible costs will determine the content of the budget

     If you can speak to your budget, and have a tight budget, you will likely be


     Look to your costing memorandum, find out eligible costs, then put anything you

    want to spend in eligible cost categories, then associate 50% to partners, then

    you can determine work packages (this is where you have say), minimizing

    partners and work packages

     Encourage partners to develop agreements with their sub partners without the

    prime contractor's direct involvement

    The art of defining deliverables:

     You have to ask for stuff that you can get

     Partners don’t want to be told what they have to do

     Partners have to have buy in

Building Communities in Project Management:

    1. Skill sets are always changing and a project manager can’t keep up with the


    2. Culture is more important that the skills. We must foster a community of

    knowledge that shares their skill sets. Share knowledge

    3. Understand how information flows in a project

    4. The person who has a grasp of the project is the best project manager

    - 4 -



     Idea of learning as a team

     Life raft of materials

     All participants taking and contributing notes

    Ideas for more professional development sessions:

     Collaborative project management

     Strategy for devising budgets

     We need to communicate more with ourselves

     More staff meetings

     Bi-weekly news bulletins

     We may need to re-write job descriptions

     From making mistakes we learn

     Take on smaller projects to learn from

     Contract, negotiations, etc

     We can create a set of on-line resources available on the Netera website. Possibility to share internally our knowledge on disciplines, projects, etc Lunch and Learn series

    - *Next Session

    - Logistics of a Project set-up. Getting your project off the ground.

    - 5 -

Report this document

For any questions or suggestions please email