> \pDaniel H. Fylstra B Finance Budget1 Budget2 Invent1 Invent2 LockboxOpen_scenchg_count _scenchg_count_scenchg_count_scenchg_count*) _scenchg1:
) _scenchg1:) _scenchg1:
) _scenchg1:&*
_scenchg10:*
_scenchg11:*
_scenchg12:*
_scenchg13:*
_scenchg14:*
_scenchg15:*
_scenchg16:*
_scenchg17:*
_scenchg18:*
_scenchg19:) _scenchg2:
) _scenchg2:
) _scenchg2:) _scenchg2:*
_scenchg20:*
_scenchg21:*
_scenchg22:*
_scenchg23:*
_scenchg24:*
_scenchg25:*
_scenchg26:*
_scenchg27:*
_scenchg28:*
_scenchg29:) _scenchg3:
) _scenchg3:) _scenchg3:) _scenchg3:*
_scenchg30:*
_scenchg31:*
_scenchg32:*
_scenchg33:*
_scenchg34:*
_scenchg35:*
_scenchg36:*
_scenchg37:*
_scenchg38:*
_scenchg39:) _scenchg4:
) _scenchg4:) _scenchg4:) _scenchg4:*
_scenchg40:*
_scenchg41:*
_scenchg42:) _scenchg5:
) _scenchg5:) _scenchg6:
) _scenchg6:) _scenchg7:) _scenchg7:) _scenchg8:) _scenchg8:) _scenchg9:) _scenchg9:anscount.Assignments;4Assignments_total;/Available_money: /Available_space: /Available_space:binary0
Chicago_boxes;0Chicago_decision:0Cost_of_products:/Dallas_boxes;/Dallas_decision:)Demand;,Holding_cost:integer7Investment_decisions;.Investments;+LA_boxes;+LA_decision:4Lockbox_decisions;lssolver_estlssolver_estlssolver_estlssolver_estlssolver_itrdlssolver_itrdlssolver_itrdlssolver_itrdlssolver_neglssolver_neglssolver_neglssolver_neg# lssolver_pivư># lssolver_pivư># lssolver_pivư># lssolver_pivư># lssolver_preư># lssolver_preư># lssolver_preư># lssolver_preư># lssolver_redư># lssolver_redư># lssolver_redư># lssolver_redư>lssolver_replssolver_replssolver_replssolver_replssolver_scllssolver_scllssolver_scllssolver_scllssolver_sholssolver_sholssolver_sholssolver_sho# lssolver_solC6?# lssolver_solC6?# lssolver_solC6?# lssolver_solC6?lssolver_timdlssolver_timdlssolver_timdlssolver_timdlssolver_tollssolver_tol# lssolver_tol?# lssolver_tol?.Miami_boxes;.Miami_decision:/Monthly_cash;2Monthly_surplus;2Monthly_surplus;1New_York_boxes;1New_York_decision:0
One_month_CDs;
.plant_decision:qpsolver_itrdqpsolver_itrdqpsolver_itrdqpsolver_itrdqpsolver_linqpsolver_linqpsolver_linqpsolver_linqpsolver_negqpsolver_negqpsolver_negqpsolver_neg# qpsolver_pivư># qpsolver_pivư># qpsolver_pivư># qpsolver_pivư># qpsolver_preư># qpsolver_preư># qpsolver_preư># qpsolver_preư># qpsolver_redư># qpsolver_redư># qpsolver_redư># qpsolver_redư>qpsolver_repqpsolver_repqpsolver_repqpsolver_repqpsolver_sclqpsolver_sclqpsolver_sclqpsolver_sclqpsolver_shoqpsolver_shoqpsolver_shoqpsolver_shoqpsolver_timdqpsolver_timdqpsolver_timdqpsolver_timdqpsolver_tolqpsolver_tol# qpsolver_tol?# qpsolver_tol?
Quantities;

Quantities;jQscen_change)N;
::,:?.scen_change;.scen_change;
+scen_change:&!
scen_date1aq@!
scen_date2Keq@
scen_name1test
scen_name2test2scen_num.scen_result;+scen_result:+scen_result:.scen_result;+
scen_user1Daniel H. Fylstra+
scen_user2Daniel H. Fylstra'scen_value1`Y0scen_value2`)0Z*?????????0
Seattle_boxes;0Seattle_decision:, Shipments;6Shipped_from_plants;8Shipped_to_warehouses;

