Construction

Tips from the Field: Speed Up Your Excel Workflow

Tips from the Field | December 20, 2023

Excel is probably the most heavily used software program in every industry, including construction. Across every discipline and every stakeholder, Excel is king. Over the years of being in the construction field and delivering Excel-based solutions such as Cost Reports and Project Monthly Reports to clients, I found some tips to help speed up your workflow.

Want to copy data without copying the formula?

If you want to copy the values in fields and not the formulas, then there is a quick way of doing this. Everyone is familiar with copy and paste. But if you select your data range, click copy, paste special, and choose values, then you only paste the data, not the formulas.

How can you avoid re-typing values from columns to rows?

If you want to copy rows and make the values display as columns or vice versa, select your data range, click copy, paste special, and check the transpose box

Looking for a simple way to look up a set of values in your worksheet?

You can insert data validation into your worksheet if you want users to pick from a list of pre-defined values. Start by creating a list of the values to choose from. Then click on a cell where you want users to select the value. From the Data tab, click on Data Validation, select List under Allow, and highlight the list of values you created for the Source. When a user clicks in the cell with the data validation, they will have to choose from the pre-defined values you defined.

There are many more of these tips in Excel that can save you time and frustration. If this article is helpful and you would like to see more Excel tips, please like and share. Also, if you have any tips and tricks, please add to the comments and help others in the community.

Lydon Solutions is an IT solutions provider in the construction industry specializing in Microsoft Azure, 365, Power Platform, SharePoint, and SQL. If you need help with your Microsoft products, you can reach out here for a free one-hour consultation.

Keeping it Simple: Cost Report Using Microsoft 365 Lists

How-To | October 3, 2023

Many construction organizations have transitioned to Microsoft 365 but might not leverage all the applications included in their subscription. Microsoft Lists is an often overlooked application that can provide unique features for tracking project deliverables such as issues, asset management, action items, and even a simple cost report. Read on to learn how to build a simple cost report using Microsoft 365 Lists.

What is Microsoft 365 Lists?

Lists is a Microsoft 365 application that allows you to create a configurable data grid, with multiple views, that can be shareable with your team members. Lists is a modern application that stores its data behind the scenes in good old-fashioned SharePoint lists. Within Lists, you can view all the lists from all the SharePoint sites you can access. The benefit of having the separate Lists application is that users don’t have to go to every SharePoint site to create or find the needed list.

Let’s create a cost report!

Let’s create a simple cost report list to demonstrate how easy Lists is to use.

Navigate to the Lists app in Office.com, click New List, and select a Blank List.

You can name your list “CostReport” and add some basic theming.

Select where to save the list. You can save your list to an existing SharePoint site or My Lists, which is saved to a SharePoint site that is automatically created for you, and you can get to My List directly through OneDrive. For this exercise, select My Lists to save your data. Note: If you intend to extend the functionality of this simple cost report, I recommend choosing a SharePoint site to save your data instead of My Lists.

Create Columns

Once you create the list, you can begin creating columns. Note: The title field is automatically created. Consider using cost code/WBS name as the title.

For your cost report’s actual cost code field, you could create a column and name it CostCode. Select “choice” as the field type and add each corresponding WBS/cost code identifier choice.

For each financial column of your cost report, create currency columns:

To calculate fields such as budget or forecast variance, create calculated columns. Calculated columns are not readily apparent from within the Lists “create a column” menu. You have to select “see all column types” and click next, which then navigates you to a SharePoint list settings create column menu

From the SharePoint list settings “create column” menu, select calculated for the “type of information in this column” and select currency as the “data type returned from this formula.”

Formulas allow you to select columns that you have previously created in your list and add them to a calculated column. Here is a list of common formulas you can use for a calculated column.

You are going to want to create your variance columns using calculated fields.

Once you create all the columns you need to track your project costs, click New to add records for each row of your cost report.

From the SharePoint list settings “create column” menu, select calculated for the “type of information in this column” and select currency as the “data type returned from this formula.”

While every cost report for every project can be different, we created a simple cost report so you understand how it can be built and customized.

Sample of a simple cost report:

Title = WBS/code description text field
CostCode = choice field
Budget, commitment, spends, and forecast are currency fields
ForecastVariance and BudgetVariance are calculated columns
- ForecastVariance = PreviousForecast-Forecast
- BudgetVariance=Budget-Forecast

