Question details

Print out your results and bring them with you to the midterm as there will be additional
$ 15.00

DS412 Midterm–takehome: forecasting: 10 points + 5 bonus points (LP)  Spring 2013

                                                                                                               

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!)

Available solutions