Question

please answer with clear work using decomposition including season factor

The next page provides the monthly sales data for Joe's Stone Crab. We have been hired to develop a forecasting model that will predict future sales for 2018 using the time-series seasonal adjustment method. Assume that data is only available through the end of 2017 You are also to prepare a 1-2 page report within a Word file that will explain to the owner (Mr. Joe) what is happening with the sales and what he should expect in the future. Remember, you are explaining your model and analysis to somcone who knows nothing about statistics or forecasting Make sure to include the 2018 forecast by month in your report, as well as the trend, the seasonal factors, and the error measurements! Make use of graphs and tables to present key data (seasonality, trend, etc.). Explain the methodology used to create the forecast (in a way that Mr. Joe will understand), and be sure to explain the meaning and implications of all of the data in the report. The report must be typed. Presentation quality will be counted. Two spreadsheets should be submitted-one with the data (with two decimal places), and the other with the formulas (use Tools, Options, Formulas to convert the spreadsheet to formulas, or using the short-cut keys "Ctri+) The report must include all of the relevant data. (Mr. Joe does not read spreadsheets!) Also, do not refer to the spreadsheets in the report Print the spreadshects on one page each (use"Fit to page" and "Landscape" options in "Page Setup" function). Sales data are in thousands of dollars per month and the forecast should be in the same units. Do not add zeros to the data. 2014 2015 2016 2017 in in in thousands thousands of dollars of dollars thousands thousands of Sales of dollars January 242 263 282 306 February 235 238 255280 March 232 247 245 270 April 178 20210 May 184 13 210214 June 140 149 160 175 July 145 157 165 182 August i52 160 175 193 September 110 2 126130 October 130 130 148 155 November 152 167 173 182 December 206 230 235 255

Answer 1

Joe Stone Carb
Annual Business Plan FY 2016							Month-wise seasonality FY2013-14			11.49%	11.16%	11.02%	8.45%	8.74%	6.65%	6.89%	7.22%	5.22%	6.17%	7.22%	9.78%	100.00%
Historcal Data - FY 2016							Month-wise seasonality FY2014-15			11.70%	10.59%	10.99%	8.50%	8.59%	6.63%	6.99%	7.12%	5.43%	5.79%	7.43%	10.24%	100.00%
Revenues - Retail ( INR, Gross inclusive of VAT, Net of Discounts)							Month-wise seasonality FY2015-16			11.85%	10.72%	10.30%	8.62%	8.83%	6.73%	6.94%	7.36%	5.30%	6.22%	7.27%	9.88%	100.00%
							Month-wise seasonality FY2016-17			11.99%	10.97%	10.58%	8.23%	8.39%	6.86%	7.13%	7.56%	5.09%	6.07%	7.13%	9.99%	100.00%
							Month-wise seasonality FY2017-18			11.30%	10.67%	10.49%	8.20%	8.02%	6.88%	7.17%	8.17%	5.22%	6.62%	7.14%	10.12%	100.00%
Sl.	Store Code	Brand	Mall	City	Carpet Area	Date of Opening	Avg Comp FY2014-15	Avg Comp FY2015-16	Avg Comp FY2016-17	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18	Full Year Total
1	001	Joe's Stone Carb	High Street	New York		1-Apr-11	-100.00%	0.00%	0.00%	307	290	285	223	218	187	195	222	142	180	194	275	2,718
	GRAND TOTAL				-					307	290	285	223	218	187	195	222	142	180	194	275	2,718

Joe Stone Carb
Annual Business Plan FY 2016					Month-wise seasonality FY2013-14	11.49%	11.16%	11.02%	8.45%	8.74%	6.65%	6.89%	7.22%	5.22%	6.17%	7.22%	9.78%	100.00%
Historcal Data - FY 2016					Month-wise seasonality FY2014-15	11.70%	10.59%	10.99%	8.50%	8.59%	6.63%	6.99%	7.12%	5.43%	5.79%	7.43%	10.24%	100.00%
Revenues - Retail ( INR, Gross inclusive of VAT, Net of Discounts)											Month-wise seasonality FY2015-16	11.85%	10.72%	10.30%	8.62%	8.83%	6.73%	6.94%	7.36%	5.30%	6.22%	7.27%	9.88%	100.00%
					Month-wise seasonality FY2016-17	11.99%	10.97%	10.58%	8.23%	8.39%	6.86%	7.13%	7.56%	5.09%	6.07%	7.13%	9.99%	100.00%
Sl.	Store Code	Brand	Mall	City	Avg Comp FY2016-17	Jan-17	Feb-17	Mar-17	Apr-17	May-17	Jun-17	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17	Full Year Total
1	001	Joe's Stone Carb	High Street	New York	0.00%	306	280	270	210	214	175	182	193	130	155	182	255	2,552
	GRAND TOTAL					306	280	270	210	214	175	182	193	130	155	182	255	2,552

Joe Stone Carb
Annual Business Plan FY 2016
Historcal Data - FY 2016									Month-wise seasonality FY2013-14			11.49%	11.16%	11.02%	8.45%	8.74%	6.65%	6.89%	7.22%	5.22%	6.17%	7.22%	9.78%	100.00%
Revenues - Retail ( INR, Gross inclusive of VAT, Net of Discounts)									Month-wise seasonality FY2014-15			11.70%	10.59%	10.99%	8.50%	8.59%	6.63%	6.99%	7.12%	5.43%	5.79%	7.43%	10.24%	100.00%
									Month-wise seasonality FY2015-16			11.85%	10.72%	10.30%	8.62%	8.83%	6.73%	6.94%	7.36%	5.30%	6.22%	7.27%	9.88%	100.00%
Sl.	Store Code	Brand	Mall	City	Carpet Area	Date of Opening	Avg SPSF FY2013-14	Avg SPSF FY2014-15	Avg SPSF FY2015-16	Avg Comp FY2014-15	Avg Comp FY2015-16	Jan-16	Feb-16	Mar-16	Apr-16	May-16	Jun-16	Jul-16	Aug-16	Sep-16	Oct-16	Nov-16	Dec-16	Full Year Total
1	001	Joe's Stone Carb	High Street	New York	-	1-Apr-11	0	-	-	-100.00%	0.00%	282	255	245	205	210	160	165	175	126	148	173	235	2,379
	GRAND TOTAL				-							282	255	245	205	210	160	165	175	126	148	173	235	2,379