Six_month_CDs:
solver_adj;
solver_adj;i
Qsolver_adj)N;
::,:?
solver_adj;
solver_adj;
F
.solver_adj)+;;C
+solver_adj)(;:!
solver_cvgC6?!
solver_cvgC6?!
solver_cvgC6?
solver_drv
solver_drv
solver_drv
solver_drv
solver_drv
solver_drv
solver_drv
solver_eng
solver_eng
solver_eng
solver_eng
solver_eng
solver_eng
solver_eng
solver_est
solver_est
solver_est
solver_est
solver_est
solver_est
solver_est
solver_ibd
solver_ibd
solver_ibd
solver_ibd
solver_ibd
solver_ibd
solver_ibd
solver_itrd
solver_itrd
solver_itr
solver_itrd
solver_itrd
solver_itr
solver_itrd.solver_lhs1;.solver_lhs1;.solver_lhs1;+solver_lhs1:+solver_lhs1:.solver_lhs1;.solver_lhs1;/solver_lhs10;/solver_lhs11;/solver_lhs12;/solver_lhs13;/solver_lhs14;.solver_lhs2;.solver_lhs2;.solver_lhs2;
+solver_lhs2:.solver_lhs2;
.solver_lhs2;.solver_lhs2;
.solver_lhs3;.solver_lhs3;+solver_lhs3:+solver_lhs3:.solver_lhs3;+solver_lhs3:.solver_lhs4;+solver_lhs4:.solver_lhs4;
.solver_lhs4;+solver_lhs4:+solver_lhs5:.solver_lhs5;.solver_lhs5;+solver_lhs6:.solver_lhs6;.solver_lhs6;+solver_lhs7:.solver_lhs7;+solver_lhs8:.solver_lhs8;+solver_lhs9:.solver_lhs9;
solver_lin
solver_lin
solver_lin
solver_lin
solver_lin
solver_lin
solver_lin
solver_lva
solver_lva
solver_mip
solver_mip
solver_mip
solver_mip
solver_mip
solver_mip
solver_mip
solver_mni!
solver_mrt333333?
solver_nam
solver_neg
solver_neg
solver_neg
solver_neg
solver_neg
solver_neg
solver_neg
solver_nod
solver_nod
solver_nod
solver_nod
solver_nod
solver_nod
solver_nod
solver_num
solver_num
solver_num
solver_num
solver_num
solver_num
solver_num
solver_nwt
solver_nwt
solver_nwt
solver_nwt
solver_nwt
solver_nwt
solver_nwt
solver_ofx
solver_ofx
solver_ofx
solver_ofx
solver_ofx
solver_ofx
solver_ofx*
solver_opt:*
solver_opt:*
solver_opt:*
solver_opt:*
solver_opt:*
solver_opt:+*
solver_opt:!
solver_pivh㈵>!
solver_pivư>!
solver_pivư>!
solver_pivư>!
solver_pivư>!
solver_preư>!
solver_preư>!
solver_pre:0yE>!
solver_preư>!
solver_preư>!
solver_pre:0yE>!
solver_preư>
solver_pro
solver_pro
solver_pro
solver_pro
solver_pro
solver_pro
solver_pro
solver_rbv!
solver_redh㈵>!
solver_redư>!
solver_redư>!
solver_redMbP?!
solver_redư>solver_rel1solver_rel1solver_rel1solver_rel1solver_rel1solver_rel1solver_rel1solver_rel10solver_rel11solver_rel12solver_rel13solver_rel14solver_rel2solver_rel2solver_rel2solver_rel2solver_rel2solver_rel2solver_rel2solver_rel3solver_rel3solver_rel3solver_rel3solver_rel3solver_rel3solver_rel4solver_rel4solver_rel4solver_rel4solver_rel4solver_rel5solver_rel5solver_rel5solver_rel6solver_rel6solver_rel6solver_rel7solver_rel7solver_rel8solver_rel8solver_rel9solver_rel9
solver_reo
solver_reo
solver_reo
solver_reo
solver_reo
solver_reo
solver_reo
solver_rep
solver_rep
solver_rep
solver_rep
solver_rep
solver_rep
solver_repsolver_rhs1(solver_rhs1#Fx." solver_rhs1j@(solver_rhs1#E(solver_rhs1#C,(solver_rhs1#Fx.(solver_rhs1# ܥ)solver_rhs10#F)solver_rhs11#Fx.)solver_rhs12#K\0)solver_rhs13#+/solver_rhs14;solver_rhs2solver_rhs2(solver_rhs2#N1(solver_rhs2#E(solver_rhs2#N1solver_rhs2(solver_rhs2#L0solver_rhs3solver_rhs3(solver_rhs3#N1+solver_rhs3: (solver_rhs3#H/(solver_rhs3#Fx.(solver_rhs4#N1(solver_rhs4#N1(solver_rhs4#N1(solver_rhs4#K\0(solver_rhs4#Fx.(solver_rhs5#N1(solver_rhs5#R3.solver_rhs5;(solver_rhs6#N1(solver_rhs6#Fx.solver_rhs6(solver_rhs7#N1(solver_rhs7#+(solver_rhs8#N1(solver_rhs8#Fsolver_rhs9(solver_rhs9#t\
solver_rlx
solver_rlx
solver_rlx
solver_rlx
solver_rlx
solver_rlx
solver_rlx
solver_scl
solver_scl
solver_scl
solver_scl
solver_scl
solver_scl
solver_scl
solver_sho
solver_sho
solver_sho
solver_sho
solver_sho
solver_sho
solver_sho!
solver_solC6?!
solver_solC6?!
solver_solC6?!
solver_solC6?!
solver_solC6?
solver_sszd
solver_timd
solver_timd
solver_timd
solver_timd
solver_timd
solver_tim
solver_timd
solver_tmp
solver_tmp
solver_tmp'
solver_tmp#E1
solver_tmp9D\'
solver_tmp#F!
solver_tol?
solver_tol!
solver_tol?!
solver_tol?!
solver_tol?!
solver_tol?!
solver_tol?
solver_typ
solver_typ
solver_typ
solver_typ
solver_typ
solver_typ
solver_typ
solver_val
solver_val
solver_val
solver_val
solver_val
solver_val
solver_val
solver_ver
solver_ver
solver_ver
solver_ver
solver_ver
solver_ver
solver_ver!
solver_ver?*
Space_used:*
Space_used:sssolver_drvsssolver_drvsssolver_drvsssolver_drvsssolver_drvsssolver_drvsssolver_drvsssolver_estsssolver_estsssolver_estsssolver_estsssolver_estsssolver_estsssolver_estsssolver_itrdsssolver_itrdsssolver_itrdsssolver_itrdsssolver_itrdsssolver_itrsssolver_itrdsssolver_linsssolver_linsssolver_linsssolver_linsssolver_linsssolver_linsssolver_linsssolver_negsssolver_negsssolver_negsssolver_negsssolver_negsssolver_negsssolver_negsssolver_nwtsssolver_nwtsssolver_nwtsssolver_nwtsssolver_nwtsssolver_nwtsssolver_nwt# sssolver_preư># sssolver_preư># sssolver_preư># sssolver_preư># sssolver_preư># sssolver_pre:0yE># sssolver_preư>sssolver_repsssolver_repsssolver_repsssolver_repsssolver_repsssolver_repsssolver_repsssolver_sclsssolver_sclsssolver_sclsssolver_sclsssolver_sclsssolver_sclsssolver_sclsssolver_shosssolver_shosssolver_shosssolver_shosssolver_shosssolver_shosssolver_sho# sssolver_solC6?# sssolver_solC6?# sssolver_solC6?sssolver_timdsssolver_timdsssolver_timdsssolver_timdsssolver_timdsssolver_timsssolver_timd# sssolver_tol?sssolver_tol# sssolver_tol?# sssolver_tol?# sssolver_tol?# sssolver_tol?# sssolver_tol?)Supply;/Three_month_CD1:/Three_month_CD2:E(Three_month_CDs)%::*
Total_cost:*
Total_Cost:+*
Total_cost:.Total_interest:) Total_NPV:) Total_NPV:=h\+8X@"1Arial1Arial1Arial1Arial1Helv1Helv1Helv1Helv1
MS Sans Serif1
MS Sans Serif1
MS Sans Serif1
MS Sans Serif1
MS Sans Serif1Helv1Helv1
MS Sans Serif1
MS Sans Serif1
MS Sans Serif"$"#,##0_);\("$"#,##0\)"$"#,##0_);[Red]\("$"#,##0\) "$"#,##0.00_);\("$"#,##0.00\)%""$"#,##0.00_);[Red]\("$"#,##0.00\)5*2_("$"* #,##0_);_("$"* \(#,##0\);_("$"* ""_);_(@_),))_(* #,##0_);_(* \(#,##0\);_(* ""_);_(@_)=,:_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* ""??_);_(@_)4+1_(* #,##0.00_);_(* \(#,##0.00\);_(* ""??_);_(@_)0.0% + ) , * ! ! ! ! !t  p !` E!t ( p !` @!t D( p D!` D@!h $!h $! $!h E% ! (" # !h @ ! (" m!  "  "  E#" h  (   @ x (" @!h# @#(# @## @#@#  @!  @!  @!E!!h @%(# @%!h @%@ !h $" $! $  $  x $" @+m+
! @%(! @%#8 @%  @%! @%" @%" @#m#
!x  ` ` E `
!x ( ` ` @
!x D( ` D
! D ` D@
!8 !8  E
!8 (
"8
"8 @
!8 @( $ @ $ @@
(  (
"8
( @(!8 
#8 ( "0 "0 @
#8 @( "0 @ "0 @@ "0 " "0 " "0 E# "0 m! "0 ( "0 @ "0 h "0 @#( "0 @# "0 @#@ "0 @!h @
#8 @( "0 @#" "0 @#" "0 @#E# @@
"8
"8
"8 E $ $ @
$ @+m+
q q
y!x  q ` q ` E
y!x D( q ` D q ` D@ q 
y*8
q*8 q E
q ( ( q"4 q"0 q"4 q @
q ( @( q"4 @ q"0 @ q @@
y!8  q q ( q
q"8
y!8 q m# q#4 q h q @#h q @( q @
q#8 @ q $ @+m+
q $ @!m!
y!
!x  ` ` E
!x D( ` D ` D@ 
*8
*8 E
#8 ( "4 "0 @
( @( @
#8 @ $ @ @@
!8 
"8 (
"8
"8 "0 m# #4 "0 h "0 @#h
!8 @( $ @!m!
#8 @ $ @+m+
$ @!m!
! a! a a! a!t $a p $a p E% a!t Q%(a p Q%a p Q%@ a p a" a"x a! a x Ea! (a"t a"p a"t a!t a p @a" a! Q(a"t Qa"p Qa"t Qa!t Qa p Q@a# a" @+m+
a! $a"x $a" E% a! (a t a t @ &a t a t a t @ a t @#"a t @#"a t @#E#&a t @ a t @#m#a t a! @%(a t @! Y! Q
Y!x  Q ` Q ` E
Y!x ( Q ` Q ` @
Y!x D( Q ` D Q!t D Q ` D@Y!8  Q Q E Q (
Q"8
Q"8 @
Q ( ( Q $ Q $ @
Q ( @( Q $ @ Q $ @@ Q $ @#" Q $ @#" Q $ @#E# Q Q @Y!8 (
Y"8 Q $ 
Q $ Q $ E Q $ m
Q $ (
Q $ @ Q $ h
Q $ @(
Q $ @ Q $ @@ Q $ @h
Q ( (
Q"< Q $ @+m+
Q @@A A
I!x  A ` A ` E
I!x ( A ` A ` @
I!x D( A ` D A!t D A ` D@I!8  A A E A (
A"8
A"8 @
A ( ( A $ A $ @
A ( @( A $ @ A $ @@ A $ @#" A $ @#" A $ @#E# A A @I!8 (
I"8 A $ 
A $ A $ E A $ m
A $ (
A $ @ A $ h
A $ @(
A $ @ A $ @@ A $ @h
A ( (
A"< A $ @+m+
A @@a! a! a! Q h Q h Q h EQ h @Q h DQ h D@Q h Q h Q h (Q h Q h (Q h D(Q h DA h A h A h EA h (A h A h @A h (A h D(A h DA h D@a! a! a! Ea! (a! a! a! @a! (a! Q(a! Qa! Qa! Q@Q h EQ h @Q h D@A h A h A h A h EA h (A h @A h D(A h DA h D@! h h h E h ( h @ h D( h D h D@ h  h ( p E p ( p @ p D( p D@ x ( x a t @!E!a t @! x x (i! (Normal_BUDGET1Normal_BUDGET2Normal_FINANCENormal_INVENT1Normal_INVENT2Normal_LOCKBOXNormal_OPEN83ffff̙3f3fff3f3f33333f33333SummaryFinanceBudget1Budget2
Invent1(Invent2BLockboxcuOpen
#
dMbP?_*+%MHP LaserJet 5Pp@g
XX0sT0@MSUDHP LaserJet 5P<d
"dXX?? Finance Budget1 Budget2 Invent1 Invent2 LockboxOpenU
T0x@x@
x0
0}p0l0 dFinance ExampleseeeeeeedeeeeeeeUeMOn each example worksheet, read the comments at the bottom of the sheet, theneeeeeee\eTclick Tools Solver... to examine the decision variables, constraints, and objective.eeeeeee=e5To find the optimal solution, click the Solve button.eeeeeeedeeeeeeeVNThis workbook contains five example models from the area of corporate finance:,$* A working capital management model5 * A capital budgeting model (in two versions) 5
* An inventory policy model (in two versions)
"* A lockbox location model'* A plant opening/closing model
XPIn the working capital management model, Finance, we look at how to invest money[Sin 1month, 3month and 6month CDs, while meeting cash requirements in each month.PHIn the capital budgeting models, Budget1 and Budget2, a company wants toTLmaximize the Net Present Value of a combination of investment opportunities.PHIn the inventory policy models, Invent1 and Invent2, we compare the EOQ UM(Economic Order Quantity) with the optimal solution determined by the Solver.UMIn the lockbox location model, Lockbox, a firm needs to decide where to open VNlockboxes to minimize the 'float', i.e., the lost interest, due to mail delay.^VIn the capacity planning model, Open, we determine whether to open or close facilities&such as plants and warehouses.@
D4qxYrHQQ>CtwlplqqrzB=h\+8X>
9HPk
dMbP?_*+%&fPage &pMHP LaserJet IIInd war
DLg[<(,EVICECAPABILITIESd
"d?? Budget1 Budget2 Invent1 Invent2 LockboxOpenU}
}I }}
9T0 Z@0 0@0
x0
000000 }p0lTT$ Working Capital Management.XPDetermine how to invest excess cash in 1month, 3month and 6month CDs so as to^Vmaximize interest income while meeting company cash requirements (plus safety margin).
YieldTerm
PricePurchase CDs in months: 1mo CDs:??@@1, 2, 3, 4, 5 and 6Interest 3mo CDs:@@p@1 and 4 Earned: 6mo CDs:"@@@ 1
Total!% Month: Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 End
Init Cash:~
jA
A
L
`rA
A
A
@wA
L
A
Matur CDs: D
D D
D)D
DDD D
D D
D3D
DDDDD Interest:$D
DD$D
DD3D
DDDDD$D
DD$D
DDB,D
DDDDDDDD
1mo CDs:*
3mo CDs:~
~
6mo CDs:~
Cash Uses:0O@@j@LL@ End Cash: A
#&PmeBL`rA
A
A
@wA
L
A
A
f~ProblemyqA company wants to invest excess cash in 1month, 3month and 6month Certificates of Deposit (CDs). The companyxhas expected uses of cash in the next 6 months, and it wants to make sure that the principal and interest from maturing zrCDs meet the requirements for cash plus a safety margin for each month. For simplicity we assume that 3month CDs xcan only be bought at the start of months 1 and 4, and 6month CDs can only be bought in month 1. Initial cash availableyqis $400,000. How many and what kind of CDs should the company buy in order to maximize the earned interest, and 6.meet the safety margin of $100,000 each month?SolutionTLThe characteristics of the 3 different CDs are given in cells A5 through F9.xp1) The variables are the number of CDs to buy in each month. The variable cells are given names One_month_CDs, _WThree_month_CD1 and Three_Month_CD2, and Six_month_CDs. There are 6+2+1 = 9 variables.Dl(rxsnb~<MO9J+R,p T0!"#$%&'()*x+,0./012304567} 8p }2) The constraints are the limitations on the formulas in this model. First, there is the safety margin requirement for each !month. This gives!""Monthly_cash >= 100000"#}Then there are the logical constraints on the number of CDs to be bought. It is not possible to buy half or other fractions, #t$lor negative amounts of CDs. We can rule out negative amounts with the Assume NonNegative option. This gives$%%One_month_CDs = integer%&!&Three_month_CD1 = integer&'!'Three_month_CD2 = integer'((Six_month_CDs = integer()y3) The objective is to maximize earned interest. This is calculated by multiplying the number of CDs bought of each kind )R*Jby the interest earned for each CD. This is given the name Total_interest.*+,Remarks,tThis is a good example of how the solver can help you make intelligent decisions in investments. Before solving the W.Omodel with the Solver try to find a solution by hand. What interest is earned? ./0The time required by the solver to solve this model can be considerable. Integer problems are very difficult to solve. In a 0~1vmodel like this it would be possible to change the variables to the amount of money to be invested. This would give a 1{2s'normal' problem. We chose this form since often it is not possible to invest an arbitrary amount of money in a CD.23~4vIn some situations it is not desirable to use integer constraints. When an expected solution of a model yields a value45ythat is 2034.86, for example, it is safe to assume we can round this number to 2035. If the value is 0.34 however, it is 5}6unot safe to assume we can round this number. In each model, you have to tradeoff precision vs solution time to make a6:72decision whether or not to use integer variables. 765BCEECn+sV=h\+8X>
????mT}ëwtV4EtVhT0(1q070(hT(Aq0Fq0(mhT00}H]l0(}Dl00}@@l92qq
"
ID
dMbP?_*+%&FPage &P"d?? Finance Budget2 Invent1 Invent2 LockboxOpenU}7}$7}7} 7}
I7}7
IT0 Z@0
T0
K@0T00Z@T 0}p <0@l 00!6Capital Budgeting 1h8`A company wants to maximize the combined Net Present Value (NPV) of a maximum of 6 opportunities999999999:
p;hthat require up to 6 yearly investments. In each year there is only a limited amount of money available.<<<<<<<<<=
E>=All amounts are give in millions of dollars. Interest rate is????~
@@????A
<B4Expected Investment Cash Flows and Net Present ValueCCCCCDEFOpp. 1FOpp. 2FOpp. 3FOpp. 4FOpp. 5GOpp. 6HYear 1*II"I(II4J2HYear 2*III$II@J. HYear 3* I0I@II4I@J$
HYear 4*
I(@I@II$I@J$HYear 5*I,@I@I9@I.I@JA@HYear 6*I.@I@I.@IR@I@JA@
KNPV
LbǺ @
DB
L@
LQۘ?
LG@
LKVא@
M#w>@
BPercentage to invest CCCCCCCCCCDE*NOOOOPQQQQREQQQQQQQQQQRS Cash FlowQQQQQQQ
FTotal Q
TBudgetGSurplusHYear 1U$ L@LVVVVWIIz. I~
IF@XF@ LLHYear 2YIIIIZII I~
I>@[>@HYear 3YIIIIZII I~
I4@[4@HYear 4YIIIIZII I~
I[HYear 5YIIIIZII I~
I[HYear 6\]]]]^II I~
I_EIIIIIIIIIIR`RevenueIIIIIII
aTotal IIRKNPVL LLLLLLLL!b%z. LLctProblemuuuuuuuuuuvzwrA company has six different opportunities to invest money. Each opportunity requires a certain investment over a xxxxxxxxxxyD3l}Vv@@@@@@AL"ms,,,,,"L3 T0!"#$%&'()*+,T0./T01T2304567}8p90:l;<0=>?t wlperiod of 6 years or less. The company wants to invest in those opportunities that maximize the combined Net xxxxxxxxxxy\!wTPresent Value. It also has an investment budget that needs to be met for each year. !xxxxxxxxxxy"wtWe assume that it is possible to invest partially in an opportunity. For instance, if the company decides to invest "xxxxxxxxxxyq#wi50% of the required amount in an opportunity, the return will also be 50%. How should the company invest?#xxxxxxxxxxy$wxxxxxxxxxxy%zSolution%xxxxxxxxxxy}&wu1) The variables are the cells in the worksheet that we want to change. In this model, they are the percentages that &xxxxxxxxxxyz'wrare invested in each opportunity. By changing these values, the Net Present Value of the combined investments also'xxxxxxxxxxy^(wVchanges. The variables in this model are given the name investments in the worksheet.(xxxxxxxxxxy)wt2) The constraints are the limitations we have when changing the variables. It is not possible to invest more than )xxxxxxxxxxy+*w#100% in an opportunity. This gives:*xxxxxxxxxxy+w+xinvestments <=1+xxxxxxxxxyn,wfWe can tell the Solver not to invest a negative amount of money, using the Assume NonNegative option.,xxxxxxxxxxyLwDIt is a common mistake to forget these kinds of logical constraints.xxxxxxxxxxyy.wqThe last constraint is given by the fact that the company has a budget. The sum of the expected cash flow of the .xxxxxxxxxxyC/w;investments and the budget must be positive. This leads to:/xxxxxxxxxxy0w0xMonthly_surplus >= 00xxxxxxxxxys1wk3) The objective is to maximize the NPV which is given the name Total_NPV on the worksheet. This amount is 1xxxxxxxxxxys2wkcalculated by adding the NPV's of each investment, multiplied by the percentages that are invested in them.2xxxxxxxxxxy3wxxxxxxxxxxy4zRemarks4xxxxxxxxxxy~5wvWhen creating this model we start out by putting the characteristics of the 6 opportunities on the worksheet. In this 5xxxxxxxxxxy6wwworksheet we decided to lay out the opportunities (horizontally) vs. the years (vertically). It would be perfectly fine6xxxxxxxxxxyL7wDto switch this around and have different colums for different years.7xxxxxxxxxxy8wxxxxxxxxxxyy9wqWe then assign cells to the variables we are using. In this case we used 6 cells for 6 different investments and 9xxxxxxxxxxyw:wodefined them as investments in the worksheet. When dealing with a linear model as this one, it does not matter :xxxxxxxxxxyx;wpwhat the initial values of these variables are. In nonlinear models, however, it is very important to give the ;xxxxxxxxxxyz<wrvariables an initial value that you expect to be close to the solution. Therefor, it is good practice to give the <xxxxxxxxxxyN=wFvariables reasonable starting values. In this model, 50% for instance.=xxxxxxxxxxy>wxxxxxxxxxxyx?wpAfter the variables have been created, we must put the constraints on the worksheet. Normally, no extra work is ?xxxxxxxxxxyD`l"4OCpgH"3p"r"@T0ABCDEFG Hu@wmnecessary for logical constraints. We simply tell the solver to keep the investments between 0 and 100% when @xxxxxxxxxxywAwodefining the model. The other constraints do require some work. In this model we want the sum of the expected Axxxxxxxxxxy}Bwucashflow of the investments and the yearly budget to be positive. The easiest way to do this is to create cells thatBxxxxxxxxxxyCwycalculate this sum and tell the Solver that the values of these cells must be positive. In the worksheet these cells are CxxxxxxxxxxysDwkdefined as Monthly_surplus. Finally, we create a cell that calculates the combined NPV of all investments. DxxxxxxxxxxyEwxxxxxxxxxxyFwwYou may notice that we also created cells that calculate the NPV for each individual investment. This is not strictly FxxxxxxxxxxyxG{pnecessary, but it makes the model easier to read and understand, and it provides an easy way of calculating NPV.G}&"=h\+8X>
????mT}ëwtV4EtV?hT0(1q070(hT(Aq0Fq0(mhT0 }H]l0(0}Dl0 }@@lI2qq
"
;
dMbP?_*+%&FPage &P"d?? Finance Budget1 Invent1 Invent2 LockboxOpenU} }$ } } }
I }
;T0 Z@0
T0
K@0T00Z@ 0}p <0@l 00!Capital Budgeting 2h
`A company wants to maximize the combined Net Present Value (NPV) of a maximum of 6 opportunities
p
hthat require up to 6 yearly investments. In each year there is only a limited amount of money available.
E=All amounts are give in millions of dollars. Interest rate is~
@
<4Expected Investment Cash Flows and Net Present ValueOpp. 1Opp. 2Opp. 3Opp. 4Opp. 5Opp. 6Year 1*"(42Year 2*$@. Year 3* 0@4@$
Year 4*
(@@$@$Year 5*,@@9@.@A@Year 6*.@@.@R@@A@
NPV
bǺ @
DB
@
Qۘ?
G@
KVא@
#w>@
Decision to invest * !!!!"####$##########$% Cash Flow#######
Total #
&BudgetSurplusYear 1'$ L@L(((()z. ~
F@*F@ LLYear 2+, ~
>@>@Year 3+, ~
4@4@Year 4+, ~
Year 5+, ~
Year 6.////0 ~
1$2Revenue
3Total $NPV LL!4%z. 5mProblemnhhhhhhhhhiqoiIn this model we extend the problem we solved in Budget1. Once again, a company needs to make a decision pgggggggggjD(l}Vv@@@@@@?L"ms,,,,,"L3 T0!"#$%&'()*+,T0./T012304567}8p90 :lv onhow to invest in 6 different opportunities. This time however, the company can only go with an investment 100% pgggggggggj5!oor ignore the opportunity and thus invest 0%.!pgggggggggj"opgggggggggj#qSolution#pgggggggggjt$olThe solution is almost identical to the one in Budget1. The variables and objective have remained the same. $pgggggggggjv%onThe only difference is in the logical constraints. In Budget1 the investments needed to be between 0 and 100%.%pgggggggggj;&o3Now they are required to be 0 or 100% (or 0 or 1). &pgggggggggjw'ooThese kinds of (binary) decision variables often occur in models. They come up when decisions have to be made, 'pgggggggggjx(opsuch as: open or closed, yes or no, buy or not buy, etc. The Solver allows you to use these kind of variables by(pgggggggggja)oYentering a constraint that says the variables must be binary integer. In Budget1 we used:)pgggggggggj*o*pinvestments <= 1 and*gggggggggj+o<+p4investments >= 0 via the Assume NonNegative option.+gggggggggjd,o\In place of these constraints, we can tell the Solver to use binary integer variables, with:,pgggggggggjo%pInvestment_decisions = binarygggggggggj:.o2This will force the variables to be either 0 or 1..pgggggggggj/opgggggggggj0qRemarks0pgggggggggju1omBy making the variables 0 or 1, there is less flexibility in the investments. In mathematical terms, we have 1pgggggggggjv2ontightened the constraints. Because of this we can expect our goal, the total NPV, to be less than in Budget1. 2pgggggggggjC3o;Compare the 2 models and make sure this is indeed the case.3pgggggggggj4opgggggggggjt5olYou might be surprised by the investment decisions of this model compared to the solution of Budget1. In the5pgggggggggjy6oqprevious model we were told to invest 100% in opportunity 2. In the second model we are advised not to invest in 6pgggggggggj{7osopportunity 2 at all! The explanation is that we have a limited budget. Because the Solver can only choose between 7pgggggggggj8o0 or 1 in the variables, this can lead to surprising results. It is important to realize that simply 'rounding' the results 8pgggggggggj^9rVof the first model clearly does not guarantee an optimal (or even feasible!) solution.9skkkkkkkkkl:
Y"4_HhQ^"3g"=h\+8X>
????mX0 0sT00{T0,$0sT00sT0V0hT0(1q070(hT(Aq0Fq0(mhT0}H]l0( }Dl0}@@l;2qq
"
8.
&
dMbP?_*+%&FPage &P"d?? Finance Budget1 Budget2 Invent2 LockboxOpenU} }$ }} }$ }} $
8 T0 K@0H@ i@0
"T0
T 0 0 } p 0 l 0 Inventory Policy 1aYWhat is the best ordering policy for a warehouse to minimize cost, while meeting demands?PHThe warehouse has a limited storage capacity of 50000 cubic meters (m3).Holding Cost#Storage Space per unit (m3)Demand per monthOrdering cost per order$Storage space available (m3) Product 19@{@i@I@~
j@ Product 24@@Pt@I@ Product 3>@@y@I@ Product 4.@@b@I@$
Quantity to order each month
EOQCostSpace used (m3) Product 1~
$@3
H<@DDD?2@DDDDD#0@
DD
Product 2~
$@3
):O'D@
DDD?
2
@
DD
DDD
#
@
D
D
Product 3~
$@3/AB@DDD?2̠@DDDDD#@
DD Product 4~
$@3'In?@DDD?2ȉ@DDDDD#@
DD
Total!@%z.!@%z.mProblemnnnnnnnyoqA warehouse sells 4 products with a different demand for each product. Each product has a different holding cost pppppppxopand requires a certain amount of space. What should the ordering policy for the warehouse be, given its limited pppppppostorage capacity?pppppppopppppppqSolutionpppppppuomThere is an analytical solution for this problem, which is known as the Economic Order Quantity (EOQ) and is ppppppp~ovgiven by the following formula: q = SQRT(2 k d/h), where q is the quantity to order, k is the cost to place an order, ppppppp~ovd is the demand and h is the holding cost of the product. Unfortunately, this formula doesn't always work in the real pppppppxopworld. Demand usually fluctuates, ordering time is variable, and other factors arise to further complicate the pppppppPoHproblem. In this model we have one such factor, a limited storage space.pppppppopppppppwoo1) The variables are the amounts to order each month for each product. These are defined as Quantities in this pppppppDXl}lYGGG@g
7.n T0! " # $ % & ' ( ) * + , T0 . / T0 1 2 3 04 5 6 7 }H o@worksheet. By changing these variables we change the total cost. pppppppx!op2) The constraints are very simple. We have a logical constraint and the storage capacity constraint. This gives!ppppppp"o:"p2Quantities >= 0 via the Assume NonNegative option"pppppp#o%#pSpace_used <= Available_space#pppppp$otIf the latter constraint wasnt present, the solution to the problem could be calculated by the formula given above.$pppppppw%oo3) The objective is to minimize the total cost, which is defined as Total_cost. It is calculated by adding the %ppppppp[&oSindividual costs for each product. Those costs are calculated by using the formula:&pppppppA'o9Cost = h q /2 + k d /q, where h, q, k and d are as above.'ppppppp{(osThis formula is easy to understand if we realize that the average inventory level is q/2 and the average number of (ppppppp)oorders is d/q.)ppppppp*oppppppp+qRemarks+pppppppr,ojIn this worksheet we have also calculated the EOQ with the formula given above. Check to see that when you,pppppppwooincrease the storage capacity and thus relax that constraint, the answers found by the Solver will approach theppppppp.oanalytic solution..ppppppp/oppppppp{0osThis model is an example of a nonlinear problem, as can easily be seen by looking at the cost formula. Whereas in 0ppppppp1otlinear problems it does not matter what are starting values for the variables are, it can be very important to have 1ppppppp2oyreasonable starting values in nonlinear problems. In this model it is not possible to start with a quantity of 0, since 2pppppppA3o9this would cause an error in the calculation of the cost.3ppppppp4oppppppp5otPlease see for yourself that the Solver will still find the correct answer, even when the starting values are close 5ppppppp!6r(but not equal to) zero. 6sssssss4Cf`Ky_48_?=h\+8X>
????mT}ëwtV4EtVhT0(1q070(hT(Aq0Fq0(mhT0}H]l0(}Dl0}@@l82qq
"
,*:@
dMbP?_*+%&FPage &P"d?? Finance Budget1 Budget2 Invent1 LockboxOpenU}m}$ }
}} }$
, T0 K@0v@
K@0T0
T T0 0 } p 0 l 0 Inventory Policy 2aYWhat is the best ordering policy for a warehouse to minimize cost, while meeting demands?h`The warehouse has a limited storage capacity of 50000 cubic meters (m3) and a budget of $30,000.Holding Cost#Storage Space per unit (m3)Demand per monthOrdering cost per orderPrice per unit Product 19@{@i@I@~
i@ Product 24@@Pt@I@~
r@ Product 3>@@y@I@~
0q@ Product 4.@@b@I@~
y@ Storage Capacity~
j@ Budget~
L@ $Quantity to order each monthCost of holding
Space
EOQand ordering used (m3)
Product 1~
$@3
H<@
DDD?
2
@
DD
DDD
#
0@
D
D
Product 2~
$@3):O'D@DDD?2@DDDDD#@
DD Product 3~
$@3/AB@DDD?2̠@DDDDD#@
DD Product 4~
$@3'In?@DDD?2ȉ@DDDDD#@
DDCost of products(@e
eB
Total!@%
z.!@%
z.mProblemnnnnnnnyoqThis model continues to build on the first inventory policy model. We expand the model by giving the warehouse a pppppppwoobudget for buying new products. In other words: A warehouse sells 4 products with a different demand for each pppppppotproduct. Each product has a different holding cost and requires a certain amount of space. What should the ordering ppppppp[oSpolicy for the warehouse be, given its limited storage capacity and limited budget?pppppppopppppppqSolutionpppppppyoqThe variables are exactly the same as in the first model. So is the objective, and the way it is calculated. The ppppppp}oudifference is that we have an extra constraint which keeps us within the budget. This new constraint is expressed as:pppppppo>p6Cost_of_products <= Available_money and we also haveppppppo1p)Space_used <= Available_space as beforeppppppyoqWe still have Quantities >= 0 via the Assume NonNegative option. This time, we also require integer quantities:pppppppDNl}YYYY\xh
y.dW T0! " # $ % & ' ( ) * + o pQuantities = integer pppppp!oppppppp"qRemarks"pppppppu#omOnce again, we have calculated the EOQs as discussed in the first inventory policy model. If we would give a #pppppppb$oZunlimited budget and unlimited storage space, the Solver would find exactly those values. $ppppppp%opppppppn&ofThere is one more change we made in this model compared to the one on worksheet Invent1. This time we &pppppppy'oqrequired the variables to be integers. Whether this is a valid assumption would depend completely on the type of 'ppppppp(oyproduct that is dealt with. If a model is trying to determine how many cars, airplanes or other such articles to buy, it (ppppppp)otcould be very important to use integer variables. If the model, on the other hand, is giving an indication how much )pppppppX*rPsugar to buy, for example, it would not be appropriate to use integer variables.*sssssssBv=h\+8X>
????mT}ëwtV4EtVhT0(1q070(hT(Aq0Fq0(mhT0`}H]l0(}Dl0`}@@l,2qq
"
cDQzcqs
dMbP?_*+%&FPage &P"d?? Finance Budget1 Budget2 Invent1 Invent2OpenU}T}$ TM'testDaniel H. Fylstra'Created by Daniel H. Fylstra on 3/29/95&0
cT0 Z@0 Z@0
"ZT0@0
T0 K@0} p0l0!00SLockbox LocationvUnA company is considering opening lockboxes in several cities to reduce the 'float' (lost interest) waiting forVVVVVWXpYhmailed payments. In what cities should lockboxes be opened to minimize lost interest and operating cost?ZZZZZ[XI\AEach area can send payments to only one city.The interest rate is]]]~
^@]_X`aaaaaaabAreas concernedcccccdaef Northwest
fNorthf Northeastf Southwest
fSouthg SoutheastahDaily Payments*i AiAiOAiAiAj\Aak lCities to be considered cccccd
m
fSeattle
fChicago
fNew York
nL.A.
fDallas
gMiamioOperating cost*i@ij@iL@i@iR@j@B
p:Average number of days from mailing to clearing of payment
cccccdmfSeattlefChicagofNew YorknL.A.fDallas
gMiamiq Northwest*r@r@r@r@r@s @
qNorth*r@r@r@r@r@s@q Northeast*r@r@r@r @r@s@q Southwest*r@r@r @r@r@s@
qSouth*r@r@r@r@r@s@t Southeast*u @u @u@u@u@v@4p,Assignments of areas to cities (1=yes, 0=no)ccccccdmfSeattlefChicagofNew YorknL.A.fDallas
fMiami
gTotalq Northwest*wxxxxyz"z.
qNorth*{rrrr}q Northeast*{rrrr}q Southwest*{rrrr}
qSouth*{rrrr}q Southeast*~maaaaaaLockbox Decision*Dal{3S
9wH\wC?CC?CP{^bb^b ZT0@0!"#$%&' (Z@)* +2,T0 ./T012304567}8p90:l;<0=>?!l
Lost interest!Seattle!Chicago!New York!L.A.!Dallas
!Miami"q Northwest"""""DLLD"""""""""""""#""
#qNorth#####DLLD#############$#$q Northeast$$$$$DLLD$$$$$$$$$$$$$%$%q Southwest%%%%%DLLD%%%%%%%%%%%%%&%%
&qSouth)&&DDDD)&&DDDD)&&DDDD)&&DDDD~
&)&'DDDD't Southeast'i''''DLLD'i'''i'''i'''i'''j)')Total of Lost Interest!)*%"'*Operating Cost(*+eeB+
Total Cost!+%)*.Problem.{/sA company wants to reduce lost interest ('float') due to mail delay, for the payments it receives every day. It is /w0oconsidering opening lockboxes in 6 different cities. Each lockbox would require a certain amount of money each 0x1pyear to operate. The company receives payments from the Northwest, the North, the Northeast, the Southwest, the 1r2jSouth and the Southeast. The amounts involved per day are known. Where should the company open lockboxes?234Solution4u5mThis model differs from others in the fact that the variables do not represent amounts of money, a number of 5~6vproducts or other such values. This time the variables are decisions. Do we open a lockbox in this city? To what city 6)7!should an area send its payments?78wIt turns out that there is an easy and elegant way to describe such variables in models. We do this by using variables 89xthat can be either 0 or 1. Decision variables like this are often called binary variables. We assign a variable to each 9f:^decision and if the decision is yes we give the variable a value 1 and otherwise the value 0. :~;vOn this worksheet, we have assigned such variables for the decisions to open lockboxes in the different cities. These ;H<@are defined as Lockbox_decisions found in cells B32 through G32.<=wBy laying out the different areas versus the cities, we can also assign 01 variables to the decisions whether an area =v>nshould send payments to a certain city. On the worksheet these are defined as Assignments, found in cells B25 >l?dthrough G30. By using the properties of the numbers 0 and 1, we can now easily formulate the model. ?D'lGF;+,Ed@T0ABCDEFGHIJKLT0MNOTPQRS0TUVW}XpY0Zl[\0]^_@{As1) The variables are the decisions where to open lockboxes and the decisions where to send the mail for each area. AyBqThese variables are defined in the worksheet as lockbox_decisions and assignments. All these variables are eitherB,C$0 or 1. (They are binary variables.)CDXEP2) We must tell the Solver that the variables can be only 0 or 1. This gives us:EFFassignments = binaryFG"Glockbox_decisions = binaryGH~IvIf we do not open a lockbox in a city, we can not have any mail sent to it. These constraints are expressed as followsIJ)J!Chicago_boxes <= Chicago_decisionJK'KDallas_boxes <= Dallas_decisionKLLLA_boxes <= LA_decisionLM%MMiami_boxes <= Miami_decisionMN+N#New_York_boxes <= New_York_decisionNO)O!Seattle_boxes <= Seattle_decisionOPiQaNotice how the usage of 0s and 1s gives us the opportunity to write these constraints this way. QRtFinally, we assume an area only sends mail to one city. Again, because of the properties of 0 and 1, we can achieve RrSjthis by requiring that the sum over the cities of the variables for an area (one row) equals 1. This givesSTTassignments_total = 1TUyVq3) The objective is to minimize lost interest and operating cost. This is defined in the worksheet as Total_Cost.VWwThis is calculated by adding the operating cost and the lost interest. The operating cost is calculated by multiplying W~Xvthe decisions to open lockboxes by the cost to operate them. This is again possible because the variables are exactly X:Y20 or 1. The lost interest is similarly calculated.YZ[Remarks[z\rThe techniques used in this model are simple but very powerful. Questions that are answered by yes or no, open or \C];closed, etc. can often be solved by using binary variables.]^x_pNotice that the interest in this model is very important for the solution. If interest goes up, it becomes more _D
lHt@FMKCIOMAV+_`T0a b`wprofitable to open more lockboxes. If interest is low, it could be more profitable to use fewer lockboxes and accept a `rajhigher float. You can see how the Solver finds different answers by changing the interest rate in cell E4.a
e(=h\+8X>
????mT}ëwtV4EtV_hT0(1q070(hT(Aq0Fq0(mhT0P}H]l0(`}Dl0P}@@lch2qq
"
9v^ȑ
dMbP?_*+%&fPage &p"d?? Finance Budget1 Budget2 Invent1 Invent2 LockboxU} }m}}
9T0 u@0
@"ZT0@0
0u@0T000 u@00}0p0 @l@0@@@@Plant Opening/ClosingjbA company wants to minimize the costs of shipping goods from production plants to warehouses near !i"ametropolitan demand centers, while not exceeding the supply available from each plant and meeting######$e"]the demand from each metropolitan area. The company has plants in S. Carolina, Tennessee and ######$C%;Arizona. It is thinking about opening a plant in Arkansas. &&&&&&'
()E*=Number to ship from plant x to warehouse y (at intersection):))))+,Plants:
Total.San Fran.Denver.Chicago.Dallas.New York/0S. Carolina!1 %$23334/ 0 Tennessee! 5
% $ 67778 /
0Arizona!
5%
$
67778
/9Arkansas!:
%$;<<<=/97>>>>>/
0Totals:
7!
?
%!
@
%!
@
%!
@
%!
A%
/9777777/BCDemands by Whse >$?f@@T@@i@@d@Ak@DEPlants:FSupplyEG=Shipping costs from plant x to warehouse y (at intersection):)HHH+0S. Carolina*1`s@I$@I @I@I@I@/0 Tennessee*5@p@I@I@I@I@I@/0Arizona*5q@I@I@I@I@I"@/0Arkansas:,D$I@I@I@I@I@/JHHHHHH+, Shipping:+K:+%DD=I'DDD DD
DDD=I'DDD DD
DDD=I'DDD DD
DDD=I'DDD DD
DDD=I'DDD DD
DDD/
LM%NExtra shipping cost if opened~
OY@PDecision to open plantQ~
RDProblem vnA company currently distributes products from three plants to five warehouses in different cities. Management ######phis now thinking about opening a new plant to bring down distribution cost. Should the company decide to ######"open the new plant or not?############Solution######Dl!_
knljk[OMKh+> T0@!@"@#@$@%@&@'@(@)@*@+@,T0@@.@/@T0@1@2@30@4@5@6@7}@ 8pv nThis models uses 2 kinds of variables. First, there are the variables that indicate how many products to ship ######k!cfrom each plant to each warehouse. Second, we have a decision variable to decide whether we should !######^"Vopen the new plant. For more information on the decision variables, see the worksheet."#############q$i1) The variables are the number of products to ship from each plant to each warehouse and the decision to$######i%aopen or close the new plant. These are defined on this worksheet as Shipments and plant_decision.%######v&n2) First, there are the 'normal' distribution constraints. These are the constraints that we cannot ship more &######x'pproducts from the plants than the supply at these plants. Also, we don't ship more to the cities than the demand'######)(!from those cities. This leads to:(######)%)Shipped_from_plants <= Supply)#####*'*Shipped_to_warehouses >= Demand*#####a+YSecond, since we can't ship a negative number of products, we have the logical constraint+######,B,:products_shipped >= 0 via the Assume NonNegative option,#####jbAnd third, we must tell the Solver to strictly use 0 or 1 for the 'decision' variable. This gives:######..plant_decision = binary.#####w/o3) The objective is to minimize cost, defined as Total_cost. This is calculated by muliplying the distribution /######]0Ucost times the number of products shipped, plus the extra cost to open the new plant.0######1######2Remarks2######q3iYou might have noticed that this model resembles a pure transportation model. This is an example of a mix3######s4kbetween a transportation model and a pure decision model, like Lockbox. In real life situations, it is very4######b5Zcommon to combine different kind of models to get a better representation of the problem. 5######x6pNotice how the decision variable is used to control the supply at the potentially new plant. If the decision to 6######)7!open is no, the supply is zero. 7&&&&&&6"zEIK}fCy+~E=h\+8X>
????mT}ëwtV4EtVhT0(1q070(hT(Aq0Fq0(mhT0 E}H]l0(P}Dl0 E}@@l92qq
"
Oh+'0@H`
Edwin StraverxDaniel H. FylstramMicrosoft Excel@T@X3ј՜.+,08@`h
pFrontline Systems, Inc.SummaryFinanceBudget1Budget2Invent1Invent2LockboxOpenWorksheets
!"#$%&'()*+,./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{}~Root Entry0@@`p F@@`&]`pBookp`p``p````@@@````P``@`
``@P@````PP@P*``SummaryInformation`0@````@`(PPP```0`PP`PpppDocumentSummaryInformation``P8```````````````/