Author: Vadim Gerya, PMP

Physical Volumes for MS Project Tasks

Many projects require task scheduling in terms of physical volumes (i.e. material resources in MS Project like ten meters, five tons, twenty items, etc.). Unfortunately, Microsoft Project does not have such feature on the task level. Some years ago, I worked on a project where we decided to correct that with the development of a Construction Project add-on. In this article, I would like to present the approach used and later released. Model Highlights Suppose we have Task1 with a physical volume of 100 kg and a duration of five days. Several ‘work’ and ‘material’ resources assigned to the task are shown below. Note that MS Project does not originally support time phased data on the task level (20 kg per day on above figure), but if we can think about task Performance (how much kg per day we achieve for Task1) and Consumption for assigned resources (how much meters per kg is required for Resource 1), we get a better idea of the scenario. Those parameters are not only use in construction, but widely used in practice. Advanced Cost Management Having task physical volume (FV as abbreviation from Full Volume), we can consider ItemCost and Cost, where Cost = FV * ItemCost. Thus, we can calculate task ItemCost based on the cost of assigned resources and/or enter it manually without considering cost of resources. Task ItemCost is also a commonly used term in the industry. It is best practice to have two costs: IN and OUT. IN for cost and OUT for price. OUT is used when you have an external project your customer pays for. Both IN and OUT have to be time phased as an original MS Project ‘Cost’ field (our Construction Project add-on reserves Baseline4-Baseline10 to organize cost time phased data for IN-OUT Costs, Actual IN-OUT Costs, and others). This model binds each ‘construction’ task (C-Task) with a ‘Contractor’ to provide operational and finance analytics of ‘who does what, when, how much it costs, etc.’. It also marks special CP-Tasks (Text29 task field used) and does not touch any other MS Project tasks. User Interface (UI) To make the add-on developed more user friendly, we designed pop-ups to edit and manage CP-Tasks. Activated from the right-click context menus or from the ribbon menus, they allow one to easily manipulate project data and get a target schedule faster. The C-Task wizard window looks like this: No deep MS Project knowledge is required to start scheduling in the wizard. Most of the parameters are intuitive, and when you change one, the others are correspondingly changed and highlighted allowing you to catch logic without diving into documentation. Changes are automatically reflected in the MS Project schedule. For example, if you change Performance for CTask1 (above) from 1 itm/d to 2 itm/d, the duration decreased from ten to five days. If you change FV from 10 itm to 20 itm, the duration doubles to 20 days and FV for each assigned resource of CTask1 is also doubled. Key Features Summary The following features are where we ended up, and we’ve developed the add-on for others to try. Links are included, as well. Task full physical volumes (FV) are distributed over time (time phased). Thus, a question like, “How much m2 of CTask2 (above) has to be done from Date1 to Date2?” can be easy answered. The same for all tasks and any kind of value (i.e. planned, baselined, actual, and remaining). Advanced cost management is made simpler as task cost is calculated by multiplying ItemCost by physical volume (FV). ItemCost can be entered manually or calculated based on cost of resources assigned to task. There are two type of task cost supported: Cost IN (what we buy) and Cost OUT (price we sell for). Cost OUT can be used for ‘external’ projects. It’s easy to use, as no extensive MS Project knowledge is required. CP-tasks wizards handle all internal calculation and output user-friendly and structured results. In addition, there are other special tasks accompanying C-tasks to cover project needs. F-tasks are used to manage payments and design a payment schedule based on C-task progress. P-tasks are designed to handle procurement of goods and services needed for C-tasks to perform effectively. The best way to catch CP-Task features is to install the Construction Project add-on from link below, load the demo project, and play with it. You can easily create new C-project with CP-Tasks, and I’d love your feedback in the comments below. Install the Construction Project add-on from You can easy deactivate it in MS Project, by going to File->Options->AddIns dialog or uninstalling it from the systems Program menu). Select Load Demo-project when Construction Project starts for the first time, or click the ‘Start Page’ tab and select Load Demo-project. To create a new project, click ‘New Project’ on the ‘CP-Construction’ tab. You can also convert an already running project to C-Project to turn on CP-Task functionality by clicking on ‘Convert to C-Project’. Creating New or Converting to C-Project adds views and custom fields optimized to work with CP-tasks and Construction Project. To open CP-Task wizard, – right click on task and select ‘Construction Task’ from drop down list.  5. Check the Help Center to read more about CP-task features. To make it easy for you, I’ve included the links again below. I’d love to know what you think if you utilize this CP-Task features -on. Construction Project installation – Construction Project technical docs – C-Task usage and details –

Advanced Excel and PowerBI Reporting for MS Project

Often project data is requested for stakeholders who do not have MS Project installed or are not experienced enough to work with it. MS Project reporting to MS Excel or other visualization software is limited. An export to MS Excel allows for the generation of only plain tables of project tasks, resources, and assignments. This can be hard for users when they wish to analyze project progress, status, forecast, etc. Visual Reports on the Report tab are closer to analytical needs; however, their customization is not easy, and the resulting Pivot Tables in MS Excel lack full functionality (internal structures with initial ‘local cube’ data are required to make a MS Excel Pivot Tables work properly). To address this problem, I’d like to bring to your attention the reporting engine of a Construction Project add-on (CP), which was designed to help MS Project users to get comfortable and ‘self-sufficient’ generating MS Excel and PowerBI reports. It is free and can be used for any project. CP reporting generates MS Excel and PowerBI files with customizable MS Excel Pivot Tables or PowerBI visualizations based on active project data. CP’s reporting engine follows the below process: A user uploads selected MS Project fields as row data to hidden ‘RawData’ sheet of a specially designed MS Excel template. The filled MS Excel template generates a PowerPivot model to produce all required ‘good-looking’ dimensions and measures from row data loaded from active project. Dimensions and measures are available for selection from the PivotTable fields when you insert either a PivotTable or a PivotChart into the MS Excel worksheet. Default reports contain many of the measures and dimensions commonly used in projects (Tasks, Resources, Time, Costs, Work, Duration, %Complete, Baselines etc.), and it is also possible to add new ones based on any MS Project custom fields uploaded. There is no hidden code used; everything can be viewed and modified in the PowerPivot model. PowerBI reports are generated in a similar way, but do not pull data from MS Project directly (a MS Excel report is used). PowerBI reporting opens a PowerBI template which has been specified in report settings, changes the data source to the generated MS Excel report, and then refreshes data after opening. To utilize CP reporting, you can easily install the add-on and load the ‘Demo Project’ on the ‘CP-Construction’ tab to setup default report templates. Open your project and generate a report based on the default template by clicking on the ‘Create’ button in the ‘CP-Tools’ tab and selecting ‘Report’ from list. After opening the MS Excel report, you can modify it according to your needs and use it as new template in the future. This is done by setting up a template in report configuration. Check details here. To make it easy for you, I’ve included the links again below. I’d love to know what you think if you utilize this add-on. Construction Project installation – CP Reports usage –