Goal Seek feature in Microsoft Office Excel is used to find out the correct input value to a formula in order to get a desired formula result. This video shows how to use the Goal Seek feature to calculate loan period for a given monthly payment amount and interest rate.

Advertisements

A PivotTable report is an interactive way to quickly summarize large amounts of data. In this video I will show you how to create a summary report using PivotTable in Microsoft Office Excel 2010.

Increased complexity in contemporary projects puts a heavy burden on the project management profession. Project Managers do no longer have the luxury to be task-oriented only; they need to evolve into Project Leaders in order to bring success to their projects. Accelerating business, environmental, economic, and political changes necessitate the invisible force of running projects, namely leadership, to emerge and save organizations.

A cook lacking the skill to mix the right amounts of ingredients will produce distasteful food even if she has all needed ingredients and cooking tools. And a football team’s couch will drive his team out of a tournament if he does not integrate players’ skills to create the harmony required in the field. Not far from that is the failure project manager whose sole job is to complete project deliverables within given time, scope, cost, and resources without envisioning the project, communicating vision to the team, integrating efforts and removing roadblocks to accomplish the vision.

Effective project leadership has two main concerns; team needs, and project requirements. A successful project leader is one who creates the balance between both concerns and moves forward in achieving project vision. Concern about team needs encompasses understanding each member’s personal and professional aspirations and helping him/her achieve them in line with the project vision. Starting with clear assignment of roles and responsibilities, to assessing member’s abilities and providing required training to accomplish the job, to fully comprehending team evolution dynamics comprise the road map a project leader should adopt to develop the ‘team side’ of her leadership skill. On the other hand, managing project requirements is what brings deliverables to existence. Analyzing stakeholders’ needs, scoping project, managing cost and time constraints, foreseeing risks, creating key performance indicators and monitoring them are the primary tasks a project manager is hired to perform.

Balancing people side and task side is essential to implement a successful project. Excessive concern about team’s needs will not produce deliverables within given constraints by stakeholders, and, on the other hand, neglecting people’s needs while focusing on tasks will demoralize members, tear team apart, and result in a failure project. The right mixture of people and task orientation is what effective leadership brings to project management.

Effective communication remains number one skill a project leader should possess to influence her team and project tasks towards vision fulfillment. Creating a project vision is the beginning of a story that concludes only when vision is clearly communicated, understood, adopted, and accomplished by project team. Although not all team members are required to agree with the vision since it might be impossible, each member must understand the vision and commit to it. Continuous monitoring and controlling to the vision is critical to success. Project leader should keep any eye on vision completion progress and its alignment with stakeholders’ needs and organizational strategy. She should act proactively to adjust vision when strategy and circumstances necessitate that. This can only be achieved by an effective communication plan.

Creating a sense of team accountability is another vital aspect of project leadership. A leader should clearly communicate expectations of members, develop a practical measurement system to evaluate performance, give the team the ability to assess themselves against expectations, and finally develop awards and sanctions limits. Establishing accountability is a hard job to perform by the leader as it requires assessing the current abilities of the team and raising them to perform up to expectations then rewarding or sanctioning members upon evaluation. This all should be done rightly otherwise accountability will incur harmful consequences. If low performers are sanctioned for out of-control factors, resentment will develop and commitment will fall. Similarly, if high-performers are rewarded for out-of-control factors, favoritism will develop and negative conflicts will surface.

Understanding team dynamics and developing the team from individualism to team spirit is the first and foremost priority on the people side of project leadership. Most often team is comprised of members with diverse backgrounds, attitudes, and hidden agendas which make up the recipe of conflicts in projects. Hence, conflicts are inevitable, and project leader should exploit such conflicts to the betterment of the project. Naturally, members cannot evolve to the performing stage immediately. A leader should understand and help the team pass from Forming to Storming to Norming until it reaches the Performing stage in which the team experiences real cohesion and start focusing its efforts to complete project tasks.

By this every project manager needs to explore this invisible critical success factor and assess oneself against various leadership aspects whose absence could doom the project to fail. So, are you people-oriented, task-oriented, or do you create a balance between both? Bring this hidden power to surface and move forward to accomplish your vision.

