Plantworld: develop a regression model to explain and predict supplies expense

Plantworld is a large nursery and retail store specializing in house and garden plants and supplies. Tito Nieves, the assistant manager, is in the process of budgeting monthly supplies expense for the next 12 months. He assumes that in some way supplies expense is related to store hours, sales dollars or employee hours worked or some combination of these. He has collected these data for store hours, sales dollars, sales employee hours worked and supplies expenses for Jul 2003 through Jan 2006, and has estimated employee hours, sales dollars, store hours for the next 12 months.

Date Supplies
Expense Employee
Hours Sales Dollars
(000's Omitted) Store
Hours
Jul-03 $2,828 249 $924 136
Aug-03 $3,327 574 $1,745 162
Sep-03 $2,836 1,485 $932 140
Oct-03 $2,681 757 $1,283 131
Nov-03 $3,202 566 $1,970 153
Dec-03 $3,157 1,603 $375 151
Jan-04 $3,127 244 $633 149
Feb-04 $3,392 1,171 $4,042 162
Mar-04 $2,971 784 $5,050 142
Apr-04 $3,099 161 $6,147 149
May-04 $2,394 628 $3,088 116
Jun-04 $2,834 87 $1,372 138
Jul-04 $2,541 119 $1,175 128
Aug-04 $2,650 738 $1,664 127
Sep-04 $2,948 135 $1,414 143
Oct-04 $3,054 164 $2,857 147
Nov-04 $3,159 1,038 $4,248 156
Dec-04 $2,935 1,082 $2,491 143
Jan-05 $2,964 1,533 $3,383 148
Feb-05 $2,629 834 $2,359 126
Mar-05 $3,074 531 $1,867 151
Apr-05 $2,404 284 $1,301 112
May-05 $2,659 679 $4,525 130
Jun-05 $3,555 714 $5,323 174
Jul-05 $2,589 373 $2,261 121
Aug-05 $3,227 248 $1,075 157
Sep-05 $2,828 739 $1,865 133
Oct-05 $2,655 842 $1,426 126
Nov-05 $2,693 1,404 $2,475 131
Dec-05 $3,172 804 $3,623 152
Jan-06 $3,012 379 $684 145
Feb-06 161 $4,485 126
Mar-06 909 $651 122
Apr-06 836 $1,311 162
May-06 119 $3,450 142
Jun-06 1,581 $3,217 158
Jul-06 379 $4,227 163
Aug-06 636 $1,504 172
Sep-06 867 $786 124
Oct-06 794 $1,680 157
Nov-06 1,157 $1,194 129
Dec-06 497 $2,448 153
Jan-07 127 $1,676 151

Required:

1. Using the high-low method, determine the fixed and variable cost. Use Store Hours as the cost driver.

2. Develop the regression model that Tito should use based on these data and using the regression procedures in Excel (adopt 2 as the threshold for the t value).
a. Include in your answer the regression model (both LINEST and Regression) with all independent variables included.
b. And present the LINEST, TRENDLINE (if appropriate) & Regression for the model you have chosen to explain the Supplies Expense.

3. What are the predicted monthly figures for supplies expense for the twelve months ended January 2007?

Hints:
Using regression analysis, the Y intercept i.e. the fixed cost is: $132.67