Download - Decision Models Example
-
7/29/2019 Decision Models Example
1/10
GennadiySverzhinskiy
Assignment#2
Problem1
Conclusions and Recommendations
The optimal production schedule for Surfs Up is shown below.
This production schedule yields an annual profit of $21,050, yielding a net profit margin ofapproximately 25%.
Managerial Problem Definition
Surfs Up faces a seasonal demand for their high-end surfboards. Having limited capacity, Surfs Upmeets the high summer demand by producing and storing surplus surfboards during the winter. Inaddition to the manufacturing costs, Surfs Up has two other costs to consider. The first is a start-upcost, which is a flat fee for any month of production. The second is a sales person cost, which is aflat fee for any month of sales. In order to maximize their profits they would like to determine theoptimal production schedule so that the demand is met at the minimal cost. In addition, toaccommodate for any fluctuations in demand, Surfs Up needs to maintain a minimum level of safetystock.
Formulation
Decision variables:
! = ! = (1 = ,0 = )! = (1 = ,0 = )
Jan Feb Mar Apr May June July Aug Sept Oct Nov Dec
Surfboards 0 0 0 70 0 85 100 100 65 0 0 0
0
15
30
45
60
75
90
105
SurfboardsProduced
Monthly Production Schedule
-
7/29/2019 Decision Models Example
2/10
GennadiySverzhinskiy
Assignment#2
Intermediate Variables:
! = ! =
Other Variables:
! = ! =
Maximize:
200! 500! 125! 1000! 5!!!!"!!! Equation1Subject to:
! =
! =
0 ! !
! !
!!!, !"!!" 5
!!! 10
Solution Methodology
The model consists of five sections. The first section contains the revenue and costs associated withthe production and storage of the surfboards on a unit basis as well as the monthly start-up and salesperson costs. The second, third and fourth sections contain the production, sales and demand, andinventory figures. The fifth section contains the summary of the revenues, costs, and total profit.
Excel Solver is used to maximize the total annual profit based on the three decision variables and
the six constraints listed in the Formulationsection above. In order to maintain linearity of the SUiconstraint, an intermediate variableEC
iwas introduced. This variable represents the effective
capacity, equal to 100 or 0 based on the decision to start production (and consequently pay thestart-up fee.) Similar method was used to determine the upper bound for the S
i, via introduction of
theEDivariable.
-
7/29/2019 Decision Models Example
3/10
GennadiySverzhinskiy
Assignment#2
1
2
3
4
5
6
7
8
9
1011
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
B C D E F G H I J K L M N O P Q R
Price per Board $200
Production Cost Per Board $125
Inventory Cost Per Board $5
Set-Up Cost $500
Salesperson Cost $1,000
Sold Effective Sales Prsn Actual
Actual Eff. Cap. Produce Max Cap. Demand Hire Demand Beginning Ending Safety
Jan 0 - 0 100 0 - 0 10 5 5 5
Feb 0 - 0 100 0 - 0 14 5 5 5Mar 0 - 0 100 0 - 0 15 5 5 5
Apr 70 100 1 100 20 20 1 20 5 55 5
May 0 - 0 100 45 45 1 45 55 10 10
June 85 100 1 100 65 65 1 65 10 30 10
July 100 100 1 100 110 110 1 110 30 20 10
Aug 100 100 1 100 110 110 1 110 20 10 10
Sept 65 100 1 100 40 40 1 40 10 35 10
Oct 0 - 0 100 30 30 1 30 35 5 5
Nov 0 - 0 100 0 - 0 15 5 5 5
Dec 0 - 0 100 0 - 0 10 5 5 5
Revenue $84,000
Set-Up Cost 2,500
Production Cost 52,500
Inventory Cost 950
Sales Person Cost 7,000
Total Profit $21,050
Production Inventory
=F9*G9
=M12*L12
=P9
=O13+C13-I13
=F1 * SUM(I9:I20)
=F4 * SUM(F9:F20)
=F2 * SUM(C9:C20)
=F3 * SUM(O9:O20)
=F5 * SUM(L9:L20)
=F1 * SUM(I9:I20) - F4 * SUM(F9:F20) - F2 * SUM(C9:C20) - F3 * SUM(O9:O20) - F5 * SUM(L9:L20)
-
7/29/2019 Decision Models Example
4/10
GennadiySverzhinskiy
Assignment#2
Problem 2
Conclusions and Recommendations
Tim Cook should set the prices for the iPod Touch and iPod Nano at $250.1, and $190.4,
respectively, for a total maximum profit of $1,913,133.2.
Managerial Problem Definition
Based on historical demand of the iPod Touch and iPod Nano, Tim Cook would like to (i)determine the demand functions for each product, and (ii) determine the price points that wouldmaximize combined total revenues for both products. Having two version of a similar productresults in demand curves that are dependent on each other as consumer demand for one productwill not only be affected by that product, but also its substitute.
Formulation
Demand Curves
Decision Variables:
!,!,! = !,!,! =
Other Variables:
!"#$!,
!"#$%,
!"#$,
!"#$%=
!"#$!,!"#$%&' ,!"#$,!"#$%&! =
Minimize:
!"#$!, !"#$% !"#$!, !"#$%&' !!!
!!!
!
!"#$, !"#$%!"#$, !"#$%&'
!!!
!!!
!
Subject to:
-
7/29/2019 Decision Models Example
5/10
GennadiySverzhinskiy
Assignment#2
19,000 ! 27,000
0 ! 15
70 !
30
7,000 ! 17,000
100 ! 0
0 ! 25
Profit
Decision Variables:
!"#$! = !"#$ =
Other Variables:
!"#$! = !"#$ =
Maximize:
!"!"!!"#$! + !"#$!"#$ Subject to:
!"#$!,!"#$ 0
0 !"#$! 400
0 !"#$ 300
-
7/29/2019 Decision Models Example
6/10
GennadiySverzhinskiy
Assignment#2
Solution Methodology
Demand Curves
The demand curve constants we solved for using the Premium Solver, an Excel add-on. Thissection will describe the solution methodology for the iPod Touch demand curve, but similarmethodology was used to solve for the iPod Nano demand curve.
The requirement for upper and lower bounds required by Premium Solver required that an estimateof the bounds be obtained. The estimates of the constraints were obtained as follows. A scatterplot of the iPod Touch was graphed for each iPod Nano price point. Observing the graphicalresults allowed for an approximation of the B1 and C1 constants. Once the B1 and C1 constantswere estimated, A1 was estimated by plugging in the B1 and C1 constants into the demand equationand solving for A1.
After obtaining the approximations for the intervals, Premium Solver produced the variables for allthree constants using the Evolutionary method. The results were further refined by using the GRG
non-linear method.
4,000
6,000
8,000
10,000
12,000
14,000
16,000
$0.0 $100.0 $200.0 $300.0 $400.0
Demand
Price
DemandofiPodTouch
$100.0
$150.0
$200.0
-
7/29/2019 Decision Models Example
7/10
GennadiySverzhinskiy
Assignment#2
Profit
Premium Solver was also used to solve for the optimal price points using the objective function andconstraints described above using the Evolutionary method. The results were further refined usingthe GRG non-linear method.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
A B C D E F G
Constants Lower Bound Touch Upper Bound
A1 19,000.0 20,279.3 27,000.0
B1 0.0 1.5 15.0
C1 (70.0) (51.6) (30.0)
iPod Nano iPod Touch Given Formula Difference Difference2
$100.0 $100.0 15,166 15,274 (108) 11,663
100.0 150.0 12,266 12,694 (428) 183,591
100.0 200.0 10,875 10,115 760 577,668
$150.0 200.0 10,222 10,192 30 910
150.0 250.0 7,771 7,612 159 25,184
150.0 300.0 4,410 5,033 (623) 387,862
$200.0 300.0 5,320 5,110 210 44,244
Sum of Difference 2 1,231,123
Price Demand (in thousands)
=A_2 + B_2 * $A9 + C_2 * $B9
=(C13-D13)
=E14^2
=SUM(F9:F15)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
A B C D E F G
Constants Lower Bound Nano Upper Bound
A2 7,000.0 10,313.5 17,000.0
B2 (100.0) (32.9) 0.0
C2 0.0 2.8 25.0
iPod Nano iPod Touch Given Formula Difference Difference2
$100.0 $100.0 7,311 7,311 0 0
100.0 150.0 7,387 7,453 (66) 4,296
100.0 200.0 7,499 7,594 (95) 9,063
$150.0 200.0 5,992 5,951 41 1,660
150.0 250.0 6,398 6,093 305 93,077
150.0 300.0 6,210 6,235 (25) 604
$200.0 300.0 4,431 4,592 (161) 25,802
Sum of Difference2
134,501
Price Demand (in thousands)
=A_1 + B_1*$A9 + C_1*$B9
=(C13-D13)
=E14^2
=SUM(F9:F15)
-
7/29/2019 Decision Models Example
8/10
GennadiySverzhinskiy
Assignment#2
1
3
4
5
6
7
8
9
10
11
12
13
14
15
1617
18
19
A B C D E F
iPod Touch iPod Nano
Price $250.1 $190.4
Max $400.0 $300.0
Cost $93.0 $38.0
Margin $157.1 $152.4
iPod Touch iPod Nano Total
Profit $1,204,854.4 $ 726,278.8 $1,931,133.2
iPod Touch iPod Nano
Demand 7,671 4,766
Min 0 0
iPod Touch iPod NanoA 20,279.3 10,313.5
B 1.5 (32.9)
C (51.6) 2.8
Constants
=(B3-B5)*B12 =(C3-C5)*C12
=SUM(B9:C9)
-
7/29/2019 Decision Models Example
9/10
GennadiySverzhinskiy
Assignment#2
Problem 3
Conclusions and Recommendations
The minimum expansion cost is $3.115 million. The annual expenditures and capacities are
summarized in the tables below.Year 1 Year 2 Year 3 Year 4 Year 5 TotalCosts Incurred (in
$1,000s) $950.0 $250.0 $670.0 $550.0 $695.0 $3,115.0
Capacity
Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Added 100 10 100 100 125
Current (EOY) 750 850 860 960 1060 1185
Minimum 780 860 950 1060 1180
Managerial Problem Definition
A power company is interested in increasing its generating capacity to meet expected demand in itsgrowing service area at the lowest possible cost. Their total generating capacity must meet theminimum required needs.
Formulation
Decision variables:
!,! = where, i = purchase year and j = generator size
Minimize:
(!,! ,!,!
!!!
!!!
)
!!!
!!!
Subject to:
!,! =
-
7/29/2019 Decision Models Example
10/10
GennadiySverzhinskiy
Assignment#2
! !"#!
Solution Methodology
The model consists of three sections. The first section contains the decision variables, Pi,j. Thesecond section contains the costs associated with purchasing a generator of a specific capacity in agiven year as well as the costs incurred in each year. The third section contains the added, total andminimum generating capacities.
Using Excels Solver, the decision variables are obtained to meet the minimum capacity in a givenyear, while simultaneously using those same decision variables to do so at the lowest cost. Cell I15contains the total coast of expansion.
1
2
34
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
A B C D E F G H I J K
Generator Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Size (MW)
10 0 1 0 0 025 0 0 0 0 1
50 0 0 0 0 0
100 1 0 1 1 1
Generator
Size (MW) Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
10 $300.0 $250.0 $200.0 $170.0 $145.0
25 460.0 375.0 350.0 280.0 235.0
50 670.0 558.0 465.0 380.0 320.0
100 950.0 790.0 670.0 550.0 460.0
Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Total
Costs Incurred (in $1,000s) $950.0 $250.0 $670.0 $550.0 $695.0 $3,115.0
Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Added 100 10 100 100 125
Current (EOY) 750 850 860 960 1060 1185
Minimum 780 860 950 1060 1180
Cost of Generator (in $1,000s) in
Capacity
=SUM(C16:G16)
=SUMPRODUCT(C3:C6,C10:C13)
=SUMPRODUCT(G3:G6,$A$3:$A$6)
=F21+G20