Introduction to Management Science 12th Edition Chapter 15, Problem 28P ***NEED

Question

Introduction to Management Science 12th Edition

Chapter 15, Problem 28P

***NEED HELP ON HOW TO SOLVE/ENTER INTO EXCELS SOLVER - PLEASE PROVIDE DETAILS OR SCREEN SHOTS***

Carpet City wants to develop a means to forecast its carpet sales. The store manager believes that the store's sales are directly related to the number of new housing starts in town. The manager has gathered data from county records on monthly house construction permits and from store records on monthly sales.

Questions:
a. Develop a linear regression model for these data and forecast carpet sales if 30 construction permits for new homes are filed.
b. Determine the strength of the casual relationship between monthly sales and new home construction by using correlation

These data are as follows:

Monthly Carpet                                           Monthly Construction

Sales (1,000 yd.)                                                    Permits

            5                                                                   21

            10                                                                    35

            4                                                                    10

            3                                                                    12

            8                                                                    16

            2                                                                    9

            12                                                                    41

            11                                                                    15

            9                                                                    18

            14                                                                    26   

Explanation / Answer

Linear relationship between two vaiables X & Y is given by: Y = a + bX where Y is dependent variables and takes the values based on the values of independent variable X. "a" and "b" are constants. Values of these constants can be obtained from the following two normal equations:

Sigma Y = n*a + b*Sigma X   and Sigma XY = a*Sigma X + b*Sigma X2

We are required to forecast carpet sales (Y) based on construction permits (X). Solution is as follows:

Normal equations are 78 = 10a + 203b and 1860 = 203a + 5153b Solving normal equations we get:

a = 2.359655              and b =   0.267997

Therefore Y carpet sales (1,000 yatds) = 2.359655 + 0.267997 * X

When X= 30, from above equation Y = 10.39957  

say 10,400 yards of carper sales corresponding to 30 construction permits.

b. Co-efficient of correlationship between X and Y is calculated as .699 or say .7 or in simple terms 70%

Formula is Sigma xy / SQRT{(sigma x2)*(sigma y2)} where x and y represents deviations between X and Y from their respective means.

Nor very strong correlation for regression but may be considered for forecasting.

Carpet sales Con.Permits Sr.No. (Y) (X) XY X*X 1 5 21 105 441 2 10 35 350 1225 3 4 10 40 100 4 3 12 36 144 5 8 16 128 256 6 2 9 18 81 7 12 41 492 1681 8 11 15 165 225 9 9 18 162 324 10 14 26 364 676 TOTAL 78 203 1860 5153

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.