Suggestions for online billing application...

Y

YeOldeStonecat

[H]F Junkie
Joined
Jul 19, 2004
Messages
11,330
Not my forte...I really don't like to get involved with software itself...rather..let me build the servers for them. I have an accounting firm client that made a side business of "site management". What they do, is manage payroll, rent, etc etc....for several different "big chains" of fast food outlets, and one large retailer, that many of you in the States eat at or buy things from.

Another client of mine who is a heavy duty Access/Excel programmer built a database which they have been running on for a few years..but they're exploading in growth, needs, and stress.

Here is a copy of an e-mail from this current Access person...my question is...is there someone here who has come across similar needs, or has a familiarity with a canned solution out there?

"Goal:

1. User enters data into tables via internet. Data consists of total sales per day for each month to be entered on the 1-4th day of the month following the month that the sales are generated for.
2. After entering the sales they need to be able to generate a bill that calculates a variety of fees that are billed based on their sales, store type, and anniversary date. The bill also needs to calculate any credit/balances they have from previous months. (present Access Database already does these calculations and generates the bills without problems)
3. Presently there are 1500 users who would need to access the database during the 1st-4th day of the month. Eventually this will grow to 6000+. It is feasible that they could enter the data each day however the site needs to be able to able to accomodate 6000+ users over a 4 day period of time.
4. When the bill is generated, the user needs to be able to choose their method of payment. ACH withdrawal from their bank is the preferred payment. Presently they pay via ACH and Check. There are no charge card payments at this time.

Presently the database is built in Access and is generating bills for 1500 stores between the 1st - 5th of the month. The contract requirement is that the company bills the stores within 24 hours of receiving their data. Their manpower is at its max and they will not physically be able to bill 6000 stores with the present staffing ratio. The goal is not to add staff but to find a mechanism to allow this quantity of bills to be generated in the short window of time with no hiccups."

And another e-mail from her...

"Rebecca is planning for the increasing growth of the database store numbers which could go to 6000. Presently we're at 1500 stores. The present process and database works but is strained by the number of bills that need to be processed in a 24 hour period, which their contract requires (24 hour turn around from the time they get the bill). Staffing ratios at the present time allow them to process 407 invoices in 24-38 hour period. They are at capacity on staff. Rebecca is interested in looking to a web based solution that would allow the user to enter the data and the bill (incudling credits and balances) to be generated. From what I can tell the database would need to be housed on a dedicated server. If they are to use the present database to do this the server or vendor they go with would need to be able to work with Access, else the software needs to be rewritten or they would need to go with an off the shelfsoftware. In addition, if they with the present database, I'd most likely plan to link the Access tables to SQL tables due to the size of the data and how fast it will grow with 6000 stores."
 
wow, sounds like quite the mess...


VERY well written emails though... i'm not used to such eloquent descriptions... usually i get emails like "IT DONT WORK FIX IT.... HURREEEE!!! help"


my question is... what is the problem with the access database? too slow? i know people always moan about how it isn't designed to process as much as SQL and things... but is there time processing those 1500 stores worth of bills in that 24 hours?

how long does it take now?
 
goodcooper said:
wow, sounds like quite the mess...


VERY well written emails though... i'm not used to such eloquent descriptions... usually i get emails like "IT DONT WORK FIX IT.... HURREEEE!!! help"


my question is... what is the problem with the access database? too slow? i know people always moan about how it isn't designed to process as much as SQL and things... but is there time processing those 1500 stores worth of bills in that 24 hours?

how long does it take now?
Click to expand...

I think the problem with it now....is the sheer mechanics of the process..very labor intensive. I think they want to make it more "web based" so that the store clients can securely log in..and enter the data ..instead of the e-mail transport now, and/or faxing of stuff to the office...it's the handling of this e-mail and receiving faxes 'n what not..that's a big part of the problem.
 
I asked their Access person about the "routine", or process.

"All accounts receive a CD with an excel spreadsheet to enter their monthly data. The spreadsheet also produces a bill (without credits and debits) based on the specific store demographis so they have an idea of what their various fees are going to be.

Stores "should" email the excel spreadsheet to TTTSM. Many fax their numbers in and the TTTSM staff data enter it themselves. This process is extremely time consuming and would be one that should be eliminated however many store owners have no clue as to how to use a computer or do not have internet access.

Each spreadsheet that comes in is openned and verified for missing data etc. - again time consuming and something I wouldn't do but it is their practice to ensure a correct bill. I believe at this time they also print out 2 of the spreadsheet pages to keep on file and keep the bill with it. Rebecca would need to verify this. Again, I'd probably eliminate this part too as I hate paper.

