Download Report

Quiz Number 1
Group 1 – North of Newark
Thamer AbuDiak
Reynald Benoit
Jose Lopez
Rosele Lynn
Dave Neal
Deyanira Pena
Professor Kenneth D. Lawerence
New Jersey Inst. Of Tech
Problems Assigned

Ragsdale
 2.13, 2.16, 2.20
 3.10, 3.13, 3.16, 3.21, 3.24, 3.28, 3.41, 3.44, 3.45

Dielman
 3.6
 3.24
Ragsdale 2.13 by Reynald
The marketing manager for Mountain Mist soda needs to decide how many TV spots and
magazine ads to run during the next quarter.
Initial Set up

Decision Variable




Objective function





The number of TV spots and magazine ads to run
X1 = TV Spot
X2 = Magazine Ad
TV spots expected to increase sales by 300,000 cans
Magazine ads expected to increase sales by 500,000 cans
Mountain Mist makes .05 cents a can
MAX: 0.05 * (300,000X1 + 500,000X2 )
Constraints






A total of $100,000 may be spent
No more than $70,000 may be spent on TV spots
No more than $50,000 may be spent on magazine ads
5,000X1 + 2,000X2 <= 100,000
5,000X1 <= 70,000
2,000X1 <= 50,000
Excel Initial Settings





D7 = (B7*B6+C7*C6)*C15
D10 = B10*B6 + C10*C6
D11 = B11*B6 + C11*C6
D12 = B12*B6 + C12*C6
Changing Cells B6 and C6
Solver
Results

In order to maximize profit Mountain
Mist should run 10 TV spots and 25
Magazine ads which will result in
$775,000 in profit.
Ragsdale 2.16 by Rosele


Problem: What combination of generators and alternators should Electrotech Corporation
manufacture in order to maximize profit?
Decision variables: how many generators and alternators should the Electrotech
Corporation manufacture?



X1 = generator X2 =alternator
Objective Function: How can the Electrotech Corporation get the
maximum income?

MAX: 250 X1 +150 X2
Constraints
Each generator requires 2 hours of wiring, each alternator requires 3 hours of
wiring. Electrotech can not exceed a total of 260 hours wiring time.
2 X1 +3 X2 < 260
 Each generator requires 1 hour of testing time, each alternator requires 2 hours
of testing time. Electrothech can not exceed a total of 140 hours testing time.
1X1 +2X2 <140
 Electrotech decides it needs to make at least 20 generators and 20 alternators.
X1>20
X2>20
LP Model
MAX: 250 X1 +150 X2
Subject to:
2 X1 +3 X2 < 260
1X1 +2X2 <140
X1>20
X2>20
Solver Parameters
Electrotech Corporation
Generators
Alternators
Number to Make
100
20
Total Profit
Unit Profits
250
150
$28,000
Constraints
Used
Available
Wiring Hrs Required
2
3
260
260
Testing Hrs Required
1
2
140
140
Summary
If additional wiring time becomes available at a reasonable cost should Electrotech do
so? Why or why not?
No, Electrotech should not do so because they do not see an increased profit since they
are again only making 120 units.
Electrotech Corporation
Number to Make
Unit Profits
Constraints
Wiring Hrs Required
Testing Hrs Required
Generators
100
245
2
1
Alternators
20
145
3
2
Total Profit
$27,400
Used Available
260
500
140
140
Problem 2-20, Thamer AbuDiak



Decision Variables:
X1: Number of hours that Mine1 worked
X2: Number of hours that Mine2 worked


Objective functions:
MIN: 200X1+160X2






Constrains:
6X1+2X2 >= 12
2X1+2X2 >= 8
4X1+8X2 >= 24
X1 >=0
X2 >=0



Answer:
1 Hour of Operation/Day for Mine 1
3 Hour of Operation/Day for Mine 2


