DOC

# 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

2. PROJECT MANAGEMENT

3. LUNCH AND LEARN

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

SUM

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

i.e. =SUM(e45+d12)

VLOOKUP

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

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

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

values

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

SUMIF

(range,criteria,sum_range)

Range is the range of cells you want evaluated

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

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

Annual Expenditures =

G I H

=G1x(1+H1+I1)

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 -

2. PROJECT MANAGEMENT

Project Management Timeline:

Build a budget

Plan

Schedule

Budget

Approval Process

Implementation

Lessons Learned

*We never learn from previous projects.

A good budget will get you your funding.

Building a Budget:

The costing memorandum

Eligible costs

Partners

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

successful

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

information

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 -

3. LUNCH AND LEARN

Introduction:

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
cust-service@docsford.com