CoPilot function example

I just thought of a possible example of how the CoPilot function could be used. Since I don’t have access to it yet, this is speculative, but it points to the direction of where this AI revolution could be going.

Ever tried tracking 400+ subcontractors’ time and figuring out how much to accrue for expenses, especially back in the early 2000s?

Back then, we didn’t have all of those fancy FP&A automation tools or predictive analytics. AI was not even a whiff in the air. Everything was done manually, unless you know how to wield Excel as your ninja tool.

Background of challenge

Back then, I moved into a group that worked with the help desk and network monitoring. Subcontractors working for 3rd party companies staffed the help desk and there was roughly 400+ of them. During close, one of the analysts had to figure out what, if anything, to accrue for these subcontractors. How many hours did they work, and did we pay invoices for that work?

That was the purview of one of the analysts in the group. During close, she was the last one to finish because she was trying to get at the accruals figure. Often times, she would leave work dragging one of those luggage carriers pulling a box piled with subcontractor timecards.

Then one day management transferred her to another role, and I temporarily picked up the accrual activity.

So, I was going to do this massive endeavor.

She showed me how she did it. It was all manual.

Her approach to accruals

Basically, what she did was track all 400+ subcontractors on a spreadsheet. She would take each subcontractors’ weekly timesheets and plug their hours into the spreadsheet – name by name, week by week. The spreadsheet would then calculate the expenses that we would have to pay and provide a grand total for the week. If I remember correctly, she would take those weekly grand totals, add them up for the month, and compare that total to what had hit the books. By subtracting what had hit the books from what should have hit the books would give us the estimated accruals to book.

This was very clerical work.

Something totally not up my alley. I thought: “I have to find a better way to do this.”

My approach to accruals

When I downloaded the accounting data from SAP, I found that one vendor supplied the 400+ subcontractors and that vendor religiously sent us their weekly invoices dated Monday’s date. The SAP data contained the PO numbers (purchase orders that named who we were hiring to do what work at a certain hourly rate) and the invoice date. Once I saw that, I knew I had a quicker method.

My spreadsheet had the Monday dates running across the columns at the top and the PO numbers running down the left-hand side. I created array formulas that pulled in the invoice amount for the PO/date combination. Each invoice was for a single PO and had a Monday date.

All I chose a Monday date to initiate the tracking. So, if I’m accruing for February, I might start with January Monday dates.

I downloaded the SAP data for the GL account holding the subcontractors’ invoices and I would start a few weeks before the current month. So again, if I’m accruing for February, I might download SAP data, starting from January. I plopped that data into a tab designated to hold that data.

The array formulas in another tab would pull in the invoices paid data for each PO, with the weekly amount running across the tab, matching the Monday dates at the top.

The final touch was to do conditional formatting to highlight blank cells in yellow to represent unpaid invoice for that PO/week.

I believe I also had something to calculate the average weekly rate paid and use that as the accrual amount for the PO.

Alternative method to accruals

Another method, which I didn’t know at the time, was to make a pivot table out of the downloaded SAP data. You can make a pivot table with the dates running across the columns at the top and the PO numbers running down the column at the left, pulling in the weekly amount paid for each PO.

That would have been a cleaner route but I didn’t know about the pivot tables back then.

So, my methodology was formula driven.

Using CoPilot function as example

This is speculative but we might be able to use the new CoPilot function to figure out the accruals to make or at least make the table. The analyst would not have known how to set up array formulas but she might be able to corral the CoPilot function to design the same type of table I had created.

She might have written something like:

=COPILOT(“what was the amount paid for each PO/date combination and if there was no invoice paid, leave blank”)

Okay, there may be more necessary because we would have to know how to convey to the AI where are the dates and POs. But this is my speculation: she won’t need to know how to create the array formulas, she could just ask CoPilot to set it up.

In this situation, the CoPilot function helps her out and does me no good. I’m going to have to level up to stay ahead of her.

The key will be, will she figure out how to get CoPilot to do it? Or does this idea not occur to her? I’m secretly thinking she won’t know how to do it or think of it.