Projected Sales


V

Vince

Is there a way to run a report for projected sales based on what I sold the
previous month and or last 3 months and or last year same month?

Thank You
Vince :)
 
Ad

Advertisements

T

Terrible Tom

Not directly from RMS, and not easily with any method.

Statistics are funny. I did statistical analysis on telephone switching
software for a while back in the early 90s. For one, your projections will
only be as good as your historical data. Projections based on small data
samples have a huge margin of error. For two, a few accurate projections do
not make you a fortune teller--your next projection may be horribly wrong.

For example, let's say you started business on 1/1/05 and had monthly sales
that looked like this:
Jan - $10K, Feb - $11K, Mar - $12K and so on until Dec - $21K. A nice,
smooth increase of $1K/month all year long.

Now, will you assume that Jan '06 will be $22K or will it be closer to last
years 10K?

Let's say that Jan '06 comes in at $12K. What can we expect in February?
The safe bet would be on $13.1K (last year +20%, similar to January). If
you looked at just the last three months, you'd think that sales were going
down at a horrifying pace: $20K in November, $21K in December, now just
$12K in January? The 3-month trend is negative!

You need years and years of data to make any sort of reasonable forecast,
and even then you're more likely to be wrong than right.

For simple trend lines (linear or logarithmic), export your sales data to
Excel.

Good luck,
Tom
 
J

Jeff

Killjoy ;-)



Not directly from RMS, and not easily with any method.

Statistics are funny. I did statistical analysis on telephone switching
software for a while back in the early 90s. For one, your projections will
only be as good as your historical data. Projections based on small data
samples have a huge margin of error. For two, a few accurate projections do
not make you a fortune teller--your next projection may be horribly wrong.

For example, let's say you started business on 1/1/05 and had monthly sales
that looked like this:
Jan - $10K, Feb - $11K, Mar - $12K and so on until Dec - $21K. A nice,
smooth increase of $1K/month all year long.

Now, will you assume that Jan '06 will be $22K or will it be closer to last
years 10K?

Let's say that Jan '06 comes in at $12K. What can we expect in February?
The safe bet would be on $13.1K (last year +20%, similar to January). If
you looked at just the last three months, you'd think that sales were going
down at a horrifying pace: $20K in November, $21K in December, now just
$12K in January? The 3-month trend is negative!

You need years and years of data to make any sort of reasonable forecast,
and even then you're more likely to be wrong than right.

For simple trend lines (linear or logarithmic), export your sales data to
Excel.

Good luck,
Tom
 
V

Vince

Jeff,
What you talking about? :) Did you send a link or something to download?
I tried to open the extension, then used outlook, and still nothing???
Regarding the projected sales, I fully understand what you are stating
Terrible Tom, and you have a good point, but if ilke you said I could look at
last year in January, and project what I might need this year in January that
might be a good basis.
If there was any sort of report out there, I thought it might be cool to get
my hands on it that was all.
 
M

Mickie

Oh crap! You mean RMS can't tell me what my sales are going to be next year?
I want a refund!

Just kidding! You gotta laugh.

I use excel and a logarithimic trend line projection. After you have LOTS
of data, you can change the trend line to last 12 months average so you can
send a general growth trend. But, our business is very seasonal and neither
method predicts on a month-to-month basis. All you can do is look at that
same month the past 2 years or so and then factor in your average annual
growth rate. It's all you can do besides pick numbers out of a hat.
<shrug>
 
J

Jay

Vince said:
Jeff,
What you talking about? :) Did you send a link or something to download?
I tried to open the extension, then used outlook, and still nothing???
Regarding the projected sales, I fully understand what you are stating
Terrible Tom, and you have a good point, but if ilke you said I could look at
last year in January, and project what I might need this year in January that
might be a good basis.
If there was any sort of report out there, I thought it might be cool to get
my hands on it that was all.
Vince,
If you go to my post about the monthly sales report
(you maybe able to get a good forecasting tool. The report will list in
columns the Month totals and the rows would be each item. One column
would be Qty On Hand. You would be able to view sales trend at a quick
glance.

Jay
 
Ad

Advertisements

J

Jeff

Vince,

If you looked at just the last three months, you'd think that sales were
going
down at a horrifying pace: $20K in November, $21K in December, now just
$12K in January? The 3-month trend is negative!
Hence, the Killjoy comment!

I don't know of any forecasting software for RMS. Looking though!


Jeff,
What you talking about? :) Did you send a link or something to download?
I tried to open the extension, then used outlook, and still nothing???
Regarding the projected sales, I fully understand what you are stating
Terrible Tom, and you have a good point, but if ilke you said I could look
at
last year in January, and project what I might need this year in January
that
might be a good basis.
If there was any sort of report out there, I thought it might be cool to get
my hands on it that was all.
 
T

Terrible Tom

Vince,

Gather your historical data and create a table in Excel. The most logical
format (to me, at least) is one row for each year and a column for each
month. With a few years of data, you can use the TREND, LINEST and LOGEST
functions to fill in the future months of the current or future years.
TREND and LINEST will give similar (if not identical) results. LOGEST is
probably more accurate if you have a large data sample (and could be wildly
inaccurate with a small data sample).

Same month forecasting would be the best way to go. Find the average
percent increase in same month year-over-year sales and then apply that same
percentage to future months. This method is generally more accurate for
predicting November/December sales than March/April sales and practically
useless for January sales.

Good luck,
Tom
 
Ad

Advertisements

O

ozzie

Never thought I'd say this, but stats are fun! So is forecasting!

But what you want can be quite complicated, and could take a fair bit of
work to get going, but...

Probably best to use fairly simple moving averages + seasonal adjustments
(over 12 month period), as pretty much all retail is seasonally affected.
There are also a few tests (simple!) that you can use to determine whether
there is any value in using forecasting as opposed to simply using the last
months sales figures as your projection ie is your prediction better than
simply guessing next month will be like this month.

I'd keep it pretty basic unless you are running multi-site $mega$-a-month
operation where it really would pay to keep predictions for stock control
tight.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

USA Cash Flow Projection 2
Canada 52 Week Cashflow Projection 2
USA Prepaid Expenses . R&D Projects ... 0
HealthCare Sector: PDPR - Sales Projection 0
Projects 1
USA US-based Project Accountant moving to UK - what next?! 1
Project Allocation 2
USA Project Accounting 1

Top