Before
After
Problem 2-20 cont., Thamer AbuDiak
Ragsdale 3.10 by Deyanira
A. LP Model
x1= contemporary tables
x2= country tables
MAX:
450x1 + 350x2 } revenue
Subject to:
2.0x1 + 1.5x2
4.5x1 + 3.0x2
1.5x1 + 2.5x2
X1
X2
X1
1X2
1000 } router constraint
 2000 } sander constraint
 1500 } polisher constraint
 .30 } has to produce at least 30%
 .20 } has to produce at least 20%
 0 ) simple lower bound
 0 } simple lower bound
Spread Sheet
Furniture Manufacture
Contemporary
Number of Makes
Unit Revenue
Constraints
Router
Sander
Polisher
Country
$450
$350
Total Revenue
$0
2
4.5
1.5
Used
1.5
3
2.5
Available
1000
2000
1500
0
0
0
Microsoft Excel 11.0 Answer Report
























Worksheet: [quiz 1 problems 10-13 Ch 3.xls] Sheet1
Target Cell (Max)
Cell
$D$6
Name
Unit Revenue Total Revenue
Adjustable Cells
Cell
$B$5
$C$5
Name
Original Value
Number of Makes Contemporary 74.07407407
Number of Makes Country
555.5555556
Constraints
Cell
$D$9
$D$10
$D$11
$B$5
$B$5
$C$5
$C$5
Name
Cell Value
Router Used
981.4814815
Sander Used
2000
Polisher Used
1500
Number of Makes Contemporary
Number of Makes Contemporary
Number of Makes Country
Number of Makes Country
Original Value
$ 227,777.78
Formula
$D$9<=$E$9
$D$10<=$E$10
$D$11<=$E$11
74.07407407
74.07407407
555.5555556
555.5555556
Final Value
$ 227,777.78
Final Value
74.07407407
555.5555556
Status
Not Binding
Binding
Binding
$B$5>=0.3
$B$5>=0
$C$5>=0
$C$5>=0.2
Slack
18.51851852
0
0
Not Binding
Not Binding
Not Binding
Not Binding
73.77407407
73.77407407
555.3555556
555.3555556
Optimal Solution
Furniture Manufacture
Number of Makes
Contemporary
74.07407407
Country
555.5555556
Total Revenue
Unit Revenue
$450
$350
$227,777.78
Constraints
Router
Sander
Polisher
2
4.5
1.5
1.5
3
2.5
Used
981.481
2000
1500
Available
1000
2000
1500
Ragsdale 3.13 by Deyanira
A.
Lp model.
x1= bonds
x2= home mortgages
x3= car loans
x4= personal loans
Max: .10x1 + .085 x2 + .095x3 + .125x4 } total return
Subject to:
x4  162500 } 25% of total portfolio
x2  x4
} invest more on mortgages than personal loans
x1  x4
} invest more on bond than personal loans
x1 + x2 + x3 + x4 = $650,000 } total investment
x1,x2,x3,x4  0 } no negativity conditions
Spreadsheet
Bank Portfolio













Bonds
Home Mortgages
Car Loans
Personal Loans
Total Investment:
Total Available: $
Amount Invested
$0
$0
$0
$0
$ 0
650,000.00
Maximum
0
0
0
$162,500.00
Total
Return
10%
8.5%
9.5%
12.5%
$0
Microsoft Excel 11.0 Limits Report

















Worksheet: [quiz 1 problems 10-13 ch 3.xls]Sheet2
Cell
$D$9
Cell
$B$5
$B$6
$B$7
$B$8
Target
Name
Total Return
Value
$ 66,625.00
Adjustable
Lower
Name
Value
Limit
Bonds Amount Invested
$325,000.00
$325,000.00
Home Mortgages Amount Invested
$162,500.00
$162,500.00
$66,625.00
Car Loans Amount Invested
$
$
Personal Loans Amount Invested $162,500.00
$162,500.00
Target
Result
$66,625.00
Upper
Limit
$325,000.00
$162,500.00
Target
Result
$66,625.00
$66,625.00
$66,625.00
$66,625.00
$
$162,500.00
$66,625.00
$66,625.00
Excel 11.0 Answer Report
































