QRB501(ppt+Excel)

profilexoon
winter_indices_with_clarification_of_data.xls
Home>Business & Finance homework help>Accounting homework help>QRB501(ppt+Excel)

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
Year 1
Year 2
Year 3
Year 4
Forecast for Year 5
Winter Historical Inventory Data Typical Seasonal Demands for Winter Highs Actual Demands (in units)

Sheet2
histogram of inventory data

Sheet3