Spreadsheets are then imported into the database - usually no more than 20 at a time and bills are generated. They generate the bill one a time so that they can then check it for being correct, print to pdf and either email back to the store or fax back if no email address.

All of this is has a 24 hour turn around requirement from the time they receive the data to the time they send the bill back.

There are many steps I'd eliminate but to be honest <name of the client stores> is such a tyrant to Rebecca if even a penny is off that they don't dare eliminate any of these steps for fear a bill will be wrong. I don't even live under this fear with the federal gov't!

The large majority of the bills are perfectly fine, however if payments aren't applied correctly (something that rarely happens as Tammy does a great job) or if one of the spreadsheets is an old one that doesn't have the rounding fix on the tax in it etc. things may be off by a penny. The remainder of the month is spent with a variety of reports that are sent to <name of the client store chains> and if they are off by the slightest amount (sometimes it has nothing to do with Rebecca's group at all but is due to faulty info given to them etc.) there is hell to pay."
 
OK lets try to answer -

As for a program to receive and bill in there are many many choices such as peoplesoft/JD Edwards, etc. They are built for many end users like you need, they can also be customized for business processes that are unique and dont fit into a canned business model. I would talk to a specialized consultant that deals with these apps or call the companies directly. They have consulting arms that can help with these specific issues.

Now for the business model efficiency. To my (and correct me if I am wrong) you want users to be able to log in fill out some data and bingo bill is sent.
Now to make it more efficient you need to implement a scanning/storage solution, which is also called content management. Basically you need an interface between an email (that the end user sends in) or a fax (that the end user sends in) and want to try and automate this and stick it into your billing solution.

Again there is definately many solutions. What I would do is set up something like IBM content manager to be the 'interface' from the end user; take that data and plow it into the backend billing software.
The content manager can have built in checking for missed entries and help to free up employees that do this function now. There would have to be a standard 'form' if you will that the clients send in via fax or email so the content manager knows what should and shouldnt be there. You can also have different 'forms' for each client but this would all need to be implemented on CM.

So you have a 2 fold problem - First is to pick a billing solution. I would definately look at something heavy duty like I mentioned above
Second is to get a management solution to cut the human error and make the process more automated. This is is definately content management ground (IBM) great guys to work with.
 
YeOldeStonecat said:
however many store owners have no clue as to how to use a computer
Click to expand...


there's one problem :p


but in all seriousness, this is going to cost some mondo cash... and it is times like these i wish i was an enterprise web app programmer with enough time to take your client on as a client

.... there is money to be made here peoples
 
It sounds like you all need a CO or Central Office that will poll other locations and grab the data from them.

This is how I have seen many retail and chain stores run.
 
OMG- 6000 stores and they're sending them Excal spreadhseets to fill out?? I can't imagine how labor intensive that must be as well as the opportunity for error. Sounds like they've really outgrown themselves. I would think they should look at a product like Deltek Vision. Web based, runs on SQL, on a dedicated server(s) . Multi-tiered server structure depending on infrastructure and access requirements. You can house the database on one server, publish the app on others. This scenario allows web based access at remote sites using browser based secured access- ie: ssl. But moving to this also requires revamping accounting, as it would (and should) be all tied together. Very flexible, very customizable. Very expensive :D , but look at what they're trying to accomplish! If you ever want to see it working, I'll port you into a client site and you can log onto their server and look at the sample database.
 
LoStMaTt said:
It sounds like you all need a CO or Central Office that will poll other locations and grab the data from them.

This is how I have seen many retail and chain stores run.
Click to expand...

Could be done. Their office is 1 mile from my office. Next to me is a big data center on an OC3. The guy that runs the data center provides managed bandwidth (such as T-1s, RLAN DSL, Canopy, etc) to business clients...including this office I'm talking about So they could have a good connection to a data center where we could host web servers, etc.
 
twwabw said:
OMG- 6000 stores and they're sending them Excal spreadhseets to fill out?? I can't imagine how labor intensive that must be as well as the opportunity for error. Sounds like they've really outgrown themselves. I would think they should look at a product like Deltek Vision. Web based, runs on SQL, on a dedicated server(s) . Multi-tiered server structure depending on infrastructure and access requirements. You can house the database on one server, publish the app on others. This scenario allows web based access at remote sites using browser based secured access- ie: ssl. But moving to this also requires revamping accounting, as it would (and should) be all tied together. Very flexible, very customizable. Very expensive :D , but look at what they're trying to accomplish! If you ever want to see it working, I'll port you into a client site and you can log onto their server and look at the sample database.
Click to expand...

Thanks...forwarded your quote, and a link to Deltek, to her. If they want a peek at it...I'll call ya. They're in New London CT...not tooo far of a drive for ya. :)
 
You must log in or register to reply here.
Back
Top