This is an individual assignment – Do not work with others.

Use only the data set you are assigned to – you will not receive any credit for other data sets.  Here’s how to determine which data set to use:

If your last name begins with the letter…

 Data Set: A B C Wednesday A – C D – Q R – Z Thursday A – C D – N P – Z

All three data sets are available on eCollege

Deliverables

1. A hardcopy of your write-up (Word file) is due by Thursday, April 27.  The hardcopy is to be brought to class or dropped off at my office, (271 Dan Rogers Hall).
2. Email me an electronic copies of both your write-up AND Excel file prior to class on 4/27.

Your assignment will not be considered turned in until BOTH sets of material submitted.

In your data file, you will find 3 worksheets.  Develop the models shown on each worksheet, complete the metrics of error, and then answer the questions below.

Simple Average Forecasts

In this worksheet, you are to develop three models of daily forecast, (rows 14 – 344), then evaluate the performance of each model over a holdout period, (rows 349 – 377).  Space is provided for model building, error calculation, and performance metrics.

For the simple exponential smoothing model, use Excel’s solver tool to determine the optimal value of alpha, subject to the constraints outlined in your notes.  Program solver to minimize MAPEduring the model building period.

Once you have developed the models and calculated the metrics, state which model you would use and explain your decision.  Use tables and graphs as appropriate.

Trend Forecasts

In this worksheet, you are to develop two models of weekly demand.  Assess model fit by calculating error for each period and then metrics of performance.  (Note: there is no holdout period in this problem).

For Holt’s exponential smoothing model, use Excel’s solver tool to determine the optimal value of alpha and beta, subject to the constraints outlined in your notes for the Holt-Winter model.  (The difference being that you don’t have gamma in this model).  Program solver to minimize MAPE.

Once you have developed the models and calculated the metrics, state which model you would use and explain your decision.  Use tables and graphs as appropriate.

Seasonality and Trend Forecasts

In this worksheet, you are to develop two models of truckload shipments each quarter.  Assess model fit by calculating error for each period and then metrics of performance.  (Note: there is no holdout period in this problem).

For Holt-Winter’s exponential smoothing model, use Excel’s solver tool to determine the optimal value of alpha, beta and gamma, subject to the constraints outlined in your notes.  Program solver to minimize MAPE.

Once you have developed the models and calculated the metrics, state which model you would use and explain your decision.  Use tables and graphs as appropriate

Write-up

Begin your write-up with anintroduction section that introduces the project, but also:

• Explains time series models, (e.g. how they work, pros and cons, how one identifies potential models).
• Explains how one evaluates a forecast model and the various metrics that are used.

Next, create a section for each worksheet and address the questions listed in the instructions above. In each section, be sure to support your recommendations with tables and graphs in addition to discussion.  There is no set requirement for the inclusion of tables or graphs, but use them as appropriate.

Overall, this write-up will be much less extensive than Assignment 1. However, take time to make sure your coverage of the material is thorough and that the appearance is professional.

Guidelines for thewrite-up

The report is to look professional – that is, it should be of the quality you would turn in to an upper level manager.  In fact, imagine that I intend to show this to every company you interview with.  What kind of work would you want them to see?

• Coversheet:  Include only the following:
• The data set used for your analysis
• Use proper grammar and sentence structure.  Clarity of writing is important.
• Graphs and tables:
• Must be included in the report body. (i.e. Do not group them all together at the end).
• Must include titles, axis titles, and scales
• Proper scaling is required
• Proper formatting of decimals is required
• Round to 2 or 3 decimals
• Convert any scientific numbers to decimals – even if 0.000
• Be sure to reference your tables and graphs in your discussions. Refer the reader to the graph or table by number:  g., “Referring to Graph 1, it is evident that…….”

15%        Style, organization, professionalism of appearance, and written content                                                             (e.g., clarity, organization, sentence structure, grammar, and punctuation)

25%        Introduction

60%        Sectionsdedicated to each worksheet (20% each)

This is an individual assignment – Do not work with others.

Use only the data set you are assigned to – you will not receive any credit for other data sets.  Here’s how to determine which data set to use:

If your last name begins with the letter…

 Data Set: A B C Wednesday A – C D – Q R – Z Thursday A – C D – N P – Z

All three data sets are available on eCollege

Deliverables

1. A hardcopy of your write-up (Word file) is due by Thursday, April 27.  The hardcopy is to be brought to class or dropped off at my office, (271 Dan Rogers Hall).
2. Email me an electronic copies of both your write-up AND Excel file prior to class on 4/27.

Your assignment will not be considered turned in until BOTH sets of material submitted.

In your data file, you will find 3 worksheets.  Develop the models shown on each worksheet, complete the metrics of error, and then answer the questions below.

Simple Average Forecasts

In this worksheet, you are to develop three models of daily forecast, (rows 14 – 344), then evaluate the performance of each model over a holdout period, (rows 349 – 377).  Space is provided for model building, error calculation, and performance metrics.

For the simple exponential smoothing model, use Excel’s solver tool to determine the optimal value of alpha, subject to the constraints outlined in your notes.  Program solver to minimize MAPEduring the model building period.

Once you have developed the models and calculated the metrics, state which model you would use and explain your decision.  Use tables and graphs as appropriate.

Trend Forecasts

In this worksheet, you are to develop two models of weekly demand.  Assess model fit by calculating error for each period and then metrics of performance.  (Note: there is no holdout period in this problem).

For Holt’s exponential smoothing model, use Excel’s solver tool to determine the optimal value of alpha and beta, subject to the constraints outlined in your notes for the Holt-Winter model.  (The difference being that you don’t have gamma in this model).  Program solver to minimize MAPE.

Once you have developed the models and calculated the metrics, state which model you would use and explain your decision.  Use tables and graphs as appropriate.

Seasonality and Trend Forecasts

In this worksheet, you are to develop two models of truckload shipments each quarter.  Assess model fit by calculating error for each period and then metrics of performance.  (Note: there is no holdout period in this problem).

For Holt-Winter’s exponential smoothing model, use Excel’s solver tool to determine the optimal value of alpha, beta and gamma, subject to the constraints outlined in your notes.  Program solver to minimize MAPE.

Once you have developed the models and calculated the metrics, state which model you would use and explain your decision.  Use tables and graphs as appropriate

Write-up

Begin your write-up with anintroduction section that introduces the project, but also:

• Explains time series models, (e.g. how they work, pros and cons, how one identifies potential models).
• Explains how one evaluates a forecast model and the various metrics that are used.

Next, create a section for each worksheet and address the questions listed in the instructions above. In each section, be sure to support your recommendations with tables and graphs in addition to discussion.  There is no set requirement for the inclusion of tables or graphs, but use them as appropriate.

Overall, this write-up will be much less extensive than Assignment 1. However, take time to make sure your coverage of the material is thorough and that the appearance is professional.

Guidelines for thewrite-up

The report is to look professional – that is, it should be of the quality you would turn in to an upper level manager.  In fact, imagine that I intend to show this to every company you interview with.  What kind of work would you want them to see?

• Coversheet:  Include only the following:
• The data set used for your analysis
• Use proper grammar and sentence structure.  Clarity of writing is important.
• Graphs and tables:
• Must be included in the report body. (i.e. Do not group them all together at the end).
• Must include titles, axis titles, and scales
• Proper scaling is required
• Proper formatting of decimals is required
• Round to 2 or 3 decimals
• Convert any scientific numbers to decimals – even if 0.000
• Be sure to reference your tables and graphs in your discussions. Refer the reader to the graph or table by number:  g., “Referring to Graph 1, it is evident that…….”