Worksheet: [quiz 1 problems 13 ch 3.xls]Sheet2
Target Cell (Max)
Cell
$D$9
Name
Total Return
Adjustable Cells
Cell
$B$5
$B$6
$B$7
$B$8
Name
Bonds Amount Invested
Home Mortgages Amount Invested
Car Loans Amount Invested
Personal Loans Amount Invested
Original Value
$ 325,000.00
$ 162,500.00
$
$ 162,500.00
Name
Total Investment: Amount Invested
Bonds Amount Invested
Home Mortgages Amount Invested
Bonds Maximum
0
Home Mortgages Maximum
Car Loans Maximum
Bonds Amount Invested
Home Mortgages Amount Invested
Car Loans Amount Invested
Personal Loans Amount Invested
Personal Loans Amount Invested
Cell Value
$ 650,000.00
$ 325,000.00
$ 162,500.00
$C$5>=$C$5
0
0
$ 325,000.00
$ 162,500.00
$
$ 162,500.00
$ 162,500.00
Original Value
$ 66,625.00
Final Value
$ 66,625.00
Final Value
$ 325,000.00
$ 162,500.00
$
$ 162,500.00
Constraints
Cell
$B$11
$B$5
$B$6
$C$5
$C$6
$C$7
$B$5
$B$6
$B$7
$B$8
$B$8
Formula
$B$11=$B$12
$B$5>=$B$8
$B$6>=$B$8
Binding
$C$6>=$C$6
$C$7>=$C$7
$B$5>=0
$B$6>=0
$B$7>=0
$B$8>=0
$B$8<=$C$8
Status
Binding
Not Binding
Binding
0
Binding
Binding
Not Binding
Not Binding
Binding
Not Binding
Binding
Slack
0
$162,500.00
$
0
0
$325,000.00
$162,500.00
$
$162,500.00
0
Optimal Solution
Bank Portfolio









Bonds
Home Mortgages
Car Loans
Personal Loans
Total Investment:
Total Available: $
Amount Invested
$325,000.00
$162,500.00
$
$162,500.00
$ 650,000.00
650,000.00
Maximum
0
0
0
$162,500.00
Total
Return
10%
8.5%
9.5%
12.5%
$ 66,625.00
Ragsdale 3.16 by Jose
Decision Variables
M1 = Number of electric trimmers to make
Objective Function
MIN: 55M1 + 85M2 + 67B1 + 95B2
M2 = Number of buy trimmers to make
B1 = Number of electric trimmers to buy
B2 = Number of gas trimmers to buy
Constraints
Subject To: M1 + B1 = 30,000
M2 + B2 = 15,000
0.2M1 + 0.4M2  10,000
0.3M1 + 0.5M2  15,000
0.1M1 + 0.1M2  5,000
Solved by Jose F. Lopez
M1, M2, B1, B2  0
Ragsdale 3.16
Number to
Electric
Gas
Model
Model
Cost to
- Make
30,000
10,000
- Make
$55
$85
Total Cost
- Buy
0
5,000
- Buy
$67
$95
$2,975,000
Hours Required
# Available
30,000
15,000
# Needed
30,000
15000
Used
Available
- Production
0.2
0.4
10,000
10,000
- Assembly
0.3
0.5
14,000
15,000
- Packaging
0.1
0.1
4,000
5000
The Solver
Solved by Jose F. Lopez
Ragsdale 2.21 by Jose
Decision Variables
X1 = Number of workers starting at 12 am
Objective Function
MIN: X1 + X2 + X3 + X4 + X5 + X6
X2 = Number of workers starting at 4 am
X3 = Number of workers starting at 8 am
X4 = Number of workers starting at 12 pm
Constraints
Subject To: X6 + X1  90
X5 = Number of workers starting at 4 pm
X1 + X2  215
X6 = Number of workers starting at 8 pm
X2 + X3  250
X3 + X4  165
X4 + X5  300
Solved by Jose F. Lopez
X5 + X6  125
Xi  0
Solution
THEME PARK SCHEDULING
Employees
Time Period
Scheduled To
Employees
Minimum
Start At Time
Available In
Employees
Period
Time Period
Needed
12 am to 4 am
90
90
90
4 am to 8 am
250
340
215
8 am to 12 pm
0
250
250
12 pm to 4 pm
175
175
165
4 pm to 8 pm
125
300
300
8 pm to 12 am
0
125
125
Total Employees:
640
Solved by Jose F. Lopez
The Solver
Solved by Jose F. Lopez
Ragsdale 2.24 by Rosele

 3-24
