Access development

[cm]

Hi,

Could somebody help me, please? I was asked to develop an application in
Access to check the forecast accuracy and then to create new forecast index.

The original table in Access has the fields: product code, forecast group,
warehouse, seasonal indexes for 13 periods.

So far, I built thousands of queries but the problem I am facing now is how
to group the products so to determine the new forecast indexes.

Based on sales history for 4 years I calculated historic indexes for all
products and compared them with seasonal indexes. Then all the products not
having the accurate seasonal indexes have to be grouped together in different
forecast groups to calculate the appropiate forecast indexes. The problem is
which method to use to group products having the similar historic sales
allure and to calculate the new forecast indexes.

Has anybody approached this kind of applications and could help me? I really
do not know what to do.

The other problem I encountered is when performing some calculations, Access
changes the index field which has to be numeric into text. I tried to change
the field type but got others errors (the numeric is not like 1.32, but round
to 1).

Please any help would be most appreciated.

Thank you.

 

[Steve]

To start, your table seems incorrect. From your description consider:
TblProduct
ProductID
ProductDescription
ProductCode
SeasonalIndex

TblWarehouse
WarehouseID
<Other warehouse fields.

TblProductWarehouse
ProductWarehouseID
WarehouseID
ProductID

TblForecastGroup
ForecastGroupID
ForestGroupName

TblProductForecastGroup
ProductForecastGroupID
ForecastGroupID
ProductID

Can you calculate what you need from these tables?

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[cm]

Hi Steve,

Thank you very much for answering me.

Yes, you are right. The tables you recommended are logical built. I created
these tables but to compare the indexes I need to have all fields in the same
table.
The idea is using these tables to create a new query/table so I can
determine the accuracy of forecast item within the forecast group. Logically
this should be: if the variance is lower than 20% then the forecast index is
accurate and keep the forecast group, then the forecast index is not accurate
and need to calculate a new one for the products having the similar sales
history. If it is a total forecast that would be quite easy, but I do not
know how to compare all 13 periods. I mean to compare 13 forecast indexes
with 13 historic index and then to find a way to group products to aggregate
sales and calculate the new forecast index to be used in the business.

Any idea, please?

Thank you.

Carmen

 

[Steve]

<< to compare the indexes I need to have all fields in the same table >>

No, put that thought out of your mind! With the correct design of your
tables, you can get all the fields you need into a query and do your
calculation in that query.

Let's go to private email and work this out.

Please answer the following questions:
1. How do warehouses enter into all this?
2. Seasonal Index ---
a) Explain what a seasonal index is.
b. Is there a seasonal index for each product?
c Are there more than one seasonal index for each product?
3. Explain in detail what a forecast group is.
4. What is a forcast index?
5. What is a Historic Index?
6. Explain what the 13 periods are.


PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed).

 

[Tony Toews [MVP]]

Let's go to private email and work this out.

Let's keep the discussion in the public newsgroups.

Carmen

Be aware that Steve has a history of aggresively soliciting work in the newsgroups.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Keith Wilby]

Let's keep the discussion in the public newsgroups.

Carmen

Be aware that Steve has a history of aggresively soliciting work in the
newsgroups.

Let's face it, that is *ALL* he is here for.

 

[Steve]

Hi Carmen,

I was very interested in your forecast application and was prepared to help
you start to finish to complete your forecast application. Unfortunately,
you chose to let a couple of no-gooders ruin the opportunity for you. Did
you notice how much help they offered you? That's too bad!!

Good luck with doing it on your own.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Tony Toews [MVP]]

I was very interested in your forecast application and was prepared to help
you start to finish to complete your forecast application. Unfortunately,
you chose to let a couple of no-gooders ruin the opportunity for you.

No. Carmen did not choose anything. Her only response in this thread has been to
a posting of yours. So don't go laying a guilt trip on her or us.

Did
you notice how much help they offered you? That's too bad!!

You started off quite nicely but then when you said to "Let's go to private email and
work this out." well, that raised the usual alarm bells.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Keith Wilby]

Hi Carmen,

I was very interested in your forecast application and was prepared to
help you start to finish to complete your forecast application.

But I have a similar application and this thread would have helped me. Oh,
that's right, "let's go private" killed off that completely.

To the OP: if you want to do business with Steve then you'd be wise to read
this first:

http://home.tiscali.nl/arracom/whoissteve.html

Unfortunately, you chose to let a couple of no-gooders ruin the
opportunity for you. Did you notice how much help they offered you? That's
too bad!!

You just don't have a clue do you Steve?

 

[cm]

Hi Steve,

Thank you very much for answer.
OK, you are right. I built the query to calculate the Forecasting Error.

Answering to your questions:

1. How do warehouses enter into all this?

A1. The primary key for the Product Seasonal Index Table is Product_Cd
and Warehouse_Cd. The seasonal index is calculated based on sales movement
and the demand varies for different Warehouses because the customers have
different needs/wants.

2. Seasonal Index ---
a) Explain what a seasonal index is.

A a) A seasonal index is an index used to calculate days supply when placing
a new Purchase Order into the Buyer System. The seasonal index is calculated
based on sales movement. Seasonal index gives the seasonality for a time
series. The calculated forecast is multiplied by the current period index
(there are 13 periods for 105 sale movement weeks). The result is a seasonal
forecast.
There is a third-party application in COBOL (Buyer System) which calculates
all this indexes. In Access I have to check the forecasting accuracy and to
recalculate all the seasonal indexes when the forecasting is not accurate.

