DS412 Midterm–takehome: forecasting: 10 points + 5 bonus points (LP) Spring 2013
... [Show More]
Print out your results and bring them with you to the midterm as 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 March 12th. 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 to the judiciary committee 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)
A professor has been teaching DS412 every spring term has been teaching DS412 at SF State since 1991. She’s recorded both the average grade on the exam (expressed as a percent) and also San Francisco’s average temperature during the week of exam time, as seen on the table below.
year
avg. temp (degrees F)
final exam average score
year
avg. temp (degrees F)
final exam average score
1991
62
84%
2002
76
73%
1992
66
75%
2003
70
80%
1993
63
83%
2004
77
63%
1994
66
74%
2005
68
75%
1995
62
80%
2006
72
73%
1996
67
82%
2007
69
75%
1997
65
85%
2008
71
78%
1998
68
82%
2009
75
66%
1999
70
74%
2010
73
68%
2000
73
74%
2011
71
70%
2001
72
71%
2012
75
69%
She is wondering if there may be some underlying explanation of what may be influencing exam scores, since they do seem to vary quite a bit. To help her, you will use MS Excel to investigate. You may use either Excel regression technique that was demoed in class, but you should be familiar with how to interpret the solutions for BOTH techniques for the in-class part of the midterm.
Part I (4 points) Perform the appropriate linear trend analysis
Part II (5 points) Perform the appropriate associative forecast
For both parts I and II
· Circle and label the correct values for a (intercept), b (slope), and R2.
· Provide an Excel graphic that displays both the underlying data and the linear regression. If the graphics are too small, distorted, or hard to read, you lose credit.
You will earn the formatting point if you have done all of the following very simple things correctly:
· List and highlight all team members in alphabetic order, A to Z, by last name, at the top right of the 1st page.
· Provide at most 2 pages for this forecasting assignment- ONE for part I, ONE for part II (cover sheets are wasteful!) in addition to whatever you choose to attempt for the extra credit.
· If handling in more than 1 page, use a stapler. As I am not your mother (thank goodness, right?), don’t expect me to bring a stapler to class.
This is a BONUS part of the take-home that your group can attempt. As it is a BONUS, it is more work for the same amount of points as a regular question, and I will not be as generous with bestowing partial credit, in the event that something is 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 related to this situation.
You win the lottery and decide to take some time off from SF State by enjoying life on a remote tropical island in the Pacific near Tahiti. (Who said midterm questions have to be dull?) But even in paradise you cannot escape linear programming, for the islanders learn of your analytic skills and seek your business advice. They supplement their enjoyable but subsistence-level existence by selling tourists decorated clay objects in the style of their culture’s traditional ceremonial items. They have 4 potters and 4 decorators, and these skills are very exacting, taking months to learn, so decorators can’t make pottery or vice-versa, nor can anyone else perform either task. Furthermore, each craftsperson has at most 10 hours a week spare time to do this work. The islanders can produce 3 different items at the prices listed and with the material and labor inputs required per item (there’s lots of clay around, but the decorating items- paint, tiles and semi-precious stones, are in finite supply) shown in Table 1. Assume the islanders can sell all items they make, and that the currency of relevance is US Dollars.
Table 1: recipes and sales prices for the items the Islanders can make
bowl
platter
pitcher
sales price
$ 10.00
$ 20.00
$ 25.00
pottery making time (hours)
1
1.5
3
Decorating time (hours)
2
2
2
Pots of paint needed
0.5
1
2
tiles needed
20
30
20
Semi-precious stones needed
1
4
4
The islanders receive an allotment of supplies each week for free, as part of a United Nations aid package designed to encourage native craftwork. They can also purchase some additional supplies from other nearby islands, but doing so will cut into their profits. While there’s enough paint and stones for sale, at most 200 tiles would be available for purchase in any one week.
Table 2: Free Supplies Bestowed and Additional Supplies’ Costs and Limits
Materials
(Free) Supply
Purchase cost for more
paint
30
$1/pot
tiles
250
$0.1/tile ( only 200 more for sale)
stones
40
$3/stone
Your goal is to help the islanders maximize their weekly profits while satisfying all constraints. Formulate as a linear program and solve. For simplicity, you do not need to worry about rounding to integers for this problem:, e.g. answers like 12.3 bowls and 4.5 platters would be acceptable.
4 BONUS points: Formulate and solve the problem in Excel (or similar open source spreadsheet program) in canonical form (variables in columns, constraints in rows) and print out on 1 page. Also type the names of your team into an Excel cell near the top of the formulation page (or lose 1 point.)
Assuming you reach a proper solution, you can get 1 more BONUS point by printing out the entire Sensitivity Report on 1 page.
If you turn this Bonus part in, please staple it to your Forecasting take-home (thus, you should have AT MOST 4 pages to turn in) or risk losing one of your bonus points. You must work with the same team that you did the forecasting portion with. Any evidence of collaboration outside of the team (i.e. if you copy from another team or from individual not in your team) will be punished with all teams/individuals earning a zero on the entire midterm. (takehome and inclass!) [Show Less]