Investment Portfolio Allocation–Excel Solver

Excel is great desktop business analytics software; we can use it to analyze many different business problems.  And quite often, Excel has multiple ways to solve the problems; we can choose the one that we are more comfortable.  For example, I used Solver Foundation to determine the investment portfolio allocation in one of my previous blog,  I am an IT consultant so I like Solver Foundation because I can use code to define the problem.  However, coding may be too remote for many business users.  Don’t worry, there is another approach that I am going to show you and this time, it would be more end user friendly.

Excel Solver

Excel Solver is nothing new and it was introduced to Excel 3.0 in 1991.  However, it was one of the best kept Excel secrets; i.e. not a lot of Excel users know about it.  If you want to find out how you can enable it in Excel 2010, please refer to my blog Excel 2010 – Solver.

Let’s review the original worksheet.  It is a very simple worksheet and we have the historical returns, cross correlation and covariants of various investment vehicles.

image

If we want to use Excel Solver, then we have to define most of the problem in Excel,  So I am going to add couple more columns beside Mean return for Allocation% and Expected Returns for each allocation.  Expected return is equal to Allocation% * Mean Return.

image

I need another row under the covariants table and let’s call it Expected Risk.

image

Expected Risk of Stocks is calculated as =SUMPRODUCT(C25:C30, $K$7:$K$7:$K$12)*K7; where C25:C30 and $K$7:$K$7:$K$12 are the covariants of Stocks with other investment vehicles and Allocation% respectively.

Out last step is to add the objective, the total expected risk, =SUM(C31:H31).

image

Now we can launch the Solver.

  • Click Data.
  • Select Solver.

image

Then the Solver Parameters dialogue would be displayed.

image

  • Set Set Objective: to $I$31 (total expected risk)
  • Change To: to Min;; we want to minimize the expected risk..
  • Set By Changing Variable Cells: to $K$7:$K$12 (Allocation %)

image

Then we need to add couple constraints:

  • Total Allocation % ($K$13) should be added up to 100%

image

  • Expected Return ($L$13) >= Required Return ($C$33)

image

Now, we have defined the problem and we are ready to solve it.

  • Click the Solve button.

image

Instantly, Solver found a solution.  Let’s click the OK button and examine the results.

image

Here are the results:

image

And if we compare the results with the ones that were generated by Solver Foundation, they are the same.

image

Conclusion

Both approaches empower us to solve sophisticated mathematical models without being a mathematician.  And we have a choice.  I am a coder and I like to go through the problem line by line’; so I prefer Solver Foundation.  But if you are a heavy Excel user, then you may want to use Excel Solver.  There are other pros and cons of both approaches but I would leave it to another blog.

I often ask myself how much do I know Excel; Excel is like a treasure island with so many useful features that are hidden from us.  What is your most favorite Excel feature?

Andrew Chan helps you to make better and faster decisions!

 

 

Protect Excel VBA Projects

I have a blog that described how to protect our Excel worksheet.  A few readers asked me how to protect Excel VBA projects.  It is actually quite simple and you are going to see how easy it is.

Assume we are already in Excel VBA Editor.

  • Click Tools
  • Select VBAProject Properties…

image

 

We would see a Project Properties Dialogue.

  • Click the Protection tab
  • Enable Lock project for viewing
  • Enter a password twice.
  • Click the OK button.

image

Now, we can save the file.  Next time when we want to view the VBA code, we would have to enter the password first.

image

Password protection is very simple to setup; however, password protection is not very secure in Excel environment.  If you Google “Remove Excel Password”, you would find many utilities that claim to be able to unlock your password.  Password protection is a good way to protect your Excel investment against our users who accidentally modify our data / code.  But if we really want to protect our intellectual property, then password protection may not be the most suitable approach.  Of course, a strong password always help!

 

Andrew Chan helps you to make better and faster decisions

Can we afford to have a supercomputer?

Tianhe-1A is currently the fastest supercomputer in the world with 2.5 PFLOPS computation power. It is nearly 50% faster than the former number one system – the Cray  XT5 “Jaguar”.  Jaguar’s computation power comes from its 224,256 AMD Operon cores but Tianhe-1A’s architecture is fundamentally different.  Tianhe-1A is equipped with 14,336 Intel CPU and 7,168 NVidia Graphics processing units (GPU), so most of its computation power comes from video card (GPU) rather than CPU.

What can GPU do in reality?  Especially in finance industry?   In 2009, Bloomberg realized the power of GPU.  So instead of installing 1,000 new servers, Bloomberg is using 48 server / GPU pairs to price its asset-backed security.  According to their CTO, Shawn Edwards, “Overall, we’ve achieved an 800% performance increase, what used to take sixteen hours we’re computing in two hours.”  Bloomberg is not alone; BNP also uses GPU to price their derivatives.  Other software vendors such as MATLAB supports GPU in its Parallel Computing Toolbox.  General-Purpose computation on Graphics Processing Units (GPGPU) has been gaining huge momentum in financial industry.

What is GPU anyway?

If you have a computer, then you have a GPU.  GPU is the processor on your video card; it is used to produce 2D graphic, 3D scenes, capture TV signal, decode / encode high definition video… etc.  Like CPU, it is also programmable; game developers have been using Open GL and DirectX to create video games which involves a lot of computations.

What are the difference between CPU and GPU?

Currently, i7-990X is the fastest Intel CPU and it has a clock speed of 3.46 GHz.  3.46 GHz?  It is not very impressive when we looked back to CPU history; Intel Pentium 4 had already reached the clock speed of 3.4 GHz back in 2004.  The CPU clock speed didn’t increase a lot for the last 7 years; it has reached its limit.  Clock speed used to be the major performance factor for CPU.  Instead of increasing clock speed, CPU manufacturers, AMD and Intel have increased the number of cores in each CPU, e.g. 2-core, 3-core, 4-core.  There are 6 cores in i7-990X. 