Problem: How many of each type of apartment should the developer produce while leasing
5 one bedroom apartments and 8 two bedroom apartments to a silent partner, having a
maximum of 40 units per location, and 40,000 square feet per location?
Decision variables: how many of each type of apartment should the
developer produce?
X1 = efficiencies
X2 = one bedroom apartments
X3 = two bedroom apartments
X4 = three bedroom apartments
Objective Function: How can the developer get the maximum income?
MAX: 350 X1 +450 X2 +550 X3 +750 X4
Constraints:
The developer can build no more than 15 one bedroom apartments, 22 two
bedroom apartment and 10 three bedroom apartments. As well, the silent
partner requires the developer to lease to him 5 one bedroom apartments and 8
two bedroom apartments.
Upper and Lower Bounds:
X1 >0
X2 >5and< 15
X3 >8 and < 22
X4 < 10
Each efficiency requires 500 square feet, each one bedroom apartment requires
700 square feet each two bedroom apartment requires 800 square feet and each
three bedroom apartment requires 1000 square feet. The developer can not
exceed a total of 40,000 square feet in a location.
500 X1 +700 X2 +800 X3 +1,000 X4 < 40,000
Zoning restrictions only allow 40 or less units per location
X1 +5X2 + 8X3 + X4<40
LP Model
MAX:
350 X1 +450 X2 +550 X3 +750 X4
Subject to:
X1>0
X2 >5 and < 15
X3 >8 and < 22
X4 < 10
500 X1 +700 X2 +800 X3 +1,000 X4 < 40,000
X1 +5X2 + 8X3 + X4<40
Solver Parameters
Real Estate Development Project
Efficiencies 1 Bedroom
Number to Make
Units to Rent
0
2 Bedroom 3 Bedroom
8
$350
22
$450
$550
10
$750
Constraints
Sq. Ft Reqd.
Units Required
Total Profit
$23,200
Used Available
500
700
800
1,000
1
1
1
1
33,200
40
40,000
40
Questions C and D
C. The optimal solution is to make:
no – efficiencies
8 – one bedroom apartments
22 – two bedroom apartments
10- three bedroom apartments
D. The number of units to make limits the builders potential income. In this
example the builder maxed out at 40 units while only using 33,200 square
feet.
Problem 3-28, Thamer AbuDiak

















Before

After
Decision Variables:
X11: Newspaper to be used for Newsprint, X12: Newspaper to be used for Packaging.
X21: Mixed Paper to be used for Newsprint, X22: Mixed Paper to be used for Packaging, X23: Mixed Paper to be used for Print Stock.
X31: White Office Paper to be used for Newsprint, X32: White Office Paper to be used for Packaging, X33: White Office Paper to be used for Print Stock.
X41: Cardboard to be used for Newsprint, X42: Cardboard to be used for Packaging.
Objective functions:
MIN (6.5+15)/.85 X11 + (11+15)/.80 X12 + (9.75+16)/.90 X21 + (12.25+16)/.90 X22 + (9.5+16)/.70 X23 + (4.75+19)/.90 X31 + (7.75+19)/.85 X32+ (8.5+19)/.80 X33+
(7.5+17)/.80 X41+ (8.5+17)/.70 X42

