IT Solutions for Corporate & Government Organisations
Many businesses have a streamlined process for managing invoices and bills, but recording irregular expenses such as train tickets and store receipts can be a whole different matter.
For busy employees, recording their spending as it’s happening can feel impossible. When it’s the end of the month and their expense reports are due, your employees may then waste hours hunting through pockets, bags and inboxes for missing and misplaced receipts. Your HR department may also waste time chasing employees for missing reports - only to then receive hastily put-together spreadsheets full of holes and mismatched receipts.
In this article, I’ll show you how to streamline the expenses process for everyone involved.
We’ll be using Microsoft Flow to build a form where your employees can quickly and easily report each expense on their smartphone or tablet, and can even take photos of receipts, invoices and tickets, using their device’s camera. Microsoft Flow will then automatically log all of this information in an Excel spreadsheet, creating a “living” record of the employee’s spending.
At the end of the month, your HR department just need to grab a copy of this spreadsheet, and they’ll have all the information they need, including photographic evidence of that employee’s spending.
In this blog, we’ll be creating the following:
● A Flow for Mobile button that launches a form. The employee can then enter all the information about this expense request, and attach a photo of the receipt, invoice, ticket, or any other documentation that supports this request.
● A OneDrive folder where any photographic evidence will be stored.
● An Excel spreadsheet that records information about each expense request, including a link to any photographic evidence that’s stored in OneDrive.
Let’s start by creating our Excel spreadsheet.
Every time the user submits an expense request via the Flow for Mobile app, all of their information will be recorded in this spreadsheet, including a link to that employee’s photographic evidence, where available.
● Sign into your Office 365 account.
● Select the “App launcher” icon in the upper-left corner of your web browser.
● Select “All apps.”
● Choose “Excel.”
● Select “New blank workbook.”
● Once this workbook has been created, click to place your cursor inside the title field and give this document a descriptive title; I’m using “Expenses.”
● Give your columns the following headers: Timestamp, Amount, Category, ImageLink, and Address.
● Now, we need to format this workbook as a table. To start, highlight all the columns that you want to add to your table (Timestamp, Amount, Category, ImageLink, and Address).
● Select “Format as Table” from Excel’s toolbar.
● In the subsequent popup, make sure that Excel is adding the correct columns to your table.
● Select “My table has headers,” and then click “OK.”
Excel will save your spreadsheet automatically, so this spreadsheet is now ready to use.
Next, I’m going to create a folder in OneDrive where we’ll store all the employee’s photographic evidence, such as receipts, tickets, and invoices.
Once we’ve created our workflow, Microsoft Flow will copy the link to each photograph and paste it into the correct cell within our Excel spreadsheet.
● In your Office 365 account, select the “App launcher” icon in the upper-left corner.
● Select the “OneDrive” application.
● In your OneDrive account, click “+ New” followed by “Folder.”
● Give this folder a distinctive name; I’m using “Receipts.”
● Click “Create.”
Now, we need to create the workflow that’ll present a simple form to the employee, and then record their responses in the correct section of our Excel spreadsheet. If the employee provides a photo, then this workflow will also upload that photo to OneDrive and copy/paste its link into our Excel spreadsheet.
The easiest way to create this workflow, is to use one of Microsoft Flow’s ready-made templates:
● In your Office 365 account, select the “App launcher” icon in the upper-left corner.
● Select the “Flow” application.
● In Microsoft Flow’s left-hand menu, select “Templates.”
● Search for the “Track my expenses in an Excel Online (Business) spreadsheet” template, and select it when it appears.
The initial part of this workflow is completed for us, so scroll to “If yes: Add Expense Item Row No Receipt” as this is where we’ll need to enter the details of the table we just created:
● Open the “Table” dropdown and select the table that you just created.
● Open the “Location” field and select “OneDrive for Business.”
● Open the “Document Library” dropdown and select “OneDrive.”
● Find the “File” field, and click its accompanying “Folder” icon.
● Select your “Expenses” workbook from the subsequent popup.
Next, scroll to the “If no: Add Expense Item Row” section, and repeat the previous steps:
● Open the “Location” field and select “OneDrive for Business.”
● Open the “Document Library” dropdown and select “OneDrive.”
● Find the “File” field, and click its accompanying “Folder” icon. When the option appears, select your “Expenses” workbook.
● Open the “Table” dropdown and select the table that you just created.
Now, we need to tell Microsoft Flow where it should store all photographic evidence provided by the employee:
● Find the “Create file” section and click to expand.
● Find the “Folder path” section, and click its accompanying “Folder” icon.
● Select “/Root” from the dropdown, followed by the name of the folder you just created, which in my instance is “/Receipts.”
There’s lots of additional settings that make up this workflow, so you may want to spend some time customizing this template by exploring its various dropdowns and text fields. However, we’ve done enough to get this workflow up and running, so let’s hit “Save” and put Microsoft Flow to the test!
This workflow is designed to be triggered manually through the Microsoft Flow for mobile app. If you don’t already have this app setup, then you can download it for Android or iOS, and then sign in using your existing Office 365 username and password.
Once you’ve setup the Microsoft Flow app, you can trigger this workflow at any point:
● Launch the Microsoft Flow for Mobile app.
● Select the “Buttons” tab.
● Find the “Track my expenses” button and give it a tap. This launches a form where you can enter all the information about your expense request.
● In the “Amount” field, enter the amount of money you’re requesting.
● Give “Category” a tap and then choose the appropriate category from the dropdown.
● Select “Receipt image.” At this point, you can upload a photo from your device’s library, or you can take a photo using your device’s camera.
● Once you’re happy with the information you’ve entered, tap “Done.”
All of this information will now be logged in your Excel for Business spreadsheet, and the photo will be uploaded to your OneDrive account. If you head over to the Excel spreadsheet, then you’ll see that your request has been added as a new row.
If you copy/paste the ImageLink into your web browser, then it’ll load the photo you provided as part of your expense request. Alternatively, if you navigate to your OneDrive account and open the “Receipts” folder, then you’ll see that your photo has been added as a new file.
Interested in using more methods to automate your work flow? Take a look at our tutorial on how to send a working from home email automatically with Microsoft Flow.
Speak to a member of our team today 0114 292 2911 or email sales@systemsassurance.com if you need any assistance.
Share this article on social media
If you found this article useful, please share it on social media.
Subscribe to our blog...
We will only use your email to send you new blog posts.
Automatically Track Your Expenses With Excel Online and One DriveMany businesses have a streamlined process for managing invoices and bills, but recording irregular expenses such as train tickets and store receipts can be a whole different matter. For busy employees, recording their spending as it’s happening can feel impossible. When it’s the end of the month… Read More
Automatically Send a Working From Home Email with Microsoft Flow Do you give your employees the option to work from home (WFH)? Remote working can offer many benefits, including increased employee satisfaction and productivity, but if you don’t have WFH policies in place, then it’s easy to lose track of where your employees are. In this blog, we’ll… Read More
Create a Vacation Approval Workflow with SharePoint Online, Forms and Microsoft Flow. Do your staff waste hours performing the same repetitive tasks, over and over? You could save your business time, effort and money, by using Microsoft Flow to automate all of your most commonly-performed processes. In this article, I’ll show how to take the pain… Read More
Does your business rely on Windows Server 2008, 2008 R2, 2012 or 2016, or SQL Server 2008, 2008 R2, 2012 or 2014? If you’re using any of these products, then they’re rapidly approaching end-of-life (EOL).
If you continue to use any of these versions of Windows Server or SQL Server after the EOL deadline, then you’ll no longer receive security updates, even for critical security threats. To avoid exposing your business to data breaches, malware and even potential legal ramifications, it’s vital that you take action before the end-of-life deadline.
So, what steps should you take, if you’re currently using any of these Microsoft products? Read More
Prepare for Windows Server 2008 and SQL Server 2008 end of support.With end of support for Windows Server 2008 and SQL Server 2008 fast approaching, now is the time to start thinking about modernizing your infrastructure.Extended Support for SQL Server 2008/2008 R2 ends July 9, 2019Extended Support for Windows Server 2008/2008 R2 January 14, 2020After… Read More
Windows Server and SQL Server End of Life. What should I expect?Software doesn’t last forever! It doesn’t make financial sense for a technology company to continue patching older versions of a particular piece of software, when newer versions are already available – and Microsoft’s products are no exception. All Microsoft software has a lifecycle, which starts when… Read More
The Top 6 Cloud Threats Facing Your BusinessCloud computing can completely transform the way organizations store and access data. However, with more and more businesses making the leap to cloud computing, the cloud is increasingly becoming an attractive target for hackers. Regardless of whether you’re considering migrating to the cloud, or you’re already storing your… Read More
9 Reasons Why Your Business Needs to Move to The Cloud.Cloud computing is a huge buzzword, and today is widely recognized as a safe, reliable andfinancially sensible solution for businesses of all sizes. You may have a vague idea about the benefits of cloud computing, but moving your entirebusiness to the cloud can be a daunting… Read More
Free E-Book – 10 Myths About Moving to The Cloud To help in your migration to the cloud, we’ve created this myth-busting guide for all things Office 365. When considering Office 365 to power your organization, you need to know the facts. Your organization depends on it. Get your FREE E-book
Most organizations that choose to move to the cloud do so because they have decided they need it for business agility and want the cost savings that come with it. How can you know whether or not you’re getting it all? If your organization is considering Microsoft Office 365 as your first step in moving applications… Read More