NVIDIA did a comparison between CPU and GPU and we can see there is a quiet revolution.  GPU’s computation power had grown exponentially where CPU remained quite linear.

image

Well, let’s look at today’s GPU; AMD Radeon HD 6970 has 1536 stream processors; its computer power is 2.7 TFLOP and 683 GFLOP for single precision and double precision respectively.

How about price?  Intel i7-990X is $999 and AMD 6970 is only $359.

CPU has its advantages.  A desktop PC can have up to 24 GB memory for as little as $400 and server can install up to 144 GB memory.  GPU on the other side is quite expensive to increase its memory, e.g. NVIDIA GPU, C2070 has 6 GB memory but it costs $4,000.  So if our application requires a lot of database activities and little calculation, then CPU is still the best.

CPU is still much faster in term of single thread processing; AMD 6970 has only up to 880 MHz Engine clock where we can find CPU with clock speed over 3 GHz.  If our application involves heavily sequential processing and cannot break down for parallel computing environment  (multi cores), then CPU would perform much quicker.

How to program GPU?

GPU used to required special programming languages (e.g. OpenGL) to develop GPU applications.  In 2006, NVIDIA released CUDA, a SDK that allows developers to use C, a general programming language to code in GPU.  It is a major milestone towards General-Purpose computation on graphics processing units (GPGPU).  CUDA has been evolved to support other general programming languages such as C++, FORTRAN, Java, Python and Microsoft .Net Framework.

NVIDIA also introduced Parallel Nsight, a FREE GPU development environment that is tightly integrated with Microsoft Visual Studio, the world’s most popular development environment.

The bottom-line is if we know Visual Studio, C / C++ and have vector programming experience, then congratulation!  We can start GPGPU development now.  Here is a link to some CUDA samples.

If we like to develop GPU applications that can run on heterogeneous systems rather than just NVIDIA video cards, then we can consider either OpenCL or DirectCompute.

What is the future of GPU?

Before we look into the future, let’s look at today!  GPU is getting very popular in scientific research, financial modeling and other general purpose computation intensive areas.  For example,  Folding@Home is a distributed computer project that study the relationship between protein folding and diseases such as cancer, Mad Cow (BSE), Alzheimer’s…etc.  It has a computation power of 9.4 PFLOPS and 92% (8.7 PFLOPS) is generated from GPU.  We can find more successful stories from NVIDIA.

Not every application can benefit from GPU but GPGPU is going to offload a lot of computation intensive code from CPU in near future.  Is our application ready?

Conclusion

GPGPU has offered us an opportunity that is cost effective and scalable.  Let’s review Bloomberg’s bond pricing system again; how much would it cost to build 1,000 servers, plus future maintenance cost including space rental and electricity?  Now they have only 48 servers!  It is also much easier to scale it up!

No matter if our systems are going to run on CPU or a hybrid environment (both CPU / GPU), it is going to be multi core.  Intel is working on an experimental 48-core processor that can theoretical expand to 1,000 core.  So we must re-think, re-design and re-develop our algorithm, code and system to support multi core.  If we want to migrate out software to GPU, we must also consider the constraint of GPU; i.e. it does not work well with memory hunger systems.

How much video cards do we need to build a supercomputer?  I would say if we can build a PC farm that accommodates 20 video cards, then we have a supercomputer in our basement.  We can install 3 video cards on a motherboard, e.g. ASUS Rampage II Extreme; so we need about 7 PCs. 

Can we afford to have a supercomputer?

Multi core is our future!

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

Solver Foundation for Excel – Investment Portfolio Allocation

We can use Solver Foundation to solve many problems.  In my last blog, Solver Foundation for Excel – Supply Chain Planning Part 1, I demonstrated how we can maximize profit.  And I like to use another Microsoft example to demonstrate how we can minimize investment risk using Solver Foundation.

Let’s open worksheet Quadratic Portfolio.xlsx.  It is a very simple worksheet but you need some basic statistical background.  We have historical figures for certain investment vehicles; then we work out the cross correlation and convariants.

Our objective is to determine the portfolio mix that has the lowest investment risk.

image

 

Sets

We only have two sets for this model, i.e. I, and J.

image

 

Parameters

Three parameters are used in this model and they are:

image

  • RequiredReturn – Required return rate for the future investment.

image

  • Mean – Mean return for each investment.

image

  • Cov – Covariant matrix based on the historic data.

image

 

Decisions

There are only two decisions and they are:

  • Allocation – Allocation for each investment in the portfolio.

image

  • Yield – Yield of the portfolio allocations.

image

 

Goals

There is only one simple goal – Given a required return rate for the future investment, the variance (or the risk of being way off the required return) is minimized.  Lower convariant means better diversification.

image

Sum[
        {iter3, I},
        Foreach[
          {iter4, J},
          Cov[iter3, iter4] * Allocation[iter3] * Allocation[iter4]
        ]
      ]

Constraints

We only find 3 simple constraints in this model.

image

  • Constraint1 – The yield should not less than the required return rate. Yield >= RequiredReturn
  • Constraint2 – The allocations for each investment should sum up to 100%. Sum[
          {iter1, I},
          Allocation[iter1]
        ] == 1
  • Constraint3 – Yield is calculated from the summation of the products between each investment’s allocation and mean historic return rate.

    Plus[-Yield, Foreach[
          {iter2, I},
          Mean[iter2] * Allocation[iter2]
        ]] == 0

    Model

    Now, we have defined everything that is required for this model and we can review it.