Simplifying: 25.29 X11 + 32.5 X12 + 28.61 X21 + 31.39 X22 + 36.43 X23 + 26.39 X31 + 31.47 X32+ 34.38 X33+ 30.63 X41+ 36.43 X42
Constrains:

Answer BOX: (After Recycling):
X11 + X21 + X31
= 500, Newsprint that the company needs to produce.
Newsprint produced from Newspaper:
499
X12 + X22 + X32 + X42 = 600, Packaging that the company needs to produce.
Packaging produced from Newspaper:
10
X13 + X23 + X33
= 300, Print Stock that the company needs to produce.
Newsprint produced from Mixed Paper: 1
X11/.85 + X12/.80
<= 600, Maximum Newspaper available.
Packaging produced from Mixed Paper: 56
X21/.90 + X22/.80 + X23/.70 <= 500, Maximum Mixed Paper available.
Print Stock produced from Mixed Paper: 300
X31/.90 + X32/.85 + X33/.80 <= 300, Maximum White Office Paper to available.
Newsprint: produced from White Office Paper:
0
Packaging produced from White Office Paper:
255
X41/.80 + X42/.70
<= 400, Maximum Cardboard available.
Print Stock produced from White Office Paper :
0
X11, X12, X21, X22, X23, X31, X32, X33, X41, X42
>= 0, Non Negativity Constrain.
Newsprint produced from Mixed Paper: 0
Packaging produced from Mixed Paper: 279
Ragsdale 3.41 by Dave
Eagle's Beach Wear and Gift Shop
Jan
Accounts Receivable 1.50
Planned Payments
1.80
GOAL: Minimize Net Financing Costs
Feb
March
April
May
1.00
1.40
2.30
2.00
1.60
2.20
1.20
0.80
June
1.00
1.20
Beginning Cash Balance = $400,000?
Desired Monthly Balance >=$25,000?
Finance Option
A) Delay Pymt
B) Borrow 75% A/R
C) Short Term Loan
Available for month Loan term Finance Charge
1,2,3,4,5,6
1 month
2.0%
1,2,3,4,5,6
1 month
1.5%
1
6 months
1.0% / month
Defining the Decision Variables
A1, A2, A3, A4, A5, A6 = amount (in $1,000s) financed in option A at the beginning of months
1,2,3,4,5,6, respectively.
B1, B2, B3, B4, B5, B6 = amount (in $1,000s) financed in option B at the beginning of months
1,2,3,4,5,6, respectively.
C1 = amount (in $1,000s) financed in option C at the beginning of month 1.
3.41 cont.


monthly balance = (total amount available @ beginning of month) - (payment) + A/R + (amount
financed)
Interest = 0.5% per month
Ragsdale 3.44
Ragsdale 3.45
Dielman 3.6 by Reynald
Variable
Coefficient
Std Dev
T Stat
PValue
Intercept
2.0336
0.5405
3.76
0.001
EPS
0.3740
0.2395
1.56
0.126
Standard error = 1.84975;
R-Sq = 5.7%;
R-Sq(adj) = 3.4%
Source
DF
Sum of
Squares
Mean
Square
F Stat
P Value
Regression
1
8.345
8.345
2.44
0.126
Error
40
136.864
3.422
Total
41
145.208
a) The same regression equation relating dividends to EPS
a) DIV = 0.3740EPS + 2.0336
Cont.
b) Is there a linear relationship between dividend yield and EPS?
•
•
•
•
Hypothesis: Ho: B1 = 0, H1: B1 ~= 0
Decision Rule: Reject H0 if t > 2.021 or t < -2.021, Do Not Reject if -2.021 <= t <=
2.021
Test: t = 1.56
Decision: Do not reject
c) There is not sufficient evidence to conclude that a linear relationship between
dividend yield and EPS
d) Construct a 95% confidence interval estimate of B1


0.374 +- ( 2.021 )( 0.2395 )
0.374 +- 0.4840
e) Construct a 95% confidence interval estimate of B1


