Question

Month	time	Sales
Jan	1	200
Feb	2	203
March	3	210
Mar	4	218
April	5	230
May	6	245
Jun	7	346
Jul	8	376
Aug	9	389
Sep	10	231
Oct	11	200
Nov	12	189
Dec	13	155
Jan	14	178
Feb	15	193
Mar	16	192
Apr	17	201
May	18	212
Jun	19	367
Jul	20	391
Aug	21	401
Sep	22	204
Oct	23	201
Nov	24	183
Dec	25	145
Jan	26	196
Feb	27	199
Mar	28	214
Apr	29	228
May	30	231
Jun	31	376
Jul	32	402
Aug	33	426
Sep	34	205
Oct	35	206
Nov	36	178
Dec	37	142
Jan	38
Feb	39
Mar	40
Apr	41
May	42
Jun	43
Jul	44
Aug	45
Sep	46
Oct	47
Nov	48

The Swim Wear Store sells bathing caps. As the store does not sell a lot of them, such that ordering them periodically during the year when they run out has been the method of restocking. The store has the opportunity to purchase them in bulk at a much cheaper price from a supplier, but the supplier is only willing to ship these once a year. Thus, the Swim Wear Store needs to predict how many they will need next year. They have many loyal customers that will be very upset if they are not able to purchase a swim cap. Swim Wear Store has determined that customers will not be upset if they cannot purchase swim caps in December, just in case they run out. Swim Wear Store has kept the monthly sales records of swim caps for the years, 2015, 2016 and 2017. The store would also like to know the monthly sales forecast for swim caps in 2018, as it will help them to determine sales of other products, along with the total sales of swim caps for 2018. Follow the steps below. 1. Create a numerical reference for each month in the Time column. 2. In the column CMA, use the centered moving average for the month. 3. In the column seasonal index, (Sales/CMA) for each month. 4. Find the seasonal ratio, using the table labeled Seasonal Ratio 5. Calculate the deseasonalized demand. (Sales/Seasonal Ratio) 6. Use Trend to forecast demand. 7. Seasonalize the forecast (Trend X Seasonal Ratio) How much is the overall demand for bathing caps for 2018? What is the monthly demand?

please give exact excel formulas

Answer 1

FORMULAS:

Cell	Formula	Copy to
D8	=AVERAGE(AVERAGE(C2:C12),AVERAGE(C3:C13))	D8:D32
E8	=C8/D8	E8:E32
F8	=AVERAGEIF($A$8:$A$32,A8,$E$8:$E$32)	F8:F32
G2	=C2/VLOOKUP(A2,$A$8:$F$32,6,0)	G2:G37
H2	=FORECAST(B2,$G$2:$G$37,$B$2:$B$37)	H2:H49
I2	=H2*VLOOKUP(A2,$A$8:$F$32,6,0)	I2:I49

Total yealy demand for 2018 = 2907

Monthly demand is shownin the table

Jan	197
Feb	207
Mar	206
Apr	210
May	216
Jun	359
Jul	376
Aug	388
Sep	214
Oct	198
Nov	185
Dec	153