SOP: AR Reporting Process

Learn how to efficiently manage AR reporting by exporting, cleaning, and summarizing accounts receivable data in Excel. This guide covers best practices for daily updates, handling payments, and preparing reports for accounting.

April 10, 2026 By Jessica Esslinger

In this guide, we'll learn how to prepare and export an AR Aging Detail Report, organize the data in Excel, and finalize the report for review and distribution. The steps include running the report, cleaning and sorting the data, using formulas and macros, and saving the final version as a PDF. This process helps ensure accurate and efficient AR reporting each week.

Let's get started

Okay. For AR reporting, enter all AR, ACH payments, and check deposits for the week. I recommend updating these daily to avoid spending two hours on Friday. Excuse me.

Step #1: Click on "Accounting" — Click on "Accounting"

Go to Reports, then Custom Reports. Find the AR Aging Detail Report Export File.

Step #2: Click on "reports" — Click on "reports"

We are going to run it. I could have just clicked Run on the side, but I prefer to do it manually.

Step #5: Click on "Detail" — Click on "Detail"

Step #6: Click on "A / R Aging Detail Report - Export File" — Click on "A / R Aging Detail Report - Export File"

We will export using Excel instead of CSV. Excel is recommended because using tabs in CSV can cause you to lose your work if you forget, which can be very frustrating.

Step #8: Click on "Export to Excel" — Click on "Export to Excel"

Step #9: Click on "Report - Export File" — Click on "Report - Export File"

That's harsh. . Okay.

I'll enable editing.

Step #12: Click on "Enable Editing" — Click on "Enable Editing"

Step #13: Double-click on "Sheet1" — Double-click on "Sheet1"

I'll label this one as Original Export.

Type "Original Export"

I'll leave that as it is. Sometimes I make mistakes.

Step #15: Right-click on "Original Export" — Right-click on "Original Export"

Step #16: Click on "Move or Copy" — Click on "Move or Copy"

Step #17: Click on "a copy" — Click on "a copy"

Step #20: Double-click on "Export" — Double-click on "Export"

This is raw data.

Type "Raw Data"

I select all, go to Home, click Clear, then Formats.

Type "Ctrl + A"

Step #25: Click on "Data" — Click on "Data"

Step #26: Click on "Home" — Click on "Home"

Step #27: Click on "Clear" — Click on "Clear"

Step #28: Click on "Clear Formats" — Click on "Clear Formats"

Step #29: Click on "03/31/202 :" — Click on "03/31/202 :"

Then I grab this section and bring it over.

Step #30: Click on "1 A / R Aging Detail Report - Export File" — Click on "1 A / R Aging Detail Report - Export File"

Step #31: Drag "As of Apr 10 , 2026" — Drag "As of Apr 10 , 2026"

Step #32: Click on "JA / R Aging" — Click on "JA / R Aging"

I will delete column A, then delete row six. This allows me to select all the data and apply Data > Filter.

Step #33: Right-click on "JA / R Aging Detail Report - Export File ManageAbility , Inc." — Right-click on "JA / R Aging Detail Report - Export File ManageAbility , Inc."

Step #34: Click on "Delete" — Click on "Delete"

Step #35: Click on "City Of Detroit" — Click on "City Of Detroit"

Step #36: Click on "CompOne / SCA - Variety Wholesalers" — Click on "CompOne / SCA - Variety Wholesalers"

Type "Ctrl + A"

Step #38: Click on "5698" — Click on "5698"

Step #39: Right-click on "6" — Right-click on "6"

Step #40: Click on "Delete" — Click on "Delete"

Type "Ctrl + A"

Step #43: Click on "Data" — Click on "Data"

Step #44: Click on "Filter" — Click on "Filter"

Step #45: Click on "MGY - 8062" — Click on "MGY - 8062"

From here, I will review my payment in My Payments first.

Step #46: Click on "▾ Transaction ty▾ Due date ▾ Num" — Click on "▾ Transaction ty▾ Due date ▾ Num"

Step #47: Click on "Select" — Click on "Select"

Step #48: Double-click on "Select" — Double-click on "Select"

Step #49: Click on "Select" — Click on "Select"

Step #50: Click on "01/31/2024 CR - MGY - 8064 Credit Memo 01/31/2024 CR - MGY - 8067" — Click on "01/31/2024 CR - MGY - 8064 Credit Memo 01/31/2024 CR - MGY - 8067"

