Linear Programming: Production Planning and Inventory Tracking with Excel Solver

Enjoyed this content & want to support my channel? You can get the spreadsheet I build in the video or buy me a coffee! Links below:
Buy me a coffee: www.buymeacoff...
Buy complete spreadsheet: www.buymeacoff...
Buy me an item from my wishlist: www.buymeacoff...
Given demand estimates over several months, how much to produce and carry in inventory each month in order to minimize production and holding costs?

[@dusankollar7005]

Awesome! Please make more videos like this! :-)

[@ap2290]

Is it necessary to have inventory (row 6) as a decision variable? Couldn't you just have production as the decision variables only, then set a constraint for the had + made - sold row

[@TallysYunes]

You are correct. I like having the inventory variables for better readability of the model, but it's up to you.

[@ap2290]

@@TallysYunes thank you. Could I PM you to somehow receive your assistance on a similar MRP production and inventory planning problem? There are some differences but the hardest part is the problem is a larger scale so it adds to the complexity and it’s a struggle to get things set up correctly with solver

[@TallysYunes]

Is this homework?

[@ap2290]

@@TallysYunes no, CI project at work

[@SardsSards-p7j]

@@TallysYunes per checking, not using Inventory as a decision variable, I am getting 896K as total cost vs 889.5K with the Had + Made - Sold = Inventory constraint in place.

[@pradyumnkhanna8094]

Hello Tallys, very informative video... could you also tell how can we make a graph by using these values

[@TallysYunes]

What kind of graph?

[@robertosei-danso5729]

How do you formulate this with two plants?

[@TallysYunes]

If by "two plants" you mean two different products whose inventories have to be tracked, you can simply duplicate the variables and repeat the same set of constraints for each product individually.

[@robertosei-danso5729]

@@TallysYunes It's more transshipment in my case. Solver cannot find a solution.

[@TallysYunes]

