Sheet1
Winter Historical Inventory Data | |||||||||||||||||||||||
Typical Seasonal Demand for Winter Highs | Use seasonal indices to analyze the inventory data. | ||||||||||||||||||||||
Actual Demands (in units) | |||||||||||||||||||||||
Month | Year 1 | Year 2 | Year 3 | Year 4 | Forecast | Use the slope-intercept formula to determine the annual increase in inventory. | |||||||||||||||||
1 | 55,200 | 39,800 | 32,180 | 62,300 | 45318 | Annual Inventory = 2866 *X + 35457.5 Where X is the year value 1,2,3,4 ….. | |||||||||||||||||
2 | 57,350 | 64,100 | 38,600 | 66,500 | 56345 | ||||||||||||||||||
3 | 15,400 | 47,600 | 25,020 | 31,400 | 26042 | ||||||||||||||||||
4 | 27,700 | 43,050 | 51,300 | 36,500 | 34440 | Identify the busy months of year. | |||||||||||||||||
5 | 21,400 | 39,300 | 31,790 | 16,800 | 30519 | The busy months seems to be month 1,2,10,11,12 | |||||||||||||||||
6 | 17,100 | 10,300 | 31,100 | 18,900 | 15420 | Identify the slow months of year. | |||||||||||||||||
7 | 18,000 | 45,100 | 59,800 | 35,500 | 29520 | ||||||||||||||||||
8 | 19,800 | 46,530 | 30,740 | 51,250 | 25296 | The slow months are 5,6,7 | |||||||||||||||||
9 | 15,700 | 22,100 | 47,800 | 34,400 | 17730 | ||||||||||||||||||
10 | 53,600 | 41,350 | 73,890 | 68,000 | 47849 | Construct a histogram of the inventory data using Microsoft® Excel®. | |||||||||||||||||
11 | 83,200 | 46,000 | 60,200 | 68,100 | 69040 | ||||||||||||||||||
12 | 72,900 | 41,800 | 55,200 | 61,100 | 61050 | ||||||||||||||||||
Avg. | 38,113 | 40,586 | 44,802 | 45,896 | 38,214 | SUMMARY OUTPUT | |||||||||||||||||
Provide monthly seasonal indices for the given data. | |||||||||||||||||||||||
SEASONAL INDEX | Regression Statistics | ||||||||||||||||||||||
Month | Year 1 | Year 2 | Year 3 | Year 4 | Multiple R | 0.9457403384 | |||||||||||||||||
1 | 144.83247186 | 98.063371606 | 71.8271505736 | 135.7416768346 | R Square | 0.8944247876 | |||||||||||||||||
2 | 150.4735916879 | 157.9362341694 | 86.156867997 | 144.8928011156 | Adjusted R Square | 0.8416371814 | |||||||||||||||||
3 | 40.4061606276 | 117.2818213177 | 55.8457211732 | 68.4155481959 | Standard Error | 1556.8804278642 | |||||||||||||||||
4 | 72.6786135964 | 106.0710589859 | 114.5038167939 | 79.52762768 | Observations | 4 | |||||||||||||||||
5 | 56.1488206124 | 96.8314197014 | 70.9566537208 | 36.6044971239 | |||||||||||||||||||
6 | 44.8665809566 | 25.3782092347 | 69.4165439043 | 41.1800592644 | ANOVA | ||||||||||||||||||
7 | 47.2279799543 | 111.1220617947 | 133.4761840989 | 77.3487885655 | df | SS | MS | F | Significance F | ||||||||||||||
8 | 51.9507779498 | 114.6454442419 | 68.6130083478 | 111.6655046191 | Regression | 1 | 41069780 | 41069780 | 16.943840652 | 0.0542596616 | |||||||||||||
9 | 41.1932936268 | 54.4522741832 | 106.6916655506 | 74.9520655395 | Residual | 2 | 4847753.33333333 | 2423876.66666667 | |||||||||||||||
10 | 140.6344291974 | 101.8824225102 | 164.925672961 | 148.1610597873 | Total | 3 | 45917533.3333334 | ||||||||||||||||
11 | 218.2982184556 | 113.339575223 | 134.3690013839 | 148.3789436988 | |||||||||||||||||||
12 | 191.2733188151 | 102.9911792244 | 123.2087853221 | 133.1270698972 | Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | |||||||||||
Intercept | 35457.5 | 1906.7813193966 | 18.5954727159 | 0.0028794306 | 27253.2821514535 | 43661.7178485465 | 27253.2821514535 | 43661.7178485465 | |||||||||||||||
X Variable 1 | 2866 | 696.2580939087 | 4.1162896706 | 0.0542596616 | -129.7567882236 | 5861.7567882236 | -129.7567882236 | 5861.7567882236 | |||||||||||||||
CLARIFICATION | |||||||||||||||||||||||
USING THIS DATA RUN REGRESSION IN DATA -----DATA ANALYSIS HEAD | |||||||||||||||||||||||
1 | 38,113 | ||||||||||||||||||||||
2 | 40,586 | ||||||||||||||||||||||
3 | 45,896 | ||||||||||||||||||||||
4 | 45,896 | ||||||||||||||||||||||
X RANGE | Y RANGE |
Sheet1
Sheet2
Sheet3
Z-Table QRB/501 Version 3 |
1 |
University of Phoenix Material
Z-Table
z |
.00 |
.01 |
.02 |
.03 |
.04 |
.05 |
.06 |
.07 |
.08 |
.09 |
-3.4 |
.0003 |
.0003 |
.0003 |
.0003 |
.0003 |
.0003 |
.0003 |
.0003 |
.0003 |
.0002 |
-3.3 |
.0005 |
.0005 |
.0005 |
.0004 |
.0004 |
.0004 |
.0004 |
.0004 |
.0004 |
.0003 |
-3.2 |
.0007 |
.0007 |
.0006 |
.0006 |
.0006 |
.0006 |
.0006 |
.0005 |
.0005 |
.0005 |
-3.1 |
.0010 |
.0009 |
.0009 |
.0009 |
.0008 |
.0008 |
.0008 |
.0008 |
.0007 |
.0007 |
-3.0 |
.0013 |
.0013 |
.0013 |
.0012 |
.0012 |
.0011 |
.0011 |
.0011 |
.0010 |
.0010 |
-2.9 |
.0019 |
.0018 |
.0018 |
.0017 |
.0016 |
.0016 |
.0015 |
.0015 |
.0014 |
.0014 |
-2.8 |
.0026 |
.0025 |
.0024 |
.0023 |
.0023 |
.0022 |
.0021 |
.0021 |
.0020 |
.0019 |
-2.7 |
.0035 |
.0034 |
.0033 |
.0032 |
.0031 |
.0030 |
.0029 |
.0028 |
.0027 |
.0026 |
-2.6 |
.0047 |
.0045 |
.0044 |
.0043 |
.0041 |
.0040 |
.0039 |
.0038 |
.0037 |
.0036 |
-2.5 |
.0062 |
.0060 |
.0059 |
.0057 |
.0055 |
.0054 |
.0052 |
.0051 |
.0049 |
.0048 |
-2.4 |
.0082 |
.0080 |
.0078 |
.0075 |
.0073 |
.0071 |
.0069 |
.0068 |
.0066 |
.0064 |
-2.3 |
.0107 |
.0104 |
.0102 |
.0099 |
.0096 |
.0094 |
.0091 |
.0089 |
.0087 |
.0084 |
-2.2 |
.0139 |
.0136 |
.0132 |
.0129 |
.0125 |
.0122 |
.0119 |
.0116 |
.0113 |
.0110 |
-2.1 |
.0179 |
.0174 |
.0170 |
.0166 |
.0162 |
.0158 |
.0154 |
.0150 |
.0146 |
.0143 |
-2.0 |
.0228 |
.0222 |
.0217 |
.0212 |
.0207 |
.0202 |
.0197 |
.0192 |
.0188 |
.0183 |
-1.9 |
.0287 |
.0281 |
.0274 |
.0268 |
.0262 |
.0256 |
.0250 |
.0244 |
.0239 |
.0233 |
-1.8 |
.0359 |
.0351 |
.0344 |
.0336 |
.0329 |
.0322 |
.0314 |
.0307 |
.0301 |
.0294 |
-1.7 |
.0446 |
.0436 |
.0427 |
.0418 |
.0409 |
.0401 |
.0392 |
.0384 |
.0375 |
.0367 |
-1.6 |
.0548 |
.0537 |
.0526 |
.0516 |
.0505 |
.0495 |
.0485 |
.0475 |
.0465 |
.0455 |
-1.5 |
.0668 |
.0655 |
.0643 |
.0630 |
.0618 |
.0606 |
.0594 |
.0582 |
.0571 |
.0559 |
-1.4 |
.0808 |
.0793 |
.0778 |
.0764 |
.0749 |
.0735 |
.0721 |
.0708 |
.0694 |
.0681 |
-1.3 |
.0968 |
.0951 |
.0934 |
.0918 |
.0901 |
.0885 |
.0869 |
.0853 |
.0838 |
.0823 |
-1.2 |
.1151 |
.1131 |
.1112 |
.1093 |
.1075 |
.1056 |
.1038 |
.1020 |
.1003 |
.0985 |
-1.1 |
.1357 |
.1335 |
.1314 |
.1292 |
.1271 |
.1251 |
.1230 |
.1210 |
.1190 |
.1170 |
-1.0 |
.1587 |
.1562 |
.1539 |
.1515 |
.1492 |
.1469 |
.1446 |
.1423 |
.1401 |
.1379 |
-0.9 |
.1841 |
.1814 |
.1788 |
.1762 |
.1736 |
.1711 |
.1685 |
.1660 |
.1635 |
.1611 |
-0.8 |
.2119 |
.2090 |
.2061 |
.2033 |
.2005 |
.1977 |
.1949 |
.1922 |
.1894 |
.1867 |
-0.7 |
.2420 |
.2389 |
.2358 |
.2327 |
.2296 |
.2266 |
.2236 |
.2206 |
.2177 |
.2148 |
-0.6 |
.2743 |
.2709 |
.2676 |
.2643 |
.2611 |
.2578 |
.2546 |
.2514 |
.2483 |
.2451 |
-0.5 |
.3085 |
.3050 |
.3015 |
.2s981 |
.2946 |
.2912 |
.2877 |
.2843 |
.2810 |
.2776 |
-0.4 |
.3446 |
.3409 |
.3372 |
.3336 |
.3300 |
.3264 |
.3228 |
.3192 |
.3156 |
.3121 |
-0.3 |
.3821 |
.3783 |
.3745 |
.3707 |
.3669 |
.3632 |
.3594 |
.3557 |
.3520 |
.3483 |
-0.2 |
.4207 |
.4168 |
.4129 |
.4090 |
.4052 |
.4013 |
.3974 |
.3936 |
.3897 |
.3859 |
-0.1 |
.4602 |
.4562 |
.4522 |
.4483 |
.4443 |
.4404 |
.4364 |
.4325 |
.4286 |
.4247 |
0.0 |
.5000 |
.4960 |
.4920 |
.4880 |
.4840 |
.4801 |
.4761 |
.4721 |
.4681 |
.4641 |
z |
.00 |
.01 |
.02 |
.03 |
.04 |
.05 |
.06 |
.07 |
.08 |
.09 |
0.0 |
.5000 |
.5040 |
.5080 |
.5120 |
.5160 |
.5199 |
.5239 |
.5279 |
.5319 |
.5359 |
0.1 |
.5398 |
.5438 |
.5478 |
.5517 |
.5557 |
.5596 |
.5636 |
.5675 |
.5714 |
.5753 |
0.2 |
.5793 |
.5832 |
.5871 |
.5910 |
.5948 |
.5987 |
.6026 |
.6064 |
.6103 |
.6141 |
0.3 |
.6179 |
.6217 |
.6255 |
.6293 |
.6331 |
.6368 |
.6406 |
.6443 |
.6480 |
.6517 |
0.4 |
.6554 |
.6591 |
.6628 |
.6664 |
.6700 |
.6736 |
.6772 |
.6808 |
.6844 |
.6879 |
0.5 |
.6915 |
.6950 |
.6985 |
.7019 |
.7054 |
.7088 |
.7123 |
.7157 |
.7190 |
.7224 |
0.6 |
.7257 |
.7291 |
.7324 |
.7357 |
.7389 |
.7422 |
.7454 |
.7486 |
.7517 |
.7549 |
0.7 |
.7580 |
.7611 |
.7642 |
.7673 |
.7704 |
.7734 |
.7764 |
.7794 |
.7823 |
.7852 |
0.8 |
.7881 |
.7910 |
.7939 |
.7967 |
.7995 |
.8023 |
.8051 |
.8078 |
.8106 |
.8133 |
0.9 |
.8159 |
.8186 |
.8212 |
.8238 |
.8264 |
.8289 |
.8315 |
.8340 |
.8365 |
.8389 |
1.0 |
.8413 |
.8438 |
.8461 |
.8485 |
.8508 |
.8531 |
.8554 |
.8577 |
.8599 |
.8621 |
1.1 |
.8643 |
.8665 |
.8686 |
.8708 |
.8729 |
.8749 |
.8770 |
.8790 |
.8810 |
.8830 |
1.2 |
.8849 |
.8869 |
.8888 |
.8907 |
.8925 |
.8944 |
.8962 |
.8980 |
.8997 |
.9015 |
1.3 |
.9032 |
.9049 |
.9066 |
.9082 |
.9099 |
.9115 |
.9131 |
.9147 |
.9162 |
.9177 |
1.4 |
.9192 |
.9207 |
.9222 |
.9236 |
.9251 |
.9265 |
.9279 |
.9292 |
.9306 |
.9319 |
1.5 |
.9332 |
.9345 |
.9357 |
.9370 |
.9382 |
.9394 |
.9406 |
.9418 |
.9429 |
.9441 |
1.6 |
.9452 |
.9463 |
.9474 |
.9484 |
.9495 |
.9505 |
.9515 |
.9525 |
.9535 |
.9545 |
1.7 |
.9554 |
.9564 |
.9573 |
.9582 |
.9591 |
.9599 |
.9608 |
.9616 |
.9625 |
.9633 |
1.8 |
.9641 |
.9649 |
.9656 |
.9664 |
.9671 |
.9678 |
.9686 |
.9693 |
.9699 |
.9706 |
1.9 |
.9713 |
.9719 |
.9726 |
.9732 |
.9738 |
.9744 |
.9750 |
.9756 |
.9761 |
.9767 |
2.0 |
.9772 |
.9778 |
.9783 |
.9788 |
.9793 |
.9798 |
.9803 |
.9808 |
.9812 |
.9817 |
2.1 |
.9821 |
.9826 |
.9830 |
.9834 |
.9838 |
.9842 |
.9846 |
.9850 |
.9854 |
.9857 |
2.2 |
.9861 |
.9864 |
.9868 |
.9871 |
.9875 |
.9878 |
.9881 |
.9884 |
.9887 |
.9890 |
2.3 |
.9893 |
.9896 |
.9898 |
.9901 |
.9904 |
.9906 |
.9909 |
.9911 |
.9913 |
.9916 |
2.4 |
.9918 |
.9920 |
.9922 |
.9925 |
.9927 |
.9929 |
.9931 |
.9932 |
.9934 |
.9936 |
2.5 |
.9938 |
.9940 |
.9941 |
.9943 |
.9945 |
.9946 |
.9948 |
.9949 |
.9951 |
.9952 |
2.6 |
.9953 |
.9955 |
.9956 |
.9957 |
.9959 |
.9960 |
.9961 |
.9962 |
.9963 |
.9964 |
2.7 |
.9965 |
.9966 |
.9967 |
.9968 |
.9969 |
.9970 |
.9971 |
.9972 |
.9973 |
.9974 |
2.8 |
.9974 |
.9975 |
.9976 |
.9977 |
.9977 |
.9978 |
.9979 |
.9979 |
.9980 |
.9981 |
2.9 |
.9981 |
.9982 |
.9982 |
.9983 |
.9984 |
.9984 |
.9985 |
.9985 |
.9986 |
.9986 |
3.0 |
.9987 |
.9987 |
.9987 |
.9988 |
.9988 |
.9989 |
.9989 |
.9989 |
.9990 |
.9990 |
3.1 |
.9990 |
.9991 |
.9991 |
.9991 |
.9992 |
.9992 |
.9992 |
.9992 |
.9993 |
.9993 |
3.2 |
.9993 |
.9993 |
.9994 |
.9994 |
.9994 |
.9994 |
.9994 |
.9995 |
.9995 |
.9995 |
3.3 |
.9995 |
.9995 |
.9995 |
.9996 |
.9996 |
.9996 |
.9996 |
.9996 |
.9996 |
.9997 |
3.4 |
.9997 |
.9997 |
.9997 |
.9997 |
.9997 |
.9997 |
.9997 |
.9997 |
.9997 |
.9998 |

Get help from top-rated tutors in any subject.
Efficiently complete your homework and academic assignments by getting help from the experts at homeworkarchive.com