Question Sales Forecasting Tool

[pbowler]

Howdy all,
I am attempting to replace a gigantic Excel sheet (I bet you've never heard that here) that tracks sales by dealers (Excel Worksheets) for each product (Row), monthly (Columns) then rolls the info up (very clumsily) into a single per Product (still rows) monthly breakdown (still Columns). Dealers fall into pricing levels such as Gold Silver etc. (and no it's not 5%-10%15% of course, it's custom made up prices).

The sheet also includes a differentiation for forecasted sales versus actual orders (VERY SIMPLY, as in "how many did we order last year for October?"

I figure I need a transaction table that would allow me to record each entry, consisting of:

Customer
Product
Forecast Q (or actual Q for existing periods)
Period (month/year)
pricing level

I am trying to keep the user interaction as similar as possible, (our ERP got pushed off and I can't sleep knowing this excel sheet plays such a large role and does it so poorly) essentially somebody will add info a bit at a time.

entries will be likely one dealer month at a time, eg. "here are the November numbers for Dealer Brothers inc." which will either be next November (projected sales) or last November (actual sales). and will include number of units sold/forcasted for each product.

anyway, I figured I'd throw this out there and see if I'm close.
cheers in advance

 

[jdraw]

I suggest you Google "forecasting using ms access" and review some of the result links.
There are quite a few. If you see anything promising, you should ask specific questions here.
As you suggested -- this isn't the first time the question has been asked.

Good luck

 

[Lightwave]

A lot of forecasting can be made a lot easier by using pivot queries.

I suggest you have one table which stores all the data with regard to the sales from the salesman. The table definition to me looks correct. You then just want to query it in different ways to get the information out of it quite possibly concentrating on pivoting.

To make sure you understand pivots you might find it useful to make up a short table and place 4 rows in it.

Tbl001Sales
ProdA / Salesman A / Qtr1-2012 / £100
ProdB / Salesman B / Qtr1-2012 / £150
ProdA / Salesman A / Qtr2-2012 / £200
ProdC / Salesman C / Qtr3-2012 / £300

From this table (or something similar) you should be able to get any combination of what you are looking for using something called Pivoted Queries. Pivots exist in excel as well and in older versions of Excel were called Transformations. MS have gone all goey about Pivots recently in SQL Server particular calling them power pivots but they've been around for er since 97 I think.

Creating a Pivot Query and making
Salesman the Row
Period the Column and
sum of Sales the Value

would take the above table and create a table something like

(I had to use dashes as place holders to get the formatting right)

--------------Qtr1-2012---Qtr2-2012---Qtr3-2012
Salesman A-----£100---------£200
Salesman B-----£150
Salesman C ------------------------------£300

Changing the row value would mean that the product names would be down the left hand side. You can have multiple values in the row headings so you could further group Sales of products by salesman if you want.

This is no different from power pivot.

Practice getting a pivot query working with this small table and then you should start to see the power of pivoting things and it will give you the understanding to set up your table with a bit more detail.

If you are having difficulties setting pivots up just go to you tube and look up pivot for the particular version of Access you are trying to do this in.

I can assure you that access will be able to handle anything that is in a spreadsheet. And the reports that you can produce will be a lot neater once you get a hang of it.

It should be noted that there may be a bit of a manual process which involves taking the information out of the excel format and transforming it from your table format into the more vertical format. Its usually not that hard though and only needs to be done once afterwhich you enter new figures straight into the table

 

[pbowler]

Howdy all, good suggestions thanks.

I think I may have sent you guys of on an incorrect tangent with the use of the word "Forecasting".

I should have thought of this earlier but what I'm trying to do doesn't actually do ANY forecasting, it simply totals up what the Dealers have told us they think they will order.

All of the forcasting tools that show up include some sort of function/logarithm to PREDICT sales, I am really just looking for a way to record what our customers TOLD us they will be purchasing.

The information for Each Dealer we track is month by month, how many we'll probably order, then after the fact, how many we actually ordered. that's it.

I don't need sales rep info or anything, just a way to track Which Dealers said they would order how many of each product each month.

Does my transaction Table seem like the right approach?

 

[Lightwave]

Yes I think so

The scenario I have had a go at mapping out would work just as well for trend analysis as forecasting.

 

[pbowler]

I'm Back!


After a number of false starts and a decent amount of research I came up with the following design.

Tables:
Distributors (ID, Dealer_name)
Products (ID, Product_name)
Period (ID, Period)(Dec12, Jan13, Feb13, Mar13)
Transactions (ID, Dealer_name, Product_name, Period, Forcast, Sold).

Query
Dist_Query
Prod_Query
Per_Query
Tx_Query

Forms
Transaction Form

My idea is that anytime we get forecast info (or retro active sold info) it gets entered into a form which populates the Transaction table.
the Queries from each table I set up after reading about the evils of Lookups in tables.

I can get the Transactions table update from the form with the IDs for Cust, Prod, etc., OR I can get the Dropdowns to show the name of the Cust Prod but it onlly records the Forcast and Sold columns, it won;t drop the Customer ID in the transaction table.

help anyone?

thanks