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

### Solutions

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

Pri

Submitted on: 24 May, 2018 12:18:02 This tutorial has not been purchased yet .