The topic of the previous blog post – ISO and Six Sigma – came from the conversation with Mike Negami. And this topic also came from the conversation with him because it was pretty hard to explain on the phone how I am using Office 365 in the Lean Six Sigma projects. So this post explains it with some pictures.
Although I wrote this topic regarding to the Lean six Sigma project, the technique can be applied to any project if you use the Microsoft Office 365.
1. Background of using Office 365 in workflow patterns
The lean six sigma project has five phases of DMAIC. Each phase is pretty hard to execute, but especially the Measure phase and Analyze phase are tiresome because they take long time and need a lot of efforts to execute. And reliability of data is always questionable even with careful execution.
The Measure/Analyze phases in manufacturing environment could be easier because tangible objects come through the process next by next. The Measure/Analyze phases in office environment are harder because objects flowing in the process are intangible, and the lead-time of the process is long. So sometimes in a project for office process improvement, I want to skip the Measure/Analyze phases , and jump to the Improve phase to implement my ideas.
To reduce the burdens in the Measure/Analyze phases, I have started using the Office 365 combined with other software, and created some combination patterns (i.e., workflow patterns). Then I have applied the workflow patterns to other Lean Six Sigma projects. The more I apply the workflow patterns to the projects, the easier becomes the project execution.
The combinations of Microsoft Office 365 tools with other software make the Measure and Analyze phases:
- Easier
- Faster
- More frequent data collection
- More accurate data collection
2. Software in workflow patterns
I explain the software using in the workflow patterns before explaining the workflow patterns themselves.
Word and Excel: These primary software are needless to explain. In the workflow patterns, the Word can be used as a format of report generated from the workflow patterns. The Excel plays important roles in the workflow patterns. It is used as a repository of temporally data, and as a programming tool (VBA).
Outlook: Needless to explain, the Outlook is a software for email. The mailbox is a treasure box and a big data of information.
SharePoint: The SharePoint also plays important roles in the workflow patterns because the data collected from mobile devices and PCs are saved in the SharePoint site. The SharePoint is a great tool for data sharing, and cooperation with other software such as Excel, PowerApps, Flow and InfoPath.
PowerApps: You can create a mobile app very easily with the PowerApps. If you launch the PowerApps from a SharePoint library, the PowerApps generates a mobile app which links to the SharePoint library. Nowadays everyone has a smartphone, and the advantage of PowerApps is getting increasing.
Teams: Team is a chat-based software. If you use an email-based communication, it is pretty hard to trace back the historical conversations in a mailbox, and hard to find the latest version of attached files. If you use the Teams, you can manage the historical conversations and the latest files at one place. The Teams create own SharePoint sites. It makes the file management easier.
InfoPath: A form for entering and editing data could be very convenient when a recode consists of many data. The InfoPath allows to create a form for a SharePoint library. Even a form with pages can be designed by using the InfoPath.
Flow: The Flow allows to create an automation process such as transferring data from one software/service to another software/service, or sending an email notification. The Flow monitors network and detects an event for starting the automation process.
Power BI: The Power BI can visualize graphs and charts on web browsers as the business intelligence (BI). You can set a filter value on the graphs and the charts so that you can get only the data you want. Using the powerful data processing capability, you can easily create an business intelligence site.
The following software are not included in the Microsoft Office 365. But some software such as R, RStudio and R Markdown are available for free.
LeanKit: The LeanKit is a Web-based Kanban board software. The team can manage their tasks by replacing their tack lists to the Kanban cards. The LeanKit allows the team to manage their project progress by the location of the cards. All information of the Kanban cards can be downloaded to an Excel file for project analysis.
R Programming Language: The R is a programming language for statistics. The R can process big and complicated data with a few statements. In the workflow patterns, the R program reads the data from an Excel file, cleanses the data, analyzes the data statistically, and prepare the data for the Power BI visualization. Instead of the R, you can use Python or other programming language.
RStudio/R Markdown: RStudio is an IDE (Integrated Development Environment) for the R, and it makes the R programming easier. The RStudio can launches the R Markdown for creating a beautiful report with graphs and charts which are generated by the R program. The R Markdown is a markdown language for the R.
SurveyMonkey/SAP, Others: The workflow patterns use many other software and services for data collection. If the data can be downloaded and converted to an Excel file, any software or service can be used by the workflow patterns. The SurveyMonkey is especially a convenient service to get feedback through a survey.
3. Workflow Patterns
This section explains the workflow patterns with combinations of the software tools explained above.
There are four workflow patterns for “Data Collection”. All workflow patterns eventually obtain all data in an Excel format.
There are one workflow pattern for “Data Analysis and Data Visualization”. The workflow pattern reads the Excel data to analyze and visualize the data for presentation.
3.1 Workflow patter for data collection 1
This workflow pattern can be used when collecting data from mobile devices.
First a SharePoint library is setup with data elements. The PowerApps generates a mobile app from the SharePoint library which works together with the mobile app.
The team members install the mobile app to their mobile devices, and enter the data through their mobile devices. The data entered to the mobile devices are automatically saved in the SharePoint library. The data on the SharePoint library are downloaded to an Excel file periodically or on demand. Using a query makes downloading easier.
3.2 Workflow pattern for data collection 2
This workflow pattern can be used when a record consists of many data elements.
The InfoPath allows to create a form on a SharePoint library. The form can consist of pages, dynamic selection, and others. It makes data entry easier.
The data entered to the form are saved in the SharePoint library. The data on the SharePoint library are downloaded to an Excel file periodically or on demand. Using a query makes downloading easier.
3.3 Workflow pattern for data collection 3
This workflow pattern can be used when there are many teams, and the team members in each team share their conversation and their files in the team.
The Teams can creates a communication space and an information sharing space for each team. And the Teams creates a SharePoint site for data sharing. But a problem with the Teams is that the files created by the teams are scattered in many SharePoint sites, and other team members cannot access to the SharePoint sites which they don’t belong to.
To prevent such problem, the scattered files are automatically copied to the central public SharePoint library by using the Flow so that all team members can share their data and files on the central public SharePoint library.
Again, the data on the SharePoint library are downloaded to an Excel file periodically or on demand. Using a query makes downloading easier.
3.4 Workflow pattern for data collection 3
The mailbox in the Outlook is a big-data and a treasure box of information. Why don’t you use it?
The emails in the Outlook can be downloaded to an Excel file, and the downloaded Excel file (or text data) can be analyzed by a R program. The Text-mining techniques can extract useful information from the downloaded text data.
You can also get useful information by using other software and services. The LeanKit provides status information of project progress, the Surveymonkey provides feedback information from a survey, and the SAP provides historical information.
3.5 Workflow pattern for data analysis and visualization
The collected data by using the workflow patterns are eventually converted and downloaded to an Excel file. And the downloaded data are analyzed by a R program.
The data processed by the R program can be used by the R Markdown and the Power BI for visualization with graphs and charts. You can obtain only the information that you want get by setting a filer on the Power BI.
4. Case Study: Analyze work time
Once you create such workflow patterns, you can reuse the patterns in other projects. The following case study is an example of such workflow patterns.
An office (transaction) process had a problem of long lead-time, and the project tried to shorten the process lead-time. But before improving the process, we needed to measure the cycle-times of each step in the process. The process was executed by many staffs, and measuring the cycle-times for step and for each staff was not realistic. So we asked each staff to report his or her work time for each step in the process.
The purpose of the Measure phase was to collect data as many as possible and as accurate as possible with minimum burden on the staffs’ work.
We created a mobile app by using the PowerApps, and asked the staffs to install the mobile app to their smartphone so that the staffs could report their work time anytime and anywhere (Workflow Patter for Data Collection 1).
The collected data were analyzed every week, and the information was visualized with graphs and charts on the Power BI site (Workflow Pattern for Data Analysis and Visualization)
Eventually, we collected data from 29 staffs for two months. They entered the data in a break time or a lunch time with minimum effort. We were able to close the Measure phase and Analyze phase in the two months with pretty accurate and big data.