Some payments have not been applied yet.

Step #52: Click on "Home" — Click on "Home"

Step #53: Click on "$ % 950000" — Click on "$ % 950000"

These are duplicate payments I entered today. I have inquiries with Michelle Hibbard about them. These three need to be booked to interest, and this one is an overpayment.

Step #56: Click on "( 1,164.30" — Click on "( 1,164.30"

Step #57: Click on "( 1,199.68" — Click on "( 1,199.68"

Step #58: Click on "▾ Open balance" — Click on "▾ Open balance"

Step #59: Click on "( 32.30" — Click on "( 32.30"

Step #60: Click on "( 1,257.08" — Click on "( 1,257.08"

Step #61: Click on "( 152.86" — Click on "( 152.86"

I will make sure those are taken care of. If they are not handled before maternity leave, filter Credit Memo and Payments. Start from the top left and drag all the way down.

Step #62: Click on "Transaction tyDue date ▾ Num" — Click on "Transaction tyDue date ▾ Num"

Step #63: Click on "Invoice" — Click on "Invoice"

Step #65: Click on "10 C10128" — Click on "10 C10128"

Step #66: Drag "10 C10128" — Drag "10 C10128"

It... Can I go any further? No.

Step #67: Right-click on "CompOne / SCA - Variety Wholesalers" — Right-click on "CompOne / SCA - Variety Wholesalers"

Right-click Delete, then press...

Step #68: Click on "Delete Row" — Click on "Delete Row"

Step #70: Click on "Data" — Click on "Data"

Where is my data?

Step #71: Click on "Clear" — Click on "Clear"

Clear it, and then there's this person I haven't written off yet, which I need to do. Don't forget to delete it, because JB will notice.

Step #74: Right-click on "▾ Open balance" — Right-click on "▾ Open balance"

Step #76: Click on "Invoice" — Click on "Invoice"

I select all, then sort A to Z by customer name. I don't think I did it correctly.

Type "Ctrl + A"

Step #79: Right-click on "Invoice" — Right-click on "Invoice"

Step #80: Click on "Get Data from Table / Range" — Click on "Get Data from Table / Range"

Step #81: Click on "↓ Sort A to Z" — Click on "↓ Sort A to Z"

Step #82: Right-click on "na" — Right-click on "na"

Step #84: Click on "Put Selected Formatting Icon On Top Custom Sort ..." — Click on "Put Selected Formatting Icon On Top Custom Sort ..."

Step #85: Click on "Company" — Click on "Company"

Customer full name.

Press OK.

Step #88: Click on "Auto Owners Insurance" — Click on "Auto Owners Insurance"

Make sure the entire table is sorted by the customer number for John Bradley. Also, check that there is no total hidden in the table.

Step #91: Drag "Customer full" — Drag "Customer full"

Step #93: Click on "Amica Mutual Insurance Company" — Click on "Amica Mutual Insurance Company"

Type "Ctrl + F"

Type "total + Enter"

Yes. Sometimes I forget to delete this total line.

Step #97: Click on "392 TOTAL" — Click on "392 TOTAL"

Step #98: Right-click on "392 TOTAL" — Right-click on "392 TOTAL"

Step #99: Click on "Delete" — Click on "Delete"

Make sure you delete that.

Step #100: Click on "Close" — Click on "Close"

Okay.

Now we have a bit of fun.

Now we can delete column C, Transaction Type. Then, we'll add it back and update it as needed.

Step #104: Right-click here — Right-click here

Step #105: Click on "Delete" — Click on "Delete"

Step #106: Right-click here — Right-click here

Step #107: Click on "Insert" — Click on "Insert"

Conjugate, grab the customer code, comma, quotation mark, add a space, then conjugate.

109

Type "=conc"

110

Type "=conc"

Step #111: Double-click on "CONCATENATE" — Double-click on "CONCATENATE"

Step #112: Click on "The Auto Club Group" — Click on "The Auto Club Group"

113

Type ",": ","

Step #114: Click on "The Auto Club Group" — Click on "The Auto Club Group"

That combines the items with a colon, and then we will drag it down.

115

Type ") + Enter"

