DATA = Gold Mine

Organizations make business decisions every day. These decisions may range from how much discount should be given to clients, how much raw materials we need, marketing promotion…etc.  They can have a direct impact on costs and revenue to the organization. For example, giving a customer a discount may increase revenue but may not help the bottom line.

How do we make business decision?  Based on experience? guessing?  Unfortunately, not all of us have the right business instinct or it would take a long time to build up such experience.  How can we make a better decision?  Information!  We need strategic information to make a better decision!  Accurate, consistent, timely information can lead to lower cost, higher revenue and better customer satisfaction.

Reducing Costs

Once the information backlogs are eliminated, we can easily identify the root causes of any inefficiency in our operations and take prompt action to fix the problems e.g. building model to optimize business processes; identifying wasted resources and reducing inventory costs.

Increasing Revenue

“Who are our best clients that delivered 80% of our profits?”, “What are our most effective marketing channels?”  Such information allows us to micro segment our markets, gain an edge over the competition and empower the sales force to focus on high profitability customers and products.

Improving Customer Satisfaction

We can dramatically reduce the time to answer our clients’ questions, better understand our customer behaviors and hence provide customized services and even provide information to our clients so that they can make smarter decisions.

Challenges

In God we trust; all others must bring data. – W. Edwards Deming

By now, we should understand the importance of information.  Unfortunately information is not always readily available to every decision maker.  They have to wait or even guess!  What are the challenges? We have many business applications; e.g. finance, human resources, customer relationship management, supply chain, manufacturing.  They contain vast amount of data; however, data is not information.  It is a long way to transform data into information!

Garbage in, garbage out

Data quality is always the biggest challenge.  Poor data quality can jeopardize decision makers’ ability to make a better business decision or even make the wrong decision.  This problem could take a lot of effort to fix; so it is far better to implement a proper data strategy to avoid such problem.

Too slow

Each system generates many reports that deliver a lot of information to the decision makers.  However, if we need extra or the latest information, then we have to rely our IT department to extract more information for us.  We all know how long it would take!  And it may take a few cycles to understand where the real problem is.  It is simply too slow and we don’t always have the time to wait!

Inconsistent

Some of our power users know how to use Excel or other reporting tools to extract data directly from the enterprise applications.  Great!  They can retrieve the latest information in real time.  However, it leads to another issue, i.e. consistency.

“How many people are working for our organization?”  It is a very simple question, but you may be surprised to find out how many different answers you can get; e.g. should I include employee with disability pay / maternity leave? how about contractor? If an employee who works 3 days a week; should we count 1 or 0.6?  Even such simple question can lead to so many different, inconsistent results.  Imagine what you would get if we want a more sophisticated report!

Lack of 360 views

Our organization has implemented more and more systems over time and each system has captured different data.  However, only a few employees may have access to all data; our database administrators.  For example, account manager may only have access to sales data but he may like to gather other information, e.g. customer satisfaction, inventory level, and other market information before he heads to his client’s office.  He may send an email to his department head; and his department head would forward his request to other department heads who would subsequently pass the request to their analysts.  Good luck if you can gather all your information in 2 weeks time!

Business Value of Data

What is the business value of data?  It is enormous!  Data in our system is the gold mine in our office.  It would help us to reduce cost, increase revenue / productivity and improve customer satisfaction!  What is the business value of a loyal and satisfied customer; especially in today’s downhill economy?

Ask yourself the following questions:

  • Do you find it takes too long to get the information that you need to make a business decision?
  • Do you receive consistent information, no matter who prepare the information?
  • How many places do you need to go to get the answer?
  • Do you know where to look for the information?
  • How often is data rekeyed?
  • How much information do you need to manually aggregate?
  • How much data needs to be manipulated to get to an answer?

Do you need a better reporting system that can transform data into a good mine?  Most companies said “No, we already have a management information system.  If it ain’t broken, don’t fix it”  However, a well implemented business intelligence solution could give your competitive advantages and empower you to grow stronger!  So act now and benefit tomorrow!

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