Model[
  Parameters[
    Sets[Any],
    I,
    J
  ],
  Parameters[
    Reals[-Infinity, Infinity],
    Annotation[RequiredReturn, "description", "Required return rate for the future investment."],
    Annotation[Mean[I], "description", "Mean return for each investment."],
    Annotation[Cov[I, J], "description", "Covariant matrix based on the historic data."]
  ],
  Decisions[
    Reals[0, 1],
    Annotation[Allocation[I], "description", "Allocation for each investment in the portfolio."]
  ],
  Decisions[
    Reals[0, Infinity],
    Annotation[Yield, "description", "Yield of the portfolio allocations."]
  ],
  Constraints[
    Constraint1 -> Annotation[Annotation[Yield >= RequiredReturn, "description", "The yield should not less than the required return rate."], "enabled", False],
    Constraint2 -> Annotation[Annotation[Sum[
      {iter1, I},
      Allocation[iter1]
    ] == 1, "description", "The allocations for each investments should sum up to 100%."], "enabled", False],
    Constraint3 -> Annotation[Plus[-Yield, Foreach[
      {iter2, I},
      Mean[iter2] * Allocation[iter2]
    ]] == 0, "description", "Yield is calculated from the summation of the products between each investment’s allocation and mean historic return rate."]
  ],
  Goals[
    Minimize[
      Variance -> Annotation[Annotation[Sum[
        {iter3, I},
        Foreach[
          {iter4, J},
          Cov[iter3, iter4] * Allocation[iter3] * Allocation[iter4]
        ]
      ], "order", 0], "description", "Given a required return rate for the future investment, the variance (or the risk of being way off the required return) is minimized."]
    ]
  ]
]

Solve the Model

The model is ready and we can click the Solve button on Solver Foundation tag to start the process.

image

Below is the results and log that were output from the model:

imageimage

 

Conclusion

We don’t have to be a mathematician to solve such sophisticated mathematical model.  I have no idea what algorithms Solver Foundation used but it delivered the results.  All we have to do is to define our problem, bind the input data from Excel, then Solver Foundation would do everything for us.  We can use it to solve many problems, no matter if it is finance, risk management, operation.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

Solver Foundation for Excel – Supply Chain Planning Part 2

In my last blog, I demonstrated how to create Sets, Parameters, Decisions and Goals in Solver Foundation Excel Add In.  In this blog, I am going to continue the demonstration and discuss the last component, Constraints.

Add Constraints

There are 7 constraints in this supply chain model and we are going to look at each one of them.

  • CapacityConstraint – Each factory has a capacity limit on how many products it can produce within a fixed period of time that we are planning on.
  • Constraint2 – Summation of units from all promotions should equal to the units shipped for each product produced in factories that will be sold in areas.
  • Constraint3 – The product units sold should match the sales forecasted units.
  • Constraint4 – total cost for each product involves manufacture cost and shipping cost.
  • Constraint5 – Total revenue is the forecasted price times the units sold.
  • Constraint6 – Total profit is the summation of each product’s revenue minus its cost.
  • DemandConstraint – The units of products with promotions that will be sold in areas will be produced should be less than the units forecasted.

image

 

CapacityConstraint

 

image

The capacity required to produce the planned number of products is:

Plan[iter2, iter3, iter4, iter1] * manufactureLoads[iter2]

and iter2, iter3 and iter4 represent loops that would sum up the total capacity required per factory ({iter1, Factories}).

The total capacity required must be smaller or equal to factory capacity factoryCapacity[iter1].

Constraint2

 

image

Plus[-ShippingPlan[iter5, iter6, iter7], Foreach[
            {iter8, Promotions},
            Plan[iter5, iter6, iter8, iter7]
          ]] == 0

The planned number of products produced, Plan[iter5, iter6, iter8, iter7], should be equal to the number of products shipped, ShippingPlan[iter5, iter6, iter7] .

Constraint3

 

image

Plus[-SalesForecastUnits[iter9, iter10, iter11], Foreach[
            {iter12, Factories},
            Plan[iter9, iter10, iter11, iter12]
          ]] == 0

The planned number of products produced Plan[iter9, iter10, iter11, iter12] should be equal to the number of product sold, SalesForecastUnits[iter9, iter10, iter11].

Constraint4

 

image

Plus[-TotalCost[iter13, iter14], Foreach[
         {iter15, Factories},
         ShippingPlan[iter13, iter14, iter15] * (unitManufactureCost[iter13] + transport[iter13, iter14, iter15])
       ]] == 0
The total cost TotalCost[iter13, iter14] should be equal to the manufacture cost ShippingPlan[iter13, iter14, iter15] * unitManufactureCost[iter13] and shipping cost ShippingPlan[iter13, iter14, iter15] * transport[iter13, iter14, iter15].

 

Constraint5

 

image

Plus[-TotalRevenue[iter16, iter17], Foreach[
          {iter18, Promotions},
          SalesForecastUnits[iter16, iter17, iter18] * demandForecastPrice[iter16, iter17, iter18]
        ]] == 0

The total revenue TotalRevenue[iter16, iter17] should be equal to the multiple of number of products sold SalesForecastUnits[iter16, iter17, iter18] and price demandForecastPrice[iter16, iter17, iter18]
.

Constraint6

 

.image

This one is quite simple.  Total Profit is equal to Total Revenue – Total Cost.

 

DemandConstraint

 

image

This is another simple one.  Sales SalesForecastUnits[iter21, iter22, iter23] must be smaller or equal to demand demandForecastUnits[iter21, iter22, iter23].

Model

Now, the model is completed and if we click on the model tag, then we can see the whole model in OML format.

image

Below is the full model:

Model[
  Parameters[
    Sets[Any],
    Products,
    Factories,
    Areas,
    Promotions
  ],
  Parameters[
    Reals[-Infinity, Infinity],
    Annotation[manufactureLoads[Products], "description", "Manufacture loads for each product types."],
    Annotation[factoryCapacity[Factories], "description", "Each factory has a capacity limit on how many products it can produce within a fixed period of time that we are planning on."],
    Annotation[unitManufactureCost[Products], "description", "Unit cost of each product manufactured."],
    Annotation[transport[Products, Areas, Factories], "description", "Shipping the products manufactured in a specific factory to a specific geographic area for sale has certain cost."],
    Annotation[demandForecastPrice[Products, Areas, Promotions], "description", "A sales forecast on the price at which it will be sold of each product with each promotion type in every geographical area it is going to be sold."],
    Annotation[demandForecastUnits[Products, Areas, Promotions], "description", "A sales forecast on the number of units to be sold of each product with each promotion type in every geographical area it is going to be sold."]
  ],
  Decisions[
    Reals[0, Infinity],
    Annotation[Plan[Products, Areas, Promotions, Factories], "description", "how many units of Products will be produced in Factories that will be sold in Areas with Promotions."],
    Annotation[ShippingPlan[Products, Areas, Factories], "description", "how many units of Products that are manufactured in Factories will be shipped into Areas"],
    Annotation[SalesForecastUnits[Products, Areas, Promotions], "description", "how many units of Products with Promotions that will be sold in Areas will be produced"],
    Annotation[TotalRevenue[Products, Areas], "description", "total revenue of selling Products in Areas"],
    Annotation[TotalCost[Products, Areas], "description", "total cost of selling Products in Areas"],
    Annotation[TotalProfit, "description", "total profit of a plan"]
  ],
  Constraints[
    CapacityConstraint -> Annotation[Foreach[
      {iter1, Factories},
      Sum[
        {iter2, Products},
        Foreach[
          {iter3, Areas},
          Foreach[
            {iter4, Promotions},
            Plan[iter2, iter3, iter4, iter1] * manufactureLoads[iter2]
          ]
        ]
      ] <= factoryCapacity[iter1]
    ], "description", "Each factory has a capacity limit on how many products it can produce within a fixed period of time that we are planning on."],
    Constraint2 -> Annotation[Foreach[
      {iter5, Products},
      Foreach[
        {iter6, Areas},
        Foreach[
          {iter7, Factories},
          Plus[-ShippingPlan[iter5, iter6, iter7], Foreach[
            {iter8, Promotions},
            Plan[iter5, iter6, iter8, iter7]
          ]] == 0
        ]
      ]
    ], "description", "Summation of units from all promotions should equal to the units shipped for each product produced in factories that will be sold in areas."],
    Constraint3 -> Annotation[Foreach[
      {iter9, Products},
      Foreach[
        {iter10, Areas},
        Foreach[
          {iter11, Promotions},
          Plus[-SalesForecastUnits[iter9, iter10, iter11], Foreach[
            {iter12, Factories},
            Plan[iter9, iter10, iter11, iter12]
          ]] == 0
        ]
      ]
    ], "description", "The product units sold should match the sales forecasted units."],
    Constraint4 -> Annotation[Foreach[
      {iter13, Products},
      Foreach[
        {iter14, Areas},
        Plus[-TotalCost[iter13, iter14], Foreach[
          {iter15, Factories},
          ShippingPlan[iter13, iter14, iter15] * (unitManufactureCost[iter13] + transport[iter13, iter14, iter15])
        ]] == 0
      ]
    ], "description", "total cost for each product involves manufacture cost and shipping cost."],
    Constraint5 -> Annotation[Foreach[
      {iter16, Products},
      Foreach[
        {iter17, Areas},
        Plus[-TotalRevenue[iter16, iter17], Foreach[
          {iter18, Promotions},
          SalesForecastUnits[iter16, iter17, iter18] * demandForecastPrice[iter16, iter17, iter18]
        ]] == 0
      ]
    ], "description", "Total revenue is the forecasted price times the units sold."],
    Constraint6 -> Annotation[Plus[-TotalProfit, Foreach[
      {iter19, Products},
      Foreach[
        {iter20, Areas},
        TotalRevenue[iter19, iter20] – TotalCost[iter19, iter20]
      ]
    ]] == 0, "description", "Total profit is the summation of each product’s revenue minus its cost."],
    DemandConstraint -> Annotation[Foreach[
      {iter21, Products},
      Foreach[
        {iter22, Areas},
        Foreach[
          {iter23, Promotions},
          SalesForecastUnits[iter21, iter22, iter23] <= demandForecastUnits[iter21, iter22, iter23]
        ]
      ]
    ], "description", "The units of products with promotions that will be sold in areas will be produced should be less than the units forecasted."]
  ],
  Goals[
    Maximize[
      Profit -> Annotation[Annotation[TotalProfit, "order", 0], "description", "Total profit is maximized."]
    ]
  ]
]

Solve the Model

The model is ready and we can click the Solve button on Solver Foundation tag to start the process.

image

 

Log

We can review the log when the process is completed.  It took 2 ms to solve the model and the total profit is 9,158,191.67.

image

 

Solver Foundation Results

We can find all the details results (Decisions) in the Solver Foundation Results that is stored in a new Excel worksheet, e.g. factory at China would produce 7,000 deluxe products to ship to Asia that would sell at discount.

Solver Foundation Results

 

Name

Value

Solution Type

Optimal

Profit

9158191.667

Plan["Deluxe", "Asia", "discount", "China"]

7000

Plan["Deluxe", "Asia", "discount", "Hungary"]

0

Plan["Deluxe", "Asia", "discount", "Mexico"]

