Docstoc

FinalExam

Document Sample
FinalExam Powered By Docstoc
					This is a take home final examination. Work in study groups should cease
You are expected to work independently on this examination. Evidence tha
or indirectly) will result in a referral to Honor Council.

Again, YOU ARE EXPECTED TO WORK INDEPENDENTLY ON THIS EXAMINA


The examination should be submitted via email to lynch_ak@mercer.edu by Wedn
Thanks and good luck!
roups should cease once this examination is out.
nation. Evidence that help has been provided (directly


LY ON THIS EXAMINATION!


@mercer.edu by Wednesday April 30 at midnight.
A furniture manufacturer produces two types of tables (country and contemporary) using three types of machines. The
to produce the tables on each machine is given in the 'input requirements' table below. Country Tables sell for $400 and
tables sell fo $425. Management has determined that at least 24% of the tables made should be country tables. How
each type of table should the company produce if it wants to maximize revenue? (Assume whole units must be produce

Create a spreadsheet model for this problem and solve it using EXCEL



Input Requirements
                                                Available   Time
Machine          Country          Contemporary Machine Time Used

Router                      1.5              2          1,100
Sander                     3.25              4          1,950
Polisher                    2.5            1.5          1,450


Table Produced                                   Revenue                 Total Revenue

Country                    1332                   $532,800.00
Contemporary                967                   $386,800.00              $919,600.00
                           2299     0.57938234
                 country          contemporary
Router                      732              1             1100
Sander                      600              0             1950
Polisher                      0            966             1449
g three types of machines. The time required
 Country Tables sell for $400 and comtemporary
 should be country tables. How many of
me whole units must be produced)
                                                                  Bavarian Motor Company


                Ship      From                     To                  Unit Cost             Nodes                  Net Flow Supply/Demand
                100          1      Newark              2   Boston       $30                   1       Newark         -200             -200
                100          1      Newark              4   Richmond     $40                   2       Boston         100               100
                 0           2      Boston              3   Columbus     $50                   3       Columbus         0               160
                 0           3      Columbus            5   Atlanta      $35                   4       Richmond       180               180
                 0           5      Atlanta             3   Columbus     $40                   5       Atlanta         50               170
                 0           5      Atlanta             4   Richmond     $30                   6       Mobile         170               170
                 0           5      Atlanta             6   Mobile       $35                   7       Jacksonville   -300             -300
                 0           6      Mobile              5   Atlanta      $25
                 80          7      Jacksonville        4   Richmond     $50
                 50          7      Jacksonville        5   Atlanta      $65
                170          7      Jacksonville        6   Mobile       $50

                                        Total Transportation Cost      $22,750




A car distribution faces the networking problem outlined above. The firm has 250 cars in ports in Newark and 400 cars in ports in Jacksonville. The distribution pa
are captured in the upper lefthand corner of the spreadsheet along with the associated costs per unit along the path.

Using simple linear programming, please determine the distribution network that will minimize total transportation costs. Assume demand for cars is as follows:

Newark                                 50
Boston                                 100
Columbus                               160
Richmond                               180
Atlanta                                170
Mobile                                 170
Jacksonville                           100
                                          930
                         out          in
                 250           -200          0
                                  0        100
                                  0          0
                                  0        180
                                  0         50
                                  0        170
                 400           -300          0




sonville. The distribution paths


and for cars is as follows:
                                lyra              libra          hydra          new lyra       new libra
capacity (1000)                         1000               800        900            1600           1800
fixed cost(in $M)                       2000              2000       2600            3400           3700

                                                   profit margin by car line (in $k)
lyra                                          2              0             0            2.5            2.3
libra                                         0              3             0              3            3.5
hydra                                         0              0             5              0            4.8

resulting plants open                         1              0             1               0               1


production quantities (in $M)                                                                                  total
lyra                                        450              0             0               0         460                910
libra                                         0              0             0               0        1320               1320
hydra                                         0              0           900               0          20                920
total                                       450              0           900               0        1800
                                       <=             <=            <=             <=             <=

logical upper bound                     1000                 0           900               0        1800

Demand Diversion Matrix
                                lyra              libra          hydra
lyra                            na                         0.3           0.05
libra                                         0 na                        0.1
hydra                                         0              0

Total Profit ($M)                           900              0       4500                  0        5774           11174
                                                                                                                    2874
     inexpensive
     sporty compact
     luxury




     demand     diverted
<=       1400          910
<=       1100         1320
<=        800          920
              MetroBank
                                 Jeff Spicoli is responsible for purchasing the paper use
Annual Demand         26,000     purchase 26000 boxes of paper, which will be used at a
Cost per Unit          $40       year. Each box of paper costs $40. It costs $100 to p
Cost per Order        $100       costs). There is a cost of 12% to funds allocated to su
Holding Cost           12%       customers; hence this is the opportunity cost of tying u
                                 optimal number of orders) that will minimize the costs o