2.0336 +- ( 2.021 )( 0.5405 )
2.0336 +- 1.0924
Dielman 3.24 by Dave
Dave Neal / Group North
Dielman Problem 3.24
 (Dependent Variable): Y = COST is the total cost of the production run.
 (Independent Variable): X = NUMBER is the number of items produced
during that run.
 Regression calculated using Minitab.
 Regression Analysis: COST versus NUMBER
 The regression equation is
COST = 28.3 + 2.15 NUMBER
Scatterplot of COST vs NUMBER
110
COST
100
90
80
70
20
22
24
26
28
NUMBER
30
32
34
36
b. What percentage of the variation in “Y” has been explained by the
regression?
R-Sq = regression sum of squares(SSR)/total sum of squares(SST) =
1813.9/1991.3 = 91.1%
Predictor
Coef SE Coef T P
Constant
28.311 4.083 6.93 0.000
NUMBER 2.1549 0.1437 15.00 0.000
S = 2.84016 R-Sq = 91.1% R-Sq(adj) = 90.7%
Analysis of Variance
Source
Regression
Residual Error
Total
DF
SS
MS
F
P
1 1813.9 1813.9 224.86 0.000
22 177.5
8.1
23 1991.3
c. Are “Y” and “X” linearly related? Conduct a hypothesis test to answer this
question and use a 5% level of significance.
 Hypothesis to be tested: Is the “total cost of a production run” linearly related
to the “number of items produced during that run”?
 The hypotheses are:
H0: 1 = 0 (Cost does not change when number of items produced increases)
Ha: 1 ≠ 0 (Cost does change)
 The decision rule: If the data fits well, Mean Square Regression (MSR) will be
large compared to the Mean Square due to Error (MSE).
Reject H0 if t > 2.074 or if t < -2.074
 The test statistic: F statistic = (MSR) / (MSE).
 Decision: F statistic = 1813.9 / 8.1 = 223.9
The MSR is large relative to the MSE.
T = 15.00 > 2.074 (reject H0).
 Conclusion: There is a significant relationship between project size and cost.
d. Estimate the fixed cost involved in the production
process. Find a point and a 95% confidence interval
estimate.
Fixed cost is equal to the slope of the equation = $28.31
Point estimate = sample 95% confidence mean Y
t24-2 = 2.074
Fixed Cost =
b0+/-tn-2sb0= 28.311+/-(2.074)4.083
= 28.311+/-8.468
95% sure that the fixed cost is between $19.84 and $36.78
e. Estimate the variable cost involved in the production process. Find a
point estimate and a 95% confidence interval estimate.
Calculate using one-way ANOVA.
Variable Cost = 2.1549 x NUMBER.
COST Mean = 78.25
78.25 = (28.311+/-8.468) + Variable Cost
95% sure that the variable cost is between $41.47 and $58.41
One-way ANOVA: COST versus NUMBER
Source
DF
SS
MS
F
P
NUMBER
14 1963.92 140.28 46.05 0.000
Error
9
27.42
3.05
Total
23 1991.33
S = 1.745 R-Sq = 98.62% R-Sq(adj) = 96.48%
Individual 95% CIs For Mean
Level
20
22
23
24
25
26
27
28
29
30
31
32
33
34
35
N Mean
1 74.50
2 78.25
1 81.00
1 80.50
2 82.25
1 81.50
2 83.75
2 85.75
2 86.75
3 91.83
1 96.00
2 98.25
2 101.50
1 103.00
1 109.00
StDev -+---------+---------+---------+-------* (--*--)
1.06
(-*--)
*
(--*---)
*
(--*--)
1.77
(--*-)
*
(--*--)
1.77
(--*-)
1.06
(-*--)
1.77
(-*--)
2.02
(-*)
*
(--*--)
1.77
(-*-)
2.12
(--*-)
*
(--*--)
*
(--*--)
-+---------+---------+---------+-------72
84
96
108