Most people know me as an expert in Excel and data analytics, but my career actually began in finance. I even earned a master’s degree in finance and spent the majority of my brief full-time career as a financial analyst.
Finance plays a pivotal role within any organization, offering a wealth of opportunities. It’s an excellent career path. However, during my time in the field, I noticed a significant issue: many finance professionals showed little interest in tools and technology. They seemed content to spend their days inefficiently in Excel, compiling the same manual reports without ever pausing to consider the larger “Why?” or to think innovatively about the data.
Thankfully, the landscape appears to be shifting with the advent of AI. Finance professionals are beginning to recognize—or are being compelled to recognize—the immense potential of automation and analytics. For someone like me, who initially turned to Excel to expedite finance tasks and glean deeper insights, this evolution is incredibly exciting.
Introducing Copilot for Finance in Excel
A significant milestone in the finance sector’s shift towards a more serious consideration of AI is the release of Copilot for Finance. Although it’s still in its early stages and available only as a preview, I’m already quite enthusiastic about the potential it offers. We’re going to explore its inaugural use case for Excel—providing a reconciliation process.
For those interested in learning more about Copilot for Excel, I recommend visiting Microsoft’s official blog. There, you’ll learn more about the wider product and learn how to gain free access to the preview version. Please note, however, that availability is contingent upon your organization’s IT policy.
Loading the add-in
Once you’ve accepted the Copilot for Finance preview, you can activate it in Excel by navigating to File > Get Add-ins > Admin Managed. From there, click on Copilot for Finance (Preview) and select “Add:”
It’s important to note that this tool, Copilot for Finance, differs from Copilot for Excel (which itself is part of Microsoft 365 Copilot); it’s a specialized tool designed specifically for finance, whereas Copilot for Excel is a general-purpose generative AI productivity tool. I understand that Microsoft’s naming, marketing, and pricing strategies can be perplexing. Even as a professional Excel consultant, I find it confusing.
When you activate Copilot for Finance, a popup will appear on the right side of your worksheet. Currently, it offers one option: Reconcile data. Financial and accounting data, often highly proprietary and challenging to simulate realistically, can be a hurdle to use in a setting like this. Fortunately, Excel provides some sample data that we can use to practice. Go ahead and insert this sample data to get started.
Upon activating the Copilot for Finance feature, your workbook will receive two additional worksheets. The first presents a budget for a hypothetical business trip, while the second outlines the actual bank statement. The objective here is to reconcile the budgeted versus actual expenses—a task that, undoubtedly, many finance professionals have found themselves laboriously undertaking by hand.
To begin the reconciliation process, click on “Reconcile data” within Copilot. The initial step involves formatting your data into tables. With the advent of tools such as Power Query, Power Automate, and Copilot, which heavily rely on tables, there’s a growing emphasis on organizing data within Excel tables.
For a smoother reconciliation process, I recommend positioning your budget as the first worksheet and the actual bank statement as the second. This arrangement is critical because Copilot’s formulas will calculate the difference by subtracting the values in the second table from those in the first. Since a scenario where the budget exceeds the actual expenses is considered favorable, this difference should be represented as a positive number.
Following the table formatting, you’ll proceed to define some rules to ensure the appropriate categories and values are being accurately matched for the reconciliation process. Copilot employs an AI algorithm to facilitate this, but it’s crucial to remember that the AI does not inherently know the “truth” of your data.
Instead, it makes educated guesses based on column labels, the types of data present, and other related factors. This highlights the paramount importance of having well-labeled and well-structured data when aiming to leverage AI effectively.
If you encounter any discrepancies or issues with the setup, there’s flexibility within Copilot to address this. You have the option to request Copilot to attempt the reconciliation process again. This iterative approach allows you to refine the parameters and rules as needed, ensuring the reconciliation is as accurate and useful as possible.
Finally, you’ll receive your automated reconciliation report:
Copilot organizes your data into three distinct categories: unmatched transactions, potentially matched transactions, and matched transactions. The tables presented are not linked to the original data source. If you encounter any obvious errors in the reconciliation process, such as incorrect column usage or incorrect totals, you can prompt the AI to regenerate the entire report by selecting “Regenerate” from the Copilot menu.
Additionally, you will have the option to download a PDF summary of the reconciliation process. This summary provides a useful, albeit somewhat simplistic, report. Before sharing it widely, you might want to customize it with your company’s branding and ensure the report’s content is accurate and well-prepared. While it serves as a good starting point, refinement is recommended for a professional presentation.
Under the “Suggested Action” section at the bottom of Copilot, you can also ask to include the summary in your report. This will automatically add the same write-up from the PDF to the end of your worksheet, allowing you to edit it as needed.
Now, let’s proceed to review the reconciliation process, evaluate its accuracy, and determine if any modifications are required.
Evaluating the unmatched transactions
The initial phase of reconciliation involves reviewing unmatched transactions. This process displays your actual financial figures alongside your budgeted numbers, organized by Copilot for your convenience. By doing so, you can effortlessly identify discrepancies by both date and category:
The formula used in column I to generate the difference is quite a mouthful! Thankfully, assistance is available to navigate through this complexity.
Understanding the report’s formulas
Here’s the the formula that Excel used in the report to compare the budget with actuals for unmatched transactions:
For assistance with understanding this formula, you can indeed seek help from Copilot. Simply access the main Copilot tool and ask for clarification on the formula you’re interested in. This feature can provide you with step-by-step explanations or further information to enhance your understanding.
This guidance indeed appears to be useful, particularly when dealing with complex formulas in Excel designed to identify discrepancies and apply rounding for more consistent formatting, which helps in minimizing errors. The complexity in the report’s formulas often arises from the lengthy names assigned to variables or ranges, which could be simplified by utilizing the Name Manager in Excel for easier reference.
Again, it’s important to note that there are two distinct products being used here: Copilot for Finance and the main Copilot platform. Access to these products may vary, requiring users to switch between them depending on their specific needs. However, this Excel-based method is just one of several approaches to explain Excel formulas. Other generative AI tools, such as ChatGPT, can also effectively assist here.
Potentially matched transactions
The next section of the report pertains to transactions that Copilot cannot categorically determine. This uncertainty arises when transactions appear to match but necessitate the amalgamation of multiple entries into a singular transaction. For instance, a total expenditure of $120 on food on February 19th may seem straightforward, but it derives from two separate entries in the bank statement, specifically rows 5 and 6. Copilot faces a slight confusion in confirming if these transactions directly correspond, though the cumulative totals align. The primary factors used for matching in such instances are the category of the expenditure and the date of the transaction.
Matched transactions
Finally, we have the matched transactions, where Copilot displays a high level of confidence in its matching accuracy. In fact, it’s so confident that it automatically groups these transactions and hides them within the worksheet. However, if you wish to review them yourself, you can easily do so by clicking on the plus sign located to the left of your workbook, which will expand and reveal the details.
Troubleshooting a transaction
If you’re uncertain about the origin of a number in your report, you can consult the reconciliation report summary. There, under “Suggested Action,” you can select “Troubleshoot a transaction.” This option offers guidance on how specific budgeted or actual figures were derived in the report by directing you to click on the cell in question to learn more.
For instance, if the source in your report of the $120 listed for food on the 19th is unclear, clicking on that cell will reveal that it stems from two transactions, each valued at $60.
Additionally, while you can always refer back to the original source data, for complex datasets, the ability to automate this data retrieval and gain preliminary insights before delving into the data manually is immensely valuable.
Saving the report
Given the complexity and potential variability of Copilot’s AI-generated reconciliation process, it’s essential to recognize that it may not always follow a predictable or straightforward path. Documentation becomes crucial in this scenario, as there might not be a direct way to trace the reconciliation tables back to their original data sources.
Therefore, saving your work is imperative, either by embedding it directly within the workbook or by exporting it to a PDF. While for simpler datasets, you might not notice significant differences between runs, there’s still a possibility of minor variations. Hence, securing your documentation is key, as you might not have the opportunity to revisit or redo the process.
The future of finance is here
This is an exciting development in the early stages of Excel enhancements, and although various tools are available to automate reconciliation, this represents a significant step forward. It enables users to perform tasks in Excel that previously might have required expensive and less flexible financial tools.
This advancement encourages people to become more accustomed to seeking AI assistance and to programmatically work with Excel tables. We can only expect positive outcomes from this, and I am eager to witness the next innovation from Copilot for Finance in Excel. It appears that variance analysis will be the next feature, a fundamental aspect of finance where Excel and AI could greatly unlock value.
Do you have any specific questions about AI, Copilot, and the future of finance and Excel that you would like to explore further? Whether you’re curious about the latest trends, how these technologies can be integrated into your finance team, or any other related topic, feel free to share your questions or concerns in the comments. Additionally, if you’re interested in incorporating these tools into your finance team and need assistance, please don’t hesitate to reach out.
Leave a Reply