Going outside the box

If you are looking for something a bit more complex than a simple cost report using Lists, you could extend the functionality further by incorporating other Microsoft 365 applications into your solution. For example:

Integration from your financial systems for commitments and spends using Power Apps and Power Automate workflows.
Standardized WBS/cost codes across cost reports using SharePoint site columns for consistent reporting.
Build Power BI reports for slicing and dicing the cost report data.
Automatically save cost reports by period and compare variances between periods using Power Automate to copy the list and update the PreviousForecast column.

We hope this simple cost report exercise provides an easy way to spin up a straightforward tracking tool for your project. If you need help customizing Lists to your specific project needs or need help with Microsoft 365, you can contact us for a free consultation.

If you need a more robust solution for Microsoft 365, you can request a demo of Construction Viz, our enterprise construction management solution that can be deployed to your Microsoft 365 tenant.

Visualize Your Project Data with Microsoft Lists Calendar View (constructionviz.com)

Protect Remote Workers from Cyberattacks with Microsoft 365 and Endpoint Manager

How-To | December 15, 2021

The ongoing pandemic has forced us all to rethink how we work and interact with each other and the information assets held within our organizations. The increase in remote working elevates the likelihood of security threats, potentially putting our information at risk.

Now is the time to evaluate your infrastructure and security, not only for the immediate threat but also to plan for what comes next.

Better Remote Worker Security with Microsoft Endpoint Manager

At Lydon Solutions, we have been a virtual organization right from the start. At the moment, of course, we have increased the number of staff working from home, and we have been able to do so seamlessly by using services from Microsoft 365 and Azure because we do not have an on-premise infrastructure. We use Microsoft 365 for email and collaboration (SharePoint, Teams, etc.) and use Microsoft Azure Endpoint Manager (previously called InTune) to manage all the devices our employees use.

Endpoint Manager, a cloud-based enterprise mobility management service from Microsoft, ensures that your employees' devices and applications access your company information securely.

Here are some of the ways we use Endpoint Manager at Lydon Solutions to protect our information and systems:

Multi-factor security. We require employees to enroll all devices in Endpoint Manager and use multi-factor authentication before allowing access to company information.
Asset management. We provision and manage all company-issued laptops with Endpoint Manager, automatically configuring all devices with our organization settings and ensuring they are compliant with our security policy requirements.
BYOD device management. Our employees are free to use their devices of choice, and with Endpoint Manager, we can specifically target only the apps that contain (or access) company information, such as Office.
Policies. Using public computers can also be risky. So, to ensure that our employees are not accessing company information from these devices and potentially leaving sensitive information behind, we create policies not to allow access.

As part of our Microsoft 365 managed services, we can set up your Microsoft 365 tenant and implement Endpoint Manager so that your company can manage projects effectively and your information and assets are protected.

Find out more about our services and how we can help you in your goal of secure remote working by scheduling a no-obligation consultation and demo.

Microsoft Power Automate Construction Use Cases

Case Study | April 27, 2021

Here are a few examples of how we delivered real-world efficiencies for our construction industry clients using Microsoft Power Automate.

If you have been following our posts, you will know that we are big fans of the Microsoft Power Platform and, in particular, Power Automate, Microsoft's modern take on workflows. Available in Microsoft 365 with E and M licenses or separately if you need to access premium connectors, Power Automate provides a quick way to take advantage of workflows to improve processes predictability and business efficiencies for your organization.

Construction Use Cases for Power Automate

Microsoft provides a gallery of Power Automate workflow templates, which can be used for simple business scenarios or as a starting point for more complex solutions. We have helped numerous construction industry clients achieve their goals by leveraging Power Automate. Below are a few use cases of Power Automate for construction.

Document Review

There are several ways to create document approval workflows in Power Automate. For example, you could leverage the built-in approval actions to send approval requests via email or SMS to a mobile phone where the user clicks on a link to perform the approval.

One of our clients needed their sub-contractors to approve and respond to document changes without having access to the company's project management information system (PMIS). We leveraged Microsoft's Adaptive Cards technology to allow recipients to approve document modifications without leaving the email they received with the notification for action. Handling approvals entirely within emails was more convenient for the approver and helped improve engagement while increasing the turnaround time for feedback since users didn't have to learn a new system. A bonus was that the client didn't have to purchase additional licenses for their sub-contractors.

