![[Dividing Line Image]](peterA/div.gif){kind=small}
EXTRA BONUS: Download our Excel add-in DOTPRD32.XLL, free of charge! (This add-in is used in one Investment Example). For details, see online Help in the Premium Solver Evaluation Version.
| Learn about our new Premium Solver Platform -- The Most Powerful Spreadsheet Optimizer Ever! |
When you click on any of the links below, some Web browsers will actually open and display the workbook in Excel, within the browser window. Select File Save... to save the file in a convenient directory on your hard disk. Other Web browsers will ask you if you want to save the file -- answer Yes or OK, and then choose a convenient directory on your hard disk. If you are prompted for authentication information, just click the Cancel button. If you don't want to open Excel in your browser window, in Internet Explorer you can right-click and choose Save Target As..., then choose a convenient directory on your hard disk.
When the file has been downloaded and saved, you can start Excel, select File Open..., navigate to the directory where you saved the workbook, select its name and click OK. Then read the instructions on the first sheet tab in the workbook. These workbooks are in Excel 5.0/95 format -- you can open them in Excel 2000, Excel 97, Excel 95 or Excel 5.0.
DOTPRD32.XLL
Excel Add-In (31K)
In Internet Explorer 4.0 or above, you may need to right-click on
the above link and select "Save Target As..."
ZIP File of All Examples (6 workbooks, 41 worksheets) plus Excel Add-In (166K)
Each example application workbook is described briefly below. These descriptions are also included on the first sheet tab of each downloaded workbook, so you can view them later in Excel. If you have any questions, please email us at sales@frontsys.com or call us toll-free at (888) 831-0333 or at (775) 831-0300.
CLICK HERE to visit Frontline Systems' Website home page.
This workbook contains five example models from the area of corporate finance: A working capital management model, a capital budgeting model (in two versions), an inventory policy model (in two versions), a lockbox location model, and a plant opening/closing model .
In the working capital management model, Finance, we look at how to invest money in 1-month, 3-month and 6-month CDs, while meeting cash requirements in each month.
In the capital budgeting models, Budget1 and Budget2, a company wants to maximize the Net Present Value of a combination of investment opportunities.
In the inventory policy models, Invent1 and Invent2, we compare the EOQ (Economic Order Quantity) with the optimal solution determined by the Solver.
In the lockbox location model, Lockbox, a firm needs to decide where to open lockboxes to minimize the 'float', i.e., the lost interest, due to mail delay.
In the capacity planning model, Open, we determine whether to open or close facilities such as plants and warehouses.
The models in this workbook deal with decisions to invest in stocks and bonds. To help you understand the models in this workbook, we briefly explain the ideas behind the stock and bond models in the Theory worksheet.
The Markowitz model uses the Solver to determine the percent of our available funds to invest in each of five stocks, so as to incur the least risk for a target rate of return. The resulting portfolio of stocks is called an efficient portfolio. In this model, we assume that the variances and covariances of stocks are known. In the Full Markowitz model, we calculate the variances and covariances of stocks from a history of stock prices.
The Efficient Frontier worksheet contains a Markowitz model and an embedded chart. A short VBA program, also included in the workbook, uses the Solver to optimize the Markowitz model for several different target rates of return. The VBA code is run and the resulting portfolio returns and variances are plotted on the chart when you press the button on the worksheet.
The Sharpe model computes an efficient portfolio using alphas and betas which relate the return on each stock to the return on the market and the risk-free rate. The alphas, betas and residual variances are computed via linear regression from a history of stock prices.
The four Bond worksheets illustrate how a portfolio of bonds may be 'immunized' against changes in interest rates. The model Bond1 assumes that bond durations are known. In model Bond2, the durations are calculated using Excel’s DURATION function.
Models Bond3 and Bond4 illustrate 'exact matching' in fixed-income portfolios. Here we assume that the investor has a series of liabilities (for example, pension payments) to be met in future periods, and the principal and interest on the bonds must cover the liabilities in each period.
An important group of Solver applications is based on distribution or network models. The amount of money that companies save each year by applying linear programming towards their distribution problems is enormous.
In this series we will look at a simple transportation problem in worksheet Transport1, then extend it to a 2-level multi-product model in worksheets Transport2 and Transport3.
We'll also examine a frequently encountered class of problems called 'knapsack' problems, in worksheet Knapsack. As an example we will look at a truck that has to transport different kinds of gas.
In the Facility worksheet we will look at a facility location problem, where a company has to decide if it's profitable to close down one or more of their plants and save overall costs.
Finally, in the Prodtran worksheet, we will examine a combination production and transportation model where the number of products made in plants depends on choices made in distribution. This kind of combination is often possible, but many users prefer to split these models up into smaller ones to simplify the problem.
One of the areas where linear and non-linear programming is applied most frequently is in manufacturing and production. There are many different ways in which the Solver can be used to increase productivity, lower cost, reduce waste, etc. We will limit ourselves to 5 different types of models.
First, in the ProductMix worksheet we examine a production mix model. Here we see how to use parts to build different products and maximize profit.
Second, we look at a machine allocation problem, in two different versions (Alloc1 and Alloc2). This model determines which machines to use to produce products, in order to meet a certain demand and minimize cost.
Third, in worksheets Blend1 and Blend2, we look at a 'continuous' rather than 'discrete' production problem, where the end product requires certain qualities and is a mixture of previously produced products. This kind of blending problem is very common in the oil industry and in agriculture, for example.
Fourth, worksheet Process is a process selection problem, where we have several different ways of producing something (planed wood in this example) and we want to pick the process that minimizes cost (or maximizes profit).
Finally, in worksheet Cutstock we look at a classic example of a cutting stock problem. In this model we determine how to cut steel sheets so as to minimize the waste of steel.
In this series of models we will look at how the Solver can help make decisions about purchasing goods, awarding contracts, etc. You can view these purchasing models as allocating a scarce resource -- namely money -- to various uses in an optimal way.
In the contract awards worksheets Award1 and Award2, we have bids from 3 different suppliers to supply diskettes at different prices to our facilities in 4 different states. We want to choose from the suppliers' bids in a way that will minimize our total cost. In Award2, one supplier has specified a minimum size bid for each state.
In the inventory policy worksheets Invent1 and Invent2 we compare the EOQ (Economic Order Quantity) with the optimal amounts the Solver suggests we should order. These inventory policy models can also be found in the Finance Examples workbook.
In the Media worksheet, a company wishes to buy advertising at the lowest possible cost while still reaching a certain target number of prospects. This type of media buying decision is a common Solver application.
In the Purchase worksheet, we examine a purchasing/transportation problem where a company can buy goods at several different places and it needs those goods delivered to several different locations.
In this series of models we will see how the Solver can help in staff scheduling with employee preferences, assigning people to offices, and in larger-scale problems such as airline crew scheduling and employee hiring, firing and training.
In the Crew worksheet, we look at a very simplified application where a small airline needs to schedule crews to operate various flights. A crew 'rotation' must begin and end in the same city.
In the HireFire worksheet, we consider a company that has to change the composition of its workforce towards more highly trained employees. It can hire, fire and train employees at various costs. Different objectives are possible, such as minimizing costs or employee turnover.
In the Offices worksheet, we look at the common problem of assigning employees to offices, taking into account employee preferences.
In the three Scheduling worksheets, an amusement park needs to assign employees to different work schedules to meet the demand for operation of the various rides on busy days. In Sched2, we take into account employee preferences in scheduling. In Sched3, we also take into account seniority and assign greater weight to senior employees' preferences.
In the Troops worksheet, an army needs to move troops from 3 different training camps to 4 bases. The army can minimize cost or time by using this transportation model. Similar models occur in the Logistics Examples workbook.
DOWNLOAD ZIP FILE CONTAINING ALL EXAMPLES (150K)
This file (solvexam.zip) contains compressed versions of all six of the Excel workbooks described above. To use this file, you must have a ZIP archive program such as WinZip or PKUNZIP on your system. If you don't have a ZIP archive program, please download the individual Excel workbooks using the hyperlinks above. If you have any questions, please email us at sales@frontsys.com or call us toll-free at (888) 831-0333 or at (775) 831-0300.
SOFTWARE LICENSE
Frontline Systems Inc. ("Frontline") offers the example application Excel workbooks ("Workbooks") on this page to you under a limited-use license free of charge. When you follow the hyperlinks on this Web page and download these files, you are signifying your agreement to the following terms and conditions. Please read them before proceeding.
The Workbooks are protected by United States copyright laws and international copyright treaty provisions. Therefore, you must treat the Workbooks just like any other copyrighted material: You may use and modify the Workbooks, incorporate any material from them in your own applications, and copy and distribute your applications without any restrictions, but you may not redistribute the original Workbooks themselves to anyone else without written permission from Frontline. If you'd like to arrange for distribution of the Workbooks within your organization, please contact us by phone (775-831-0300), fax (775-831-0314) or email.
This license agreement is governed by the laws of the State of Nevada.
WARRANTY STATEMENT
THE WORKBOOKS ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. THE ENTIRE RISK AS TO THE RESULTS AND PERFORMANCE OF THE WORKBOOKS IS ASSUMED BY YOU.
FRONTLINE DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE, WITH RESPECT TO THE WORKBOOKS. THIS WARRANTY GIVES YOU SPECIFIC RIGHTS, AND YOU MAY HAVE OTHER RIGHTS WHICH VARY FROM STATE TO STATE.
IN NO EVENT SHALL FRONTLINE BE LIABLE FOR ANY DAMAGES WHATSOEVER (INCLUDING WITHOUT LIMITATION DAMAGES FOR LOSS OF BUSINESS PROFITS, BUSINESS INTERRUPTION, LOSS OF BUSINESS INFORMATION, AND THE LIKE) ARISING OUT OF THE USE OR INABILITY TO USE THE WORKBOOKS, EVEN IF FRONTLINE HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. BECAUSE SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR INCIDENTAL OR CONSEQUENTIAL DAMAGES, THE ABOVE LIMITATION MAY NOT APPLY TO YOU. IN STATES WHICH ALLOW THE LIMITATION BUT NOT THE EXCLUSION OF SUCH LIABILITY, FRONTLINE'S LIABILITY TO YOU FOR DAMAGES OF ANY KIND IS LIMITED TO $1.
U.S. GOVERNMENT RESTRICTED RIGHTS
The Workbooks are provided with RESTRICTED RIGHTS. Use, duplication or disclosure by the Government is subject to restrictions as set forth in subdivision (b)(3)(ii) of The Rights in Technical Data and Computer Software clause at 252.227-7013. Contractor/manufacturer is Frontline Systems, Inc., P.O. Box 4288, Incline Village, NV 89450.
![[Dividing Line Image]](div.gif){kind=small}
