Software·43,775 discussions

MS Access – Annual Inflation on Price Lists

Locked

Hi

I have an inventory database. Each item is given a price based on the lowest price paid during the year. Thus, I have a query called “Product Prices” which lists almost all products and the lowest price during the year (some products weren’t purchased and therefore have no price yet).

However, when the next year starts, I need to adjust all those prices according to inflation. I’m not concerned with what I actually paid for the items, but rather what they are worth (or should be worth) today. Thus, I need to adjust for inflation.

I’ve been struggling a while trying to figure out how to accomplish this so any help would be great!

Cheers

Topic

Clarifications

In reply to MS Access – Annual Inflation on Price Lists

Clarifications

It seems you are departing from reality somewhat …

In reply to MS Access – Annual Inflation on Price Lists

Since, by definition, the influence of inflationary pricing cannot have any effect on presently owned goods, quite the reverse.

Unless you are describing stock items that are held awaiting resale. In this case there is a terminology for what you are trying to do – it’s called “cheating the taxman”.

You buy at the under-inflated price and hold it in stock (and on your financial accounts) at the lower price. When you come to sell it on, you calculate taxable revenue from the combination of profit + the lower price, but actually sell it at profit + higher price.

I may be mistaken but that is a common practice among scoundrels in the UK. Usually the profit is slender due to the enforced corruption charges when they finally get caught.

You certainly need to go back to school to understand what inflation is and what causes it – oh no, forget what causes it, you already know that answer – YOU DO ! 😉

Seriously?

In reply to It seems you are departing from reality somewhat …

You have no idea what my situation actually is and what I’m doing with this information. It has no impact on how I book revenue, what my profit margins are, or how my accountant assesses the value of my inventory for fiscal year-end. You’ve based your answer and insults on very little information. How about we focus on the question and not my business practices or level of education I hold?

Let me try and restate my question so that others won’t assume I’m trying to “cheat the taxman”.

I have a field in a table which holds a price. There are hundreds of records each containing this price field. I would like the user to press a button on a form to iterate through all the records and adjust ALL the prices by some user defined percentage. I’m assuming I’ll have to code this so I’m looking for help on how to go about it, not on business advice.

Thanks

I’m just not getting what you are after here

In reply to MS Access – Annual Inflation on Price Lists

Ajusting your lowest price (well in fact all of them really) is of little value as far as I can see and will seriously mess up other analysis.

On top of that unless you do only one type of product any inflationary rise ( given that you can even start to identify it as due to inflation which I doubt) is unlikely to be across the board.

Personally I’d change lowest price, to lowest price over the last 12 months, or the most reent before that, or perhaps teh loweest of the last n buys or some such.

Based on the asuumption that what you really want is a provisional cost / price.

As for how.
Some vgaue clue as to what this data is stored in might be useful.

Good point – let me elaborate

In reply to I’m just not getting what you are after here

We install lawn sprinkler systems. We buy all of our inventory at the beginning of the year, and use it up (via installations) through the season. Let’s suppose we purchase a sprinkler head for $50 in 2005 from a supplier. Now, lets say that we don’t use that sprinkler head for two years because business is slow. There are two pieces of information we need to know, based on the 2 year old price of the sprinkler head:
1) How much should we charge the customer for that head now in 2008?
2) How much should we budget for purchasing more sprinkler heads for 2009?

You might think “go look it up”. Well this is difficult when we have over 600 different parts that we need to charge people for and that we need to budget for repurchase.

Does that make any more sense?

MS Access structure as it is now:

Product Table:
ProductID, Supplier, etc.

Orders Table:
OrderID, DateOfPurchase

Orders Details Table:
OrderID (lookup in Orders table), ProductID (lookup in Product table), Quantity, PricePaid

We derive a product’s price by how much we are charged at the time of ordering. We don’t look up the price in a catalog or something. Now, we may order the same part a few times during the year because we’ve run out. Therefore, we use the lowest price paid during the year for that product’s price.

*Deep breath*

So, with a query I get a list of the minimum price paid for each product for which we’ve ordered and use that number for billing and/or budgeting purposes.

Now, using the example at the beginning, how would I adjust the $50 price tag on the sprinkler head to what it might be now? We have a good idea of the percent increase in products from year to year and we need to use that percentage to bump the price on these items.

Does that make any sense?

two ways

In reply to Good point – let me elaborate

If you drive all your suggested price and budgeting from the minumum price query then
just mutiply it by x

If you want to store the value then personally I’d create a table called ListPrices keyed by product Id and then once every time the factor changes empty it nad then do an append query and again multpily it by this years factor.

You could get even cleverer and add year as a key to listPrices and the append to it each year and have nice table whenre you could look at procies chnages in more detail, even project into years ahead.

If you put year in as a paraneter and ListPrices had a structure of year,ProductId,Price

and you min proces query yields ProductId, price.

then
Delete from ListPrices where year = ?
followed by
insert ListPrices(Year,ProductId,Price)
Select pYear,……

put the rest of your min prices query here.

On y va

This is the central portion

In reply to MS Access – Annual Inflation on Price Lists

“update Inventory
set ProductPrice = ProductPrice *” & CalculatedRation & “;”

You embed this in VBA.

[Author]

Replies