monthly sales forecast

[Tammy]

Hello,

I have a report broken out by month and I would like to
forecast a goal for the year and project the amount we
would have to selll each month to accomplish that goal.
Please help!

July ....Dec Jan ...Feb March April May June Forecast
200 300 100 100 100? 100? 100? 100?2400?

 

[JE McGimpsey]

One way:

Assuming your values are in A2:L2, with the forecast in M2:

A2: =$M2/(COLUMN($M2)-COLUMN())
B2: =($M2-SUM($A2:A2))/(COLUMN($M2)-COLUMN())

Then, as you enter data in A2..L2 (overwriting the formulas), the
remaining months will self-adjust to meet the goal.

 

[Bob Phillips]

Hi Tammy,

Assumptions.

The year forecast is in M2
The actual sales are in A2:L2
The target sales are in A3:L3

In A3, enter this formula

=IF(A2="",($M$2-SUM($A$2:$L$2))/(12-COUNT($A$2:$L$2)),"")

and copy across to L3

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)