Step #116: Click on "A00700 : The Auto Club Group" — Click on "A00700 : The Auto Club Group"

Step #117: Double-click on "03/05/202" — Double-click on "03/05/202"

Next, go to the Manageability folder, open AR Reports, and select 2026. Then, choose the latest macro-enabled report from that folder.

Step #120: Click on "ManageAbility" — Click on "ManageAbility"

Step #121: Double-click on "MAI - AR Reports" — Double-click on "MAI - AR Reports"

Step #122: Double-click on "AR Report Exports" — Double-click on "AR Report Exports"

Step #123: Double-click on "2026 AR Reports" — Double-click on "2026 AR Reports"

Step #124: Click on "AR Summary Report" — Click on "AR Summary Report"

Step #125: Double-click on "MAI AR Reports" — Double-click on "MAI AR Reports"

I will unhide my Raw Data tab because it contains formulas I want to copy and paste.

Step #126: Click on "AR Detail" — Click on "AR Detail"

Step #127: Right-click on "AR Detail" — Right-click on "AR Detail"

Step #128: Click on "..." — Click on "..."

Step #129: Click on "File" — Click on "File"

Step #130: Click on "OK" — Click on "OK"

Step #131: Click on "0-60 Days" — Click on "0-60 Days"

I will take Range and G, and copy it.

Step #132: Click on "Range" — Click on "Range"

133

Type "Ctrl + C"

I'm going to paste it and make sure that it...

136

Type "Ctrl + V"

Step #137: Double-click on "0-60 Days" — Double-click on "0-60 Days"

Just paste, and do not use Control+P or Paste Special. I have tried that before, and it does not work well.

138

Type "Esc"

Step #139: Double-click on "0-60 Days" — Double-click on "0-60 Days"

Then I will drag it down. Then I will go back to my other sheet.

Here we go. I will unhide Raw Data 2 because this is how we want it to look.

Step #142: Right-click on "Raw Data 1" — Right-click on "Raw Data 1"

Step #143: Click on "Unhide ..." — Click on "Unhide ..."

Step #144: Click on "OK" — Click on "OK"

I will go back to this sheet, copy it quickly, and create a new one.

Step #148: Right-click on "Raw Data" — Right-click on "Raw Data"

Step #150: Click on "Move or Copy ..." — Click on "Move or Copy ..."

Step #151: Click on "OK" — Click on "OK"

Step #152: Double-click on "Raw Data ( 2 )" — Double-click on "Raw Data ( 2 )"

I will copy and paste special the entire content so my conjugate and range remain unchanged.

153

Type "Raw Data 2"

155

Type "Raw Data 2"

156

Type "Ctrl + A"

157

Type "Ctrl + A"

158

Type "Ctrl + C"

159

Type "Ctrl + C"

Step #162: Double-click on "The Auto Club Group" — Double-click on "The Auto Club Group"

Step #163: Click on "0-60 Days" — Click on "0-60 Days"

Step #165: Click on "1 A / R Aging Detail Report - Export File" — Click on "1 A / R Aging Detail Report - Export File"

I will remove this column and 80.

Step #166: Right-click here — Right-click here

Step #168: Click on "1 A / R Aging Detail Report - Export" — Click on "1 A / R Aging Detail Report - Export"

Step #170: Right-click on "JA / R Aging Detail" — Right-click on "JA / R Aging Detail"

Step #171: Click on "Delete" — Click on "Delete"

Now I'll go back and start copying and pasting, which is probably a relief. I enjoy copying and pasting.

175

Type "Ctrl + C"

Step #177: Right-click here — Right-click here

Paste.

Everything looks good.

180

Type "Ctrl + A"

181

Type "Ctrl + A"

182

Type "Ctrl + A"

183

Type "Ctrl + A"

Step #184: Double-click here — Double-click here

186

Type "Ctrl + B"

You don't need to worry about how this one looks. It's just your raw data.

Next, go back to the other file and copy and paste the AR data.

Step #190: Drag "0-60 Days" — Drag "0-60 Days"

Step #191: Right-click on "AR Detail" — Right-click on "AR Detail"

Step #192: Click on "Move or Copy ..." — Click on "Move or Copy ..."

Step #193: Click on "Create a copy" — Click on "Create a copy"

Step #194: Click on "MAI AR Reports.xlsm" — Click on "MAI AR Reports.xlsm"

