Query for Forecasting Fuel Usage

[Walter]

I’m developing a database for fuel inventory. I designed seven tables. I’m
trying to forecast 16 weeks of usage based on historical sales, but I can’t
figure out how to do this. I made a query that takes the current inventory
and subtracts the sales. It looks like a spreadsheet. The query is
Month1week 1 and goes out to Month4week4. How can I make an Access query and
get out of my Excel spreadsheet thinking? The forecasting is the critical
part my database. I was using a spreadsheet to do my forecasting, but it’s
very large and hard to use. I think Access is a better way to accomplish my
forecasting. Thank you in advance for your help.


Company
ID (Primary Key)
CompCode
CompLocation


Inventory Data
ID (Primary Key)
Product (Foreign Key)
CurrentInv
MaxStorage

Inventory Transaction
ID (Primary Key)
TransType (Foreign Key)
CompCode
Product
CargoNum
Quantity
DischargeDate

InventoryTransType
ID (Primary Key)
TransType (5 Types)

Product
ID (Primary Key)
ProductCode
ProductGroup
Price
ProductName
Discontinued


SalesData
ID (Primary Key)
CompanyCode (Foreign Key)
Product ID (Foreign Key)
1MonthAvgSales
Remarks

DischargeGroup
ID (Primary Key)
CompanyCode (Foreign Key)
DischargeGroup
ProdctCode
DischargeMode


Walter

 

[vanderghast]

Forecasting can work with a set of data points (x_i, y_i), where the x_i are
values in time (date and optionnaly time) and the y_i are some numerical
measurement.

Right now, your x_i are not supplied as data but as names, so it would be
preferable to change the data presentation from

Product Month1Week1 Month1Week2 ... Month4Week4 ' fields
1010 10 11 ... 12
' values


to something like:

who when amount 'fields
1010 9/7/2009 10
1010 9/14/2009 11
....
1010 12/28/2009 12 ' values



so, for one item, instead of one record with 16 amounts, you now have 16
records, with one amount and one date (plus other relevant '' id " )

As far as what kind of model you would use for you forecast, that is up to
you (do you have 'seasonal fluctuations' or not, within a year? are you
using a 'growth' model? or are you interested in the trend rather than to
day-by-day fitting curve? etc) , but generally, it will involve various SUM
of various product. For the simple linear model, as example, someone can
use:

y = mx + b

over a set of point (xi,yi), then

m= (SUM(xi * yi) - SUM(xi)*SUM(yi)/COUNT(xi)) / ( SUM(xi^2) -
(SUM(xi)^2)/ COUNT(xi) )
b= AVG(yi) - m*AVG(xi)


http://mathworld.wolfram.com/LeastSquaresFitting.html would give your more
mathematical details (maybe with too much details in the derivation of the
results, though, imho).

You can also Google/Bing on "time series" for other mathematical models.




Vanderghast, Access MVP