Explore BrainMass

Explore BrainMass

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

    This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

    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

    © BrainMass Inc. brainmass.com June 3, 2020, 6:41 pm ad1c9bdddf
    https://brainmass.com/statistics/regression-analysis/plantworld-develop-a-regression-model-to-explain-and-predict-supplies-expense-70772

    Attachments

    Solution Summary

    The solution shows all the calculations, plots and graphs needed to respond to the questions for Plantworld's monthly budget for supplies expense.

    $2.19

    ADVERTISEMENT