Linear Programming, Labor Optimization, and Capital Investment

​In my opinion, within FP&A, the understanding of operations management and analysis is far undervalued and mostly nonexistent.  CAPEX and capacity planning requires an understanding of resource scarcity and constraints on operations systems.  Utilizing linear programming is a great way to get an understanding of how these constraints impact costs and investment.  While I used Solver here, due its easy availability, it is a very basic computational application and not suitable for many more granular operational applications.  That said, from a higher-level planning view for budgeting and CAPEX it is suitable for test runs and analysis.  Hopefully, you will find that this also furthers communication between finance and operations for better analytics. 

Just a side note:  You invest at the Bottleneck, anywhere else is superfluous to operational effectiveness

---

​Linear Programming, sometimes referred to as integer programming, is simply optimization of resources, minimizing or maximizing.  In a business or operation this is typically minimizing cost, which is correspondingly maximizing efficiency. 
 
From a math perspective, this is closely related to a set of linear equalities for which the number of equations must equal the number of unknown variables for a unique solution.

​ eg                        3 equations, 3 unknown variables
 
                              2a + 3b + c = 18
                              a + b = 5
                              c – a = 3
 
solving: a = 3, b = 2, c = 6  

If we were seeking exact equalities, we would need to match the number of equations to the number of variables for a unique solution to each variable.  If you have more equations than variables, then you have considerably more possible solutions to the variables, in fact it could be infinite.  We could reduce the number of possible solutions by offering ‘constraints’, or limits on what we need from our variables and equations.  For example, if we said that we had to have a solution for our variables that was comprised of only integers, then we would eliminate all answers that were not whole numbers.  We can now expand on this idea of constraints to real-world scenarios.
 
In a real-world scenario solving for a set of equations would not need to involve exact equalities.  As opposed to a+b = 5, above, we might want to offer a range of values so that a+b <= 5 and we eliminate c-a = 3 altogether so that we solve for only
 
                              2a + 3b + c = 18
                              a + b <= 5
 
As mentioned, with two equations and three unknowns, we have many possible solutions, even if we were to limit all solutions to be whole numbers and no zeros. 
 
Some Examples: 
a = 1, b = 1, c = 13
a = 1, b = 2, c = 10
a = 1, b = 3, c = 7
a = 1, b = 4, c = 4
a = 2, b = 1, c = 11
a = 2, b = 2, c = 8
a = 2, b = 3, c = 5
a = 2, b = 4, c = 2
…
 
But, say we want to minimize ‘c’.  Then we would further restrict (constrain), our system of equations toward even fewer possible solutions.  This is exactly what we do with linear equations for linear programming analysis for business situations.
 
We try to maximize the profit or minimize the costs relative to a manufacturing process, labor hours on a project, minimal production costs relative to inventory costs, transportation routes to minimize mileage, and even mixtures of materials for products relative to raw materials carried in inventory. 
 
For business planning problems with limited resources, employees, inventory, equipment, and so on, the availability of resources and the uses of the resources provide the constraints, you need to construct the linear equations.
 
Excels Solver is quite effective for many planning problems if you become familiar with its usage and setting up the equations and constraints.
 
A quick simple example before getting a bit more complex.
 
Say we have disaster clean up needs after severe storms hit North Carolina and Virgina.  You have crews in South Carolina, Georgia, and Florida that are available for the work, but you want to minimize your costs by sending the optimal number of crews by location to their destination.  You might put this into a Cost Matrix, having determined various travel and housing costs for each team if it was sent to a specific location like so:

​A closer look at the Cost Matrix (top) and Solutions Matrix (bottom) that will be filled.

​The available crews from GA, SC, FL are in the ‘Available’ column, and the needed for each of NC, VA, is across the ‘Needed’ row.  The weekly cost in $1,000s is in the matrix. 
Using a few Excel formulas for our Solutions Matrix and Solver we can find an optimal solution. 
 
Our Excel Formulas:
 
We need to put in the SUM formula for each column in the Solutions Matrix to keep the total of Supplied for a comparison against the total Needed from the Cost Matrix.
 
We also need to SUM the row totals for Used for a comparison against the Available.
 
Finally, we need the SUMPRODUCT of the two matrices for the total cost … seen below with final answers.

