Portfolio Optimization - Sharpe Model (CAPM): Uses Excel's regression functions to calculate alphas and betas for stocks relative to a market index, then uses these to find an efficient portfolio.
Stock Portfolio Management: Uses a VBA macro to optimize several scenarios for minimum risk at different target rates of return, then draws a graph of the efficient frontier.Portfolio Optimization - Markowitz Model: Allocate funds to stocks to minimize risk for a target rate of return - with known or computed variances and covariances.Capacity Planning: Determine which plants should be opened or closed.Cash Management: Determine where to locate lockboxes to minimize the "float" or interest lost to due mailing delays.Inventory Management: Compare inventory stocking and reordering policies with the EOQ (Economic Order Quantity) model.Capital Budgeting: Choose a combination of capital projects to maximize overall NPV (Net Present Value).Working Capital Management: Invest in 1-month, 3-month, and 6-month CDs to maximize interest while meeting cash requirements.Examples by Functional Area Corporate Finance You can do this any time after signing up.
When you download and install a free trial of our enhanced Solvers for desktop Microsoft Excel, you'll find that more than ninety (90) small, but fully functional, example models are available for your use - covering conventional optimization, simulation and risk analysis, decision analysis (using decision trees), simulation optimization, stochastic optimization, and robust optimization. You can run all of these models with the basic Excel Solver. Here is a comprehensive list of example models that you will have access to once you login. To learn more, sign up to view selected examples online by functional area or industry. All constraints are satisfied.Optimization is a tool with applications across many industries and functional areas.
This solution gives the minimum cost of 26000. Check 'Make Unconstrained Variables Non-Negative' and select 'Simplex LP'.Ĭonclusion: it is optimal to ship 100 units from Factory 1 to Customer 2, 100 units from Factory 2 to Customer 2, 100 units from Factory 2 to Customer 3, 200 units from Factory 3 to Customer 1 and 100 units from Factory 3 to Customer 3. Click Add to enter the following constraint.ħ. Click Add to enter the following constraint.Ħ. Enter Shipments for the Changing Variable Cells.ĥ. You have the choice of typing the range names or clicking on the cells in the spreadsheet.Ĥ. The result should be consistent with the picture below. Note: can't find the Solver button? Click here to load the Solver add-in.Įnter the solver parameters (read on). On the Data tab, in the Analyze group, click Solver. To find the optimal solution, execute the following steps.ġ. We shall describe next how the Excel Solver can be used to quickly find the optimal solution. It is not necessary to use trial and error. With this formulation, it becomes easy to analyze any trial solution.įor example, if we ship 100 units from Factory 1 to Customer 1, 200 units from Factory 2 to Customer 2, 100 units from Factory 3 to Customer 1 and 200 units from Factory 3 to Customer 3, Total Out equals Supply and Total In equals Demand. Total Cost equals the sumproduct of UnitCost and Shipments. Range NameĮxplanation: The SUM functions calculate the total shipped from each factory (Total Out) to each customer (Total In). To make the model easier to understand, create the following named ranges. What is the overall measure of performance for these decisions? The overall measure of performance is the total cost of the shipments, so the objective is to minimize this quantity.Ģ. What are the constraints on these decisions? Each factory has a fixed supply and each customer has a fixed demand.Ĭ. What are the decisions to be made? For this problem, we need Excel to find out how many units to ship from each factory to each customer.ī. To formulate this transportation problem, answer the following three questions.Ī.