Sales Forecast

[marquin]

How to create a workbook that computes a sales forecast for the nex
year assuming an increase in sales of 5 percent, list each month fro
january through december with a total forecasst for each month and fo
the year

 

[joeu2004]

How to create a workbook that computes a sales forecast for the next
year assuming an increase in sales of 5 percent, list each month from
january through december with a total forecasst for each month and for
the year.

Do you mean that total sales increases 5% per year, and you want to
apply the monthly rate equally to each month (surprise!) so that you
get a 5% annual increase?

In that case, the monthly rate can be computed as (in A1, for example):

=rate(12, 0, -1, 1+5%)

See how to apply that rate below.

Or do you mean that you want a year-over-year increase of 5% applied to
each month?

In that case, put 5% into A1.

In either case, if B1:B12 are last year's sales figures for each month,
next year's sales forecast can be computed in C1:C12 as follows (using
C1as an example; copy down to C2:C12):

=B1*(1+$A$1)

The total forecast for the year is simply SUM(C1:C12). The annual
percentage increase of total sales is (formatted as Percentage):

=sum(C1:C12) / sum(B1:B12) - 1

Or do you mean something else altogether? What?

 

[joeu2004]

Errata....

Do you mean that total sales increases 5% per year, and you want to
apply the monthly rate equally to each month (surprise!) so that you
get a 5% annual increase?
In that case, the monthly rate can be computed as (in A1, for example):
=rate(12, 0, -1, 1+5%)

On second thought, that does not result in a 5% annual increase. It is
nonsense in this context. Sorry.

Or do you mean that you want a year-over-year increase of 5% applied to
each month?

That results in a total 5% annual increase.

The total forecast for the year is simply SUM(C1:C12).

..... Which is all you wanted.

The annual
percentage increase of total sales is (formatted as Percentage):
=sum(C1:C12) / sum(B1:B12) - 1

This is necessary only if you want to account for any anomaly caused by
rounding.