“Would you please help me print out this ‘WBS’? It won’t fit in one page. I have a status meeting with the Sponsor in 30 minutes!” a colleague of mine approached me and asked. “This is not a WBS! It is a Schedule in a form of hierarchal structure.” I said sarcastically when I saw her WBS. She listed all project deliverables, and listed all activities below each one. This is not what a WBS is intended to be used for. Does your sponsor or client need to know how you’re going to complete each deliverable? Do you really need to present a 100+ boxes on your WBS to get your sponsor agree on what’s included and what’s excluded in your project scope? Absolutely not.

The primary benefits of having a WBS in any successful project dictate the need to keep it simple. Firstly, a WBS depicts the boundaries of project scope. A client or a sponsor can easily sign off a well-structured WBS as it includes all project scope and excludes whatever out of scope. Secondly, it ensures that effort is not wasted on unnecessary or out-of-scope deliverables. That is, if the WBS lists a redundant Work Package, this would require extra resources, time, and cost. Finally, a good WBS can be used on a project dashboard to communicate scope (changes) without confusing stakeholders with scores of activities needed to complete deliverables. The latter point is actually the key to build a good WBS. A WBS does not include activities; it only lists deliverables down to the Work Package level. Leave listing activities to the Project Schedule. WBS is composed of tangible deliverables without activities whereas a Schedule describes all activities required to complete those deliverables outlined in the WBS.

From another perspective, a WBS represents the project lifecycle that is different from the PM Process Groups. WBS is not used to chart Initiating, Planning, Executing, Monitoring & Controlling, and Closing of a project. These are process groups that describe how you manage a project from start to end but not what the project includes and what it excludes (scope). On the other hand, a project lifecycle describes the phases into which a project evolves to complete each of the agreed upon deliverables. Hence, one of the most commonly-used WBS’s is breaking down the project into phases, deliverables, sub-deliverables, and Work Packages that collectively constitutes the overall scope of the project.

If WBS represents the lifecycle, how should PM processes be represented as part of the project effort? Project Management is actually a phase in the WBS that has its own deliverables, sub-deliverables, and Work Packages. Taking a software development project as an example, the WBS shown in figure (1) is what is expected to represent the lifecycle and scope of the project (WBS in its initial structure for illustration purposes). 

Figure (1)-Software Development WBS

As shown in figure (1) the Analysis phase of the project consists of two deliverables: Glossary, and Requirements Specifications. The SRS deliverable consists of three sub-deliverables: Use Cases, Supplementary Specifications, and Reporting Requirements. The sub-deliverables can be broken down further into Work Packages (components that can be estimated for required time, resources, and cost). Use Cases, for instance, can be broken down into more specific use cases discussed with customer or user of the system under development.

Although WBS is progressively elaborated (built in increments as project progresses) a PM should make sure her WBS is complete and constitutes 100% of the agreed scope. All phases should make up the 100% completeness of the project. And all child deliverables under each phase should make up 100% of the parent phase, and so on. This is one way to ensure a WBS, so scope, completeness.

After a WBS is structured correctly, a project schedule can be established. From each deliverable or Work Package in the WBS PM with her team members can list the required activities to build each component. Time, resources, and cost are then allocated to each activity of the component after which a project schedule (e.g. MS Project Plan) is generated and schedule baseline is then set.

As we are approaching 2010 end people have been very busy creating budgets and setting objectives for 2011. While I am confident there are a lot of organizations listing improvement projects on their priority lists to achieve their strategic objectives I feel skeptical about Quick-Win initiatives being within these lists. Those ‘tiny’ projects that would relatively consume little resources but would have significant impact on the bottom line.

A decision taken by top management to assign every team the mission of listing a few quick-win projects would probably be a breakthrough in cost-saving for the next year. Let’s not talk about long-term goals and the projects or programs required to achieve them, that in some cases are doomed to fail due to unstructured methodologies, lack of buy-in, overallocated or insufficient resources, etc. Let’s not drain our precious resources on unnecessary projects that can be replaced by simple, few-day ones that would triple the ROI and boost our morale. Let’s commit to things that everyone in the organization is able to do because it reflects common sense. Let’s start looking around for a few minutes every day and hunt down something to improve.

