Getting out the starting blocks - mining sales data for predicting future stock. (1 Viewer)

peskywinnets

Registered User.
Local time
Today, 08:34
Joined
Feb 4, 2014
Messages
567
I really want to learn access...& I've set myself a simple task (which will really help my fledgling business predict how much stock I need to hold based on recent sales - stock prediction is a nightmare so this will really help!)

I've some sample sales for about 8 months in spreadsheet form, structured with columns like this....

Order date product quantity sold


What I really need is an access database filled with that data, then to be able to run a report, something like this....

User enters a date period (from/to) to drill down on the data

User enters number of days stock holding required (how much stock I need to hold to satisfy the chosen number of days)

Which then gives the following output results....

Total quantity sold for each & every SKU in the database.
Average Units sold per day for each & every SKU in the database.
How many SKUs will be needed for the chosen number of days stock holding for each & every SKU.

To explain, let say over a 20 day period, I sold 2 items per day of a given product & I want to know how much of of this item I need to order to satisfy 180 days stock holding.

Example:
User selected date period = from 1 Jan 2014 to 20 Jan 2014
No of days stock holding required = 180

report result...

Total quantity sold = 40
Average units sold per day = 2
How many SKUs will be needed for the chosen number of days stock holding = 360


Bearing in mind I have little access database experience (But a fair amount of excel experience) ...with a little coding in Basic coding small MCUs (PICs), how long would it take me?

Can anyone help get me started (see sample data attached) ...I'm figuring I can't be first person who require a stock ordering predictor so there's probably a simple database that I can use/modify somewhere or other :)

Many thanks,
pesky.
 

Attachments

  • temp_sales.xls
    440 KB · Views: 253

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 08:34
Joined
Sep 12, 2006
Messages
14,835
This isn't simple, to be honest - but you need a database that stores historical sales data in a way that you can extract suitable activity summaries for your required period.

you can then use some math to calculate a future projection, based on the historical data - the accuracy of which will depend on many things.
 

Mihail

Registered User.
Local time
Today, 10:34
Joined
Jan 22, 2011
Messages
2,373
To explain, let say over a 20 day period, I sold 2 items per day of a given product & I want to know how much of of this item I need to order to satisfy 180 days stock holding.
Unfortunately, the prediction theory say that you need to observe the stoke in a period of 180 days then to make predictions for 20 days (or less).

There are many things to consider here.
As example, the stoke for Whisky is not the same in the winter as on the summer, as well as the stoke for beer is not the same on the summer as on the winter.

Before to know how to program this, you should know:
1) how to collect the information;
2) how to apply math for this.

It is very easy to program something.
Hard is to know what to program.
(Mihail) :)
 

CazB

Registered User.
Local time
Today, 08:34
Joined
Jul 17, 2013
Messages
309
Purely based on the original question, and keeping it as simple as possible, you could just import your spreadsheet into Access as a new table, and then write a query on it to perform the calculations you said you wanted, above... something like in the attached file.

However, as has already been said, there are lots of issues with predicting future sales based on previous sales without taking account of seasonal fluctuations, and other factors....
 

Attachments

  • Database1.accdb
    932 KB · Views: 208

Users who are viewing this thread

Top Bottom