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

[malwatson]

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.

 

[Michael R Middleton]

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]

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,D4457,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.