Check out the transshipment example on my playlist (if you haven't yet) and double check that all your constraint formulas are correct. If it can't find a solution, there's at least one constraint that is being too strict.

[@rambabugupta5812]

Optimisation For its future marketing campaign, the bank has allocated a budget of ₹1,50,000 and has also decided to segment the customers based on their marital status and educational background. Also, notably, the cost incurred by the bank for a one-minute call to any customer is ₹10. Considering all these factors, you, as a consultant, need to provide the analysis to the bank regarding the number of calls to be made to each customer segment('Customer segment' is explained below) such that the total number of customers opening the term deposit account is maximised. Customer Segment: In the dataset, the customers are segregated based on their Marital status and educational background. 'Marital status' and 'educational background' has three categories as follows. MARITAL STATUSEDUCATIONAL BACKGROUNDSingleBachelorsMarriedMastersDivorcedDoctorate Each combination of marital status and educational background is considered as a customer segment. Examples: ‘Single - Bachelors’ is considered as one segment. ‘Single - Masters’ is considered as one segment. Similarly, ‘Married - Masters’ is considered as one segment, ‘Married - Doctorates’ is considered as one segment and so on. The Conditions of the Bank: The bank is concerned about the overall customer diversification. It wants to ensure that it reaches out to all the customer segments. For this, it has provided you with the following information to include in your analysis: From each customer segment(‘customer segment’ as explained above), at least 50 customers need to be contacted. The total number of calls made to each customer category should meet the minimum number of calls as mentioned in the following table: Bachelors400Masters500Doctorate600Married600Single300Divorced350 The total number of conversions of the following customer categories should match a minimum number as mentioned below: Bachelors120Masters120Doctorate120Married150Single150Divorced100 Main Objective of Optimisation Problem Within the conditions given above, you need to estimate the number of calls needs to be made for each customer segment such that the total estimated no of converted calls for the future marketing campaign is maximised.

[@nazeefhossain678]

What if there is a shortage due to limitation of production capacity and there is a lost goodwill cost associated with every unit of shortage? How to balance inventory in that case?? Help would be highly appreciated.

[@TallysYunes]

You need to create a new variable per month, say Z1, Z2, etc. This variable will capture how much shortage there was each month, that is, if demand was greater than availability of product, Z captures the difference (what you call the shortage). Then you can multiply Z by the goodwill cost in the objective. The trick is how to insert Z in the model and relate it to the other variables. I'll leave that as an exercise for you :-)

[@nazeefhossain678]

@@TallysYunes I'm actually stuck in that part. Adding z in the model and relating it to the other variables. :'( Ending Inventory= Beginning Inventory+Production-(Demand-Shortage) . Is this equation correct for this model? Assuming, that a demand a lost to shortage can not be satisfied in the future.

[@TallysYunes]

This is not going to work. If (begining inv + production - demand) is not equal to zero, it could be either positive or negative. If it's positive, it's inventory. If it's negative, the absolute value of that negative number is the shortage. So you want a mechanism to capture how far from zero the quantity (beginning inv. + prod - demand) is, in either the positive or the negative direction. You can do this by using a trick similar to what I explain in my Goal Programming video here: kzbin.info/www/bejne/pmXVdYGAjdyraK8

[@lindohlily]

Sir, what if you are not given the inventory capacity but the cost per unit of unsold inventory is given?

[@TallysYunes]

The inventory left over at the end of each month is the unsold inventory. The cost you talk about is just like the holding cost I talk about in the video. You pay for it in the objective function like I explain.

[@kostastefanidis574]

Hello Tallys, nice video. Thank you. I have a question regarding the Had + Made + Sold. If we do not make that constrained to equal inventory, solver will not find a solution. Do you have a good way of explaining why that it? I mean, why can we not leave out that constraint?

[@TallysYunes]

Hi Kostas. One way to think about it is to keep in mind that all constraints that are present in the the mathematical model need to be represented in Excel. If we omit that, the Excel model will be missing a piece of the math. The purpose of the formula Had + Made - Sold = Inventory is to create the relationship between inventory and production each month, and also to connect consecutive months via the inventory variable. These inter-relationships are present in real life and need to be mirrored in the math (and Excel).

[@souadsaoud9979]

@@TallysYunes can you partage with me the this work excel. thanks [e-mail]

[@TallysYunes]

@@souadsaoud9979 You can easily make one just like it. Type in the initial data just like you see it in the video, and all the formulas that need to be typed in afterward are explained during the video.

[@random-kc8gx]

lets say a machine makes product a in x minutes and product b in y minutes, given profit and costs, time etc for each item, is there a way to extract the numbers of each of products to be made.

[@TallysYunes]

Yes. Is this homework?

[@random-kc8gx]

@@TallysYunes haha no, i want to use this for our actual business that we run, just started using excel for our numbers!

[@TallysYunes]

If you can send me more specific details I can try to point you in the right direction. Do you have any knowledge about modeling optimization problems? Email me at [e-mail].

[@random-kc8gx]

@@TallysYunes Thanks.a bunch, ill send an email with more details. Sorry not familiar with modelling optimization but sounds interesting, ill take a look.

[@DS.99]

Could you please tell whether this is level strategy or chase strategy?

[@TallysYunes]

Never heard of these terms. Can you provide a reference where they are defined?

[@magdalenechee3425]

Hi there, may i know what should i do if the information im provided with is the staff salary instead of the production cost? It is also stated that one staff can produce 20 units of demand.

[@TallysYunes]

I'd need more information. Given only what you said, this probably means you're minimizing total salary, but there are other details needed. Is there a limit on staff available each month? Is there a budget on total salary? etc.

[@notaCHEEter]

@@TallysYunes I think we are working on the same problem. there's no budget on total salary. however, there will be no fires or hires during the period. (aka manpower remains consistent across the period) The goal would be to minimise total cost (manpower and storage costs)

[@TallysYunes]

Simple change to the objective. The inventory cost part remains the same and the production cost part gets translated into the salary cost (how much does it cost salary-wise for each unit of product produced?).

[@notaCHEEter]

@@TallysYunes each worker is paid $1,800 per month and can produce up to 20 units per month. However, there is 1 month of paid leave for every 11 consecutive months of work.

[@TallysYunes]

How many workers do you have? What's the planning horizon? What's the given monthly demand? What are the inventory costs? How are inventory costs charged?

[@rambabugupta5812]

Sir, Please provide solution using excel solver

[@patchrapatulyaprawat1273]

Could you please use Solver for Wagner within Algorithm

[@TallysYunes]

What do you mean exactly? Is there an example you're trying to understand/solve?

[@sajadabdulcader5337]

i was able to get 857,500 as cost. I did calculate the closing stock just as you did. I was solving for ONLY the production. The constrain i replaced was that the calculated closing stock=solver closing stock . Instead the calculated closing stock was made to have a value more than 0. the production looks like this - month 1=10,000 month2=20,000 month3=10,000 month 4=20,000. the total cost calculated is 857,500 and inventory held on the last month is 0. pls can you check ?

[@TallysYunes]

Typing your solution in my sheet gives a cost of 897,500. It could be that there is either a mistake in your cost formula or the production and inventory costs per unit that you are using are different from mine.

[@jassiemrobinson6038]

If capacity isn’t given is there a workaround I can use ?

[@TallysYunes]

I need more details. Workaround for what? If capacity for production and/or inventory isn't given, you simply omit those constraints.

[@jassiemrobinson6038]

Tallys Yunes I’m working on a problem that says a manufacturer has a fixed capacity but it doesn’t say what the exact capacity is. But when I tried to omit the inventory capacity excel solver said they needed an equal amount of cells

[@jassiemrobinson6038]

Tallys Yunes I just put it a pseudo constraint for the inventory capacity and I got the number I was looking for. Thanks !! Very helpful video