Invoicing / Inventory

[focus12321]

Ive been using excel for awhile to create invoices for our customers and although it works I want something a little more cohesive (and friendly to someone other than myself).

I want to create invoices with access but I'm running into a mental road block on how to set up inventory and pricing. We have contracts with our clients that sometimes set a price on an item. So I need a way to set a price on an item per a customer and/or just a generic price (for when a client doesnt have the provision). Also some clients have a set mark up on items, so sometimes they might have a 10% mark up. I need a way of handing all these different situations. Not 100% sure where to start. Is this something I will be able to accomplish? ... Also it would be nice to track inventory so I don't necessarily want different items for each customer, just pricing.

(Im just in the planning phase of this project)

Please HELP

 

[jdraw]

A good place to start is Allen Browne appInventory

Do some research (Google or Bing)
Inventory management
Stock Control


It is quite a common request. There are several threads with discussion/ideas.

Here's a tutorial that might help you getting started.
Good luck.

 

[spikepl]

Your intentions are commendable but I want to ask what I always ask in such cases: what is the reason that you are considering in-house development (which is always much more expensive than it seems at the outset) above some commercial tool? "Invoicing" seldom comes alone: you often need orders, deliveries, purchase orders, shipping notes, shipping, credit notes ...

 

[RainLover]

http://www.access-programmers.co.uk/forums/showthread.php?t=219125&highlight=Inventory

This may help.

 

[focus12321]

Your intentions are commendable but I want to ask what I always ask in such cases: what is the reason that you are considering in-house development (which is always much more expensive than it seems at the outset) above some commercial tool? "Invoicing" seldom comes alone: you often need orders, deliveries, purchase orders, shipping notes, shipping, credit notes ...

We already have an (expensive, fairly comprehensive) accounting suite that does everything for us.. problem is, I handle all our "maintenance billing" and finding something that gives completely different reports for each client is next to impossible. Every client we have has specific things they want on an invoices. (Little ridiculous at times, but thats just how it is) Currently in excel, using complex formulas Ive done pretty well. Id just like to move to access because I can make a form entry and have people work on it without the ability of messing it up lol. And be able to see how many "widgets" we installed across all of the accounts.

 

[focus12321]

A good place to start is

Do some research (Google or Bing)
Inventory management
Stock Control


It is quite a common request. There are several threads with discussion/ideas.

Here's a
Good luck.

Not quite sure how to apply this to my problem.?? This could provide helpful, in general, but I need to assign different prices for one item to many customers, not sure how to do that.

 

[spikepl]

Fair enough.

How about some more examples of " specific things they want on an invoice"? Simply for the readers to get an impression of span of variations.

Also, is your presentation of pricing complete or is there more?

Further, since you already have some ERP-system, would you want to export data from Access to that system or do billing (and all management thereof) from Access ?

 

[focus12321]

Fair enough.

How about some more examples of " specific things they want on an invoice"? Simply for the readers to get an impression of span of variations.

Also, is your presentation of pricing complete or is there more?

We are an electrical contractor who does traffic equipment maintenance etc. So most invoices have, time of day, date, location, employee, hours worked, who called in the complaint, a detail of what they did and items they used to fix the problem. Some clients have specific numbers for each location, or extra information they may want to see. The majority of the invoices look the same/ similar information but the pricing is often "Bid" on. So they might have a mark up over cost of 10% or a specific price for an (common) item. So Im confident I can do this project, with the exception of how to set up the pricing of materials. (Unless I were to set unique item names for each contract, which I dont want to do.)

"Also, is your presentation of pricing complete or is there more?[/QUOTE]"
Not 100% what you mean.

-THANK YOU FOR EVERYONE TRYING TO HELP, I'M HOPEFUL FOR GETTING PAST THIS ISSUE.

 

[spikepl]

For pricing you have one major decision to make. Prices can have a ValidFrom date, so that you can at any time reestablish an invoice (that has to be immutable) and so that you can set price coirrections in advance, prior to billing. You'd then maintain a link to the prrice from the ínvoice. To do it like this can be a bit of a pain for coding.

The other route is to copy prices from the pricing table (we'll get to that one later) to the invoice at the time of creation of the invoice. This way, subsequenct changes in prices will not touch the invoice. and price change will affect only the invoices created after the price change.

 

[jdraw]

I think you have a situation where there are no established procedures regarding Item pricing, at least it seems that way.
If Item A has different price for every Customer, could you consider this situation.
Item A has a standard price, but it may be discounted via some algorithm.

I'm just looking for some "function" to derive the "custom pricing".

 

[focus12321]

For pricing you have one major decision to make. Prices can have a ValidFrom date, so that you can at any time reestablish an invoice (that has to be immutable) and so that you can set price coirrections in advance, prior to billing. You'd then maintain a link to the prrice from the ínvoice. To do it like this can be a bit of a pain for coding.

The other route is to copy prices from the pricing table (we'll get to that one later) to the invoice at the time of creation of the invoice. This way, subsequenct changes in prices will not touch the invoice. and price change will affect only the invoices created after the price change.

Both of these options sound a little scary actually, I want it to be friendly to end users. It would be nice to not have to change prices for each client in the invoice. Is there a way to have one item, lets call it a traffic pole, but call it pole1 (price $1000) and pole2 (price 1500) but be able to run reports of both items together so I know how many poles were installed.

 

[spikepl]

As to you prices:

an item has a base price
the customer price can be the base price plus customer-specific markup, same for all items sold to that customer
the customer price can be some other fixed price set individually for each item

more variations?

 

[focus12321]

I think you have a situation where there are no established procedures regarding Item pricing, at least it seems that way.
If Item A has different price for every Customer, could you consider this situation.
Item A has a standard price, but it may be discounted via some algorithm.

I'm just looking for some "function" to derive the "custom pricing".

This is the EXACT problem I'm having coming up with the planning phase of this. Its not always a 10% or 15%. When we BID jobs they sometimes have a list of common items and we'll put in pricing on everything. So for each customer itemA might be 10% mark up and itemB 20% mark up.

 

[spikepl]

Concerning my previous scary post: that has nothing to do with how individual prices are set, but everything to do with how the system is to function, read the post again.

 

[focus12321]

As to you prices:

an item has a base price
the customer price can be the base price plus customer-specific markup, same for all items sold to that customer
the customer price can be some other fixed price set individually for each item

more variations?

Yes!! Any of what you said can be true.

 

[spikepl]

we'll put in pricing on everything. So for each customer itemA might be 10% mark up and itemB 20% mark up.

So you say to the customer: $ 10,000 for this job, and then you sprinkle the markup over the item types ordered until the total fits?

 

[spikepl]

Please note I 'm not asking what "can be true", beciaseu taht is not exclusive. I'm asking how the prices are determined : the entire and complete story.

 

[focus12321]

Concerning my previous scary post: that has nothing to do with how individual prices are set, but everything to do with how the system is to function, read the post again.

Can you explain your option 2 a little more? (thanks for having me re-read it)

 

[RainLover]

This is one of those "My Opinion" things.

I would always store the Invoice Values rather than recreate on the Run. Your accounts must balance and this is the way that allows you to search through the records to find mistakes.

I know that this goes against Normalisation rules but there is a time that one needs to act outside the norm.

 

[jdraw]

Here's a link to a thread where I basically played back what the poster was saying--- there's a dialog to help the OP clarify his "issue". It isn't the exact issue you are facing, but reading through the exchange may bring out some new ideas. It was along the lines of Customer specific arrangements.

I recall another with custom pricing but I can't find it at the moment. I'll keep looking.