Despite the need to learn basic problem-solving techniques and being couched on team dynamics, continuous improvement remains something that can be initiated and adopted by every employee in the organization. All what is needed is a common methodology to adopt throughout the organization in tackling improvement projects. A framework that each team can tailor to its work. Then, sky is the limit.

Low-hanging fruits exist in all places, but they need someone to look at and reap. Walk in your Accounting team, for instance, you will probably find invoices being checked or reconciled manually or semi-manually. Why not train people to use a tool in their hands more effectively to achieve more accurate results with less time? Walk in your warehouse and check how inventory is being controlled. Is it done on paper? Can it be automated with some affordable tool, I’m sure there are many out there to use. Don’t forget the ergonomics side of your operations. Does your workplace area fit employees? Is it laid out well enough to spare them strains and injuries? Is the well-being of employees on your high-priority list? There are a lot to think about just in a few-minute trip in your workplace!

To keep control of your improvement ideas and to monitor how you perform against your improvement goals you need to have some metrics. I believe that some people get confused when they hear the term KPI (Key Performance Indicator). It is so simple, though. You need to have something to measure your performance against, which is a KPI. If you find out that verifying a supplier invoice takes an accountant 30 minutes and you need to reduce it to 10 minutes then you use the 10 minutes as your KPI. So, after improving the verification process you keep an eye on the achieved cycle time and compare it to your target (10 minutes) until you achieve it.

Human resources are the most valuable asset in an organization. Success improves employees’ morale as well as the bottom line. On the other hand, failure projects demoralize employees in addition to having lost money. In order to achieve successful projects we ought to give employees the sense of self-worth by engaging them in improvement initiatives, encouraging them to hunt opportunities for improvement, and winning their buy-in.

Best of luck and Happy New Year.

Joyce Wycoff in his book “Transformation Thinking” says:

‘When an organization commits to creating an environment which stimulates the growth of everyone in the organization, amazing things start to happen: ideas pop up everywhere, people start to work together instead of “playing politics”; new opportunities appear; customers begin to notice service and attitude improvements; collections of individuals begin to coalesce into teams’.

It is a prevalent practice in organizations that start Six Sigma initiatives to employ specialist Black Belts (BB) and Green Belts (GB) to manage the improvement process of the organization’s operations. While it is a good practice to adopt, it is not the most efficient and effective. Outside BB and GB individuals are expensive and will yield shorter-term benefits than if Six Sigma culture is instilled in the workforce itself responsible for the process under improvement.

The optimum approach to nurture Six Sigma in an organization is to consider all employees as potential Green Belts then select few to receive advanced training and become Black Belts. Most of employees are capable to be Green Belts. The goal should be to train them in three main areas: problem-solving techniques, continuous improvement models, and interpersonal and team building skills. Black Belts then can be selected and trained on further advanced statistical tools and techniques with more emphasis on team building, conflict resolution, coaching and mentoring skills so that they can guide the rest of employees to achieve their optimum performance.

Motorola proved this concept when it discovered that most of cost savings, process improvement, and higher customer satisfaction came from the direct labor working on the process. Those people are the best to know the process and its areas to improve. They know what impedes achieving excellence, and only by training them on problem-solving and improvement techniques they excel in achieving breakthrough yields and highly capable processes.

Employee involvement is essential in any successful Six Sigma project. Sense of responsibility and accountability by an employee is magnified when he/she is involved in defining the problem, measuring the process, analyzing root causes, and contributing to the selection of best solution to implement. By this the organization will get the one-million-dollar worth jewel of ‘employee Buy-In’.

As Alan Larson mentions in his book ‘Demystifying Six Sigma’, “something magical happens when employees become more experienced and effective with Six Sigma tools and the results come rolling in…you can feel human energy, like static electricity, in the air”.