The Solver setup:
 
Solver ( Data Tab > Solver) sets our objectives, variables, and constraints.
 
The Objective, is to minimize our cost, ‘Set Objective’ and ‘To:’.  In this case, the cost is the SUMPRODUCT cell.

We need to find the solution by changing the ‘drivers’ for the costs … the number of crews sent (the red box).  All of these cells are selected for the ‘By Changing Variable Cells’ selection. 
 
In the ‘Subject to Constraints’ we can further restrict the settings for our calculations.  For example, the solution for the number of crews needs to be set to be an Integer value. 
 
The row of ‘Supplied’ should be equal to the row of ‘Needed’.  The column of ‘Used’ must be less than or equal to the column of ‘Available’.  This is why we required the SUM formulas to be used in the columns and rows of the Solutions Matrix.
 
With this setup we select Solve.

​The solution is fairly simple, but it is always good to start with a basic example.  But we could add further constraints.  Perhaps we do not want to commit more than 5 teams from any one area to the same location in need. 

​While we have the same usage, our distribution to the areas is now constrained by the 5-team limit.
 
 
An example that is a bit more involved with CAPEX considerations
 
 
Manufacturing and Labor, choosing between two different line setups and a Subcontract (outsource).
 
We are going to plan out our annual workforce for a manufacturer relative to the forecasted units of sales by minimizing costs while meeting labor requirements.
 
We are going to consider:
 
Quarterly production
Choosing between

• line no. 1 with a throughput of 100 units/hr and 8 employees
• line no. 2 with a throughput of 200 units/hr and 10 employees
• Subcontract (outsource) with costs as hourly for comparison, 150 units/hr

Line choice will also reflect regular hours and OT hours, both capped
Remaining hours will be considered for Subcontract
 
Finally, there will be an inventory carry cost.

---

​There is something to be said about Solver.  It can be very quick to work out simpler, well-throughout linear programming problems, but it is limited to 200 total cells for variables and constraints.  For this reason, monthly planning can quickly consume the cell space limit and should, if possible, be aggregated into quarterly planning.  As you can see above, this is a fairly simple problem of choice by quarters and it uses 35 cells.  More complex linear programming should use R or Python.

---

A closer look at some of the inputs

​We will have two line setups from which Solver will choose the least expensive to run given forecasted demand.
 
We will take the line setup as a whole set and calculate the quarterly cost to operate the lines under normal 160 hr/month conditions for the required employee count for each line and the hourly employee wage of $20.
 
We will limit regular hours to 480 for the quarter, (160 monthly hours * 4).  We will limit OT hours to half of this total, 240 OT hours for the quarter.  Any hours that exceed this will be forced into the Subcontract since all demand units will be produced.

​Any hours can be considered for either OT or Subcontract by Solver.  To offer a comparison, we will price the subcontract out at an hourly breakdown so it can be associated with its approximate throughput of 150 units/hour.  We see that Subk hrs are $180.  For reference, line no.1 has 8 employees at $20.00 hour, or $160/hr to operate line no.1 at 100 units/hr and line no. 2 has 10 employees at $20.00 hour, or $200/hr to operate line no. 2 at 200 units/hr.
 
Let’s look at the choices and constraints.

​Getting Solver to choose one line over another was quite interesting.  Solver does not like ‘IF’ statements.  To workaround this I set up two binary columns (‘line no1’, ‘line no2’) and three production constraint columns (‘line no1’, ‘line no2’, ‘choice’).
 
‘Line no1’ production constraint equation.
 
(regular hours + OT hours) * line no1 throughput + Subk hrs * Subk throughput

​The same equation is setup for line no2.  These offer a relationship between hours and throughput capacity.
 
Now, the binary choice between line no1 and line no2 forces a 1 or a 0 value for each column, while each row is constrained so that the total of the row must equal 1.
 
Finally, a SUMPRODUCT is used for the production constraint ‘choice’.

​​close up

​The SUMPRODUCT formula and Binary constraints offer a workaround to the ‘IF’ statement. As you can see from the columns, the Solver did choose line no1 for the lowest demand month.
 
The Solver setup

