project - predicting inventory for six years

[OSCStudent]

I have the table set up with Items in A3:A18. I have the Years i
B2:H2. I also put in 2003 and the sales figures to make it easier wit
the formulas. The population, change in population and percent o
customers in A22:A24. The only thing that might even be helpful her
my professor taught us was the IF function. But I do not see how thi
will get me to a prediction of inventory. Maybe (using 2299 fo
scanners sold and 810 people per in 2003 that bought them)
=If(2299/810,if(13,500+192)*6%)) or something along those line. I a
stumped on this one. My professor was gone for 4 meeks on maternit
leave so we had to read it all out of the book and I work better when
see it done. To answer the latter of your questions 810 People did sho
in the store in 2003, so based on that you take 2299 and divide it b
810 you would have an average of roughly 2.84 scanners per customer.
really appreciate the help

 

[Tom Hayakawa]

If you can get monthly numbers for 2003, as opposed to a single annual
figure, you could use the TREND function to extrapolate out to wherever you
need to go. You can also use this as a base and insert whatever other
variances you need to have.

For example, if your monthly 2003 sales figures for scanners are in cells
B2:M2 like this: 2,4,6,4,8,2,4,3,7,9,11,12 you could write the TREND formula
to predict Jan 2004 with this formula:
=TREND($B2:M2,,{13,14,15,16,17,18,19,20,21,22,23,24})
Feb 2004 would have this formula:
=TREND($B2:N2,,{14,15,16,17,18,19,20,21,22,23,24})
And so on out to December.
If you need to show a 6% growth somewhere just multiply the formula by 1.06,
like this:
=TREND($B2:N2,,{14,15,16,17,18,19,20,21,22,23,24})*1.06
Understand that you are affecting all the monthly formulas after this one as
well.
This would assume known values in B2:M2 and a trend you wanted to project
out another 12 months. Take a look at the Help for TREND. There's a lot of
detail in there about how to code the values. Be aware that your starting
and ending known values play a large role in the subsequent slope of the
trend line, so choose them carefully.

Good luck,
Tom Hayakawa