By infusing six sigma skills throughout the entire organization you will develop a continuous improvement culture in which all employees are involved towards achieving customer satisfaction within the frame of collaborative focus led by the organization executives; this is the concept behind Total Quality Management (TQM). So, if you are thinking to improve your organizational performance, and I am sure everybody is, start by thinking out your strategy to instill the culture of continuous improvement among employees by training and by allowing those savior specialists to emerge from your company instead of paying thousands to acquire external professionals who are ‘foreigners’ to your processes and to your workforce which in turn may reduce the likelihood of your projects’ success. At the end of the day you are optimizing performance on employee as well as process levels and you increase the morale amongst your people.

NB Idea of this blog was inspired by Alan Larson book “Demystifying Six Sigma, A Company-Wide Approach to Continuous Improvement”

Vlookup is a function that helps you search for a value in a range of cells and return the same or a different value from that range on the same row. To make the picture clearer, let’s take a scenario in which Zaid, an accountant working in XYZ Company, has an Excel file of two sheets; Year Revenue, and Year Cost of a list of XYZ products. Zaid is trying to combine the two sheets’ revenue and cost figures to calculate the profit for each product.

It’s obvious that Zaid has two options so that he can calculate each product’s Profit (Revenue minus Cost):

1. search for each product’s revenue in sheet1 and place it in its corresponding cell in sheet2, or

2. search for each product’s cost in sheet2 and place it in its corresponding cell in sheet1

Zaid elected to follow option 1 and added two extra columns in sheet2; Revenue and Profit.

clip_image002 clip_image004

      Figure (1)                                         Figure (2)

It’s worth mentioning that my experience with people who usually get incorrect results when applying the Vlookup function is that they either:

a. forget to ‘fix’ the lookup range (table array, as referred to in the Vlookup formula) using the dollar sign ($), and/or

b. try to lookup values of different data types. E.g. they lookup Invoice number between two sheets in which it is of Number format in one and of text format in the other.

To avoid the first problem, which is the most common, try to name the range of cells in which you need to search for Revenue values. To do this, select the range of cells that covers all data (including headers) in sheet1 and type any name in the Name Box (do not use space in the Name Box). I will name the range RevenueFigures (figure 3).

clip_image006

Figure (3)

Back to our scenario, we’re ready to apply the Vlookup formula in sheet2 to lookup the first Revenue value. I like and do prefer writing formulas than using the Insert Function, so I will write ‘=vl’ in cell C2 and Excel will narrow down my search to ‘VLOOKUP’ function as shown in figure (4). Hitting the TAB keyboard button will insert the function showing the arguments you need to insert to lookup the Revenue value of Product-I from sheet1 (figure 5).

clip_image008 clip_image010

Figure (4)                                                      Figure (5)

First, you need to specify the value in sheet2 that will be looked up from sheet1 which is captioned as lookup_value in the vlookup function. Select this value by moving the cursor to cell A2. This means that you need to search for Product-I in sheet1.

clip_image012

Figure (6)

Obviously, you have to specify the range of cells in which you need Excel to search for the lookup value, which is the second argument captioned table_array. Type a comma to end the first argument then type the first letters of the sheet1 Range name you previously defined until Excel finds it for you. Great Excel! (Figure 7).

clip_image014

Figure (7)

Well, now you’ve got to identify the value in the Range that you need Excel to return to sheet2 if the lookup value has been found in sheet1. I.e. you need to tell Excel to return the Revenue figure of Product-I from sheet1 and place it in cell C2 in sheet2. This is achieved by specifying the col_index_num. This is the column number of the return value reference to the lookup value in the Range. So, we need to return the value in column number 2 (Revenue).

clip_image016 clip_image018

Figure (8)                                                        Figure (9)

The last argument to complete the Vlookup formula is either TRUE or FALSE. Use FALSE to get the exact match (recommended always) and TRUE to get approximate match. Close the function with “)” and hit the return keyboard key to get the result. Then apply the formula to the rest of products by copy/paste.

clip_image020 clip_image022

Figure (10)                                                          Figure (11)

By this Zaid will be able to calculate each product’s profit by deducting the cost from the looked up revenue.

clip_image024 clip_image026

Figure (12)                                                          Figure (13)

After applying the Vlookup function to your data I recommend to always have a couple checks to the figures by taking samples of looked up values and comparing them to those in the searched Range.