function help??!!?

  • Thread starter Thread starter gwat
  • Start date Start date
G

gwat

I'm trying to write a little sales forecasting spreadsheet.

What I want to do it be able to input the value of inventory purchase
for a given month.

Then I want a column specifying how long I expect that month'
inventory purchases to last (X months). Business is seasonal so thi
number will vary and Iant to be able to manipulate it.

Now I want to divide the value of inventory purchases for that month b
X, and add that to the sales figures for the next X months.

Can anyone help me out here
 
Assuming your worksheet has month names from A2:A13 and sales amounts in
cells B2:B23.
Enter the following in cell F1:G4 --

Month May
Purchase 50000
Months 4
Per Month =G2/G3

In cell H1, enter: =MATCH(G1,$A$2:$A$13,0)

In cell C2, enter:

=IF(AND($H$1<=MATCH(A2,$A$2:$A$13,0),$H$1+$G$3-1>=MATCH(A2,$A$2:$A$13,0)),$G$4,0)

and copy down to C13

In column D, add the values in columns B and C.

Or, you could change the month names to month start dates, and extend
the ranges for a multi-year forecast.
 
Back
Top