# Optimization Problem in Power BI using R scripts: Part1

Posted on Jul 19, 2017

There are some traditional problems in most of industries like how we can reach maximum profit, sales, production or how we can minimize our cost and loos. These types of problems can be seen in different areas such as microeconomic and company management, such as planning, production, transportation, technology . Although the modern management issues are ever-changing, most companies would like to maximize profit or minimize the costs with limited resources.  To explain what is optimization problem let me provide some examples:

## Production Company

Imaging that we have a company which produce two different products like Product 1 and Product 2 they want to know how much of product1 and product2, they should produce to maximize theri profit. they know that if they produce one tonne from product 1, they will earn 20 NZD and if they produce 1 tonne from product 2 they will earn 30 NZD. so we need to know how much of product 1 and product 2 we need to produce to maximize the profit.Despite this objective they also have some constraint like 1- Each tonne of Product 1 consumes 30 working hours, and each tonne of Product 2 consumes 20 working hours. The business has a maximum of 2,700 working hours for the period considered. it is humman resourece constraint. 2-another limitation is about the production hours, to produce one tone of product 1 weneed about 5 hours and for product 2 we need 10 hours. But we just have 850  machine hours. That is a constraint regarding the availability of machines that produce products. the linear programming help us to solve this problem (I will show how to solve it in next post via R codes). Other examples would be selecting the product mix in a factory to make best use of machine- and labor-hours available while maximizing the firm’s profit.  Picking blends of raw materials in feed mills to produce finished feed combinations at minimum cost.

## Transportation

A publisher has orders for 600 copies of a certain text from San Francisco and 400 copies from Sacramento. The company has 700 copies in a warehouse in Novato and 800 copies in a warehouse in Lodi. It costs \$5 to ship a text from Novato to San Francisco, but it costs \$10 to ship it to Sacramento. It costs \$15 to ship a text from Lodi to San Francisco, but it costs \$4 to ship it from Lodi to Sacramento. How many copies should the company ship from each warehouse to San Francisco and Sacramento to fill the order at the least cost? Or determining the distribution system that will minimize total shipping cost from several warehouses to various market locations

## Scheduling

Schedulingschool buses to minimize the total distance traveled when carrying students. Scheduling tellers at banks so that needs are met during each hour of the day while minimizing the total cost of labor Developing a production schedule that will satisfy future demands for a firm’s product and at the same time minimize total production and inventory costs

## Allocating Humman Resource

Allocating police patrol units to high crime areas to minimize response time to 911 calls.

## Solution

There are many others areas and industries that have the same problesm. in all of above examples we have an Objective which we want to minimize or/and Maximize that. also we have some constraints that we should consider while we solving the problem. one of the great approach to cope with these types of problems is to employ “Linear programming”.

the main structure of the linrar programming is as below

Min/Max :Objective (like maximise the profit, production, or sales)

Constraints: c1 (like number of the labour in a company, cost limitation and so on)

c2

c3 and so on.

There are many software that help us to solve thes type of problems like MATLAB GAMS, and R

Also there is possiblity to do this in Excel using the SOlver component there (which has some limitation on number of constraints) In this new series, I am going to show how we can solve an optimization problem using R and Power BI. First I will talk about the main concepts on Linear programming in next posts via an east example.

http://wps.prenhall.com/wps/media/objects/2234/2288589/ModB.pdf https://en.wikipedia.org/wiki/Linear_programming https://web.sonoma.edu/users/w/wilsonst/Courses/Math_131/lp/default.html

Dr. Leila Etaati is Principal Data Scientist, BI Consultant, and Speaker. She has over 10 years’ experience working with databases and software systems. She was involved in many large-scale projects for big sized companies. Leila has PhD of Information System department, University of Auckland, MS and BS in computer science. Leila is Microsoft Data Platform MVP.