0

Plan["Deluxe", "Asia", "none", "China"]

5000

Plan["Deluxe", "Asia", "none", "Hungary"]

0

Plan["Deluxe", "Asia", "none", "Mexico"]

0

Plan["Deluxe", "Asia", "premium", "China"]

5000

Plan["Deluxe", "Asia", "premium", "Hungary"]

0

Plan["Deluxe", "Asia", "premium", "Mexico"]

0

Plan["Deluxe", "Europe", "discount", "China"]

7000

Plan["Deluxe", "Europe", "discount", "Hungary"]

0

Plan["Deluxe", "Europe", "discount", "Mexico"]

0

Plan["Deluxe", "Europe", "none", "China"]

2686.666667

Plan["Deluxe", "Europe", "none", "Hungary"]

0

Plan["Deluxe", "Europe", "none", "Mexico"]

2313.333333

Plan["Deluxe", "Europe", "premium", "China"]

5000

Plan["Deluxe", "Europe", "premium", "Hungary"]

0

Plan["Deluxe", "Europe", "premium", "Mexico"]

0

Plan["Deluxe", "NA", "discount", "China"]

0

Plan["Deluxe", "NA", "discount", "Hungary"]

0

Plan["Deluxe", "NA", "discount", "Mexico"]

2000

Plan["Deluxe", "NA", "none", "China"]

0

Plan["Deluxe", "NA", "none", "Hungary"]

0

Plan["Deluxe", "NA", "none", "Mexico"]

1000

Plan["Deluxe", "NA", "premium", "China"]

0

Plan["Deluxe", "NA", "premium", "Hungary"]

0

Plan["Deluxe", "NA", "premium", "Mexico"]

100

Plan["Luxury", "Asia", "discount", "China"]

0

Plan["Luxury", "Asia", "discount", "Hungary"]

0

Plan["Luxury", "Asia", "discount", "Mexico"]

0

Plan["Luxury", "Asia", "none", "China"]

2058.333333

Plan["Luxury", "Asia", "none", "Hungary"]

0

Plan["Luxury", "Asia", "none", "Mexico"]

0

Plan["Luxury", "Asia", "premium", "China"]

0

Plan["Luxury", "Asia", "premium", "Hungary"]

0

Plan["Luxury", "Asia", "premium", "Mexico"]

0

Plan["Luxury", "Europe", "discount", "China"]

0

Plan["Luxury", "Europe", "discount", "Hungary"]

7000

Plan["Luxury", "Europe", "discount", "Mexico"]

0

Plan["Luxury", "Europe", "none", "China"]

0

Plan["Luxury", "Europe", "none", "Hungary"]

3833.333333

Plan["Luxury", "Europe", "none", "Mexico"]

1666.666667

Plan["Luxury", "Europe", "premium", "China"]

0

Plan["Luxury", "Europe", "premium", "Hungary"]

10000

Plan["Luxury", "Europe", "premium", "Mexico"]

0

Plan["Luxury", "NA", "discount", "China"]

0

Plan["Luxury", "NA", "discount", "Hungary"]

0

Plan["Luxury", "NA", "discount", "Mexico"]

8000

Plan["Luxury", "NA", "none", "China"]

0

Plan["Luxury", "NA", "none", "Hungary"]

0

Plan["Luxury", "NA", "none", "Mexico"]

4000

Plan["Luxury", "NA", "premium", "China"]

0

Plan["Luxury", "NA", "premium", "Hungary"]

0

Plan["Luxury", "NA", "premium", "Mexico"]

400

Plan["Standard", "Asia", "discount", "China"]

0

Plan["Standard", "Asia", "discount", "Hungary"]

0

Plan["Standard", "Asia", "discount", "Mexico"]

0

Plan["Standard", "Asia", "none", "China"]

0

Plan["Standard", "Asia", "none", "Hungary"]

0

Plan["Standard", "Asia", "none", "Mexico"]

0

Plan["Standard", "Asia", "premium", "China"]

0

Plan["Standard", "Asia", "premium", "Hungary"]

0

Plan["Standard", "Asia", "premium", "Mexico"]

0

Plan["Standard", "Europe", "discount", "China"]

0

Plan["Standard", "Europe", "discount", "Hungary"]

0

Plan["Standard", "Europe", "discount", "Mexico"]

0

Plan["Standard", "Europe", "none", "China"]

0

Plan["Standard", "Europe", "none", "Hungary"]

0

Plan["Standard", "Europe", "none", "Mexico"]

0

Plan["Standard", "Europe", "premium", "China"]

0

Plan["Standard", "Europe", "premium", "Hungary"]

0

Plan["Standard", "Europe", "premium", "Mexico"]

0

Plan["Standard", "NA", "discount", "China"]

0

Plan["Standard", "NA", "discount", "Hungary"]

0

Plan["Standard", "NA", "discount", "Mexico"]

0

Plan["Standard", "NA", "none", "China"]

0

Plan["Standard", "NA", "none", "Hungary"]

0

Plan["Standard", "NA", "none", "Mexico"]

0

Plan["Standard", "NA", "premium", "China"]

0

Plan["Standard", "NA", "premium", "Hungary"]

0

Plan["Standard", "NA", "premium", "Mexico"]

0

ShippingPlan["Deluxe", "Asia", "China"]

17000

ShippingPlan["Deluxe", "Asia", "Hungary"]

0

ShippingPlan["Deluxe", "Asia", "Mexico"]

0

ShippingPlan["Deluxe", "Europe", "China"]

14686.66667

ShippingPlan["Deluxe", "Europe", "Hungary"]

0

ShippingPlan["Deluxe", "Europe", "Mexico"]

2313.333333

ShippingPlan["Deluxe", "NA", "China"]

