BA 357 Assignment 1: Forecasting Annual Demand With A Monthly Breakdown Group AssignmentBA 357 Assignment 1: Forecasting Annual Demand With A Monthly Breakdown Group Assignment

BA 357 Assignment 1: Forecasting Annual Demand With A Monthly Breakdown Group AssignmentBA 357 Assignment 1: Forecasting Annual Demand With A Monthly Breakdown Group Assignment. General Instructions:

Assignment 1

 

You are to create a set of annual demand forecasts for one year, 2015, using five different forecasting methods as specified later.  After reviewing your annual forecast and error values, pick one annual forecast to recommend and distribute the annual forecast into monthly forecasts using the multiplicative seasonal forecasting method.  Then, prepare a brief executive summary indicating which forecast method you recommend the company to use for its aggregate planning and your reasons for choosing it. This summary page is to include your recommended forecast results listed by monthly demand and with the annual total demand.  Additional pages of your spread sheet file will include a page for each of your five forecasts, an Error summary page which combines error values for all five forecasts and one page for Seasonal Distribution of your demand for your chosen forecast method.  Error and other analysis should be included on the page for each individual forecast as well.

 

A summary Excel template for the executive summary cover page is provided for your use in the Work Assignment 1 folder in Week 5 on Blackboard.

 

Background:

Your company has just acquired a new subsidiary that produces sun hats and swimsuits.  Each product requires about the same amount of labor, time, and materials. All of the managers at the acquired company quit without notice the day your company took possession. You are the person who has been assigned to run this acquisition. Since all of the managers quit, the only planning information you have is historical data, including monthly demand and average annual days of sunshine for the targeted market.

 

Your task is to a forecast for the total demand for one year, 2015. Because the demand in the past has seasonally varied from month to month, the production organization needs for you to break the final forecasted annual demand down into monthly values so that they can begin scheduling resources and making appropriate purchases of materials from the company’s suppliers.

 

You have demand and weather data for the last 10 years. Weather forecasters are predicting 198 days of sunshine in the relevant region for 2015.  Their weather forecasts have been accurate to plus or minus 10 percent in years past.  According to industry experts there is about a 25% chance that the patterns of global consumer demand for 2014 will hold in the future. However there is a 10% chance that demand will be much less (at least 30%) than predicted from previous patterns because of continuing low levels of consumer confidence. And there is a 65% possibility that demand will recover to pre 2014 trends.  This information is provided to help you with your executive summary and analysis.

Aggregate Demand Data 2005 – 2014

 

	2005	2006	2007	2008	2009	2010	2011	2012	2013	2014
JAN	1393	1613	1615	1777	1920	2032	1912	2070	1901	1668
FEB	1551	1920	2107	1779	2220	2559	2627	2997	2529	1948
MAR	4572	5705	7204	7667	8159	8380	8742	11631	9671	8447
APR	9792	11301	12452	14938	13491	14064	16282	17966	18089	16799
MAY	9412	10000	9876	11023	13216	13500	15842	16587	16954	16500
JUN	2622	3243	3234	3513	3954	4479	4683	4641	4107	4745
JUL	1722	1910	2309	2400	2727	3021	2600	2730	2664	3224
AUG	1888	2272	2604	2736	3412	4101	4875	5182	4416	4905
SEP	3711	4464	4972	5633	6774	7191	9488	9795	8762	10602
OCT	1036	1016	1316	1485	1693	1943	2340	2487	2211	2589
NOV	2056	2600	2893	3297	3889	4014	4149	4364	4223	4671
DEC	1872	2123	2354	2076	2157	2592	3357	3938	3432	3920

Note that the copy of the tables are presented as text tables so you can copy and use them with Excel spreadsheet software without having to re-enter the data values.

 

	2005	2006	2007	2008	2009	2010	2011	2012	2013	2014
Annual Days of Sunshine	249	245	261	264	269	278	285	186	245	250

 

 

Requirements (60 points total):

§  An executive summary (5 points) that tells what forecast you are recommending, and why. Your justification should include qualitative and quantitative evidence to support your choice. The text content of this summary text is not to take more than a half sheet of paper.  The summary is to be accompanied below by a table containing the monthly forecast values and total annual demand forecasted by the method you are recommending.
Use the executive summary as your cover sheet – the first sheet of your assignment upload.

 

§  Five separate forecasts (8 points each) for 2015 using:

ü  A simple moving average. (You will choose the number of periods (years) to use.)

ü  A weighted moving average.   (Use the same number of periods as you SMA forecast and choose the weighting values for each period.)

ü  the linear regression analysis method using the causal factor (Days of Sunshine.)

ü  the exponential smoothing method (Choose a value for a)

ü  Trend Projection with Regression (Choose the best number of periods to use)

ü  Error Analysis should be included with each of your forecasts, at a minimum CFE, MAD and MAPE values.

§  For each method you should show:

ü  The method/formula you are using, including any constants/parameters used

ü  The spreadsheet used for the calculations.

 

§  Error Analysis (5 points)  One page in your spreadsheet should combine error values for all 5 forecast methods.

§  Seasonality (10 points) Choose one annual forecast to recommend and break the annual total into monthly forecast amounts using the multiplicative seasonality method.

 

 

 

Submission Requirements:

§  Your Excel submission file is to be in the following order:

o   Executive summary with the table of recommended forecast values on top. This summary is to include your name.

o   Spreadsheet for simple moving average

o   Spreadsheet for weighted moving average

o   Spreadsheet for the linear regression forecast of annual demand (Causal.)

o   Spreadsheet for exponential smoothing forecast of annual demand.

o   Spreadsheet for Trend Projection with Regression.

o   Spreadsheet for Error comparison

o   Spreadsheet showing results for seasonal distribution of your one, recommended forecast.

§  Each page must be clearly labeled as to its content (with the exception of the executive summary page whose template is provided.

§  Do not make the reader guess what you are doing – include appropriate explanations on each page (additional comment functions, titles, notes, graphs etc.).

 

Upload your completed Excel file in Blackboard by 11:59 pm PDT on Sunday, July 26th.  Assignments will be accepted up to one week late at a penalty of 25% (prior to grading.)

 

 

Suggestions and Tips:

 

 

1. Start by copying the table of demand data into one page in the template, than calculate yearly totals and monthly averages for each year.
2. Work in annual numbers where ever you are able.
3. Use Excel functions such as average, sum, slope and intercept, to help you do your calculations.
4. Build formulae so that you can easily change values to optimize your forecast.
5. Make sure to mention both quantitative and qualitative analysis in your executive summary.
6. Ask questions in the Assignment 1 discussion board.

BA 357 Assignment 1: Forecasting Annual Demand With A Monthly Breakdown Group AssignmentBA 357 Assignment 1: Forecasting Annual Demand With A Monthly Breakdown Group Assignment