Invoicing with Stock Control Design ....

[mond007]

Hi
I was wondering if anybody would care to comment on how much a typical project such as the following might be costed at ? (fixed price, i know lots of pitfalls but client is adament)

I am a VBA programmer and do very complex Excel reports etc... (15 years as a Business Analyst but this is the first time I have been asked to design a invoicing system with a Stock Management.

If you don't have time, just look at the last 4 or 5 screen shots to get instant idea of the entire project.
http://www.specialistwebdesign.com/a...oice_stock.pdf

CUSTOMER_TBL
PRODUCT_STOCK_TBL
INVOICE_TRANSACTION_TBL
INVOICE_TRANSACTION_DETAIL_TBL

Am I a little naive about the database structure/project, as I have seen some pretty expensive and very elaborate stock control systems ?

Any ideas how much this would be worth ?

I have an idea in my mind and I guess I just want a second opinion. I will let you know what I had in mind after ... so as not to bias.




Thanks in advance for the insight...

Kuldip

[Datagopherdan]

I took a quick look at it and it involves invoicing, which as I'm sure as you're aware from your business analysis experience can involve a number of different variables that need to be accounted for e.g.(Are full payments received in one lump sum or can they be deferred?, Is there any type of blanket invoicing where the customer will have an open invoice for a set time frame?, how would credits be handled in the event the customer was overcharged?. Theres also inventory management involved, which can also contain a number of different variables, e.g. (Do multiple warehouses need to be accounted for?, are there any type of special order products? is backorder tracking going to be needed? Is any type of Supply Chain Management System going to be needed? Can the same product be carried by more than one supplier?).

Just quickly looking over it without looking deep into the requirements, I would say at a minimum, it's a $2,000.00 project, which may very well be substantially higher than that depending on all the variables and aspects they need. I would recommend breaking this project out into stages. This makes it easier to come up with an estimate and also for development. This way each segment can be tested and corrected along the way if need be, so all the logic is correct when you move on to the next segement and you won't have to waste time backtracking and fixing a whole lot of aspects because some of the logic is wrong.

[mond007]

Thanks Dan, really appreciated...

Always full payment on invoice. They either raise it or not. No time lag.
If they get over charged then a credit is issued.
No multiple warehouses
No specialist order product.. only simple shoes.
No back ordering or tracking required.
No Supply Chain Management System
I in fact had already quote then £1,250 to £1,500 (accepted) for just the invoicing part WITHOUT the stock control but I thought and they wanted the whole solution from the miniute go...

So I thought I price of £3,000 to £3,500 would cover me for the worst possible case.

You are quite correct split it up even though the quote is for the whole lots...


What did you think of the design ? was I naive ? or does the design look ok... there seems to be a myriad of design everywhere for this and is a maze...

Thanks in advance.. kuldip

[Datagopherdan]

I would be very careful about that "Full Payment on Invoice". That might be how they want it but let's face it, in the real world, that's not always the case. There can be overlapping invoices where the client sends full payment to cover both or they accidentally send too much or too little. With a setup like that, there would be no way to accurately account for that.

I'm surprised that they don't want to be able to track backorders. That's typically a key ingredient for inventory availability. Without it, they can find themselves taking orders and making promised delivery dates without realizing the product will not be available at the time they promised the customer, which leads to irate customers who may have promised that product to their customers based on what they were told.

I didn't go through your entire document but as you know from business analysis, you really need to sit down and write down the whole process and account for all the possible variables that can occur and bring it up with the client. You also need to use your experience to help guide the client. For example, suggesting that they go with a more flexible invoicing system where multiple payments can be applied to the same invoice isn't trying to sell them on an extra. It's something that will cover them in the event that, that happens, which it could. By doing that, they're covered, so they won't have to put things on hold and try to rush a workaround for it.

Thanks Dan, really appreciated...

Always full payment on invoice. They either raise it or not. No time lag.
If they get over charged then a credit is issued.
No multiple warehouses
No specialist order product.. only simple shoes.
No back ordering or tracking required.
No Supply Chain Management System
I in fact had already quote then £1,250 to £1,500 (accepted) for just the invoicing part WITHOUT the stock control but I thought and they wanted the whole solution from the miniute go...

So I thought I price of £3,000 to £3,500 would cover me for the worst possible case.

You are quite correct split it up even though the quote is for the whole lots...


What did you think of the design ? was I naive ? or does the design look ok... there seems to be a myriad of design everywhere for this and is a maze...

Thanks in advance.. kuldip

[mond007]

Dan,

I agree I need to sit down with them with a check list and talk over all the ramifications.

As for backorder, natually they will have the option of searching through past invoices via a Account Ref field on the invoice form. See appendix screen shots... like any access form.

Generally, I am reading that have stock full of warehouse and they sell their good via phone etc... so if they can fulfil an order in full they will do and invoice for it. If they part ship then they only ship and invoice for that part of the order they can fulfill.

They also raise a Performa which can become the basis of an invoice. The performa is no more than a glorified invoice in nothing more than storage in a table. .. with no action taken.

It is well to note that if several invoices are created for a customer they are linked together in Sage via an Account Ref field. Multiple Invoices appear againt an account. What gets posted to SAGE is an invoice net. vat and total (no invoice detail lines). They wanna keep oit this way for the accountants.

What did you think of the basic table and design ?

Thanks in Advance Kuldip

[Datagopherdan]

I didn't really go through everything but you seem to be familiar with business logic and process as well as database theory, so I'm sure everything is good with your design, which is the most important part of any database. Good luck with your project.