0

ShippingPlan["Deluxe", "NA", "Hungary"]

0

ShippingPlan["Deluxe", "NA", "Mexico"]

3100

ShippingPlan["Luxury", "Asia", "China"]

2058.333333

ShippingPlan["Luxury", "Asia", "Hungary"]

0

ShippingPlan["Luxury", "Asia", "Mexico"]

0

ShippingPlan["Luxury", "Europe", "China"]

0

ShippingPlan["Luxury", "Europe", "Hungary"]

20833.33333

ShippingPlan["Luxury", "Europe", "Mexico"]

1666.666667

ShippingPlan["Luxury", "NA", "China"]

0

ShippingPlan["Luxury", "NA", "Hungary"]

0

ShippingPlan["Luxury", "NA", "Mexico"]

12400

ShippingPlan["Standard", "Asia", "China"]

0

ShippingPlan["Standard", "Asia", "Hungary"]

0

ShippingPlan["Standard", "Asia", "Mexico"]

0

ShippingPlan["Standard", "Europe", "China"]

0

ShippingPlan["Standard", "Europe", "Hungary"]

0

ShippingPlan["Standard", "Europe", "Mexico"]

0

ShippingPlan["Standard", "NA", "China"]

0

ShippingPlan["Standard", "NA", "Hungary"]

0

ShippingPlan["Standard", "NA", "Mexico"]

0

SalesForecastUnits["Deluxe", "Asia", "discount"]

7000

SalesForecastUnits["Deluxe", "Asia", "none"]

5000

SalesForecastUnits["Deluxe", "Asia", "premium"]

5000

SalesForecastUnits["Deluxe", "Europe", "discount"]

7000

SalesForecastUnits["Deluxe", "Europe", "none"]

5000

SalesForecastUnits["Deluxe", "Europe", "premium"]

5000

SalesForecastUnits["Deluxe", "NA", "discount"]

2000

SalesForecastUnits["Deluxe", "NA", "none"]

1000

SalesForecastUnits["Deluxe", "NA", "premium"]

100

SalesForecastUnits["Luxury", "Asia", "discount"]

0

SalesForecastUnits["Luxury", "Asia", "none"]

2058.333333

SalesForecastUnits["Luxury", "Asia", "premium"]

0

SalesForecastUnits["Luxury", "Europe", "discount"]

7000

SalesForecastUnits["Luxury", "Europe", "none"]

5500

SalesForecastUnits["Luxury", "Europe", "premium"]

10000

SalesForecastUnits["Luxury", "NA", "discount"]

8000

SalesForecastUnits["Luxury", "NA", "none"]

4000

SalesForecastUnits["Luxury", "NA", "premium"]

400

SalesForecastUnits["Standard", "Asia", "discount"]

0

SalesForecastUnits["Standard", "Asia", "none"]

0

SalesForecastUnits["Standard", "Asia", "premium"]

0

SalesForecastUnits["Standard", "Europe", "discount"]

0

SalesForecastUnits["Standard", "Europe", "none"]

0

SalesForecastUnits["Standard", "Europe", "premium"]

0

SalesForecastUnits["Standard", "NA", "discount"]

0

SalesForecastUnits["Standard", "NA", "none"]

0

SalesForecastUnits["Standard", "NA", "premium"]

0

TotalRevenue["Deluxe", "Asia"]

5083000

TotalRevenue["Deluxe", "Europe"]

5083000

TotalRevenue["Deluxe", "NA"]

926900

TotalRevenue["Luxury", "Asia"]

347858.3333

TotalRevenue["Luxury", "Europe"]

3802500

TotalRevenue["Luxury", "NA"]

2095600

TotalRevenue["Standard", "Asia"]

0

TotalRevenue["Standard", "Europe"]

0

TotalRevenue["Standard", "NA"]

0

TotalCost["Deluxe", "Asia"]

1700000

TotalCost["Deluxe", "Europe"]

1683000

TotalCost["Deluxe", "NA"]

303800

TotalCost["Luxury", "Asia"]

255233.3333

TotalCost["Luxury", "Europe"]

2725833.333

TotalCost["Luxury", "NA"]

1512800

TotalCost["Standard", "Asia"]

0

TotalCost["Standard", "Europe"]

0

TotalCost["Standard", "NA"]

0

TotalProfit

9158191.667

 

Conclusion

It may take us a long while to find out the optimal solution if we are using pencil and paper and we may never be sure that we find the optimal solution.  With Solver Foundation Excel Add-in, we can optimize solutions in many different areas, e.g. finance, operation, investment, risk management, production…etc.

Microsoft Solver Foundation is a tool that every business decision maker would love when they do their business planning.  Not more guessing; not more trial and error.  We have the solution at our finger tips.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

Solver Foundation for Excel – Supply Chain Planning Part 1

Solver Foundation is a pure, managed code runtime for mathematical programming, modeling, and optimization. We can use it to develop optimization model in many areas, e.g. finance, production, supply chain, human resources… etc.

The Solver Foundation has an Excel add-in so we can build optimization model within Excel, an environment that most of us are comfortable.

Below is how the Solver Foundation Excel add-in looks like.

image

We are going to find out how we can define a model in the modeling editor.  The modeling editor has 8 different sections:

image

  • Sets are used as indexes in Parameters or Decisions.
  • Parameters are the “inputs” of the solver.
  • Decisions are the “outputs” or results of the model being solved.
  • Goals define the business objective that we are trying to achieve.
  • Constraints are where we define business logic / algorithms.
  • Directives allow us to control how solver should work or provide hints to solver.
  • Model shows the model in OML format. 
  • Log.

