Created with AI-Assisted Excel Macros
Executive Summary
- Challenge: A Vancouver-based SaaS MNE faced a tedious and error-prone annual task – rolling forward a massive year-end tax provision Excel workbook. The process took 20–30 hours of manual work each year and relied heavily on the Head of Tax’s deep knowledge to navigate complex interdependent updates.
- Solution: The Head of Tax spearheaded an automation project, using ChatGPT 4.0 (with corporate approval) to help write a series of Excel VBA macros. These macros automated each step of the roll-forward, tied together on a control panel with sequential action buttons. The development was done iteratively: the author described tasks to ChatGPT, received and refined VBA code, and tested repeatedly until the code worked flawlessly.
- Results: The roll-forward time dropped from over 20 hours to under 2 minutes – a >99% time savings. Manual errors were eliminated, as the automated process consistently performs updates in the correct order every time. What was once a risky, opaque procedure is now a reliable routine that junior staff can execute with minimal training. The automation improved data integrity and will free the tax team to focus on value-added analysis instead of rote updating.
- Lessons Learned: This project demonstrated the power of combining AI with expert knowledge – the Head of Tax’s expertise plus ChatGPT’s coding assistance led to a successful outcome much faster than traditional methods. It underscored the importance of thorough testing and iterative development when using AI-generated code. The case also highlights the need for governance when deploying AI (ensuring data confidentiality and getting stakeholder buy-in).
- Future Outlook: The success of the tax provision automation paves the way for broader finance automation initiatives. Similar workflows (in tax and beyond) can potentially be automated, yielding substantial time savings and risk reduction across the organization. As finance teams increasingly adopt AI and automation – a trend reflected in 58% of finance functions using AI by 2024 – this case study serves as a practical example of how to do so effectively. The author continues to explore other processes to automate, leveraging the momentum and lessons from this project to continue improving efficiency and accuracy in financial operations.
Background
Company Context: The author is the former Head of Tax (Director) at a Vancouver-based multinational enterprise (MNE) in the Software-as-a-Service (SaaS) sector. One of the author’s key responsibilities is overseeing the financial reporting of income taxes, including year-end tax provision and disclosure. This involves a large, multi-tab Excel workbook that calculates the company’s income tax provision under US GAAP accounting standards. The workbook consolidates data from numerous sources (domestic and international entities, temporary differences, tax rates, etc.) and applies complex logic to compute tax entries and disclosures. It was originally built by the author several years ago and has grown in complexity. Given its critical role in the financial close, accuracy and consistency are paramount.
The Roll-Forward Challenge: Each new fiscal year, the tax provision workbook must be “rolled forward” – updated for the new year’s data and moved ahead one year in all schedules. This roll-forward process is labor-intensive and structurally fragile. The workbook contains over 90 interlinked tabs with strict interdependencies; updates must be performed in a precise order (for example, certain reference data must be updated before formulas on other sheets will calculate correctly) and with care to avoid breaking the tightly linked formulas that drive the workbook calculations. In the past, the Analyst or Manager rolled the workbook forward manually. The Head of Tax had previously created macros to facilitate the process. Those macros, however, had aged and become broken over time – as the workbook evolved, the old VBA scripts were not consistently updated, leading to errors and unreliable results. As a consequence, for the last 4 years the roll-forward was done by hand, with extreme caution.
The process typically consumed 20–30 hours of work each year, often spread over several days. This lengthy manual effort had to be squeezed into the busy year-end closing timetable, creating a real risk of delays or mistakes. In addition to lost time, manual updates introduced opportunities for human error in the provision (e.g. omitted updates, formula range errors), which could jeopardize data integrity. In short, the tax provision process had become a pain point: it was time-consuming, prone to error, and heavily dependent on the one person who, as the original workbook architect, deeply understood the workbook’s logic (the author).
These challenges are not unique to this company. Tax departments at many organizations face similar issues when relying on complex spreadsheets and manual processes. For example, Sonepar USA’s tax team found themselves “spending too much time on rolling over and linking Excel schedules” for their provision and felt increasing audit risk as a result. Such situations often prompt a search for automation to improve efficiency and control.
Initiating the Automation Project
Facing a temporary but significant capacity gap — with the team reduced by a third due to a leave of absence — the Head of Tax took on several preparation responsibilities that would normally fall to the analyst. One of these was the annual roll-forward of the tax provision workbook. Rather than treat this as a one-time workaround, the author saw an opportunity to solve a recurring pain point while embedding institutional knowledge directly into the process.
Recognizing the urgent need to streamline this annual task, the Head of Tax initiated an automation project to overhaul the roll-forward process. The goals were clear: drastically reduce the time spent (from tens of hours to hopefully minutes), eliminate human error by enforcing the correct update sequence programmatically, and enable less experienced staff to perform the roll-forward with minimal training. In essence, the aim was to codify the author’s expert knowledge of the workbook into an automated procedure so that anyone on the tax team could execute it by following simple steps, without having to understand every underlying formula or dependency.
…the aim was to codify the author’s expert knowledge of the workbook into an automated procedure.
The chosen approach was to build a suite of Excel VBA macros to handle the roll-forward steps. Without the option for tax provision automation software, Excel was retained as the platform (given the company’s existing investment in the workbook), and VBA offered a way to automate tasks within that environment. The vision was to create a new “control panel” worksheet in the provision file with a sequence of buttons (Step 1, Step 2, Step 3, …) that would trigger macros performing each phase of the roll-forward. By clicking the buttons in order, the user would execute the entire sequence of updates in the correct order. To avoid losing one’s place, the button colour changed to indicate the macro had been run. This design had the advantage of transparency (each step could be documented and understood in isolation) and recoverability (if something went wrong in the middle, it would be easier to identify which step failed). It also meant a junior analyst could be trained simply to follow the button sequence.
Tangentially, the author had obtained an internal Enterprise license to utilize an AI assistant (ChatGPT) in general. In this project, the AI was leveraged for help in writing the VBA code. With the green light to proceed, the Head of Tax – who had effectively no coding experience – set out to develop the macros with ChatGPT 4.0 as a collaborator.
Solution Development: ChatGPT-Assisted Excel VBA Coding
Planning the Macros: The author began by breaking down the roll-forward into a series of discrete, logical steps, much like writing a playbook for the process. For example, a simplified plan looked like: Step 1 – clear out prior-year values and carryforward balances on all tabs; Step 2 – insert a new column for the current year in each schedule and update formulas; Step 3 – update tax rate references and external data links; Step 4 – recalculate all formulas and refresh pivot tables; Step 5 – perform consistency checks (tie-outs) between tabs. Each step would correspond to a VBA macro tied to a control-panel button. This modular approach was aligned with best practices for automation: tackling a complex task as a series of smaller, manageable tasks.
Using ChatGPT for VBA Coding: To develop each macro, the Head of Tax engaged in an iterative dialogue with ChatGPT 4.0, effectively using it as a coding assistant. The process for each macro was as follows:
- Describe the task in detail: The author provided ChatGPT with a precise description of what the macro should do. This included naming specific worksheet tabs and ranges, explaining the order of operations, and any conditional logic. For example: “Clear contents of cells B10:F100 on the ‘Deferred Tax’ sheet, then copy the range A10:A50 from ‘Deferred Tax’ to the next empty row on ‘Deferred Tax sheet,” or “Find the column labeled 2023 in the ‘Rate Reconciliation’ sheet and insert a new blank column to its right, titled 2024.” In some cases, the author also shared sanitized snippets of the Excel formula or a screenshot of the sheet layout to give context.
- ChatGPT generates VBA code: Based on the prompt, ChatGPT returned a draft VBA macro. The response typically included explanations and the code itself. The author would paste this code into the Excel VBA editor attached to the workbook’s module for that step.
- Test and debug: The macro was then run on a copy of the provision workbook to see if it performed as expected. Not surprisingly, the first attempt often revealed issues – e.g., the code might not find a sheet name if spelled incorrectly, ranges might be off by a row, or certain Excel protections (like a protected sheet) could cause runtime errors. The author tackled these by feeding the error messages or misbehavior back to ChatGPT. For instance, if a runtime error occurred at a specific line, that line (or a screenshot of the error dialog) was provided to ChatGPT with a query on how to fix it. ChatGPT would suggest modifications (such as unprotecting a worksheet at the start of the macro and reprotecting it at the end, adjusting range references, or adding error-handling code). This iterative cycle continued until the macro executed from start to finish without issues and produced the intended result. The author’s deep familiarity with the workbook’s logic was essential here – it allowed verification that the macro’s actions were correct. In a few cases, the author spotted logical errors (for example, the macro might paste values one row too low, or skip a needed column) and manually instructed ChatGPT to correct those specific points.
- Proceed to the next macro: Once Step 1’s macro was working correctly, the author moved to Step 2, then Step 3, and so on, following the same development cycle. Each macro was developed and tested in turn. Along the way, ChatGPT not only wrote code, but also helped refine it – for example, suggesting loops for efficiency when updating similar tabs, or advising on how to reference named ranges that spanned multiple worksheets. Throughout this process, the author maintained a focus on clarity and commented code, so that the final macros would be understandable to others.
After several iterations and ~30 hours of effort, including on-the-fly adjustments to the original automation plan, a full suite of roll-forward macros was completed. The author consolidated the buttons and their labels on a new Control Panel sheet at the front of the workbook, with instructions. The final setup allowed a user to open the workbook and click each button (from Step 1 through Step 18) in sequence before making any necessary input updates for the new year (e.g. load trial balance data, update tax rates on the various input tabs). The macros would execute in order, updating every part of the workbook systematically.
A flowchart illustrating the sequence of macro operations is provided below for clarity:
To verify the solution, the author performed a mock roll-forward using the new automated process. The outputs were cross-checked against the prior manual roll-forward to ensure numbers aligned. Everything balanced perfectly, giving confidence that the automation was working as intended.
Results and Impact
Time Savings
The automation project delivered dramatic results. What used to take ~25+ hours of painstaking manual work each year (often requiring full focus over multiple days) can now be completed in under 2 minutes of automated execution time. In practice, the roll-forward is no longer a significant time sink at all – it’s a matter of running the macros, which execute so quickly that the longest “delay” is the blink as Excel processes each step and clicking “ok” on the textbox acknowledging the macro’s completion. This represents well over a 95% reduction in time spent, effectively freeing up several days for the subsequent year-end schedule. The efficiency gain is in line with or even exceeds typical improvements seen with finance process automation (many organizations report time savings on the order of 70–90% when repetitive tasks are automated. In this case, because so many sub-tasks were automated end-to-end, the time savings were at the high end of the spectrum.
What used to take ~25+ hours of painstaking manual work each year (often requiring full focus over multiple days) can now be completed in under 2 minutes…
Error reduction
Equally important, the risk of human error in the roll-forward has been virtually eliminated. The macros perform the steps exactly as coded every time, so there’s no chance of forgetting to update a sheet, making a copy-paste mistake, or other manual slip-ups that could have occurred before. Automation consistently yields this benefit of improved accuracy – one industry study notes that well-designed robotic processes can achieve ~99% accuracy in their work. In our scenario, the tax provision workbook’s integrity is now far less dependent on meticulous manual edits. The author also added some basic error-checking into the macros (for example, after running, the code verifies that the total deferred tax assets equal total deferred tax liabilities on the balance sheet tab, etc.). If a check fails, the macro stops and alerts the user. This provides an additional safety net that was not there in the purely manual process.
Quality and Consistency – Control Environment
The output quality and consistency have improved to the point that the tax team has greater confidence in the roll-forward. Because the process is now standardized through code, it’s easier to explain and document. The tax director can provide the macro logic (and a description of each step) to internal auditors or reviewers as evidence of a controlled process. Consistent record-keeping and automation translate to better audit readiness – automated workflows help companies stay prepared for audits, resulting in more efficient audit processes and reduced risk of non-compliance. The move from a manual, person-dependent procedure to an automated one thus strengthens the overall control environment for tax reporting.
Training and Business Continuity
Another positive outcome is the empowerment of junior staff. Whereas previously the thought of handing off the roll-forward to a less experienced team member was risky (given the complexity and lack of reliable tools), now it is straightforward. Any new or junior employee can be trained to execute the roll-forward by following the control panel instructions. They do not need to deeply understand every single formula or link in the workbook; the heavy lifting is handled by the macros. This not only distributes work more evenly, it also serves as a development opportunity – junior staff can get exposure to the provision process in a guided way, and over time they can learn more of the underlying logic by observing what each step does. In the event the Head of Tax is unavailable during a future close, the team would still be able to perform the roll-forward, ensuring business continuity.
From a broader organizational perspective, the success of this project has reinforced the value of investing in automation for finance tasks. The time saved can now be redirected to more analytical and value-added activities, such as reviewing tax results, planning, and addressing complex issues. In other words, instead of spending days performing mechanical updates to schedules, the tax department can allocate that time to insight and review, thereby improving the quality of the tax function’s output. This outcome is consistent with external benchmarks – for instance, Sonepar USA’s tax team reported saving over 120 hours in their annual provision process after automating it, and the improvement “was recognized positively up to senior finance executives, raising the visibility and value of the tax department’s contributions”.
In our case, the dramatic cut in cycle time and the removal of potential error points while enhancing controls, knowledge transfer and business continuity, demonstrated that even a small tax team can leverage modern tools without IT support, and punch well above its weight.
The manual process takes many days when balanced against other competing responsibilities.
Lessons Learned and Future Outlook
1. The Power of Domain Experts + AI: One key lesson is that pairing a domain expert with an AI, completely changes what is possible for that person. This result bears out in study after study and reflects the vanishing boundaries around general expertise. The Head of Tax in this case had deep knowledge of tax accounting and the existing workbook’s logic, but absolutely no practical programming skill. By leveraging ChatGPT’s capabilities, the author was able to bridge that gap and produce functional code without hiring a developer or spending months learning VBA; a programming language of medium difficulty. However, the domain expertise was critical – ChatGPT by itself did not know the business logic or the specific quirks of the workbook. It was the author’s detailed guidance and verification that ensured the macros did what was needed. This underscores a broader point: AI tools are most effective in corporate finance when used to augment experienced professionals, not replace them. The professional provides the problem framing and critical review; the AI provides speed and technical assistance. Together, they can achieve results much faster than either would alone.
2. Iterative Development and Testing: The project also highlighted the importance of an iterative, test-as-you-go approach. Rather than trying to write a perfect automation script in one go, the process mirrored any other program development cycle: gradual improvement and continuous feedback (a natural fit with ChatGPT’s conversational format). Each small success (getting one macro to run) built confidence and each failure (a bug or error) was treated as feedback to refine the code. This approach kept the project moving forward and prevented being overwhelmed by complexity. Additionally, thorough testing at each step was vital. The author did not assume that ChatGPT’s first answer would be 100% correct – everything was validated. This is a crucial lesson for using AI in any sensitive workflow: always verify the output of the AI, especially when it comes to calculations or code that will be used in production. The old trust-but-verify adage holds true.
3. Upskilling and Knowledge Capture: By working through the VBA coding process with the AI, the Head of Tax also personally gained more programming knowledge. Interacting with ChatGPT served as on-the-job learning – the author picked up new VBA techniques and best practices recommended by the AI. This kind of upskilling is a significant side benefit of AI-driven projects (project-driven learning) and contributes to the finance team’s technical competency. Moreover, converting a manual process into code inherently captures institutional knowledge. The logic that was once only in the author’s head (or scattered in spreadsheet formulas and process documentation) is now embedded in a structured way. This documentation-by-product means the company is less vulnerable to losing that knowledge if personnel change.
4. Corporate Governance and Data Security: Introducing AI like ChatGPT into a corporate workflow required careful consideration of confidentiality and security. In this case, the author had an Enterprise license but still took care that no sensitive financial data was exposed in prompts, only formula structures or dummy examples. This reflects a growing trend: finance functions are cautiously embracing AI, but under governance. (Notably, a Gartner survey found that as of 2024, 58% of finance departments are already using AI in some form, a figure that has grown rapidly as companies see the benefits.) Still, organizations must have clear policies: for example, using on-premises AI solutions for very sensitive workflows, or vetting third-party AI tools for compliance with privacy standards. The lesson is that AI can be used in corporate environments responsibly – with appropriate approvals and safeguards, the risk can be managed while unlocking the efficiency gains. The author’s successful use of ChatGPT with prior approval set a positive precedent within the company for future AI-assisted projects.
5. Far-Reaching Potential for Automation: This case study is just one example of how automation (in this instance, fairly simple Excel-based automation) can transform a finance task. The same principles could be applied to many other tax and finance workflows that are repetitive and rule-based. For instance, processes like updating recurring journal entry workpapers, rolling forward forecast models, reconciling accounts, or preparing tax compliance schedules often involve similar copy-forward and adjust mechanics. With tools like VBA macros or more advanced robotic process automation (RPA) bots, those too could be streamlined. The combination of RPA and AI is especially promising – RPA can handle structured tasks across systems, and AI can assist in building or even dynamically adjusting those automations. Many finance leaders are recognizing this potential: surveys indicate that a majority are either implementing or planning to implement process automation solutions in finance. The expected benefits are significant in terms of time savings and accuracy – for example, one study found organizations implementing RPA achieved 80–90% time reductions in targeted processes alongside near-elimination of errors. Our project’s success reinforces these findings and serves as a proof of concept that even a small tax team, using readily available technology and AI assistance, can capture those benefits.
Looking ahead, the tax department of this MNE is keen to identify other areas where such efficiencies can be gained. The author had already identified a few candidates (e.g. automating parts of the quarterly tax provision interim update, and integrating the year-end provision workbook with the consolidation system to fetch data automatically, sales tax workbook preparation). There is also interest in exploring more of ChatGPT’s capabilities, such as using it to document processes or even to analyze tax data for anomalies (e.g. during a due-diligence process). As automation becomes more ingrained in the team’s operations, the role of the tax professionals will continue to shift toward review, analysis, and strategy, with rote tasks minimized. The case study thus showcases not only a one-time improvement, but a glimpse into the future of finance work – one where AI and automation tools are part of the daily toolkit, driving efficiency and allowing humans to focus on higher-value activities.
References
Reference #1 Building a Business Case for Tax Automation (2021)
Reference #3 Future of RPA: Growth Trends & Statistics (2021)
Reference #4 How AP automation enhances audit readiness and compliance (2024)
Reference #5 The Cybernetic Teammate (2025)
Reference #6 58% of finance functions using AI in 2024, finds Gartner survey (2024)
Appendix 1 – Control Panel
