21. (***) (Microsoft Excel problem). Wayne Banker is in charge of planning water usage for agriculturally intensive Burke County. August is the peak month of water usage for Burke County.Wayne has collected the following statistics from the past 15 years, showing the total March–June rainfall (in inches), average daily high temperature in July, and number of acre-feet of water used in August by farms in the area. Wayne wants to know if he can predict how much water will be needed in August, given the March–June rainfall and July temperature data.

Use Excel’s regression function to develop a multiple regression forecast for Wayne. What is the R2 for the model? Use the model to forecast water usage for 1990–2004. Calculate the MFE and MAD for the model.
In your opinion, how good is the model?

Transcribed Image Text:

AVERAGE PEAK INCHES OF DAILY ACRE-FEET OF RAINFALL, TEMPERATURE, WATER USED, YEAR MARCH-JUNE JULY AUGUST 1990 12.5 78.4 39,800 1991 11.2 74.9 43,700 1992 12.2 84.1 45,100 1993 10.6 85.1 54,500 1994 9.3 70.6 32,900 AVERAGE PEAK INCHES OF DAILY RAINFALL, TEMPERATURE, ACRE-FEET OF WATER YEAR MARCH-JUNE JULY USED, AUGUST 1995 11.7 71.0 31,500 1996 10.0 87.4 35,500 1997 13.3 91.4 35,800 1998 8.4 98.3 69,700 1999 14.9 99.6 48,100 2000 10.0 91.7 53,700 2001 12.6 91.6 40,300 2002 10.6 81.5 32,600 2003 7.1 77.0 34,100 2004 11.3 83.9 36,800