You might want to skip this one, but we don't need to make it look perfect.

Step #195: Click on "Plan.xlsx" — Click on "Plan.xlsx"

I'll show you the bare minimum.

Step #196: Click on "OK" — Click on "OK"

I didn't put it in the other one.

Don't save that.

Step #199: Right-click on "AR Detail" — Right-click on "AR Detail"

Step #200: Click on "Move or Copy ..." — Click on "Move or Copy ..."

We will move and copy, create the copy, and place it in our...

Step #201: Click on "Create a copy" — Click on "Create a copy"

Did I sort the...

Step #203: Click on "Report" — Click on "Report"

We should have renamed our export file before proceeding, but we didn't.

Step #205: Click on "OK" — Click on "OK"

Next, do the same with the AR summary. Use move and copy, create a copy, and place it in our file before that sheet.

Step #207: Right-click on "AR Summary" — Right-click on "AR Summary"

Step #208: Click on "Move or Copy" — Click on "Move or Copy"

Step #209: Click on "Create a copy" — Click on "Create a copy"

Step #210: Click on "04.02.26 - MAI AR Reports.xlsm" — Click on "04.02.26 - MAI AR Reports.xlsm"

Step #211: Click on "Inc._A_R Aging Detail" — Click on "Inc._A_R Aging Detail"

Step #213: Click on "OK" — Click on "OK"

Step #214: Click on "Safety National Casualty Corporation" — Click on "Safety National Casualty Corporation"

For this step, go to Pivot Table, then select Analyze and Design.

Step #215: Click on "PivotTable Analyze" — Click on "PivotTable Analyze"

Step #216: Click on "Source" — Click on "Source"

We will change the data source and ensure we are selecting the correct file source.

Step #217: Click on "Change Data Source" — Click on "Change Data Source"

Did I do it?

220

Type "Esc"

221

Type "Esc"

222

Type "Esc"

223

Type "Esc"

Ugh, where'd it go?

Step #224: Click on "Cancel" — Click on "Cancel"

Step #225: Click on "State of Michigan Funds Administration" — Click on "State of Michigan Funds Administration"

Where'd it go? Go away. This one?

Step #229: Click on "Save" — Click on "Save"

This one. We will change the data source by selecting raw data two and dragging it all the way down.

Then press Enter and everything updates.

Step #233: Click on "PivotTable Analyze" — Click on "PivotTable Analyze"

Step #234: Click on "Source" — Click on "Source"

Step #235: Click on "Change Data Source" — Click on "Change Data Source"

Step #236: Click on "Raw Data 2" — Click on "Raw Data 2"

I quickly make it look presentable, and then the page is finished.

Step #237: Drag "5 Customer Name" — Drag "5 Customer Name"

238

Type "Enter"

When you save this, it is ready to go.

Beautiful.

This one is a bit more difficult, but it's okay.

245

Type "Ctrl + P"

We're going to delete and get rid of all the...

246

Type "Esc"

Step #247: Click on "AR Detail" — Click on "AR Detail"

We will use no border and no fill.

249

Type "Ctrl + A"

I could have just used "Clear Formats" because that's the best way to do it.

Step #250: Right-click here — Right-click here

We also have these small items here, which are for grouping.

Step #251: Click on "BIU▾" — Click on "BIU▾"

Step #253: Click on "~ A ~" — Click on "~ A ~"

I'm going to clear that outline.

Step #254: Click on "No Fill" — Click on "No Fill"

Step #255: Click on "Clear" — Click on "Clear"

You don't need to worry about formatting. You can simply say, "I'm taking over, Jessica," and it may not look as polished.

Step #257: Click on "Data" — Click on "Data"

Step #258: Click on "Ungroup" — Click on "Ungroup"

Step #259: Click on "Clear Outline" — Click on "Clear Outline"

Step #262: Double-click on "Raw Data 2" — Double-click on "Raw Data 2"

Copy the raw data two and paste it here.

Step #264: Drag "61-90 Days" — Drag "61-90 Days"

What do I do next?

265

Type "Ctrl + C"

What should I do? We are going to...

Step #266: Click on "AR Detail" — Click on "AR Detail"

268

Type "Ctrl + V"

I can't remember the next step.

Step #270: Double-click here — Double-click here

