Refund Tracking Smartsheet- Ideas or Template

Rlorenzana

Good Afternoon Everyone,

I am overseeing customer refunds for our company. Our company requires customers to make a refund preference so when there is a refund due to them, we can deliver it to them as quickly as possible. However, because many of them do not make a preference the customer refunds are returned by our bank as undeliverable.

We have a team that does outreach to our customers in order to notify them of the undelivered refunds and need for them to set up their profile and direct deposit information to reissue the refund.

Once the customer has set up their refund preference, they contact our call center to request their refund reissued. Our call center then updates a spreadsheet indicating the customer has set up their profile and elected a refund preference and is ready for the reissuing of their refund. On a weekly basis accounting team pulls from the spreadsheet to reissue the refunds. Once the reissue has been completed our accounting department goes back to the spreadsheet and marks the refund completed.

We are currently using Excel and its not a very smooth process.

Detailed below:

We use MS Excel and create three Spreadsheets.

1) A yearly Master Spreadsheet to track all returned refunds per year.

Named: Bank Returns Master File 202X

These master files are used to keep track of other processes within the refund cycle, for example, stale dated checks, escheatment, Non-Resident Tax Withholding, and how the reissues were ultimately delivered to the customer (via EFT or Check). These Master Files, as the name implies are our primary records.

2) We create another Spreadsheet for the Call Center to track as Customers call in for their refunds (once profile and preference is made). This file is used to communicate to our accounting team to identify the customer refunds that are ready to be processed and reissued.

Named: Bank Returned Refunds 202X.

Once reissued, Accounting updates the Bank Returns Master File 202X (1. above) indicating the date the reissue was processed and deletes the line from the Bank Returned Refunds 202X file (2).

3) We also create a separate Spreadsheet for the outreach team- this spreadsheet is mainly used as a record of the final attempt to contact the customer and the outcome of that final contact attempt.

Named: Returned Refund Outreach File FY2022.

On this spreadsheet, we pull from our customer contact database, using the Customer ID, to get email addresses and phone numbers. The outreach team uses this to call/email the customer. This is a last-ditch effort to notify the customer that we have a refund for them. The Outreach team logs the outcome of the outreach on this spreadsheet.

I would like to use Smartsheet to either simplify or streamline this process.

I have been looking online for a template but nothing seems to fit this case.

Ideally, I would like to

1) automate the importing of the data from our bank. Even if that is just downloading raw bank data to excel and saving on a specific location that Smartsheet can then pull from

2) ease the process of the Accounting team and Call Center communication of refunds ready to be reissued. Hoping to not have to have two of the same files (Bank Returns Master File 202X and Bank Returned Refunds 202X)

Any feedback, or guidance you can provide would be greatly appreciated!

Andrée Starå

Hi @Rlorenzana

I hope you're well and safe!

1) automate the importing of the data from our bank. Even if that is just downloading raw bank data to excel and saving on a specific location that Smartsheet can then pull from

You could use the Premium app, Data Shuttle, for this.

2) ease the process of the Accounting team and Call Center communication of refunds ready to be reissued. Hoping to not have to have two of the same files (Bank Returns Master File 202X and Bank Returned Refunds 202X)

It sounds like they could be combined and the depending on who should have access you could have one so-called Master Sheet and maybe use either the Premium app, Dynamic Viwr or WorkApps to compartmentalize the data.

I'd also recommend exploring using the Update Request feature to help with collecting the preference from the clients.

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Rlorenzana

Good Afternoon @Andrée Starå I really appreciate your response. I do have the premium app and access to the Data Shuttle. I will also look into the compartmentalization you mentioned in your response. I was hoping to see some examples, and possibly a template to follow, as frankly, I am not visualizing the solution for my particular set of circumstances.