I am going to use an example that comes with the Solver Foundation Excel Add-in to illustrate how to develop a supply chain model that can maximize profit.  You can find the example after you download and install Solver Foundation.

Add Sets

  • Under the Sets tab, click New button

image

  • Enter Products in the Name:  box

image

Repeat the same procedures to add Areas, Promotions, Factories; so now we have 4 sets (indexes).

image

 

Add Parameters

  • Under the Parameters tab, click New button

image

  • Enter manufactureLoads in the Name:  box

image

  • Click on the radio button to bind data to this parameter.

image

  • Binding Editor dialogue is displayed
  • Select the appropriate range, i.e. Sheet1!$B$6:$C$9

image

  • Enable Range Includes Column Headers
  • Select Products at the Set column
  • Select Product at the Column Header column
  • Select ManufactureLoads at the Value Field.
  • Click OK button.

image

  • Add Manufacture loads for each product types. to Description

image

There are 5 more parameters in this model,

  • manufactureLoads – Manufacture loads for each product types.
  • factoryCapacity – Each factory has a capacity limit on how many products it can produce within a fixed period of time that we are planning on.

image

  • unitManufactureCost – Unit cost of each product manufactured.

image

  • transport – Shipping the products manufactured in a specific factory to a specific geographic area for sale has certain cost.

image

  • demandForecastPrice – A sales forecast on the price at which it will be sold of each product with each promotion type in every geographical area it is going to be sold.

image

  • demandForecastUnits – A sales forecast on the number of units to be sold of each product with each promotion type in every geographical area it is going to be sold.

image

We can have multiple indexes for each parameter, e.g. demandForecastUnits has 3 indexes.

image

So when it binds to data, it imports 4 columns.  The last column, demandForecastUnits, is the value field.
image

 

Add Decisions

Decisions are the output of the model, so we can also bind Decisions to Excel or just show the results at the Solver Foundation Results.  There are 6 Decisions in this model and they all output results to Solver Foundation Results

  • Plan – how many units of Products will be produced in Factories that will be sold in Areas with Promotions.
  • ShippingPlan – how many units of Products that are manufactured in Factories will be shipped into Areas
  • SalesForecastUnits – how many units of Products with Promotions that will be sold in Areas will be produced
  • TotalRevenue – total revenue of selling Products in Areas
  • TotalCost – total cost of selling Products in Areas
  • TotalProfit – total profit of a plan

image

Decisions can also be indexed with Sets, e.g. Plan is a vector with 4 Sets.

image

Decisions can also be a single value, e.g. TotalProfitTotalProfit does not have any Set so it is a single value Decision.

 

Add Goals

There is only one goal for this model, i.e. to maximize the TotalProfit.

image

So far, we added input (Parameters), output (Decisions), and objective (Goals) to the model.  Where are the business logic / algorithms?  Most of the algorithms are defined in Constraints. We are going to discuss in my next blog.  Please stay tuned!

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

Flaw of average (FOA) – Stochastic Model in Excel

So far, we used deterministic approach in my previous blogs, e.g. we specify the age that we want to retire, how many years of survival or the annual incremental rates.  These values are all predetermined; we can add more scenarios but all the scenarios are still determined by us.  These values may be our best estimate and most probable to happen.  However, there may be situations that are very rate but the consequence can be huge, e.g. 2008 financial market meltdown.  This is why we should consider stochastic model.

What is stochastic model?  There are many definitions but generally involve random variables.  Let me use a very simple example to illustrate.  Let’s say we want to estimate the cost of dental claims for a new client who have 2,500 employees. We have dental expense records for 1 million employee and the average claim amount per employee per year is $7,256.00.  So it is simple and the estimate total for 2,500 employee would be $18,140,000.

As I mentioned in my previous blog, Excel to handle flaw of average (FOA) – Data Table, the problem of average is that 50% of the outcome is bigger and 50% is smaller.  50% of outcome can be bigger?  How much bigger? We may like to find out more about our estimation.  This is where stochastic model would help. 

Let’s first populate all 1 million dental claims in column B and use column A as an index (lookup) column.

imageimage

We would randomly select the claims amount by generating a random number between 1 to 1,000,000 and use VLOOKUP to retrieve the corresponding claim amount.

=VLOOKUP(RANDBETWEEN(1, 1000000),$A$1:$B$1000000, 2)

Then we generate 2,500 records for our first scenario and the total claim is $18,467,408.17.

image

We repeat the same procedures to randomly generate 200 scenarios.

image

Now we have a distribution of total claim costs, then we can use Excel Percentile function to determine the percentile of of the total claim costs distributions.

=PERCENTILE($H$2503:$GY$2503,I2505/100)

image

The total claim costs at 50th percentile is $18,104,248 which is very close to our total claim costs based on the average claim ($18,140,000).  This distribution give us a better understanding on what would happen, e.g. there is only 1% chance that the total claim cost would be bigger than $19,698,418.

This is a very simple stochastic model, we may want to consider age, gender, family size, location or even use a more robust random generator when we develop a real life stochastic model.  However, I just want to illustrate what a stochastic model is and demonstrate how we can build a stochastic model in Excel.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

Flaw of average (FOA) – Excel Scenario Manager

In my previous blog Use Excel to handle flaw of average (FOA) – Data Table, I discussed what flaw of average was and how Excel Data Table could provide us a more comprehensive view about our business decisions.  However, Data Table has a limit, it only supports up to 2 variables.  Our world is quite complicated and quiet often, we need more than 2 variables.  This is why we have to use Excel Scenario Manager; it can support up to 32 variables.  Scenario Manager is a great tool to handle our demanding what-if models.

Let’s say we want to do a simple business projection.

image

The annual increment rates are our best estimate based on our experience.  But we want to study more scenarios and see what if blah, blah… happen.  That would be easy, all we have to do is to change the rates in column G.