​Our forecasted unit demand in column G is set equal to our Demand constraint in column O.  We use this constraint column to calculate the demand corresponding to production and inventory.

​This constraint allows the Solver to stock up inventory to prepare for the next period.  But, just like the labor, there is a charge associated with inventory.  The quarterly inventory carry charge is $0.50 / unit.
 
We could use this setup to assess different throughputs for line no1 and line no2, different carry costs, Subk contract costs, and levels of demand.  The Solver will choose the minimum cost arrangement.  If we were deciding between another piece of equipment or whether to oursource some products, this setup offers a quick plug and go.
 
As an example, what subcontract price might we see Solver begin to shit to Subk usage?
 
Given a choice between lines no1 and no2 and the Subcontract, we wouldn’t see subcontract usage until the approximate hourly cost breakdown for the subcontract is at $70.00

​And this total cost would only be ~$3.7k less than our other solution.  Could you venture deeper into this to analyze capabilities relative to line equipment, demand, and overall costs?
 
 
Transportation Delivery from Regional Hubs
               ~Solver and rounding tolerance
 
We can use linear equations and Excels Solver to calculate how many trucks from each regional hub are to deliver to each city in order to minimize the total number of miles. 
 
 
Each city has a need for a certain number of deliveries from the warehouses for resupply relative to the number of store locations, the “Demand”. The number of trucks located in each of the regional warehouses, the “Available”, is given by the locations in Chicago, Dallas, Atlanta, and Philadelphia.
 
We would like to minimize the number of miles, thereby minimizing travel costs.
 
Below is the basic information as a matrix.

​Rearranging the matrix and setting up a helper matrix.

​The ‘Trucks’ matrix will be used by Solver to input the trucks from each regional center to their destination.  For example, the 5 in the upper left cell is the 5 trucks from the Chicago hub sent to the Des Moines retail locations that demand 5.
 
The ‘Miles’ cell is the SUMPRODUCT of the ‘Miles Between Cities’ and ‘Trucks’, the cell we are seeking to minimize.

​We need to restrict our solutions to integers (we can’t have half of a journey) and I am restricting the number of trucks to less than or equal 8 from a regional hub to a city.
 
When I run this again, the miles will vary just slightly.  While we tell Excel to use integers as a solution, this is not always exactly possible and a tolerance is set to give Solver some flexibility for solutions.
 
Within the ‘Options’ selection you can adjust the precision and Integer Optimality % to 0.0000001 and 0 respectively but you still might see some relative to how many cycle attempts Solver used to arrive at a solution.  Keep this in mind if you need very exacting answers.

​Example of tolerance:
              
               We see that this run is 89,870 miles.  In the next run,

​we see that it is 89,990 miles.  Yet, the totals all align.  The totals aren’t off by much, but it is good to note that this does occur.

​What we might want to consider, is whether an adjustment of the supply of trucks from each hub would have more significance than the total trucks allowed to travel to each city.
 
If we were to rearrange the supply from the regional hubs so Dallas is reduced to 20 and each of Chicago, Atlanta, and Philadelphia are increased by 10 we see a small decrease in miles.  Chicago uses its full supply capacity.

​If we were to instead increase the allowed number of trucks per location to increase from 8 to 10, we see a 10,000 miles decrease.

Now, I did just come up with the cap on the max of truck loads to show another perspective on constraints and perhaps you wouldn’t use a cap.  But perhaps you limit the number of trucks per each location due to maintenance rotations or for risk reasons of not having too many assets in one location or for some reason.  It is not completely out of the realm of possibility.  Reasoning aside it is a good example of how constraints impact outcomes and how understanding how to use linear programming and constraints might further assist in strategic development and resource planning.
 
 
 
Closing
 
 
In my opinion, within FP&A, the understanding of operations management and analysis is far undervalued and mostly nonexistent.  CAPEX and capacity planning requires an understanding of resource scarcity and constraints on operations systems.  Utilizing linear programming is a great way to get an understanding of how these constraints impact costs and investment.  While I used Solver here, due its easy availability, it is a very basic computational application and not suitable for many more granular operational applications.  That said, from a higher-level planning view for budgeting and CAPEX it is suitable for test runs and analysis.  Hopefully, you will find that this also furthers communication between finance and operations for better analytics.