Order Quantity        736.00

Purchasing Cost     $1,040,000
Cost of Ordering      $3,533     Extra Credit!
Inventory Cost        $3,533
Total Cost          $1,047,065                              35.32609
  for purchasing the paper used in all the copy machines for a local bank. He needs to
  paper, which will be used at a very steady rate over the course of the
 costs $40. It costs $100 to place each order (this includes shipping and receiving
f 12% to funds allocated to supplies because such funds could be lent out to
 the opportunity cost of tying up funds in inventory.What is the order quantity (and hence,
) that will minimize the costs of buying and holding paper?
                                         Applications



3500




3000

                                                           Applications
                                                           Linear(Applications)
2500




2000




1500




1000




 500




   0
   0.0%   1.0%   2.0%   3.0%   4.0%   5.0%       6.0%   7.0%      8.0%        9.0%   10.0%   11.0%
SUMMARY OUTPUT

            Regression Statistics
Multiple R                       0.882531994
R Square                          0.77886272
Adjusted R Square                0.766577316
Standard Error                    431.048903
Observations                              20

ANOVA
                                 df                  SS            MS        F    Significance F
Regression                                  1     11779437.38 11779437     63.3974 2.62E-07
Residual                                   18     3344456.821 185803.2
Total                                      19      15123894.2

                            Coefficients        Standard Error    t Stat   P-value Lower 95%
Intercept                        3847.933481      348.9861725 11.02603 1.95E-09 3114.741
Interest Rate                   -39830.47697      5002.415685 -7.96225 2.62E-07 -50340.2




RESIDUAL OUTPUT

    Observation        Predicted Applications     Residuals        e^2
                   1             2254.714402      843.2855979    711130.6
                   2             2135.222971      649.7770288    422210.2                   1
                   3             2135.222971      379.7770288    144230.6             3.50%
                   4             1975.901063      204.0989367    41656.38
                   5             1975.901063     -102.9010633    10588.63
                   6             1776.748678      17.25132156    297.6081
                   7             1617.426771     -167.4267706    28031.72
                   8             1577.596294     -400.5962936    160477.4
                   9             1418.274386     -596.2743857    355543.1
                  10             1378.443909     -492.4439087      242501
                  11             1378.443909     -638.4439087    407610.6
                  12             1139.461047     -399.4610469    159569.1
                  13             1019.969616      -297.969616    88785.89
                  14             820.8172311     -280.8172311    78858.32
                  15             542.0038923     -167.0038923     27890.3
                  16             303.0210304      121.9789696    14878.87
                  17             103.8686456      256.1313544    65603.27
                  18             64.03816861      324.9618314    105600.2
                  19            -15.62278534      354.6227853    125757.3
                  20            -55.45326231      391.4532623    153235.7 s_e
                                                                  3344457 431.0489 Eq. (11.7)
                                                                        Interest Rate Line Fit Plot
                                                                 4000




                                                   Application
                                                                 2000                             Applications




                                                        s
                                                                     0                            Predicted
                                                                        2.0% 6.0% 10.0%
                                                                     0.0% 4.0% 8.0% 12.0%         Applications
                                                                 -2000

                                                                          Interest Rate
Significance F




                                     U
                 Upper 95% Lower 95.0%pper 95.0%
                 4581.126221 3114.741 4581.126
                 -29320.7916 -50340.2 -29320.8




            #Appl@3.5% 95% FI
                 2453.866787 1591.769
                             3315.965
SUMMARY OUTPUT

           Regression Statistics
Multiple R                     0.967836746
R Square                       0.936707966
Adjusted R Square              0.933191742
Standard Error                 0.085975748
Observations                            20

ANOVA
                               df                  SS           MS          F        Significance F
Regression                                1     1.969153602 1.969154      266.396      3.11285E-12
Residual                                 18     0.133052927 0.007392
Total                                    19     2.102206529

                          Coefficients        Standard Error   t Stat    P-value      Lower 95%
Intercept                     4.043438343       0.069607757     58.0889  6.2E-22       3.897197873
Interest Rate                -16.28519752        0.99776714    -16.3216 3.11E-12      -18.38142849




RESIDUAL OUTPUT
                                                                        exp(4.043)     57.02206756
    Observation        Predicted Log(apps)      Residuals
                   1          3.392030442       0.099050971
                   2           3.34317485        0.10165035
                   3           3.34317485        0.05736314
                   4           3.27803406       0.060422434
                   5           3.27803406      -0.005496282
                   6          3.196608072       0.057214367
                   7          3.131467282        0.02990072
                   8          3.115182085      -0.044405622
                   9          3.050041294      -0.135169477
                  10          3.033756097      -0.086322375
                  11          3.033756097      -0.164524377
                  12          2.936044912      -0.066813192
                  13          2.887189319      -0.028652122
                  14          2.805763332      -0.073369572
                  15          2.691766949      -0.117735681
                  16          2.594055764       0.034333166
                  17          2.512629776       0.043672724
                  18          2.496344579       0.093605023
                  19          2.463774184       0.066425514
                  20          2.447488986       0.078850291
                                                       Interest Rate Line Fit Plot
                                             4.00000




                                 Log(apps)
                                                                                  Log(apps)
                                             2.00000
                                                                                  Predicted Log(apps)
                                             0.00000
                                                   0.0%      10.0%        20.0%

                                                          Interest Rate




Upper 95%          U
         Lower 95.0%pper 95.0%
 4.189679 3.897198 4.189679
   -14.189 -18.3814  -14.189
SUMMARY OUTPUT

Regression Statistics
Multiple R 0.779575
R Square 0.607737
Adjusted R Square
           0.585945
Standard Error
            1833349
Observations       20

ANOVA
               df         SS       MS       F   Significance F
Regression           1 9.37E+13 9.37E+13 27.88762 5.08E-05
Residual            18 6.05E+13 3.36E+12
Total               19 1.54E+14

                    Standard Error t Stat
          Coefficients                    P-value Lower 95%Upper 95%          U
                                                                    Lower 95.0%pper 95.0%
Intercept    9675837 1484318 6.51871 3.96E-06 6557401 12794273 6557401 12794273
Interest Rate
            -1.1E+08 21276415 -5.28087 5.08E-05 -1.6E+08 -6.8E+07 -1.6E+08 -6.8E+07




RESIDUAL OUTPUT

       Predicted Apps^2
Observation          Residuals
        1 5181515        4416089
        2 4844441        2911784
        3 4844441        1480784
        4 4395009       357391.2
        5 4395009        -886880
        6 3833219        -614783
        7 3383786       -1281286
        8 3271428       -1886099
        9 2821996       -2146312
       10 2709638       -1924642
       11 2709638       -2162038
       12 2035490       -1487890
       13 1698416       -1177132
       14 1136626        -845026
       15 350119.2       -209494
       16 -324029         504654
       17 -885819        1015419
       18 -998177        1149498
       19 -1222893       1337814
       20 -1335251       1448147
                    Interest Rate Line Fit Plot
         15000000
         10000000
Apps^2




                                            Apps^2
          5000000
                                            Predicted Apps^2
                0
                0.0% 4.0% 8.0% 12.0%
         -5000000 2.0% 6.0% 10.0%
                        Interest Rate
Interest Rate Applications Log(apps)       Apps^2      When interest rates decline, Patriot Bank has found that they inund
    4.0%         3098        3.49108         9597604   staffing needs, Patriot wants to develop a regression analysis to he
    4.3%         2785        3.44483         7756225   each month as a function of the prime lending rate(X). The bank co
    4.3%         2515        3.40054         6325225   the prime rate and number of refi apps over the last 20 months.
    4.7%         2180        3.33846         4752400
    4.7%         1873        3.27254         3508129
    5.2%         1794        3.25382         3218436   Use Log and Squared terms in an attempt to find the best fitti
    5.6%         1450        3.16137         2102500
    5.7%         1177        3.07078         1385329
    6.1%          822        2.91487          675684
    6.2%          886        2.94743          784996
    6.2%          740        2.86923          547600
    6.8%          740        2.86923          547600
    7.1%          722        2.85854          521284
    7.6%          540        2.73239          291600
    8.3%          375        2.57403          140625
    8.9%          425        2.62839          180625
    9.4%          360        2.55630          129600
    9.5%          389        2.58995          151321
    9.7%          339        2.53020          114921
    9.8%          336        2.52634          112896

(1.) Create a scattergram of these data. Save this on a sheet called 'scattergram'.

(2.) Use data analysis to estimate a linear regression model on these data. What is the regression equation?




(3.) Interpret the value of R^2 obtained using the equation from question 3.




(4.) According to the equation in (3.) what is the expected level of refi apps if the prime rate 1s 3.5%?




(5.) Develop an approximate 95% prediction interval for your estimate in question (4.) above.




(6.) Is there a statistically significant relationship between interest rates and loan application levels?
Perform a formal test below.

Null:
Alternative:
Test Statistic Value:
Conclusion:
(7.) What other variables might be needed to make this a better model? In other words, if we expanded
this to become a multiple regression model (and in responding assume you have access to any data
you would like to explore) what variables would you consider adding to the right hand side of the
regression equation to strengthen the model?
triot Bank has found that they inundated with requests to refinance mortgages. To better plan
develop a regression analysis to help predict the total number of mortgage applications (Y)
 prime lending rate(X). The bank collected the data appearing to the left showing
i apps over the last 20 months.


 n an attempt to find the best fitting model!




 the regression equation?




me rate 1s 3.5%?




plication levels?
rds, if we expanded
cess to any data
  SUMMARY OUTPUT

  Regression Statistics
  Multiple R 0.052237
  R Square 0.002729
  Adjusted R Square
               -0.0685
  Standard Error
             462.3319
  Observations       16

  ANOVA
                    df        SS         MS         F   Significance F
  Regression              1 8187.918 8187.918 0.038306 0.847642
  Residual               14 2992511 213750.8
  Total                  15 3000698

                         Standard Error t Stat
               Coefficients                       P-value Lower 95%Upper 95%          U
                                                                            Lower 95.0%pper 95.0%
  Intercept     -7731.62 50084.38 -0.15437 0.87952            -115152 99688.69    -115152 99688.69
  Year          4.907353 25.07347 0.195719 0.847642          -48.8699 58.68459   -48.8699 58.68459




  RESIDUAL OUTPUT

  Observation           Residuals
Predicted Sales (thousands of dollars)
           1    2034.007    -76.0074
           2    2038.915    -287.915
           3    2043.822    466.1779
           4    2048.729    -359.729
           5    2053.637    -38.6368
           6    2058.544    -273.544
           7    2063.451    781.5485
           8    2068.359    -223.359
           9    2073.266    77.73382
          10    2078.174    -213.174
          11    2083.081    615.9191
          12    2087.988    -102.988
          13    2092.896    -218.896
          14    2097.803    -408.803
          15     2102.71    882.2897
          16    2107.618    -620.618
                                           2006   2112.525
                                           2007   2117.432
                                           2008    2122.34
                                           2009   2127.247
                                           2010   2132.154
                        Year Line Fit Plot
(thousands

               $4,000
 of dollars)

                                             Sales (thousands of
   Sales




               $2,000                        dollars)

                   $-                        Predicted Sales
                    1982    2002   2022      (thousands of
                                             dollars)
                            Year
SUMMARY OUTPUT

Regression Statistics
Multiple R 0.52627
R Square     0.27696
Adjusted R Square
           0.096201
Standard Error
           425.2081
Observations       16

ANOVA
                 df        SS         MS        F   Significance F
Regression             3 831074.9     277025 1.532201 0.256765
Residual              12 2169624      180802
Total                 15 3000698

                      Standard Error t Stat
            Coefficients                      P-value Lower 95%Upper 95%          U
                                                                        Lower 95.0%pper 95.0%
Intercept     2241.22    368.2099 6.086801 5.44E-05     1438.96 3043.481    1438.96 3043.481
lag1           -0.2493   0.191213   -1.3038 0.216759   -0.66592 0.167314   -0.66592 0.167314
lag2         0.305097    0.176638 1.727243 0.109748    -0.07976 0.689958   -0.07976 0.689958
lag3         -0.13278    0.165362 -0.80299 0.437598    -0.49308 0.22751    -0.49308 0.22751
SUMMARY OUTPUT

      Regression Statistics
Multiple R               0.72913
R Square                 0.53163
Adjusted R Square      0.219384
Standard Error         395.1703
Observations                  16

ANOVA
                          df          SS              MS         F    Significance F
Regression                      6    1595262   265877 1.702598 0.226914
Residual                        9    1405436 156159.6
Total                          15    3000698

                               Standard Error t Stat
                     Coefficients                             P-value Lower 95%Upper 95%Lower 95.0%
Intercept              2124.586     357.9108 5.936077 0.000219 1314.935 2934.236 1314.935
lag1                   -0.10487     0.217714 -0.48167 0.641536 -0.59737 0.387636 -0.59737
lag2                   0.092954     0.223313 0.416251 0.686977 -0.41221 0.598123 -0.41221
lag3                   -0.30302     0.204814 -1.47948 0.173141 -0.76634 0.160303 -0.76634
lag4                   0.414368     0.215331 1.924329 0.086455 -0.07274 0.901481 -0.07274
lag5                   -0.10386     0.244438 -0.42488    0.6809 -0.65681   0.4491 -0.65681
lag6                   -0.00158     0.195313   -0.0081 0.993713 -0.44341 0.440247 -0.44341



                                                                                                            Sales (


                                                                                       $3,500

                                                                                       $3,000

                                                                                       $2,500

                                                                                       $2,000

                                                                                       $1,500

                                                                                       $1,000
                                    Year          Sales (thousands of dollars)          $500
                                           1990   $   1,958
                                           1991   $   1,751                                $-
                                           1992   $   2,510                                 1980   1985   1990
                                           1993   $   1,689
                                           1994   $   2,015
                                           1995   $   1,785
                                           1996   $   2,845
                                           1997   $   1,845
                                           1998   $   2,151
                                           1999   $   1,865
                                           2000   $   2,699
                                           2001   $   1,985
                                           2002   $   1,874
                                           2003   $   1,689
                                           2004   $   2,985
Intercept              2124.586            2005   $   1,487
lag1                   -0.10487            2006   $   1,846
lag2   0.092954   2007   $   2,658
lag3   -0.30302   2008   $   1,601
lag4   0.414368   2009   $   2,261
lag5   -0.10386   2010   $   2,322
lag6   -0.00158
      Upper 95.0%
        2934.236
        0.387636
        0.598123
        0.160303
        0.901481
          0.4491
        0.440247



                    Sales (thousands of dollars)




980   1985     1990          1995         2000     2005   2010   2015
Year          Sales (thousands of dollars)
       1990   $    1,958
       1991   $    1,751                                             We need to create forecasts for sales for the years 2006-20
       1992   $    2,510                                             and then select the technique with the lowest value of RMS
       1993   $    1,689
       1994   $    2,015                                             Regression based forecasts
       1995   $    1,785                                             Weighted Moving Average (2 year)
       1996   $    2,845                                             Moving average (2year)
       1997   $    1,845
       1998   $    2,151
       1999   $    1,865
       2000   $    2,699
       2001   $    1,985
                                                                                                          $3,500
       2002   $    1,874
       2003   $    1,689
       2004   $    2,985                                                                                  $3,000
       2005   $    1,487
       2006                                                                                               $2,500
       2007
       2008                                                                                               $2,000
       2009
       2010                                                                                               $1,500

                                                                                                          $1,000
Sales ($k) Year            lag1           lag2           lag3        lag4        lag5        lag6
$   1,958           1990   $        -     $        -     $     -     $     -     $     -     $     -        $500
$   1,751           1991   $      1,958   $        -     $     -     $     -     $     -     $     -
$   2,510           1992   $      1,751   $      1,958   $     -     $     -     $     -     $     -           $-
$   1,689           1993   $      2,510   $      1,751   $   1,958   $     -     $     -     $     -            1982
$   2,015           1994   $      1,689   $      2,510   $   1,751   $   1,958   $     -     $     -
$   1,785           1995   $      2,015   $      1,689   $   2,510   $   1,751   $   1,958   $     -
$   2,845           1996   $      1,785   $      2,015   $   1,689   $   2,510   $   1,751   $   1,958
$   1,845           1997   $      2,845   $      1,785   $   2,015   $   1,689   $   2,510   $   1,751
$   2,151           1998   $      1,845   $      2,845   $   1,785   $   2,015   $   1,689   $   2,510
$   1,865           1999   $      2,151   $      1,845   $   2,845   $   1,785   $   2,015   $   1,689
$   2,699           2000   $      1,865   $      2,151   $   1,845   $   2,845   $   1,785   $   2,015
$   1,985           2001   $      2,699   $      1,865   $   2,151   $   1,845   $   2,845   $   1,785
$   1,874           2002   $      1,985   $      2,699   $   1,865   $   2,151   $   1,845   $   2,845
$   1,689           2003   $      1,874   $      1,985   $   2,699   $   1,865   $   2,151   $   1,845
$   2,985           2004   $      1,689   $      1,874   $   1,985   $   2,699   $   1,865   $   2,151
$   1,487           2005   $      2,985   $      1,689   $   1,874   $   1,985   $   2,699   $   1,865

                                                  2000   $ 2,699
Intercept      2124.5857                          2001   $ 1,985
lag1           -0.104866                          2002   $ 1,874
lag2           0.0929543                          2003   $ 1,689
lag3           -0.303018                          2004   $ 2,985
lag4            0.414368                          2005   $ 1,487
lag5           -0.103857                          2006    1845.712
lag6           -0.001582                          2007    2658.358
                                                  2008    1600.827
                                                  2009    2260.529
                                                  2010    2322.204
 sales for the years 2006-2010 inclusive. Use the following techniques to create forecasts
ith the lowest value of RMSE to produce final forecasts.


ar)




                                    Sales (thousands of dollars)
      $3,500

      $3,000

      $2,500

      $2,000

      $1,500

      $1,000

       $500

          $-
           1982            1987             1992            1997            2002             2007
Health Care Systems of Florida (HCSF) is planning to buind a number of new emergency-care clinics in central Florida.
divided a map of the area into seven regions. They want ot locate the emergency centers so that all seven regions will
served by at least one facility. Five possible sites are available for constructing the new facilities. The regions that can
are indicated by an "1" in the following table:

                                   Potential Site Location
Region               Sanford       Altamonte Apopka Casselberry Maitland                   Covered?
                 1             1                    1                                               2
                 2             1           1                      1          1                      2
                 3                         1                      1                                 1
                 4                                  1                        1                      1
                 5             1           1                                                        1
                 6                                  1                        1                      1
                 7                                                1          1                      1

Facility Costs
($1000's)                 475            625      525          475         525
Build?                      1              0        1            1           0

Total Costs              1475


The facility construction costs are provided below the coverage table. Find the site locations that will provide coverage t
at the lowest possible costs.
cy-care clinics in central Florida. HCSF management has
ers so that all seven regions will be conveniently
w facilities. The regions that can be served conveniently by each site




ations that will provide coverage to each of the 7 regions

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:1
posted:12/29/2013
language:Unknown
pages:33