I think I go like this. Copy or select all. I will quickly open the other file to get a macro from it. I'm going to macros, then edit, but you don't need to do this part.

271

Type "Ctrl + A"

Step #274: Click on "MACRO" — Click on "MACRO"

I don't think it will let me do it.

Step #275: Click on "Macros" — Click on "Macros"

Select all, then copy.

Step #277: Click on "Edit" — Click on "Edit"

Then you will exit the four-two, just like before.

Press Alt+F11, insert a module, paste the code, and save.

Step #281: Click on "Find last used row in column A" — Click on "Find last used row in column A"

282

Type "Ctrl + A"

283

Type "Ctrl + C"

Step #286: Click on "710.04" — Click on "710.04"

But I forgot to save it as a macro. I will save this file as a macro.

Step #287: Click on "Insert" — Click on "Insert"

Step #288: Click on "Module" — Click on "Module"

If you want to skip this part, you can and move on to the next section.

289

Type "Ctrl + V"

Step #291: Click on "Save" — Click on "Save"

Account manage, manageability AR.

Step #293: Click on "File" — Click on "File"

Step #295: Click on "Save As" — Click on "Save As"

Step #296: Double-click on "This PC" — Double-click on "This PC"

Oops.

Step #297: Click on "Excel Workbook" — Click on "Excel Workbook"

Step #298: Click on "Enabled Template" — Click on "Enabled Template"

Let's... Oh, God.

Step #300: Double-click on "3- ManageAbility" — Double-click on "3- ManageAbility"

Step #301: Double-click on "MAI - AR Credits" — Double-click on "MAI - AR Credits"

Sometimes I don't know why I am like this.

Step #302: Click on "Accounting - Jess > 3- ManageAbility" — Click on "Accounting - Jess > 3- ManageAbility"

And then 04-10-26 AR...

Step #303: Double-click on "MAI - AR Reports" — Double-click on "MAI - AR Reports"

Step #304: Double-click on "Exports" — Double-click on "Exports"

Step #305: Double-click here — Double-click here

I can't remember the name of the file.

Maybe. I don't know. And then I will select all, macros, and run.

308

Type "04.10.26 - AR ReportMAI "

Step #309: Click on "Save" — Click on "Save"

Step #310: Click on "Yes" — Click on "Yes"

It places a, what's it called?

312

Type "Ctrl + A"

Insert a row between each customer change.

Step #313: Click on "MACRO" — Click on "MACRO"

Step #315: Click on "Run" — Click on "Run"

I just deleted that.

If you want to skip formatting, you can do so now to save time and make things easier.

Step #319: Right-click on "5 A7000" — Right-click on "5 A7000"

321

Type "Ctrl + Z"

Step #322: Right-click on "A00700" — Right-click on "A00700"

Step #323: Click on "Insert" — Click on "Insert"

We want to grab it all.

Step #328: Click on "1048576R x 6C B" — Click on "1048576R x 6C B"

Step #330: Click on "Home" — Click on "Home"

What do I usually do?

Step #331: Double-click here — Double-click here

Step #333: Click on "$ % 9" — Click on "$ % 9"

It's okay.

Next, go to Data and select Subtotal. You can skip the macro I just used if you prefer. Since we're not doing formatting on this one.

Step #336: Click on "Data" — Click on "Data"

Step #337: Click on "Ungroup" — Click on "Ungroup"

Step #338: Click on "Subtotal" — Click on "Subtotal"

We will change the customer name sum. Select open balance and press OK.

Step #339: Click on "Past Due" — Click on "Past Due"

Step #340: Click on "Range" — Click on "Range"

It gives you the total at the end of each customer transaction.

If you want...

Step #343: Click on "Inv No" — Click on "Inv No"

Step #344: Click on "OK" — Click on "OK"

Go back. Did I just undo it? I did.

Xfinity, why are you calling me?

Oops.

348

Type "Ctrl + A"

349

Type "Ctrl + Z"

Okay. I put it back, then quickly filter by Total to make all these bold.

Step #350: Click on "Subtotal" — Click on "Subtotal"

Step #351: Click on "121217" — Click on "121217"

We'll do a quick line.

Step #352: Click on "Filter Z ↓ Sort" — Click on "Filter Z ↓ Sort"