For another client, we used Power Automate to generate a daily digest of new and updated documents from each program's projects for PMs to review and provide feedback. This workflow also sent a weekly digest of projects that were closing out and attached a PDF document binder (also created by the workflow) of all the project documents as an attachment to the email. In each case, the body of the email contained project-specific information extracted from the project sites.

Project Safety

One of our clients needed to log when personnel entered and left construction sites to allow them to know who was on site quickly should there be an incident. To meet this requirement with Power Automate, our client's users registered their mobile phones with the new workflow we created. Then we leveraged Power Automate's geofencing features to trigger updates to a SharePoint list on the movement of personnel around construction sites.

For another client, we created a workflow to monitor data from weather stations located at the construction site. The workflow recorded the real-time weather at the site and sent alerts in the event of inclement conditions. Another workflow monitored data from a weather service and processed weather forecasts for construction sites to allow project planners to schedule or reschedule work based on forecast local conditions.

Project Automation

We built a workflow for a client to automate the request, approval, and provisioning of Microsoft Teams sites from a predefined set of templated sites. Users would complete a form to request a new team site, specifying the owner, users, the template to use for configuration, and if it needed to be private or public. The workflow would route a request to the project manager for approval. If approved, the team site would be provisioned and configured, and an email sent confirming its approval and creation to the requestor. If the project manager denied the request, the workflow sent a notification to the user with the reason.

For another client, we created a workflow to allow users to work cost reports and budgets in Excel without changing their processes rather than forcing them to log in to the PMIS to update project financial information. The workflow reads data from the Excel workbooks and sheets and updates the corresponding SharePoint lists in the PMIS. Additionally, if someone updates the values in the PMIS, they can optionally be written back to the master Excel file.

Take Advantage of Power Automate for Your Construction Projects

The above are just a few examples of how we have helped our clients use Microsoft's Power Automate to achieve efficiencies with repeatable processes that provide predictability and ensure accuracy. There are so many more. Make sure you explore what Power Automate can do for your construction projects.

And if you would like some help, we have several construction-oriented Power Automate flows and components to jump-start workflows for your projects. You can request a free one-hour consultation here.

Microsoft Power BI and Paginated Reports for Construction

Microsoft News | August 24, 2020

As your construction organization moves to Microsoft 365, the question of how to report on all your data becomes critical. Microsoft 365’s de facto reporting solution is Power BI. It is the one-stop-shop for reporting on data generated in Microsoft’s expansive cloud platform as well as external applications.

Microsoft Power BI is known as a robust dashboard report builder. It can connect to many different data sets and applications to build stunning dashboards and KPIs that can be displayed on any device. Microsoft continually enhances Power BI with functionality and integration capabilities and puts that power in the hands of the users. This helps to make it a prime reporting solution on the market today.

While Power BI can publish SQL Server paginated reports, it did not provide the option to create paginated reports until recently when Microsoft released a new desktop Power BI Report application.

So, What are Paginated Reports and How Would I Use Them in Construction?

Paginated reports can be extensively formatted, can fit one or more pages, and are designed to be easily printed. In construction, they are great for printing a formatted form (eg. Submittal Form):

Or data logs with multiple pages (eg. Submittal Log):

Get Started with Power BI and Create Paginated Reports

To get started with creating paginated reports in Power BI, you will need a Power BI Premium license and you must download and install the Power BI Report Builder software. Please be aware that Power BI Premium is expensive. Before you consider Power BI for paginated reports, we recommend reaching out to Microsoft to understand licensing requirements. There are also some settings required in your Power BI service that will be needed. We would recommend doing an internet search on Microsoft Power BI Paginated Reports for step by step instructions as these might change.

See Microsoft’s FAQs for Power BI Paginated Reports for more information.

Lastly, to build effective reports, you will need to have a good understanding of where your information (ie. data sets) resides in your topography. We would recommend that you start mapping out your data sets sooner rather than later to ensure power users all create reports using the same structured data.

Get Reporting Expertise from Lydon Solutions