b. Is there a seasonal index for each product?

A b) Each product (Product_Cd, Warehouse_Cd) is assigned to a forecast
group. How it works: when a new product is create into the system it is
assigned automatically to the default Forecast Group with a flat series
(index is 1 for all 13 periods, meaning that it is not expected to have a
seasonality(increase or decrease in sale) for that product. The Product
Manager has to predict how will be the sales of that product and to find a
non-default forecast group. The product is then assigned to this non-default
forecast group and automatically the product will have the forecast group’s
seasonal index. All products (in that warehouse) assigned to the same
forecast group will have the same 13 seasonal indexes. Unfortunately, the new
product is not moved from the default group and it keeps the flat index even
during all product life cycle. This is a big issue for the business.

c Are there more than one seasonal index for each product?

A c) Based on the application, each product has to have only one seasonal
index, the forecast group’s seasonal index.

3. Explain in detail what a forecast group is.

A. 3 A forecast group is all items expecting to have the same seasonality
(pattern) for 13 sale periods (one year). The forecast group has a code (e.g
001 is the default forecast group, DJ3, etc.), description, and 13 seasonal
indexes.

4. What is a forcast index?

A 4. A forecast index is the same with seasonal index (the system index). I
used it to differentiate it from Historic Index.

5. What is a Historic Index?

A.5. The Historic Index is the actual index, which I calculated it based on
sales history for 4 years. This is not in the Buyer system. I calculated it
to compare the seasonal system index with the actual sale and to determine if
the forecast is accurate or not.

6. Explain what the 13 periods are.

A. 6. In this industry a year has 13 periods (instead of 12 months) because
the demand is critically for Supply process to be predicted at the week
level. A period has 4 weeks.

I tried to explain you based on what I know about this Buyer System. I have
no contribution to the design, develop of this application. All the process
is very complicated and I dare say wrong, because the forecast has to be at
product and store level, not at product warehouse level. What I was asked to
do is to correct the forecast error based on sales history and identify a
more appropriate pattern for each product in order to avoid overstock or
shortages because business loses money or customers.

 

[cm]

Hi All,

I apologize to all of you. My question it wasn't intended to start all these
discussions. I really need help with this application and I don't know whom
to ask for guidelines and/or feedback.

The reason I didn't answer untill today is we had long weekend this weekend
and this time I decided not to work from home.

Thank you all of you for your answers.

Carmen

 

[Steve]

<<You started off quite nicely but then when you said to "Let's go to
private email and
work this out." well, that raised the usual alarm bells.>>

That was none of your business!!! You butted in to something that was of no
concern to you. Rather than provide the OP any help, you chose to be
ignorant. That makes you a No-gooder. You responded to this thread twice
now, spent time responding and contributed absolutely nothing to helping the
OP.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Steve]

Carmen,

I will help you to work this out if you go to private email.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Tony Toews [MVP]]

<<You started off quite nicely but then when you said to "Let's go to
private email and
work this out." well, that raised the usual alarm bells.>>

That was none of your business!!! You butted in to something that was of no
concern to you. Rather than provide the OP any help, you chose to be
ignorant. That makes you a No-gooder. You responded to this thread twice
now, spent time responding and contributed absolutely nothing to helping the
OP.

Tough.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

I apologize to all of you. My question it wasn't intended to start all these
discussions. I really need help with this application and I don't know whom
to ask for guidelines and/or feedback.

No need to apologize. Steve brought our comments by his typical behavior.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Arno R]

<<You started off quite nicely but then when you said to "Let's go to
private email and
work this out." well, that raised the usual alarm bells.>>

That was none of your business!!! You butted in to something that was of no
concern to you.

Well Steve, naturally it is Tony's concern...
Like I said: We did stop the 'automatic answering machine' but we still are watching you *very* closely...

Private email is 'not obvious' in the newsgroups.
It is very unusual and suspect of you to suggest to go to private email.
Why won't you help the OP here ??
I am sure the *only* thing you are after is to get some money for your (questionable) help.

The obvious thing is to help people here, for free.
The obvious thing is that we all want to benefit from the knowledge that is spread here.
==> So let's *not* go to private email.

Arno R

 

[Steve]

Is your behavior typical behavior of an MVP? Is offering no help to the OP
but showing your ignorance typical behavior of an MVP?

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Steve]

That's four responses you made in this thread and not a one offered help to
the OP. That's totally ignorant! Is this what Ops can expect from an MVP?

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Tony Toews [MVP]]

That's four responses you made in this thread and not a one offered help to
the OP. That's totally ignorant! Is this what Ops can expect from an MVP?

No Steve. Your blatant forms of getting customers are the issue of discussion.
Please don't attempt to ignore that issue.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Keith Wilby]

Carmen,

I will help you to work this out if you go to private email.

No Steve, help Carmen here so that others may benefit. That's the whole
point of this forum. Why on earth would anyone insist on using private
e-mail? If someone asks you for directions on the street would you attempt
to lure them to your parlour? Perhaps this is another episode to be added
to this:

http://home.tiscali.nl/arracom/whoissteve.html