Step #354: Click on "02/28/2026 MGY - 120586" — Click on "02/28/2026 MGY - 120586"

356

Type "total + Enter"

Then I'll select Data, Clear.

358

Type "Ctrl + B"

359

Type "Ctrl + B"

Step #360: Click on "Home" — Click on "Home"

This is a quick and simple version, which you can use when it doesn't need to look perfect.

Step #362: Click on "Top Border" — Click on "Top Border"

Step #363: Click on "Technology Mutual Ins Total" — Click on "Technology Mutual Ins Total"

Step #364: Click on "Data" — Click on "Data"

Step #365: Click on "Clear" — Click on "Clear"

If you press Print, the document is already ready to go.

Amazing.

I always double-check that the grand total at the bottom matches the total in the AR summary: 444, 445, 305.52.

368

Type "Ctrl + P"

369

Type "Esc"

Step #371: Click on "606 Grand Total" — Click on "606 Grand Total"

Step #372: Drag "606 Grand Total" — Drag "606 Grand Total"

Yeah. We don't need to do any formatting.

373

Click here

Don't worry about it.

Step #375: Click on "AR Summary" — Click on "AR Summary"

I like to make things look nice, which is not a big deal.

Step #376: Click on "AR Detail" — Click on "AR Detail"

378

Type "Ctrl + A"

379

Type "Ctrl + A"

Then I will save this as a PDF.

380

Type "Ctrl + A"

Everything looks good.

382

Type "aptos nar + Tab"

Accounting Task, Manageability, AR Reports, PDF.

384

Type "Ctrl + P"

Step #385: Click on "Print" — Click on "Print"

I usually grab the Detail Report and change the date.

Step #387: Click on "Jess" — Click on "Jess"

Step #388: Double-click on "3 - ManageAbility" — Double-click on "3 - ManageAbility"

Step #389: Double-click on "- AR Reports" — Double-click on "- AR Reports"

Step #390: Double-click on "AR Report Exports" — Double-click on "AR Report Exports"

The next one will be the same.

Step #391: Double-click on "2026 AR Reports" — Double-click on "2026 AR Reports"

Step #392: Click on "04.02.26 - AR Detail Report 04.02.26 AR Summary Report 03.27.26 AR Detail Report 03.27.26 - AR Summary Report 03.20.26 AR Summary Report" — Click on "04.02.26 - AR Detail Report 04.02.26 AR Summary Report 03.27.26 AR Detail Report 03.27.26 - AR Summary Report 03.20.26 AR Summary Report"

Step #393: Click on "File name : Save as type : PDF Document ( * .pdf )" — Click on "File name : Save as type : PDF Document ( * .pdf )"

Step #394: Click on "04.02.26" — Click on "04.02.26"

395

Type "Backspace"

396

Type "Backspace"

Boop, boop, boop.

397

Type "10 + Enter"

Step #398: Click on "AR Summary" — Click on "AR Summary"

399

Type "Ctrl + P"

What is it called?

Step #401: Click on "04.02.26 - AR Summary Report" — Click on "04.02.26 - AR Summary Report"

Step #402: Click on "04.02.26 - AR Summary Report" — Click on "04.02.26 - AR Summary Report"

Step #403: Click on "26 - AR Summary Report" — Click on "26 - AR Summary Report"

404

Type "Backspace"

I'll hide these.

405

Type "0"

Then at some point... That's it.

Step #408: Click on "AR Detail" — Click on "AR Detail"

I'll double-check that I saved this one.

Step #409: Click on "Raw Data 2" — Click on "Raw Data 2"

Step #410: Right-click on "Original Export" — Right-click on "Original Export"

Do you want Microsoft Word or Clear Data?

Step #411: Click on "Hide" — Click on "Hide"

No, I'm going to re-save this as... I don't know.

Step #413: Click on "61-90 Days" — Click on "61-90 Days"

Step #415: Click on "Yes" — Click on "Yes"

I'll just click Save, then Yes.

I will send out this file with two PDFs. Jon Bradley specifically likes this page.

Step #417: Click on "No" — Click on "No"

Step #418: Click on "File" — Click on "File"

421

Type "Ctrl + S"

Step #422: Click on "Yes" — Click on "Yes"

And that's it.

Step #423: Click on "121194" — Click on "121194"

Step #425: Click on "121431" — Click on "121431"