Billing/Commission

I work for a small weekly newspaper and we are attempting to come up with a simple bookkeeping spread sheet. We would like each salesperson to be able to enter their sales into their customer's accounts and at the end of the month be able to print a statement for each of their customers and be able to enter payments as received. Also since each is on commission, print out a report that tells commissions owed to them after payment is received at any given time. What we thought would be rather simple, has turned into a hair pulling experience. Apparently we are more simple than what it is or it's too much to ask. Ideas would be appreciated.

Originally Posted by OldDj

I work for a small weekly newspaper and we are attempting to come up with a simple bookkeeping spread sheet. We would like each salesperson to be able to enter their sales into their customer's accounts and at the end of the month be able to print a statement for each of their customers and be able to enter payments as received. Also since each is on commission, print out a report that tells commissions owed to them after payment is received at any given time. What we thought would be rather simple, has turned into a hair pulling experience. Apparently we are more simple than what it is or it's too much to ask. Ideas would be appreciated.

Can you zip up what you've got so far and attach here so that we can take a look.

It sounds a relatively trivial and simple application to put together, but as always the devil might be in the detail. Good layout and design is essential to a task like this, and it will almost certainly involve a database sheet, perhaps a payments sheet or this might probably be included as part of the database sheet, a sheet which holds templates for the customer statements, and another sheet for a Commissions report.

With the best will in the world I don't think it's a good idea for users to roam freely about the workbook entering data in an uncontrolled way. I guarantee you will end up with a mess as people put things in places they shouldn't, or worse perhaps delete stuff or accidentally destroy formulae.

Data entry to the database needs to be controlled with a simple VBA menu. Don't be frightened off by this, it's not a particularly big deal and is fairly easy to design and implement, although I understand that if you are just getting to grips with the worksheet functionality, VBA might seem to be a planet too far! but really I think it's essential for the application you describe.

Are you expecting sales people to all use the same master workbook, or to all keep their own isolated data? If the latter you'll need to consider how you would want to consolidate the individual workbooks for central 'management' reports.

As I say, attach the workbook here and I'll take a look.

Rgds