Are you a decision maker who often do planning, scheduling and optimization?

Our business world is extremely complicated and anyone who did modeling before, would tell you that even the smallest model can have up to 100s variables and constraints; medium size models is in the range of 10,00s to 100,000s.  This is just too complex for “pencil and paper” and manual methods.  Fortunately, Microsoft Excel has offered a powerful optimization tool since 1991, Solver add in.  It has helped many decision makers to do business planning, project scheduling and resources optimization.  If you haven’t heard about Microsoft Excel Solver add in, you can find out more from my previous blog.

It is easy to learn Microsoft Excel Solver since it is tightly integrated with Excel and it uses Excel structures, e.g. cells and cell references that you are familiar to formulate and implement optimization models.  I managed to coach a lot of analysts to build their first model within couple hours.  It is also very powerful and flexible that you can use it to handle financial model, supply chain model and even project scheduling.

However, spreadsheet model has a few disadvantages.  It is difficult to integrate with existing legacy systems, implement version control and identify where the changes are, document and debug the model.  Excel Solver is a great tool for ad hoc modeling; however if we want to migrate it to the production environment, there can be many challenges that we have to resolve.  How can we share our model with other colleagues and not let them accidentally change it?  Can we automatically feed the results to our finance / supply chain system?  How can we document the model so that other colleagues understand our works?

Microsoft Solver Foundation (MSF) is the answer that we are looking for.  Key features include:

  • Excel add in – a complete modeling environment that allow us to develop models.
  • Application Programming Interface (API) – allows us to integrate our models into the legacy systems.
  • Optimization Modeling Language (OML) – an equation based modeling language.
  • Solver plug-in – we can develop or purchase external solvers that can hook up to MSF.
  • Parallelism – MSF will manage all threading, many-core, synchronization, and scheduling around the solvers.
  • MSF has a collection of solvers and it would automatically determine the best solver for us.
  • Simulation – We can use stochastic programming to run Monte Carlo simulation.

In this blog, I would only focus on Excel add in which includes:

  • Modeling Editor – simplify the modeling experience
  • Excel Call Binding – bind data to and from the model
  • Deployment – once we develop and test the model in Excel, we can deploy it directly into C# application

I am going to use an airline route allocation simulation model to demonstrate how MSF Excel add in works.  The objective of this model is to minimize operational cost.

It is a small model, so not a lot of data.

clip_image001

  • First, we bind data from an Excel sheet to the model through the Parameters tab.  You may notice that we can now specify description and the type of parameters, e.g. Integer, Real, InterNonnegative.

clip_image002

  • Then we bind the results of the model being solved to Decisions tab.

clip_image003

  • So far we don’t need to learn anything new to define Parameters and Decisions.  But we now need to learn how to use Optimization Modeling Language (OML) when we define the business goals in Goals tab.  OML is designed exclusively for modeling; it includes identifiers, comments, string literals, Boolean constants, and arbitrary numeric literals.
  • Our goal is to minimize operational cost which includes 2 components, shipping cost and refund.

clip_image004

  • We add restrictions to the decisions in the Constraints tab.

clip_image005

  • As we defined the model through various tabs, MSF Excel add in would automatically build the model for us and we can review the entire model in OML format at Model tab.  We can add extra descriptions to the model so other users can understand how it works.

clip_image006

  • Once the model is ready, we can click the Solve button to start the solver.

clip_image007

  • And the results publish to the specified range.

clip_image008

  • We can also review the output log.

clip_image009

  • We can deploy the model to production environment and integrate with other legacy systems after we fine tune it.

clip_image010

Microsoft Solver Foundation is a great product; we can integrate our models into legacy systems and take advantage of our multi core CPU.  We have to learn OML; however it is a simple modeling language and quite straight forward to manipulate.  So if you often need to build business models to optimize profit, schedule and other resources; you should download its express edition and see what it can offer you.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!