image

We keep change the rates for all the scenarios that we have in mind; then we copy and paste the results to another worksheet.

Scenario Manager allows us to develop what-if analysis much easier and it is also very simple to setup.

  • We select Data tab and then select What-If Analysis
  • Click Scenario Manager…

image

  • Scenario Manager dialogue would be displayed.
  • Click Add… button

image

  • Enter Best Estimate into Scenario name:
  • Select $G$2:$G$6 in the Changing cells:
  • Click OK button

image

  • Scenario Values dialogue would be displayed
  • Click Add button to add more scenarios

image

  • Add 4 more scenarios by repeating the last 2 steps.

image

  • Now, we have 5 different scenarios.
  • Click the Summary… button

image

  • Select the Result cells:
  • Click OK button

image

Here is the result that prepared by Scenario Manager.

image

Scenario Manager can be quite useful when we do our business planning.  We want to study more scenarios and well prepare for what-if situations.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

What are LinkedIn Applications?

Everyone knows LinkedIn is a professional social networking platform; we can find people and information, we can market our business, products and services.  But there is more!    Do you know LinkedIn also offers a wide range of applications with its partners?  These applications allow us to better collaborate with our connections.

Polls

by LinkedIn
image “The Polls application is a market research tool that allows you to collect actionable data from your connections and the professional audience on LinkedIn.” quoted from LinkedIn

Reading List by Amazon

by Amazon
image “Extend your professional profile by sharing the books you’re reading with other LinkedIn members. Find out what you should be reading by following updates from your connections, people in your field, or other LinkedIn members of professional interest to you.” quoted from LinkedIn

Box.net Files

by Box.net
image “Add the Box.net Files application to manage all your important files online. Box.net lets you share content on your profile, and collaborate with friends and colleagues.” quoted from LinkedIn

Company Buzz

by LinkedIn
image “Ever wonder what people are saying about your company? Company Buzz shows you the twitter activity associated with your company. View tweets, trends and top key words. Customize your topics and share with your coworkers.” quoted from LinkedIn

Blog Link

by SixApart
image “With Blog Link, you can get the most of your LinkedIn relationships by connecting your blog to your LinkedIn profile. Blog Link helps you, and your professional network, stay connected.”

WordPress

by WordPress
image “Connect your virtual lives with the WordPress LinkedIn Application. With the WordPress App, you can sync your WordPress blog posts with your LinkedIn profile, keeping everyone you know in the know.” quoted from LinkedIn

SlideShare Presentations

by SlideShare Inc
image “SlideShare is the best way to share presentations on LinkedIn! You can upload & display your own presentations, check out presentations from your colleagues, and find experts within your network.” quoted from LinkedIn

Google Presentation

by Google
image “Present yourself and your work. Upload a .PPT or use Google’s online application to embed a presentation on your profile.” quoted from LinkedIn

Huddle Workspaces

by Huddle.net
image “Huddle gives you private, secure online workspaces packed with simple yet powerful project, collaboration and sharing tools for working with your connections.” quoted from LinkedIn

Projects and Teamspaces

by Manymoon
image “Manymoon makes it simple to Get Work Done with your LinkedIn connections. Share and track unlimited tasks, projects, documents and Google Apps – for free” quoted from LinkedIn

Events

by LinkedIn
image “Find professional events, from conferences to local meet-ups, and discover what events your connections are attending.” quoted from LinkedIn

The list of LinkedIn applications is constantly growing, so I often come back and still find good applications that make my networking more effective,

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

Why reinvent the wheel . . .

When we can get an business IT solution that has  already been developed, optimized, tested and even used by thousand or even million users, why do we still want to develop our own?

A client asked me to do a peer review on a proposal submitted by his IT consultant.  He wanted to replace his old DOS systems (Believe me, DOS still exists) because it virtually crashed every week and he had to pay a few thousand dollars every year to maintain it.  His IT consultant said it would take at least 3 months and around $10,000 to develop.  My clients was very excited because his IT consultant promised to deliver a Windows application that would support multi users concurrently.

I did a very quick review on his old DOS system; it is very simple and basic, i.e. customers information, invoice, inventory…etc.  I did a quick presentation of what cloud computing was and then I jumped to demonstration.

My client couldn’t believe what he saw.  It covers everything he wants plus a lot more that he didn’t dream of, e.g. accounting, CRM, inventory, purchase, and sales.  He can access it anywhere, anytime, he doesn’t have to stay in his office until 2 am in the morning.  After I signed him up for the free trial. he can use it right away; he doesn’t have to wait for another 3 months.  But there is one thing that he was very uncomfortable, the price!  He couldn’t believe he only had to pay just over $100 per month for 3 concurrent users and it already includes hardware, software and future supports.  He asked me why there would be anyone willing to pay for $10,000 to develop a new software when you can get a software that has 10 times more features for a fraction of the cost.  He even did the number crunching himself and concluded that $10,000 can cover  up to 10 years of premium.  He said it was just too good to be true.

I spend the whole afternoon to show him the pricing structure of various cloud applications from Google Apps Marketplace, Microsoft Business Productivity Online Standard Suite, Oracle CRM On Demand, Salesforce and even some of the free applications on LinkedIn,  I also showed him some of the successful stories, e.g. Dell, Equifax, and U.S. Department of Agriculture.

Unfortunately, he still prefers to have his IT consultant to develop his own on premise software because it was too good to be true.  So I left him my question, When we can get an business IT solution that has already been developed, optimized, tested and even used by thousand or even million users, why do we still want to develop our own?

Why reinvent the wheel?  I was a little bit lost when I left his office; what else I can do / say to convince him?

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!