Excel Secrets Everyone Should Know
You can create a new Excel workbook or chart as an embedded object in your project. In this case, the new Excel object exists only within your project file. To do this, follow these steps 1 In your project plan, display the location where you want to embed the object. Only the chart area of a Gantt chart and the Notes tab in the Task Information, Resource Information, or Assignment Information dialog box can accept an Excel file as an embedded object. 4 In the Object Type box, click Microsoft Excel Chart or Microsoft Excel Worksheet. An Excel worksheet or chart appears in the location you selected (see Figure 17-7). Figure 17-7. Embed a new chart in your project plan. 6 Double-click the Excel object to start adding your information. Figure 17-7. Embed a new chart in your project plan. 6 Double-click the Excel object to start adding your information. Tip Delete an embedded Excel object
In Microsoft Project, you can link an Excel worksheet fragment or chart in the chart area of a Gantt chart or the Objects box in the Task Form or Resource Form. To do this, follow the steps listed under Embedding Copied Excel Information in a Project File earlier in this chapter. In the Paste Special dialog box, select the Paste Link option. You can link an entire existing Excel file in the chart area of a Gantt chart and the Notes tab in the Task Information, Resource Information, or Assignment Information dialog box. To do this, follow the steps listed under Embedding an Entire Excel File in a Project File earlier in this chapter. In the Insert Object dialog box, select the Link check box. You can also copy worksheet cells and link them into Microsoft Project table cells. The data looks as if they were originally typed in Microsoft Project, but they're actually linked to Excel data. To link worksheet cell data in a Microsoft Project table, follow these steps 1 Arrange the source...
To open and work with an embedded Excel object, simply double-click the object. The Microsoft Project menus and toolbars change to Excel menus and toolbars (see Figure 17-8). Use these controls to work with the Excel object as needed. Embedded Excel object, activated for editing Excel menu and toolbars Figure 17-8. When you double-click an embedded Excel object, the Microsoft Project menus and toolbars change to those of Excel. Embedded Excel object, activated for editing Excel menu and toolbars Figure 17-8. When you double-click an embedded Excel object, the Microsoft Project menus and toolbars change to those of Excel. When finished editing the Excel object, click outside the object frame. The menus and toolbars return to those of Microsoft Project again. You cannot insert an object in the Notes box of a Task Form or Resource Form. However, after you have embedded an Excel file on the Notes tab in the Task Information or Resource Information dialog box, you can work with it from the...
You can copy an Excel graph and paste it into certain areas of Microsoft Project as a picture. You can even copy Excel worksheet cells, and instead of pasting those cells into table cells, you can paste them as a picture in Microsoft Project. To paste Excel information as a static picture, do the following 1 In Excel, select the chart or other data you want to insert as a picture in Microsoft Project. Figure 17-2. Use the Paste Special dialog box to specify that you want to insert the Excel information as a picture. Figure 17-2. Use the Paste Special dialog box to specify that you want to insert the Excel information as a picture. Excel chart picture in the Excel chart picture in the Figure 17-3. The Excel information appears as a picture in your selected Microsoft Project location. Figure 17-3. The Excel information appears as a picture in your selected Microsoft Project location. For more information about embedded objects, see Embedding an Excel Object in Project later in this...
Anything in a Microsoft Project table can easily be copied and pasted into Excel worksheet cells. Unlike copying from Excel to Microsoft Project, you do not need to match columns or data types because all Excel fields can accept any data types. To copy Microsoft Project table cells and paste them into Excel worksheet cells, follow these steps 1 In Microsoft Project, display the view that contains the information you want to copy to Excel. If necessary, apply a different table or add a column that contains the information you need. 4 In Excel, select the anchor cell in the worksheet where you want the incoming information to begin to be pasted. This anchor cell will become the location of the upper-left cell of data selected in Microsoft Project. The selected project data is inserted into the Excel worksheet starting at the anchor cell. Adjust column widths in the worksheet as necessary to see the data (see Figure 17-4). Figure 17-4. Your project data is pasted into the Excel...
To embed an entire existing Excel file into the chart portion of the Gantt Chart, follow these steps 1 In Microsoft Project, open the project plan in which you want to embed the Excel file. Only the chart area of a Gantt chart and the Notes tab in the Task Information, Resource Information, or Assignment Information dialog box can accept an Excel file as an embedded object. 5 Click the Browse button and find the location of the Excel file you want to insert. Double-click the file. 6 If you want the embedded Excel file to be linked to the source, select the Link check box. If you link the file, any changes to the file in Excel update the embedded object in Microsoft Project. If you try to change the embedded object, you'll see a message indicating that the object is linked, and if you change information, the link will be removed. In this way, the linked information is protected. 7 If you want the embedded object to be displayed as an Excel icon in your project, rather than showing as...
You can import information from Excel into Microsoft Project. You can also export information from Microsoft Project into Excel. When you import information, you're bringing information in a foreign file format (for example, the Excel .xls file format) into the current application (for example, Microsoft Project, which uses the .mpp file format). When you export information, you're saving information in the current application in a different file format, so that it can be easily opened by another application. In both cases, the information will look as if it were created originally in the target application.
You can create a new Microsoft Project file as an embedded object in Excel. In this case, the object exists only within the Excel file. To create a new Microsoft Project file within Excel, follow these steps 1 Open the Excel file in which you want to create a new Microsoft Project file as an embedded object. 6 If you want the embedded project to be displayed as an icon in Excel, rather than showing a part of the Gantt Chart or other view, select the Display As Icon check box.
Using two templates specifically designed for integration, importing task, resource, and even assignment information from Excel to Microsoft Project is a breeze. With these templates, the most commonly used fields are present and the fields are already recognized and mapped in Microsoft Project. All you have to do is open the Excel file and make a couple of choices, and the data is imported into your project plan. If a team member or other stakeholder has created project information in Excel without using one of these templates, never fear. You can still import an Excel workbook into Microsoft Project the old way.
You can exchange information between Microsoft Project and Excel by embedding the source application as an embedded object in the target application. When you embed an object in one application that originated in a different application, you're basically inserting an entire file, with all of its source application's capabilities, into the target application. This means that not only can you have a picture of a Gantt chart in an Excel worksheet, but you can double-click that Gantt Chart picture to access Microsoft Project commands to change data in the Gantt Chart. You can then switch to the Resource Sheet and change information there as well. Likewise, in Microsoft Project, not only can you have just a picture of an S-curve graph generated by Excel, but you can double-click it to launch Excel and edit the format of the graph or even the underlying data for the graph.
Suppose that your company's accounting department wants to analyze your project cost information in conjunction with those of other projects taking place throughout the company. The department uses Excel to analyze project cost data, so you'll need to export your Microsoft Project information to an Excel workbook.
You can copy a picture of any Microsoft Project view and then paste it into Excel. To do this, follow these steps In Microsoft Project, display the view you want to capture as a picture for Excel. Manipulate the view to show the information the way you want it to appear in the target application. In Excel, select the location in the worksheet where you want to paste the picture of the view. A static picture of the Microsoft Project view is pasted into Excel (see Figure 17-5). You can move the image by dragging it. You can also resize the image by dragging any of the edges. Figure 17-5. The copied picture of your Microsoft Project view is pasted into Excel. Figure 17-5. The copied picture of your Microsoft Project view is pasted into Excel.
To embed an existing Microsoft Project file in Excel, follow these steps 1 Open the Excel file in which you want to embed the existing Microsoft Project file. 8 If you want the embedded project to be displayed as a Microsoft Project icon in Excel, rather than showing a part of the Gantt Chart or other view, select the Display As Icon check box. Figure 17-9. The selected project is embedded in Excel. Figure 17-9. The selected project is embedded in Excel. If you selected the Display As Icon check box, the embedded project file appears as the Microsoft Project icon (by default) in the selected location. This is most useful when you have limited space in your Excel worksheet.
And Excel Embedding Between Microsoft Project and Excel Linking Between Microsoft Project 504 and Excel Importing and Exporting with Excel . . . . 521 Several techniques for information interchange are at your disposal to exchange information in Microsoft Office Project 2003 with Microsoft Excel. Not only can you integrate Excel information into your project plan, but you can also feed information from Project 2003 to an Excel workbook. Such capabilities provide for a greater ease of information sharing among team members and project stakeholders. Transferring data between Microsoft Project and Excel can simplify aspects of building your project plan, tracking progress, reporting status, and communicating with stakeholders Using the special Excel-to-Project templates, you can have stakeholders build task lists and even a more elaborate project plan using Excel, and import those files seamlessly into Project. You can copy resource lists from an Excel worksheet. You can embed an Excel...
You can export more complete information about tasks, resources, and assignments. With this method, Microsoft Project organizes data into Task, Resource, and Assignment data types, which in turn are presented in their own separate worksheets in the single Excel workbook. To create a complete Excel workbook of project information from your project file, follow these steps 1 In Microsoft Project, open the project whose information you want to export to Excel. 4 In the Save As File Type list, click Microsoft Excel Workbook (*.xls). 5 In the File Name box, enter a name for the Excel file and then click Save. 7 On the Data page, click Project Excel Template and then click Finish. Your data is saved as a complete Excel workbook. To open and review your exported project data in Microsoft Excel, follow these steps 1 In Microsoft Excel, open the Excel file. Figure 17-24. By exporting tasks, resources, and assignments to Excel, separate worksheets are created to hold key task, resource, and...
Many project managers do well by having others on the team develop a task list of their specific areas of responsibility. A great way to automate this process is to have these individuals use Microsoft Excel to create their task lists and then import the worksheets into the Microsoft Project Gantt Chart. The standard Excel importing process involves mapping the Excel columns to the corresponding Project columns to ensure that the right information ends up in the right places in your Gantt Chart task table. Microsoft Project comes with an Excel Task List template set up for this very purpose. To use Excel and the Excel Task List template on the same computer on which Microsoft Project is installed, follow these steps Start Microsoft Excel. E2 Microsoft Excel - TASKLIST1 Figure 3-12. Share the Excel Task List template with your team to help build your project plan. Note If you're working with a version of Microsoft Excel 2000 or earlier, you can still use the Microsoft Project Task List...
If team members or other project stakeholders created an Excel workbook containing project information before they knew of the existence of the templates, you can still import Excel worksheets. 2 Go to the location on your computer or network where the Excel workbook is saved. 3 In the Files Of Type list, click Microsoft Excel Workbooks (*.xls). The workbook appears in the list of folders and files. The fields from the Excel sheet appear in the From Excel Field column. If there is an obvious match to a Microsoft Project field, it appears in the To Microsoft Office Project Field column. If Microsoft Project could not figure out a match, the field indicates (not mapped). 10 For any fields that are not mapped, click in the box and select the Microsoft Project field in which you want to store the corresponding imported field. Scroll down through the entire table and make sure that all the Excel fields you want to import are mapped to a Microsoft Project field. Map the incoming Excel...
If certain team members or other project stakeholders are doing more than just building a task list in Excel, have them use the Microsoft Project Import Export Template. This template can be used in Excel to build a project with tasks, resources, and assignments. Then you have a properly formatted worksheet for many of the essential elements of a plan for importing into Microsoft Project. The standard Excel importing process involves mapping the Excel columns to the corresponding Microsoft Project columns to ensure that the right information ends up in the right locations in your Resource Sheet. The Microsoft Project Plan Import Export Template is set up to enter more detailed resource information in the format needed by Microsoft Project. h To use this template, make sure that Excel is installed on the same computer as Microsoft Project and then follow these steps g 1 In Microsoft Excel, click File, New. Note If the Template is not in the Templates dialog box (which might be the case...
You can make your project plan more accurate and inclusive by integrating the suggestions and concrete input of team members and stakeholders into the project plan. Specifically, you can have others on the team create a task list from an Excel workbook and quickly incorporate it into Microsoft Project. Using the Microsoft Project Task List template in Excel, introduced in Project 2002, this process becomes seamless. The standard Excel importing process involves mapping the Excel columns to the corresponding Microsoft Project columns to ensure that the right information ends up in the right places in your Gantt Chart task table. Microsoft Project is supplied with an Excel Task List template set up for this very purpose. For example, suppose that the marketing department is suggesting an addition to the project plan that will provide more detail for their test marketing efforts. They want to develop a list of detailed tasks in Excel. You have them use the Microsoft Project Task List...
Want to start your project in Excel You can use the Excel template and then easily transfer the information from Microsoft Excel workbooks to Microsoft Project files. The Excel template automatically installs to the Office template folder (drive Program Files Microsoft 0ffice Templates 1033) and is available in the Templates dialog box in Excel. Note The Excel template made its appearance in Project 2002. Note You can use any of the existing import export maps to either import to or export from an Excel workbook, but you can't import an Excel PivotTable into Project. Follow these steps to transfer Excel information to Project 1. Open Excel, and choose FileONew. 2. From the New Workbook task pane, click General Templates. Excel displays the Templates dialog box, as shown in Figure 26-21. Figure 26-21 You find the Excel template on the Spreadsheet Solutions tab of the Templates dialog box. Figure 26-21 You find the Excel template on the Spreadsheet Solutions tab of the Templates dialog...
T Because Microsoft Excel is such a powerful tool for manipulating and calculating numeric w Microsoft Project for more in-depth analyses in Excel. For example, you can export project cost or earned value data to Excel. Or perhaps you want to crunch tracking data or assignment information. You can also create pivot tables and generate graphs such as S-curves to show project performance information. Exporting information from Microsoft Project to Excel has been made particularly easy with the Excel Export Wizard, introduced with Project 2002. Analyzing Numeric Project Data in Excel Although you can export most information types from Microsoft Project to Excel, it's most useful to export numeric data for further analysis in Excel. Numeric data are any data that can be used in calculations and mathematical operations. Examples of such data include cost and work data. Work data (such as hours) can be converted to numeric fields, but are stored in Microsoft Project as text because of the...
Using the Import Export Wizard, you can easily send information to Excel. The wizard gives you the choice of creating a new map or using an existing map. Follow these steps to start the process of sending information to Excel 4. Open the Save as type list box, and select Microsoft Excel Workbook or Microsoft Excel PivotTable. When you create an Excel PivotTable file. Project creates two sheets in the workbook for each type of data that you export. One sheet contains the data that is used in the PivotTable, and the other sheet contains the PivotTable. Project uses the last field in each map as the default field for the PivotTable, and all the other fields appear as rows in the PivotTable. You can choose Project Excel Template to export the entire Project file to Excel. Tip If you want your Excel workbook to contain assignments that are listed under tasks eld To Excel Field Excel Figure 26-3 As you add fields in the Task Mapping dialog box, a preview of the Excel worksheet that you're...
I am a firm believer that you should make things easy on yourself. If you like to noodle around with your task list for a project in Excel, you shouldn't have to retype everything into Project to build a Project plan. For that reason, Microsoft has provided an Excel Task List template. This template, located in the Microsoft Office template folder, can be opened from Excel. The template provides four Excel worksheets, as shown in Figure 4-4, in which you can enter tasks, resources, and resource assignments, and then export that data from Excel to Project. 1. In Excel, open the template called Microsoft Project Task List Import Template. 4. Locate the Excel Project Template file you just saved and then click Open. 6. Choose the second option, Project Excel Template, for the format of the data you're importing. Then click Next.
4 In the Save As File Type list, click Microsoft Excel Workbook (*.xls). 5 In the File Name box, enter a name for your new Excel file. 10 On the Map Options page, select the check box for each type of data you want to map and export Tasks, Resources, and Assignments. Under Microsoft Excel Options, select the Export Includes Headers check box. Click Next. In the table, specify how each field of data should be defined when it is exported to Excel. A preview of the data is shown as it will appear in Excel in the Preview area at the bottom of the Mapping page. Use the scroll bar to view all the columns. When ready, click Next. 1 Your specified project data is exported and saved as a complete Excel workbook in the exact layout you defined. To open and review your exported project data in Microsoft Excel, follow these steps 1 In Microsoft Excel, open the Excel file that you created by exporting project data. ES Microsoft Excel - 17Bld2.xls Figure 17-23. View and manipulate your project data...
Suppose you have a list of resources in a Microsoft Excel workbook. You can easily use it to populate your project's Resource Sheet. You can copy information or you can import the file. To copy a resource list from an Excel workbook, follow these steps Open the Excel workbook that contains the resource list. Select the resource names. On the Standard toolbar in Excel, click Copy. Open the project plan. If necessary, click View, Resource Sheet. You can also use the Microsoft Project Plan Import Export Template to import resources from Excel to Microsoft Project. The standard Excel importing process involves mapping the Excel columns to the corresponding Microsoft Project columns to ensure that the right information ends up in the right locations in your Resource Sheet. The Microsoft Project Plan Import Export Template is set up to enter more detailed resource information in the format
Suppose you'd like some of the same information in a more graphical format. Excel provides a nice tool for graphs with a somewhat easier-to-use interface than Access. Here's how you can create a pie chart showing the number of cases currently assigned to each status. First, fire up Excel. Then select Data Import External Data New Database Query. This will install Microsoft Query if necessary (it's not part of the default Excel installation) and then open the Choose Data Source dialog box. Select New Data Source and click OK. Name the data source and select the appropriate driver (Access or SQL Server), then connect to the database. Click OK to save the data source, then select it and click OK again. This will open the Query Wizard. Select Return Data to Microsoft Excel from the File menu. This will open the Import Data dialog box. You can now select where in the workbook to place the data I chose to put it in the default A 1 location. After a moment, Excel will retrieve the data from...
After exporting your project information to Excel, if you need to, you can change the data type of a field of information from text, for example, to numbers. For example, Duration fields are exported as text. You can easily change that text to numbers so you can run calculations on them. In Excel, select the column heading and then click Format, Cells. Click the Number tab if necessary. Under Category, select General, Number, or Currency, as appropriate (see Figure 13-9). Set any number attributes you want and then click OK. Figure 13-9. Use the Number tab in the Format Cells dialog box in Excel to change the data type or number format of a set of exported project fields. Figure 13-9. Use the Number tab in the Format Cells dialog box in Excel to change the data type or number format of a set of exported project fields. Even though the Task Mapping page of the Microsoft Project Export Wizard says they're Text fields, currency fields (such as Cost and Actual Cost) and earned value...
Analyzing Progress and Costs Using Calculating Project Information in Earned Value 401 Microsoft Excel 410 In addition, there are specialized techniques you can use with Project 2003 to analyze project information. These techniques include performing earned value calculations, creating formulas in custom fields, and exporting data to Microsoft Excel and calculating figures there.
You can bring information into Project from another Project file or from Microsoft Excel, Microsoft Access, or Microsoft Word. You also can import information that was created in any program that can save text (.TXT) files or comma-separated value (.CSV) files. When you import a Project file, you actually consolidate two Project files. When you import non-Project files, you use an import export map to define the data that you want to import.
You can export project information as XML data and create reports in other applications. You can generate this information as an HTML file for viewing on a Web site. You can send this information to a Microsoft Excel spreadsheet. In short, you can use this project XML data in any application that recognizes XML.
Custom fields can be used to create your own cost, date, duration, finish, flag, number, outline code, start, and text fields in Microsoft Project. The number of customizable fields of a given type can range from 10 to 30. When using Microsoft Project or the OLE DB Provider, you will see the fields listed with the type and the number of the custom field. When you're viewing the custom fields using Microsoft Access or Microsoft Excel, each custom field is stored in a unique table that contains all the data required to identify the custom field or custom outline code and where it is being used within a project. The table name is listed in the Microsoft Project Database column. Table B-11 shows custom fields.
Whenever you want to work with the embedded project file, just double-click the object. The Excel menus change to reflect the relevant Microsoft Project menus (see Figure 17-11). You Excel window Excel window Figure 17-11. When you double-click an embedded Project object, the Excel menus and toolbars change to those of Microsoft Project. Figure 17-11. When you double-click an embedded Project object, the Excel menus and toolbars change to those of Microsoft Project.
Think of an import export map as a template that Project uses to correctly translate information from one program to another. An import export map defines the information that you want to import or export and enables you to describe how to match the information in the Project file with the information in the other program's file. For example, when you charted earned value in Excel in Chapter 14, you selected the Earned Value Information export map to send the data to Excel. This mapping information told Project what data to send to Excel for charting and how to identify the information in Excel. Whenever you import or export, you can use one of the predefined maps that comes with Project, or you can create a new map. Because the wizard walks you through the process, creating a new map is fairly easy to do.
Or, suppose that your company is planning to move into a new manufacturing facility, and the Finance department has placed the budget for the move in an Excel spreadsheet on your company's network. You, on the other hand, must manage the move and have created a project schedule that deals with moving all the equipment into your new manufacturing facility. You can place a hyperlink in the project schedule that connects to the Excel file that contains the budget for the move to the new facility. The hyperlink appears as an icon in the Indicator field of the Gantt Chart. When you open the project file and click that hyperlink, the Excel file appears so that you can check on the budgeted dollars for each aspect of the move.
If you just love the capabilities that PivotTable reports in Excel and Visio offer, you'll be glad to know that the same capability has come to Project 2007 in the form of Visual Reports. Pivot tables allow you to view data from a Generating a Visual Report is simplicity itself you simply choose a report, decide whether you want to generate it in Excel or Visio, and view or print the report. You need to know a couple of things before you create a Visual Report. First, to access Visual Reports, you have to have installed .NET Framework 2.0 from Microsoft (a free download) before you installed Project. Second, if you have a version of Excel or Visio that's earlier than 2007, you have to add .NET Programmability support. Visit the Microsoft Project page at www.office. microsoft.com project to get information about both products. If you want to customize a Visual Report, you need some knowledge of pivot tables in Excel or Visio. Because covering pivot tables in those products is beyond...
The three products used here to describe three software-based methods of schedule risk analysis are not the only ones available to perform such tasks. Readers seeking information on such products might also consider MS Project (Microsoft), which can operate on PERT calculations, using macros and or Excel. SuperProject (Computer Associates) provides actual probability analysis, based on three time estimates and standard deviation.
Project templates should be provided in a standard electronic format. Many companies use the most common word processing or spreadsheet packages, such as MS Word or MS Excel. The formatting of the templates usually undergoes a series of editing checks and technical reviews before distribution. Text sufficient to guide the user should be included on each project template. The PMO should also maintain and publish a separate master record index with a complete list of all project templates in circulation in the company. The master record index should contain the following information
We cannot solve for the IRR directly. The IRR can only be solved iteratively. For instance, for the present value factor tables, we find that at a discount of 24 , the NPV is slightly negative. At 23 meaning the IRR is between 23 and 24 . IRR can be solved in Excel using the IRR function th exact solution is 23.598 .
You can modify the Visual Reports templates or create your own templates by using the New Template and Edit Template buttons in the Visual Reports dialog box. Editing a template allows you to add or remove fields from it creating a new template involves specifying the format (Excel or Visio), choosing the data you want to report on, and selecting fields to include.
Project KickStart is designed to help you figure out your project strategy as you come up with a list of tasks. Planning icons remind you to map out the goals of your project and plan for obstacles. Libraries of typical goals and challenges make building them into your plan simple, and you can add your own specific company or industry phrases to the libraries. Links to Outlook, Word, and Excel also offer you some flexibility in sharing information among the Office family of products, which includes Project. Check out the free trial download and online demo to get an idea of what Project KickStart can do for your projects.
WIPP has over 100 work breakdown structure (WBS) elements. Until recently, the process of accumulating data related to scope, cost, and schedule for these elements was performed manually and has proven to be time consuming and labor intensive. Task leaders developed estimates in their individual formats (using tools like Microsoft Word, WordPerfect, Excel, etc.) budget specialists then input the submitted information into a database such as FoxPro.
The variation distributions for items in the estimate were converted to cost distributions by multiplying by the base cost values. The cost distributions were then added, to form a distribution for the total budget cost (Figure 21.4). The additions were performed using the Risk simulation package embedded in a standard Excel spreadsheet. The analysis indicates a very high likelihood (better than 99 ) of achieving the budget with contingency, and a 95 likelihood of achieving a cost less than 105.
Creating one-tier systems is still a large part of what Visual Basic programmers are doing. If you are building these systems, they should be built from components. An excellent example of this is the Microsoft Office suite of products. For the most part, Word, Excel and PowerPoint are one-tier applications.
The project life cycle was sponsored mainly by the COO who, in conjunction with the CFO, provided funding to all projects. Therefore, in order to receive project funding, projects needed to follow the life cycle. Prior to the life cycle, project funding did not have a standard method of being applied. The life cycle defined a project, and the associated steps from inception the establishment of the maintenance programs. Unlike many processes that depend on standard forms, this one provided guidelines. The process was not overly cumbersome and just made plain business sense. Therefore, a business case (one of the process steps) for a project could arguably be written on the back of a napkin, assuming it was an excellent case. Currently, the PMO tracks projects bi-weekly for schedule, cost, and risk management status in a simple Excel spreadsheet. The results are entered into a project inventory for analysis and prioritization by senior management. The PMO also provides regular training...
In every industry, there are a variety of factors that influence customer demand. Today, many or most of the customers in the various markets base their choice of product or service on several key factors. In order to compete effectively, your organization is already at least average in these key factors. Therefore, there is a very important implication. The factor that an organization will pick to excel with is often one that is not a key factor today, but has the potential to become a key factor. This is a factor that may be important to less than 10 of your current market, but will be the number one factor to a much greater percentage of the market in the future.
In their most recent meeting, the sales representatives were concerned about the response time of the new system. They have to wait an inordinate amount of time when placing orders. They also are concerned because the drop-down list on one of the screens does not contain the data they need. The managers agree that the response time issue seems to be a common problem throughout the sales organization. The sales report manager mentions that her biggest problem is having to create Excel spreadsheets to manipulate the format of the data contained in the canned reports.
Would your team rate your communications as frequent and thorough enough Did you share enough of your project with resources, or did you inundate them with too much information Did you send an entire Project file to people regularly when a simple report on a specific aspect of the project would have served you and them better Did your management team feel that your reporting on the project was sufficient for their needs Should you learn to take more advantage of other software, such as Excel and Visio, accessed through Visual Reports
Tracking System One popular method of tracking risks is to begin by having project team members submit their issues in a common centralized database. Although this may sound difficult to establish, one such database could be a simple Excel spreadsheet with various columns for the required information. Exhibit 2 contains sample fields that could be maintained in the database.
The effect on payback time concept is so simple that it can be done on the proverbial back of the envelope. I created a simple example in an Excel spreadsheet, in less than an hour. I am amazed that I rarely see anyone evaluating the effect of delays and cost overruns on return on investment. Yet, if we use the Standish data, such an evaluation would show that the typical project would, based on such performance, extend the payback time to more than twice the original plan. Of course, this is another example of downside potential. And in today's business environment, such bad news is more likely to be swept under the rug, rather than to have the project rejected because of the risk. It's that denial thing, again. Unfortunately, hiding the risk does not prevent it from happening.
The process by which a model is evaluated to produce outputs will depend on the technique selected to aggregate the individual sources of uncertainty into an overall view of risk. Simulation is the most commonly used technique, using Excel for spreadsheet modelling and packages such as Risk or Crystal Ball to perform the simulation.
The fifth key event in Microsofts organizational evolution was a 1989 retreat where top managers and developers grappled with how to reduce defects and proposed the idea of breaking a project into subprojects and milestones, which Publisher 1.0 did successfully in 1988. Another was to do daily builds of products, which several groups had done but without enforcing the goal of zero defects. These critical ideas would become the essence of the synch-and-stabilize process. Excel 3.0 (developed in 1989 and 1990) was the first Microsoft project that was large in size and a major revenue generator to use the new techniques, and it shipped only eleven days late MS, 36
Now you should be able to see where you excel and where you need to improve. But simply meeting the standards won't be sufficient as you progress through your career. Remember the opening line Good, Better, Best. You will want to increase the level of each subject area as you progress in your career. The next section of this chapter offers some insight into how and where you can do just that.
Unless your organization has invested in an enterprise project management application with an emphasis on project costing or you have advanced skills in project scheduling software, I recommend the use of spreadsheet software (such as Microsoft Excel) for your project budget. I favor the spreadsheet approach for three principal reasons
The Microsoft Project database can be viewed in Access or Excel 2003 if you open a project file saved in the proper format. When viewed in either Access or Excel, the Microsoft Project database has more tables than you would see in a project file that has been accessed using the Project OLE DB Provider. Nevertheless, whether you view project data through the Microsoft Project database or the Project OLE DB Provider, you're seeing almost exactly the same set of data.
Microsoft Excel .xls Saves the file as a Microsoft Excel workbook or as a workbook or Microsoft Excel PivotTable. The file can then be PivotTable opened in Microsoft Excel. A PivotTable is a table that combines and compares large amounts of data, and in which you can rotate columns and rows to modify the source data to create different views. You can also open an Excel file in Microsoft Project.
Automating a program such as Microsoft Project means that you control the program remotely, even invisibly, as if you were actually using the interface. This means, for example, that you can use the Spelling Checker feature of Microsoft Word in Microsoft Project or the math functions of Microsoft Excel from within Microsoft Project. In fact, you can even automate Microsoft Project from a Web page.
For example, you might want to provide a set of data from your project plan to a stakeholder who doesn't have access to Project 2003, but has another compatible application such as Microsoft Excel or Microsoft Word. Note Microsoft Project has particularly convenient and robust methods for exchanging information with Microsoft Excel and Microsoft Outlook. These methods are covered in their own chapters Chapter 17, Integrating Microsoft Project with Microsoft Excel, and Chapter 18, Integrating Microsoft Project with Microsoft Outlook.
They excel in at least two of the five key skill categories (Project Management Fundamentals, Business Management Skills, Technical Knowledge, Communication Skills, Leadership Skills) and are either good enough in the other categories or staff their teams to compensate for their deficiencies.
The second focus area of the Treacy-Wiersema model is product excellence or superiority. The objective is to be differentiated from competitors and create an ah-hah demand. Obviously, such demand can usually command a price premium. There must be a dedication to innovation, upgrade, and new ideas. Risk taking, at least in product and service development and delivery, is the norm. Naturally, this is a target-rich area for project managers, and the performance measures are typically market share, customer satisfaction, revenues, and profits. The third area is operational excellence. Internal processes, methods, and procedures are made as frictionless as possible. Repetition is exploited to reduce errors and minimize variance to the mean outcome. This area is taken quite broadly and would in most businesses encompass some of the innovation and learning goals from the balanced scorecard. A good example of operational excellence exists in the back-office billing and administration systems....
Suppose that you have an Excel worksheet that provides information that you need for a budgeting task. You can insert a graphic object that shows the worksheet data on the Notes tab of the Task Information dialog box for the budgeting task. Follow these steps to insert the graphic object of worksheet data 1. Select the chart in Excel, and click the Copy button to copy the chart to the clipboard.
Thus, kaihatsu development project organization tries to function by defining which section should bear responsibilities for Quality, Cost, and Delivery (schedule), the basic management items of a project. Here, the traditional functional division bears the responsibility for Quality, while the project manager is responsible for cost and schedule. As a project is a cross-organizational system, a project manager does not necessarily excel in specific skills. In the case of a kaihatsu-style project organization, the quality of certain techniques often greatly influences the success of the whole project. Thus, the division of roles for the project is defined by having a person in charge of a functional division take responsibility for quality, while having the project manager take responsibility for the overall project quality. Examples of this are development of new automobile models and electronic products like TV. By defining in this way the role sharing of QCD, the basic management...
If we had other opportunities to evaluate, the opportunity costs would be the difference in return factor 1.2597 is called the future value factor. Future value factors are distinguished by always I than 1. Future value factors are easily calculated in Excel using the power function or they ca finance books. Table 5-6 is an abridged collection of future value factors. In Excel, the power function can be used to calculate the Factor equation. There are two argi is (1 + discount rate), and the second is N._ In Excel, the power function can be used to calculate the denominator in the Factor equation in power the first is (1 + discount rate), and the second is N.
In developing their hardware and software systems, the Webcasting company chose a hardware supplier who competed on the supply side with their integration contractor. This situation required management diligence and help in understanding how to communicate project information through all parties. The integration contractor provided a schedule that described when parts would be needed, based on their integration schedule. The Webcasting company internally managed the P.O. process to get hardware on site in time for integration and at or below the budgeted price through the integration contractor. The Webcasting company began this process using a Microsoft Excel 2000 spreadsheet, listing all of the parts required, the budgeted cost, and supplier names in separate columns. As a result, several of the first integration parts were ordered too late to be on site in time to meet the integration schedule established by the integration contractor. To help them better manage the procurement...
Since each group was responsible for its own projects, several projects were active simultaneously and resources were spread thin across them. It was not unusual for one person to be working on 6 to 10 projects at a time. At one point, several weeks were spent surveying upper management to outline all open projects. At the end of this exercise, it was determined that there were 14 pages (Excel landscape) of projects being worked on by the organization. The organization tried to prioritize these projects and work on several projects, all designated as number one priorities. However, this still caused resource issues.
Give team members the chance to excel. Just because a team is imposed does not always mean its members are incapable of performing well. Give each team member the chance to do his or her best work you may be very pleased with the results. In some cases, an employee whose performance has been substandard or untried may be waiting for the opportunity you can offer through the project.
Jabe played this role on a product called Multiplan (later to become Microsoft Excel), and it worked. The engineering and development process improved along with the quality of coordination with the business team, and throughout the hallways at Microsoft there was much rejoicing. After many memos and meetings, most teams within the company slowly adopted the role. Say what you will, good or bad, about the resulting products, but the idea makes sense. By defining a role for a line-level generalist who was not a gofer or a lackey, but a leader and a driver, the dynamics of how development teams worked at Microsoft changed forever. This role of program manager was what I did through most of my career at Microsoft, and I worked on product teams that included Internet Explorer, MSN, and Windows. Eventually, I even managed teams of people who played this role.
For more information about using OLE DB, see Microsoft Access and Microsoft Office Excel 2003 online Help. You might also find Microsoft OLE DB 2.0 Programmer's Reference and Data Access SDK helpful. You can also visit www.msdn.microsoft.com. In the Search MSDN box, enter Choosing Your Data Access Strategy and then click Go. Under Technical Resources, click Choosing Your Data Access Strategy (OLE DB Technical Articles).
The Excel spreadsheet analysis is shown in Table 17.4.3, where the first five rows are designated as the input fields. Then column C in both the income statement and the cash flow statement is reserved for entering the specific inflation rate for the item listed in that row. Since the O&M costs are responsive to inflation, we can automate the cell entries by using the following cell formulas TABLE 17.4.3 Excel's Spreadsheet Application to What If Questions What General Inflation Rate Does the Project Break Even (Example 17.15) TABLE 17.4.3 Excel's Spreadsheet Application to What If Questions What General Inflation Rate Does the Project Break Even (Example 17.15)
Importing existing task lists You can create projects from tasks that you've set up in Outlook, or you can use Excel to start your project and then easily import the spreadsheet into Project. See Chapter 3 for more information about starting projects in Outlook and Excel and then moving them into Project 2003. See Chapter 22 for more information on exchanging task information with Outlook.
The saying goes A picture is worth a thousand words. And when looking at earned value information, you may find it easier to understand the information if you use a picture rather than study Project's earned value tables. You can export the earned value information to Microsoft Excel (you must be using Excel version 5.0 or later) and then use Excel's Chart Wizard to create charts of earned value information. When you export earned values from Project to Excel, you create an Excel workbook that contains a task ID, a name, and the various earned values for each task, as shown in Figure 14-9. Figure 14-9 An Excel workbook that was created by exporting earned value information from Project to Excel. To create an Excel workbook like the one shown in Figure 14-9, follow these steps 1. In the Project file containing the information that you want to use in Excel, choose File O Save As to open the Save As dialog box. 2. Type a name for the Excel workbook in the File Name list box. Don't worry...
In the early stages of software project management, the best programmers were promoted to the role of project manager because they demonstrated competence with the tools (programming languages, compilers, etc.) and often displayed knowledge of a domain, such as a scientific, business, or real-time application. They frequently did not succeed in this position because they were unprepared for situations outside of the technical realm. Now we know that every software manager needs skills far beyond knowing how to code. A working knowledge of software engineering is necessary to succeed, but a good software manager needs to excel in people and project management skills, too.
A convenient way of creating models to solve linear programming problems is to use the What's Best add-in for Microsoft Excel. A demo version of What's Best is available from Lindo Systems. After installation, the What's Best toolbar becomes available in Excel (Fig. 1.1). Decision, uncontrolled, or endogenous variables are called adjustable cells in What's Best . The value of the objective function is called the best cell. A linear programming model can be constructed by transcribing the standard LP form described by Equation (1.1) and its restrictions into Excel, as shown in Figure 1.2. We will use this representation throughout the book. Figure 1.2 The standard LP form represented in Excel with What's Best
Excel PivotTables can be interesting and useful when you want to analyze Project earned value data. The PivotTable is an interactive table that summarizes large amounts of data in a cross-tabular format. When you use Project to create a PivotTable in Excel, you get two PivotTables in the same workbook a Task PivotTable and a Resource PivotTable. The Task PivotTable shows resources, tasks to which the resources are assigned, and costs for the resource per task. The Resource PivotTable summarizes resources by showing work that is assigned to each resource and the total cost of each resource. In addition to the PivotTable worksheets, the same Excel workbook also includes two worksheets Tasks and Resources that Excel uses to create these two PivotTables. To export Project information to create PivotTables in Excel, follow these steps 3. Type a name for the Excel workbook that you want to create in the File name box. Don't worry about the extension Project supplies it. 4. Select Microsoft...
In his book Introduction to Simulation and Risk Analysis, James R. Evans uses the Excel spreadsheet as a teaching and operational tool to illustrate simulation modeling concepts and analysis of results. Excel is a tool common to almost every office software set and can be used in direct support of your project.
Note This process uses the Export Wizard and closely resembles exporting information * ' to Excel or Access. 7. Select the options for the export map, and click Next. You see one of the mapping dialog boxes. The one(s) that you see depends on the information that you chose to export. Refer to the sections Sending Project data to Excel and Sending Project data to an Access database, earlier in this chapter, for details on completing this dialog box.
Exploratcry pro ce sses re quirw adoptive people wnd acra ptive organ izations. There are indivlduals who excel in production environments, those who strive for repeatability and precision through the use of reocr iptive p rocessex and performance measures. Every organization requires production processes for a portion of its operations. But every organization also needs exploration processes, those that excel in delivering new products, new services, and new internal busi ness niti ativesi Un fortunately, the project Aultures and management controls for exploration and productio n are ursally a t odds wi th xcth o ther, pausing organizational schizophrenia. Great organitatlons will fln d a way to deal w tn aoth pxxIo ration and production processes. Others will languish behind.
Project contains a wizard that helps you chart timescaled earned value data. You can use the Analyze Timescaled Data Wizard to automatically create a chart in Microsoft Excel of earned value information for the entire project. After you complete the wizard, the resulting chart looks similar to that shown in Figure 14-17. Figure 14-17 A chart of earned values that was created in Excel by using the Analyze Timescaled Data Wizard in Project. Figure 14-17 A chart of earned values that was created in Excel by using the Analyze Timescaled Data Wizard in Project. Note You may need to click the More Buttons button at the right end of the toolbar to * ' see and click the Analyze Timescaled Data in Excel button. 3. Click the Analyze Timescaled Data in Excel button. Project opens the first of five Analyze Timescaled Data Wizard dialog boxes, as shown in Figure 14-19. Figure 14-20 Select fields to export to Excel. Figure 14-20 Select fields to export to Excel. 8. Click Export Data to export the...
The risk analysis package Risk provides a convenient way of performing quantitative risk analysis calculations. Risk is a simulation package that embeds within a standard Excel spreadsheet. Risk has several useful features. As an example, Figure 20.9 shows how the distribution for valve supply, Element 126.96.36.199 in the irrigation case study, was represented in an Excel spreadsheet using Risk. The element valve supply has a base cost estimate of 300 000. The Risk function RiskTriang was used in cell D17 to represent a triangular variation distribution, with a most likely value at the estimate and a range from 0.75 to 1.25, i.e. a range of plus or minus 25 specified in cell C5 named VAR. The expected value of the cost distribution is displayed on the screen in cell D17. The distribution for the valve supply cost is in cell E17.
In both Microsoft Project and Excel, when you open the file that contains links, by default you will see a dialog box prompting you to update the file using the link (see Figure 17-15). In Excel, if you do not want to see this alert each time you open the workbook, click Tools, Options and then click the Edit tab. Clear the Ask To Update Automatic Links check box.
Note Because Access tables are conceptually the same as Excel worksheets both con sist of data that is organized in rows and columns you will notice that the process for exporting Project information to Access sounds suspiciously like the process for exporting Project information to Excel. And you are right
Team building starts with project planning and doesn't stop until the project is completed. It involves employing techniques to improve your team's performance and in keeping team members motivated. Motivation helps people work more efficiently and produce better results. If clear expectations, clear procedures, and the right motivational tools are used, project teams will excel.
This book is designed for intermediate to advanced computer users who manage projects. Even if you have never used Microsoft Project or managed a project before, this book assumes you have experience with Microsoft Windows and at least a couple of programs in Microsoft Office for example, Microsoft Word, Microsoft Excel, or Microsoft Outlook. Part 4 Reporting and Analyzing Project Information Microsoft Project provides a wide range of options for setting up and printing views and reports. This part outlines these methods from simply printing your current view to designing a custom report and publishing it to the Web. This part also describes how you can export data to Excel for calculation and other analysis, as well as how you can use earned value data to analyze progress and costs. Part 6 Integrating Microsoft Project with Other Programs Microsoft Project is designed to work seamlessly with other programs. You can copy, embed, link, hyperlink, import, and export information. This...
If you've ever used Microsoft Excel or one of the other popular spreadsheet programs, you already know how to enter and edit information in Project. When you begin typing in a cell, the insertion point appears in the cell to the right of any text that you enter. To edit text in a cell, click once to select the cell and then press F2 or click a second time at the location in the cell where you want to begin editing. If you press F2, the insertion point appears at the right edge of the text in the cell. If you click a second time, the insertion point appears in the cell at the location where you clicked the second time. As you enter information into a cell, the information also appears in the Entry bar, which runs along the top of the screen directly under the Formatting toolbar. The Entry bar in Project serves the same purpose as the Entry bar in Excel. You can type new text or edit existing text by clicking anywhere within the text in the Entry bar. Two buttons on the left of the bar...
When you break a link, the information remains in the project file as a separate embedded object. You can still view and edit the Excel information it's simply no longer linked. To review and work with links in Excel, follow these steps 1 In Excel, open the workbook containing the links. Figure 17-16. Use the Edit Links dialog box in Excel to review and update the links in the current workbook. Figure 17-16. Use the Edit Links dialog box in Excel to review and update the links in the current workbook.
If you want to save changes to a previously saved Project file by using a different filename or file format (for example saving as an Excel spreadsheet or a Project template), choose FileOSave As and provide a new filename or file type at the bottom of the Save As dialog box.
This is yet another useful method for comparing the financial advantages of alternative systems using the cash flow diagram. We calculate that specific rate of interest for the system that makes the net present value equal to zero. This rate is called the rate of return (ROR) and is denoted by i*. If this rate is higher than the minimum rate that satisfies the investor or the project manager, then the project is acceptable. This minimum rate is called the Minimum Acceptable Rate of Return (MARR). There is no mathematical formula for calculating MARR. This has to be done by trial and error. Fortunately, there are computer programs that make this calculation simple and fast. Most of the spreadsheets on the market, such as Quattro Pro, Excel, etc., have provisions for calculating the rate of return.
You might also consider exporting your Project information to a program such as Excel so you can use analysis tools, such as pivot tables, to figure out what's going on. If that idea excites you, you might want to check out Excel Data Analysis For Dummies by Steven Nelson.
So when we say you need to match the columns of information from Excel to Microsoft Project, what we're really saying is that you need to match the data types. The data type specifies the kind of information that can be stored in a field for example, text, number, or percentage. The data type also specifies the format that information can take for example, 14d for a duration field, 6 29 04 for a date field, or 35.90 for a currency field.
In the years since my first Scrum project, I have used Scrum on commercial products, software for internal use, consulting projects, projects with ISO 9001 requirements, and others. Each of these projects was unique, but what they had in common was urgency and criticality. Scrum excels on urgent projects that are critical to an organization. Scrum excels when requirements are unknown, unknowable, or changing. Scrum excels by helping teams excel. This book clearly reflects Ken's experience mentoring Scrum Teams and teaching Certified ScrumMaster courses around the world. Through the many stories in this book, Ken shares with us dozens of the lessons he's learned. This book is an excellent guide for anyone looking to improve how he or she delivers software, and I recommend it highly.
The Global File is also called the Global template file, and it acts like the Normal template in Word or the Bookl template in Excel. Any customized features (such as macros, toolbars, or menus) that you store in the Global File are available to any project file. On the other hand, customized features that you store in an individual project file are available only to that file.
For example, if a critical daily report is currently generated using an Excel spreadsheet by cutting and pasting information from one sheet to the next, a goal may be to create a Visual Basic application that allows the information to be inputted and formatted without cutting and pasting. If we analyze this goal we can see that there is a very low risk here. It will improve the accuracy and the speed to produce the critical report, the person entering the information will be the one affected by having a more efficient way of producing the report, and it will decrease the amount of effort by eliminating a repetitive task (cutting and pasting) and replacing it with an automated process.
Copying and pasting information is one of the easiest ways to get information from Excel to Microsoft Project. However, to paste successfully, the columns of Excel information must match the Microsoft Project columns into which you're pasting. For example, suppose that your Excel worksheet contains four columns you want to copy to your project plan Task Name, Resource, Duration, and Fixed Cost. Add, remove, and rearrange columns in a Microsoft Project task table so that these four columns appear next to each other (see Figure 17-1). Source Excel data to be copied Target Microsoft Project columns matching the incoming Excel data Source Excel data to be copied Target Microsoft Project columns matching the incoming Excel data Figure 17-1. Make sure that the order and type of Microsoft Project columns match the Excel data being copied and pasted. Figure 17-1. Make sure that the order and type of Microsoft Project columns match the Excel data being copied and pasted. The following is a...
The solution that Ray and Neil produced was a collection of menus and toolbars that managed the process for updating and reporting in Microsoft Project and the actual analysis or math in Excel. In other words, updated Project data was mapped to Excel, where formulas that calculate SPI, supplied by the company, are applied to the data. Finally, the data calculated in Excel is brought back into Project, updating the project for the company's reporting criteria.
The key outputs are the Project Schedule and, eventually, the Schedule Baseline. This can be implemented in the form of a complex Gantt chart (MS Project, Primavera's SureTrak, or some other graphic Gantt tool can be used) or as basic as an Excel spreadsheet. The Gantt software tools give you the option to set up various types of charts in their environments
As the field of alternatives narrows, there is one new responsibility for the project manager the open-issues list. An open issue is anything that needs to be decided or figured out but hasn't happened yet. It's essentially a list of questions, and it should encompass anything that needs to be done, prioritized by its potential impact on engineering. The form of this list isn't as important as the quality of issues listed and the diligence of the person driving to resolve them. I've used a designated spot on a whiteboard or Excel spreadsheets for this, and I can't say that the tool I chose made much of a difference either way. I don't think these lists need to be controlled or managed like source code (that is, unless the politics or culture of your organization make it worthwhile) the simpler the tool, the better it is.