Decision Science

Order Description

DS412 Midterm–takehome: forecasting: 10 points + 5 bonus points (LP) Fall 2015
Print out your results and bring them with you to the midterm. There will be additional questions on the midterm related to this analysis. You must hand in your take-home by the beginning of the test on October 8th. Late take-homes will not be accepted. You make work in groups of up to 4 people. If there is evidence of collaboration beyond that, either in that your group is larger than 4, or that you gave answers to other teams, you will get a 0 on the entire test (not just the take-home) and be reported for cheating. You will not be graded any differently whether you work individually or in a group, no matter what the group size is (as long as it’s 4 or less. Only one take-home per team should be turned in (If you have team members from both sections, someone must turn in the take-home at 8:10)
StinkE-Cheez is based in Marin County and sells a variety of cheeses made from organic, hormone-free milk. They have been tracking the monthly sales (measured in cases) for their cream cheese spread for the past year, as they would like to better predict demand. Use Excel to do the following, and show all forecasts and MADS to 2 decimal places:
case
Month-YY
sales
Oct-14
89
Nov-14
91
Dec-14
91
Jan-15
93
Feb-15
89
Mar-15
83
Apr-15
89
May-15
93
Jun-15
91
Jul-15
99
Aug-15
93
Sep-15
90
2 pts Calculate the forecast for October 2015 (and all earlier months as appropriate) using a 5-period moving average.
2 pts Calculate the forecast for October 2015 (and all earlier months as appropriate) using a 3-period weighted moving average where the most recent weight is 0.5 and 2nd most recent is 0.4.
2 pts Calculate the forecast for October 2015 (and all earlier months as appropriate) using exponential smoothing. Pick the alpha that gives the BEST forecast (with respect to MAD), out to 1 decimal place only. Initialize the forecast by setting November 2014’s forecast equal to October 2014’s sales.
1 pt Calculate the forecast for October 2015 (and all earlier months as appropriate) using the simple naïve method.
2 pts Calculate MAD for each of these 4 forecasts.
1 pt: You will earn this formatting point if you have done all of the following correctly:
• Type in all team members in alphabetic order, (A to Z by last name), at the top right of the 1st page. Handwritten names are not acceptable, we are all tired of last-minute moochers trying to add.
• Provide at most 2 pages for this forecasting assignment-(cover sheets are wasteful!) in addition to whatever pages you choose to attempt for the extra credit.
• If handling in more than 1 page, use a stapler.
This is a BONUS part of the take-home that your group can attempt. As it is for 5 BONUS points, there is more work for the same amount of points as you would get with a regular question, and I will not be as generous with bestowing partial credit as with regular questions, in the event that you have something wrong. However, you may want to at least look at and attempt this, because there’s a good chance you might see something on the in-class portion of the test asking you some LP related questions.
StinkE-Cheez also sells hard cheeses. Every month they buy cheese by the pound from local farmers. Since they insist on sourcing only from local, artisanal producers adhering to the highest quality and health standards, supplies are limited. Table 1 shows prices and availability of these supplies.
Table 1: Supply parameters
wholesale purchase price per pound
pounds available
Jack
$ 5.20
140
Cheddar
$ 4.80
200
Swiss
$ 6.80
160
Blue
$ 10.00
250
StinkE-Cheez cuts and repackages the cheeses into half-pound (8 oz), environmentally friendly packages, selling them at the prices listed in Table 2. Table 2 also shows that they sell a custom product, Goo-Yummy. This shredded blend is composed of equal parts of each of the 4 cheeses they buy from the farmers (with no other additives) and is sold in 8 oz tubs. No volume is lost or created in the blending process or indeed in any of the repackaging processes.
Table 2: Demand Parameters
sales price per 8oz package
Jack
$ 6.00
Cheddar
$ 6.00
Swiss
$ 8.00
Blue
$ 11.00
Goo-Yummy
$ 7.00
Blue cheese is not as popular as their other offerings, so StinkE-Cheez will offer at most 400 packages this month, but they can sell as much of their other produces as they like. StinkE-Cheez has also promised to provide at least 200 packages each of both Swiss and Jack this month to a high-volume customer (a fondue restaurant).
Your goal is to help StinkE-Cheez maximize the cheese profits for this month while satisfying all constraints. For simplicity, don’t worry about rounding to integers, e.g. answers like 12.3 packages of Swiss will be acceptable if that is truly part of the optimal solution.
• Formulate the problem in Excel using standard form (variables in columns, constraints in rows, all coefficients must be visible, and variables and constraints are given names that are meaningful.) and print out on 1 page. Full points are awarded only for standard form.
• If you got a solution, print out the entire Sensitivity Report on 1 page
Staple1 these pages to your Forecasting portion or lose a formatting point