Having the ability to create paginated reports in Power BI is a great feature. Lydon Solutions has been building SQL Server Reports (SSRS) and Power BI reports for construction for many years. If you have any questions or need help building SQL Server or Power BI reports, you can get a free consultation to talk with our team of experts. If you need an enterprise construction management PMIS that can be deployed into Microsoft 365, complete with reporting, or get a demo of our easy-to-use project management software solution, Construction Viz.

Bots and Project Management Information Systems (PMIS)

Reviews | June 21, 2016

How bots and machine learning could impact construction project management

Bots are a hot topic right now. Tech giants from Google, Facebook and Microsoft and automakers like Toyota are betting big on bots as a critical cutting-edge technology. And they aren’t alone. Thought leaders and entrepreneurs in virtually every industry and sector are exploring bot technology.

If you haven’t been following the hype, a bot is an application of artificial intelligence. Basically, it is a computer program that fetches and analyzes data. Some of these bots use machine learning to spot patterns or understand how humans perform tasks and then mimic – and enhance – that behavior.

We’re already starting to see some of these bots popping up for ordering food, booking flights, or as general personal digital assistants. Most of us see these innovations and say “wow, that’s pretty cool” and move on. But, this is a much bigger transformative technology that is evolving that will reinvent the workplace and re-define the way people work – for better or worse.

Let’s have some fun thinking about the possibilities bot technology could create for business in general and construction project management in particular.

Why I’m All in On The Bot Bandwagon

Here’s why: we’re all being asked to do more with less to stay competitive in business today. At the same time, we must keep quality high. I believe bots and machine learning will help all of us be more agile, efficient and consistent.

Businesses have transitioned to the internet and for that matter the “cloud.” So all of your data to manage and operate a business is created and transacted online. Now imagine if we could apply bot technology to the various aspects of our business – finance, accounting, manufacturing, management, etc. – and program the bots to learn from our best practices. They could use machine learning to understand how we work, analyze problems, make decisions, and ultimately get things done. So in a way they would become an extension of you – allowing everyone in your company to do more with an army of virtual assistants.

Take it a step further. What if you could buy bots that had “learned” from the masters like Warren Buffet for financial investing, or Bill Gates for technology leadership, and so on. Now your company is drawing on the best of the best in the industry.

Construction Project Management Bots

Now let’s apply this to construction. Bots could transform the way we manage projects. As everyone knows, there are way too many data points to consider when managing a construction project. There is not enough time in the day or resources to monitor all of them and their interrelationships.
But what if you were able to set up bots that could analyze all of your project data to spot patterns, find anomalies and make correlations. The bots could crunch mountains of data for you, aiding in your decision making and even taking corrective action.

For example, say you have a project risk that is added to your risk register. That risk could have an impact to your schedule, cost forecast, contingency, scope, change management, and ultimately team communication.

What if a bot could perform multiple scenarios in real time and suggest the best mitigation plan based on your company’s priorities and best practices? Bots could do all of the detailed analysis across scope, schedule, and budget and surface that information into a mobile device while you walked to over to the planning meeting.

Or consider scheduling. So many factors go into keeping your projects on time and on budget. Imagine if your team was assisted by a scheduling bot that was trained to understand scheduling concepts like schedule crashing and critical path. The bot could generate actionable reports, perform TIAs and make recommendations. You and your team could then make better, more informed decisions based the most accurate data analysis possible.

This all sounds great, but how do we get there?

I hope your mind is racing with some of the questions that will have to be answered like:

How could bots be assimilated into a PMIS to collect all of your data and analyze it?
What would the new role of a project team be? Will the focus change from reporting and analysis to data collection and decision-making?
Can you teach a bot to learn all of the work experiences one would encounter?
How will bots and people work together in the workplace of the future? Are there qualitative synergies that only occur when humans analyze data together?
What is the role of the PM? Will they perform a QA/QC to the bot or act as a tie breaker?
Who does a PM blame when a bad decision is made?

Let me know your thoughts

I believe we all have much to gain in a future where bots are helping accelerate tedious or time-consuming business tasks. But there is understandably a lot of debate about the pros and cons of AI technology. Some fear the dystopian implications of machines becoming too intelligent or self-aware (the singularity).

What do you think? I’d welcome hearing your thoughts in the comments below.