Predicting new Ys given new Xs using known relationship for X and

Hello. I know people have posted similar questions about using an existing 
relationship between X and Y to predict new Ys given new Xs but I think mine 
has a slightly different twist. I would appreciate any help that could be 
offered.

I have 20 data points for both X and Y. Each X point  represents the number 
of workers on a farm for a particular year over a 20-year period. Each Y 
point represents the amount of crop harvest each year over that same period.  
The data is from 1901 to 1920.  When I plot the points and fit an exponential 
trend line I get a high R-square of .9 .  I would like to assume that the 
number of workers for that same 20-year period was actually 20% lower than 
the actual number of workers.  Using this new population of workers I would 
like to predict the new amount of crop that could be harvested.  When I do 
this using the function: Growth(known_y's,known_x's,new_x's,const) I get 
results that do not seem to be intuitively correct. For example,  the new 
population in 1916 is approximately equal to the actual population in 1908 
but the amount of crop that is expected to be harvested differs widely.

Thanks in advance for any help. I would be happy to send the actual numbers 
I'm working with if that would be helpful in clarifying my question.














0
malwatson (2)
1/12/2005 3:57:05 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
746 Views

Similar Articles

[PageSpeed] 48

malwatson  -

From your description I don't see anything obviously wrong.

Please post the X and Y historical data.

Also, post the values of the arguments of GROWTH you're using and the 
results you're getting.

-  Mike

www.mikemiddleton.com

++++++++++++++++++++++++++++

"malwatson" <malwatson@discussions.microsoft.com> wrote in message 
news:BBE01427-8B59-4C27-9CB0-0668442FE2E8@microsoft.com...
> Hello. I know people have posted similar questions about using an existing
> relationship between X and Y to predict new Ys given new Xs but I think 
> mine
> has a slightly different twist. I would appreciate any help that could be
> offered.
>
> I have 20 data points for both X and Y. Each X point  represents the 
> number
> of workers on a farm for a particular year over a 20-year period. Each Y
> point represents the amount of crop harvest each year over that same 
> period.
> The data is from 1901 to 1920.  When I plot the points and fit an 
> exponential
> trend line I get a high R-square of .9 .  I would like to assume that the
> number of workers for that same 20-year period was actually 20% lower than
> the actual number of workers.  Using this new population of workers I 
> would
> like to predict the new amount of crop that could be harvested.  When I do
> this using the function: Growth(known_y's,known_x's,new_x's,const) I get
> results that do not seem to be intuitively correct. For example,  the new
> population in 1916 is approximately equal to the actual population in 1908
> but the amount of crop that is expected to be harvested differs widely.
>
> Thanks in advance for any help. I would be happy to send the actual 
> numbers
> I'm working with if that would be helpful in clarifying my question.


0
middleton1 (108)
1/12/2005 8:12:35 PM
Dear Mike,

Thanks for your quick response. Here is the data I'm analyzing:
Year      Actual pop.    Act. Produc.     New Pop.          New Production
1907	 58,073 	1,810,000	51,115	           2,038,541
1908	 57,070 	2,804,000	48,869	           1,922,837
1909	 77,524 	5,396,000	   65,866	    2,992,151
1910	 128,446       10,878,000       113,123	    10,231,406
1911	 166,015       17,584,000	146,535	     24,403,674
1912	 188,050       31,276,000	166,935	      41,490,691
1913	 188,250       47,440,000	165,613	      40,087,515
1914	 161,379 	62,024,000	136,951	      19,017,921
1915	 170,741 	113,564,000	144,563	     23,182,750
1916	 196,123 	125,626,000	168,172	      42,847,677
1917	 220,758 	160,044,000	191,015	      77,628,495
1918	 201,964 	156,778,000	170,719	      45,783,112
1919	 237,134 	212,906,000	205,197	     112,269,217
1920	 216,588 	202,660,000	184,062	        64,782,937

I'm trying to use the new population numbers in the 4th column to project 
the new 
production amount in the last column. I used the following excel command to 
generate the numbers in the last column:
=GROWTH(C44:C57,B44:B57,D44:D57,TRUE)

Based on juse eyeballing the new numbers they do not seem to be correct.  
The 'actual production' numbers increase every year excep for 2 years but the 
'new production' numbers seem to fluctuate widely.  The production numbers 
represent pounds of product.  If I compare the pound of product produced per 
person for the actual and new they are also widely divergent.  I'm not sure 
if it would be more correct to just take the pounds produced per person for 
the actual numbers and multiply it by the new population to get the new 
production.  There are other things happening over this 20-year period 
besides simply the labor force though that affect production and that is why 
I thought using the 'Growth' command made sense. Other factors affecting 
production would include price changes in the product and the lag between 
planting the crop and when it is ready to harvest. I thought using the 
'Growth' command would be a more objective way of generating a trend most 
similar to the actual one.

Thanks again.










"Michael R Middleton" wrote:

> malwatson  -
> 
> From your description I don't see anything obviously wrong.
> 
> Please post the X and Y historical data.
> 
> Also, post the values of the arguments of GROWTH you're using and the 
> results you're getting.
> 
> -  Mike
> 
> www.mikemiddleton.com
> 
> ++++++++++++++++++++++++++++
> 
> "malwatson" <malwatson@discussions.microsoft.com> wrote in message 
> news:BBE01427-8B59-4C27-9CB0-0668442FE2E8@microsoft.com...
> > Hello. I know people have posted similar questions about using an existing
> > relationship between X and Y to predict new Ys given new Xs but I think 
> > mine
> > has a slightly different twist. I would appreciate any help that could be
> > offered.
> >
> > I have 20 data points for both X and Y. Each X point  represents the 
> > number
> > of workers on a farm for a particular year over a 20-year period. Each Y
> > point represents the amount of crop harvest each year over that same 
> > period.
> > The data is from 1901 to 1920.  When I plot the points and fit an 
> > exponential
> > trend line I get a high R-square of .9 .  I would like to assume that the
> > number of workers for that same 20-year period was actually 20% lower than
> > the actual number of workers.  Using this new population of workers I 
> > would
> > like to predict the new amount of crop that could be harvested.  When I do
> > this using the function: Growth(known_y's,known_x's,new_x's,const) I get
> > results that do not seem to be intuitively correct. For example,  the new
> > population in 1916 is approximately equal to the actual population in 1908
> > but the amount of crop that is expected to be harvested differs widely.
> >
> > Thanks in advance for any help. I would be happy to send the actual 
> > numbers
> > I'm working with if that would be helpful in clarifying my question.
> 
> 
> 
0
malwatson (2)
1/12/2005 9:07:01 PM
Reply:

Similar Artilces: