Question

20. (**) (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 YEAR INCHES OF RAINFALL MARCH-June AVERAGE PEAK DAILY TEMPERATURE. JULY ACRE-FEET OF WATER USED, AUGUST 2005 12.5 78.4 39,800 2006 74.9 43,700 2007 841 45,100 2008 54,500 2009 32,900 2010 31,500 2011 35,500 2012 35,800 2013 89,700 88 2014 48,100 2015 53,700 2016 40,300 2017 81.5 32,600 2018 770 34,100 2019 36.800 Use Excel's regression function to develop a multiple regression forecast for Wayne. What is the R for the model? Use the model to forecast water usage for 2005-2019. Calculate the MFE and MAPE for the model. In your opinion, how good is the model?

Answer 1

We will solve using Excel regression function.

Go to File-> Options-> Excel Add ins -> Add "Data Analysis" add ins

Now, go to Data -> Data Analysis and select "Regression"

Solver Data Analysis Reapply Show Queries From Table Get External New Data Query - La Recent Sources Get & Transform A Connections E Properties Refresh All - Edit Links Connections Group Ungroup -- Subtotal Clear B Flash Fill - Consolidate 8-1 Remove Duplicates - Relationships Filter Text to y Advanced Columns Data Validation Sort & Filter Data Tools Il Sort What If Forecast Analysis Sheet Forecast Outline E Analyze C5 А в E F G H I J K L M N O P Q R Year Inch of Rainfall Data Analysis ? X | OK 6 7 8 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 Cancel 12.5 11.2 12.2 10.6 9.3 11.7 10 13.3 8.4 14.9 10 12.6 10.6 7.1 11.3 D Acre- feet of water used 39,800 43,700 45,100 54,500 32,900 31,500 35,500 35,800 69,700 48,100 53,700 40,300 32,600 34,100 36,800 Average Peak daily temp 78.4 74.9 84.1 85.1 70.6 71 87.4 91.4 98.3 99.6 91.7 91.6 81.5 77 83.9 Analysis Tools Covariance Descriptive Statistics Exponential Smoothing F-Test Two-Sample for Variances Fourier Analysis Histogram Moving Average Random Number Generation Rank and Percentile Regression Help 10 11 12 13 14 15 16 17 18 19

Setup Regression with dependent variable Y - amount of water needed (column D) and independent variables Xs - rainfall and daily temperature (column B, C).

Show Queries 21 ÁA Y Clear E Flash Fill - Remove Duplicates -- Consolidate Relationships ? Solver Data Analysis From Table Group Ungroup -- Subtotal Connections E Properties Refresh All - Edit Links Connections Žl Sort Filter Get External New Data Query - Lo Recent Sources Get & Transform IsReapply Text to y Advanced Columns Data Validation What-If Forecast Analysis Sheet Forecast Sort & Filter Data Tools Outline Analyze G1 - X V fx 85.1 в E F G H I J K L M N o P Q R L x Year Inch of Rainfall Regression ? Input Input Y Range: OK 3 $D$1:$D$16 Cancel 12.5 11.2 12.2 10.6 9.3 11.7 Input X Range: $B$1:$C$16 Help Labels 6 7 8 Constant is Zero 95 % D Acre- feet of water used 39,800 43,700 45,100 54,500 32,900 31,500 35,500 35,800 69,700 48,100 53,700 40,300 32,600 34,100 36,800 Confidence Level: 10 Average Peak daily temp 78.4 74.9 84.1 85.1 70.6 71 87.4 91.4 98.3 99.6 91.7 91.6 81.5 77 83.9 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 9 10 11 13.3 8.4 14.9 $G$1:$0$14 12 10 Output options O Output Range: New Worksheet Ply: O New Workbook Residuals ✓ Residuals Standardized Residuals 13 12.6 10.6 7.1 11.3 15 16 Residual Plots Line Fit Plots Normal Probability Normal Probability Plots

B H K L M N O P A Year Inch of Rainfall SUMMARY OUTPUT 1 m Average Peak daily temp 78.4 74.9 84.1 85.1 70.6 in Regression Statistics Multiple R 0.704824 R Square 0.496777 Adjusted R Square 0.412907 Standard Error 8109.712 Observations 7 71 8 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 15 Acre- feet of water used 39,800 43,700 45,100 54,500 32,900 31,500 35,500 35,800 69,700 48,100 53,700 40,300 32,600 34,100 36,800 12.5 11.2 12.2 10.6 9.3 11.7 10 13.3 8.4 14.9 10 12.6 10.6 7.1 11.3 ANOVA df 87.4 91.4 98.3 99.6 91.7 91.6 81.5 77 83.9 10 11 12 13 14 15 16 17 18 Regression Residual Total SS MS F gnificance F 2 7.79E+08 3.9E+08 5.923147 0.016239 12 7.89E+08 65767424 12 14 1.57E+09 Coefficientsandard Errit Stat P-value Lower 95%Upper 95%ower 95.0%pper 95.0% Intercept -10095.4 21344 -0.47299 0.644711 -56600 36409.18 -56600 36409.18 Inch of Rainfall - 1753.71 1148.112 -1.52748 0.152562 -4255.24 747.8088 -4255.24 747.8088 Average Peak daily temp 849.6812 250.2384 3.395487 0.005314 304.4586 1394.904 304.4586 1394.904

Hence, looking at coefficient values we can get the multiple regression equation as

Y = Intercept + Slope1*X1 + Slope2*X2

Acre- feet of water used = Intercept + Slope1*(Inch of Rainfall) + Slope 2 * (Average Peak daily temp)

Acre- feet of water used = -10095.40 + (-1753.71)*(Inch of Rainfall) + (849.68)* (Average Peak daily temp)

R-square for the model is 0.496777. This means only ~50% of variable in Y ( Acre- feet of water used) is explained by Xs (Inch of Rainfall, Average Peak daily temp)

Using multiple regression equation, we will estimate forecast for 2005-2019 and calculate MFE and MAPE

MFE = Summation (Errors)/ n

MAPE = (100%/n) * Summation (|Error|/ Actual )

A B C D E N O P Q R A F G H I J K M Acre-feet of water used = -10095.40 + (-1753.71)*(Inch of Rainfall) +(849.68)* (Average Peak daily temp) Intercept -10095.4 Slope - rainfall -1753.71 Slope-temp 849.68 Year Inch of Rainfall Average Peak daily temp Acre- feet of water used Error Error % Error 5 6 1 8 12.5 11.2 12.2 10.6 9.3 11.7 10 9 10 11 12 13 14 15 16 17 18 19 20 21 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 78.4 74.9 84.1 85.1 70.6 71 87.4 91.4 98.3 99.6 91.7 91.6 81.5 77 83.9 13.3 8.4 14.9 10 12.6 10.6 7.1 11.3 Forecast (Regression) 34,598 $G$2+B6*$G$3+C6*$G$4 33,904 $G$2+B7*$G$3+C7*$G$4 39,967 $G$2+B8*$G$3+C8*$G$4 43,623 same 33,583 same 29,713 same 46,630 same 44,241 same 58,697 same 48,402 same 50,283 same 45,639 same 40,564 same 42,879 same 41,376 same 39,800 43,700 45,100 54,500 32,900 31,500 35,500 35,800 69,700 48,100 53,700 40,300 32,600 34,100 36,800 5,202 D6-F6 9,796 D7-F7 5,133 D8-F8 10,877 same (683) same 1,787 same (11,130) same (8,441) same 11,003 same (302) same 3,417 same (5,339) same (7,964) same (8,779) same (4,576) same 0.068 AVERAGE(16:120) MFE 5,202 ABS(16) 9,796 ABS(17) 5,133 ABS(18) 10,877 same 683 same 1,787 same 11,130 same 8,441 same 11,003 same 302 same 3,417 same 5,339 same 7,964 same 8,779 same 4,576 same 13.1% K6/D6 22.4% K7/07 11.4% K8/D8 20.0% same 2.1% same 5.7% same 31.4% same 23.6% same 15.8% same 0.6% same 6.4% same 13.2% same 24.4% same 25.7% same 12.4% same 15.2% AVERAGE(M6:M20) MAPE 22 23

MFE is 0.068 and MAPE is 15.2%.

Since MFE is low and MAPE <20% hence we can say that forecastability of data is good with this regression model. However, r-square <0.5 which means effect of independent variable on dependent is not strong. Hence, the model performance is moderate with scope of further improvement and refinement with additional data and variables.

Please give thumbs up/ likes if you consider my answer useful. Thank you!