all right time to have another feed formulation practice today actually i was thinking to make some videos some tutorial videos and show you how to use some feed formulation software such as w-u-f-f-d-a or cfc five to formulate the feed but i thought okay if as a nutritionist i don't have access to a feed formulation software what should i do should i give up should i formulate the feed manually yeah it's possible but you know if you're gonna formulate the diet manually it will take time but if we use our creativity we can just use excel spreadsheet to make our own feed formulation spreadsheet it's easy and in this video i'm gonna show you how we can use a excel spreadsheet to balance a diet based on least cost feed formulation in my next video i'm gonna show you how to use non-linear programming models to formulate the feed based on a maximum profit feed formulation so let's get it started so i'm gonna just share my screen and fire up the excel sheet okay so first of all we need to have solver function in the data menu to optimize the diet actually we use the solver function for optimization practice but originally it's not activated here and you need to activate it although i have made another video on how to activate solver function in excel in this video i'm gonna show you again to activate the solver function you need to click on file and then click on options and then just click on add-ins and then click on go and check the solver add-in box and click on ok so if we take a look at the data section we can see solver function has been activated over here and we will use this to formulate the feed based on either least cost with formulation or maximum profit fee for emulation okay so actually uh to formulate it by it we need to have information on ingredients price and ingredients nutrient composition and we need to know the nutrient requirements of the animal and then we will combine those ingredients in such a way to meet the nutrient requirements so i'm gonna start off with importing ingredients information so you can just use the first row as headings for the ingredient information previously i have imported the feed ingredients list here just to save the time in this video so it's a simple diet you can see you can put items like corn different ingredients corn wheat wheat mealing soybean oil canola soybean meal so as you can see in each diet we need to have energy feed protein feed and additives so here corn wheat with middlings and soybean oil and canola are the energy ingredients soybean meal and fish meal is pro r protein ingredients so i've got oyster shell meal and limestone as sources of calcium and i have thai calcium phosphate as a source of calcium and phosphorus i've got sodium bicarbonate or soda as a source of sodium and i've got salt as a source of sodium and chloride actually i will may i will make more videos on nutritional management in animal nutrition and i try to explain every single trick and art that we can use in feed formulation but i'm gonna take this opportunity to introduce one of those tricks so i have seen in most of the diets all around the world people use salt as a source of sodium and chloride but the idea is as you can see here salt has 39 percent sodium and 60 percent chloride it means that its chloride is way higher than its sodium and on the other hand the requirement of sodium and chloride are same both are from 0.16 to 0.23 percent in the diet the requirement of sodium and chloride okay if you're gonna add salt to balance the sodium requirement as you can see this ingredient has more chloride than the sodium then your dietary chloride will go up and it means that your diet will have more negative charge it means chloride and it can cause several problems skeletal problems and leg problems in poultry that's why i strongly recommend using sodium bicarbonate that has only sodium as a source of sodium and use the salt balance the sodium and chloride it's really important some people think okay sodium b carbonate is used in under the heat heat stress conditions but the reality is under normal situation also you need to use sodium bicarbonate and it's really important okay then i've got synthetic amino acids additives for example lysine dl methionine which is used to balance the methionine and methionine blood cysteine in the diet and i've got threonine and i've got vitamin and prim and mineral premixes okay so these are my ingredients that i'm going to use in this practice so the next step actually if you want to make it fancier you can just select your ingredients and then on the home tab you need to click on format as table and you can make a table to to actually make it fancier and i'm gonna check my table has headers there we go okay now you can see uh i've got my feed stuff in blue color and every other um nutrient composition are in the pillow or even i can change it to actually other format yeah i think this looks better because you can see every other row has different colors and i think it's more easier to go through the data in this section okay so this is my ingredient tables what else do i need then actually i need to put ingredients constraints because i'm gonna um give minimum and maximum value for each feed for this stuff because it's important we need to pay attention to anti-nutrient in the feed stuff and also uh fidestov's price and both of them are limitation limiting factors so based on uh the presence of anti-nutrient inefficient and also based on the fittest of cost we need to determine the amount of its usage in the diet so because i'm gonna separate it from my table and i don't want to attach it to the table i'm just gonna copy this cell actually cut it and paste it over here okay now i need to have minimum amount based on percentage and maximum amount again based on percentage and here actually will be my inputs so i'm gonna just choose this one this color from here from style to make it more fancier and easier to detect and work on that okay so because actually it is based on percentage so i'm gonna choose these cells and put it as a percentage style and i'm gonna add two decimals it means if i for example let's say soybean oil can be used maximum five percent if i put five percent you can see five point zero zero it means uh i put it as percentage and i change the format from here okay so to make it easier i'm gonna put zero for all the minimum values and 100 for all the maximum values but as i said i can change them whenever i want in during my feed formulation so for the vitamin premix and mineral premix actually we are using them as in a fixed value in a fixed amount and it is 0.25 percent it means two and a half kilo per ton of the feet so i'm gonna put minimum and maximum value same for vitamin and mineral premix okay so then i would have ingredients inclusion in my diet it means the final formula so i'm gonna just okay adjust the cell size now it's better so over here i will have my final feed formulation so i'm gonna format it same as here it means that for example if corn is 60 percent you can see it will be shown as 60 percent in the diet okay okay it's the first section of feed formulation it means that we need to know our ingredients nutrient composition and also their cost and we need to specify minimum or maximum values that they can be used in the diet okay the second step as i said is uh specifying the nutrient requirements okay so i'm gonna move my video over here okay now i'm going to put nutrient constraint over here and i will have minimum and maximum value okay and i'm gonna just merge these two cells yeah it looks better and to separate it from the ingredient table i'm gonna format it in a different color so actually i can just i'm trying to actually separate is a section okay this one i'm gonna format it as different color there we go and i don't need these extra columns and i'm gonna get rid of them okay so i can actually change the color here okay so now i can see i have specified this color for the nutrient requirements section okay then i need to have quiet specification okay before actually writing the diet specification let's just uh import the nutrient requirements over here actually i'm gonna use a ros 308 nutrient requirements and to formulate a feed and i'm gonna use the grower phase nutrient requirements so as you can see rus 308 broiler chickens need um 3 100 kilo calorie per kilogram energy metabolizable energy i can import it as 3.1 because i'm gonna change the unit to mega calorie per kilogram so and it's not the fixed amount i think in my previous video i talked about fluctuation in nutrient requirements and i told you that nutrient requirements are in the range not a fixed amount so based on my experience i would say for a grover phase i can accept 2.9 mega calorie per kilogram dietary energy as well so as you can see i put 2.9 and as a minimum value for energy and 3 2 1 for maximum value actually i think yeah this is the weight of the diet so to make it easier i'm gonna um go to view and freeze the first column okay now if i move over here you can see the first column is here and i can see that to make it easier okay to work on that okay i'm gonna put them as m e or metabolizable energy nutrient requirements the next nutrient is protein if i look up at protein requirements it is 21.5 percent for the grover section i'm gonna put it 21.5 and for the minimum value i can choose um 20 percent so the next nutrient i got here actually it's mineral it's calcium so calcium requirement is 0.87 i'm gonna put 0.87 and for phosphorus i've got 0.435 0.435 for the sodium and chloride usually the range is between 0.16 to i can put 0.17 for both of them for the lysine i've got 1.29 as you can see here for amino acids requirements there is actually um two columns total and digestible for sure using the digestible section is better but i will talk about that in my next videos and i will explain that especially when we are using byproducts uh for example um poultry byproducts in the diet in that case we need to use digestible amino acids but if you if your ingredients you know are normal ingredients like corn wheat and in that in in this case you don't have to use digestible amino acids but if you do there is no problem so lysine requirement is 1.29 and i'm gonna import it here for the next one is methionine for methionine i've got 0.51 0.51 and for the methanol plus cysteine um i've got 0.99 for throwning i've got zero 0.88 and for the trip to fun i've got 0.21 so now i have completed the nutrient constraints but one thing remains and it is the weight of the diet as you can see i have put one for all ingredients and here i'm gonna put one it means that my diet has to be formulated in 100 percent okay the next step is diet specification in diet specification i can actually again format it [Music] because we shouldn't uh touch this part i mean i'm going to put formula and for the formula section you shouldn't touch the formula section again because the software is gonna calculate those numbers that's why i am going to choose warning text text okay and i have actually okay format it as warning text in red color to show that you shouldn't touch there okay so this column was redundant and i'm gonna get rid of that okay so now i'm gonna put formula and calculate my diet specification if you are not savvy in excel that's totally fine because i'm gonna use a simple formula here and it is sum product to insert a formula first you need to put equal sign when you put equal sign it shows that you are going to use formula in this set so over here i'm gonna write some product actually it popped up and i'm gonna just oops yeah yeah some product in some product what i'm going to do is i'm going to multiply all of these cells i mean the weight of each ingredient and put comma i'm gonna multiply it by the inclusion rate in the diet okay because now these cells are zero you can see that my cell here is zero but just for test that if i put one you can see it is 0.01 and if i put it as hundred percent just imagine i'm going to use 100 percent of my diet as core it's not a good idea i'm just testing my software so if i put hundred percent you can see that diet specification cell is one so i can actually save it here for now to import the other cells as well okay now i'm going to specify my dietary energy so if i formulate my diet i will see the dietary energy over here so i'm gonna put again some product you know product means the product of a multiplication and the sum means i'm going to add them up together so then i'm saying sum product it means for example if i'm going to calculate the dietary energy i'm going to multiply each ingredient inclusion in the diet by its energy value and then i'm gonna add them up together to have my final value for the dietary energy it is the meaning of some product so for energy i'm gonna choose these cells these row i mean the energy one and then i'm gonna put comma and then i'm gonna choose the feed formula section or ingredient in collusion section all right and i can put parentheses there we go see because i have put 100 percent of the diet as corn just to test that and the dietary and the energy level of corn is 3.35 mega calorie per kilogram so it shows that my final energy value is 3.35 mega calories so i'm going to actually repeat that for all of the ingredients in my diet okay some product for protein section i'm gonna choose israel put comma and then inclusion rate and then i'm gonna close parentheses okay for calcium section again i'm gonna put some product and choose the calcium values put comma and then choose inclusion rate and close the parentheses and i'll do that for actually available phosphor section as you know some part of the phosphorus in plants has been bound by the phytate as anti-nutrients and that part is not available for the monogastric animals not for ruminants or ruminants it's available but for monogastric animals it's not available and that's why we need to consider free phosphorus or available phosphorus i will make more videos on these stuff on nutritional management and i will try to explain everything everything regarding the precise nutritional management okay i'm gonna choose inclusion rate close parentheses and so actually if you want to be to make it easier the other trick i'm gonna show you is in this cell because i don't want to spend time to you know choose every row separately in this cell if i for example in the first one in the in this one if i see the b22 up to r22 is same and fixed for all of my calculations in this column so here if i click between b and the number of cell or the number of row i mean 22 and then i press f4 you can see it's gonna put double dollar sign around the p it means that this cell will be same and fixed for the upcoming cells in the formula and i'm gonna do same for this one for r there we go now if i click here in this cell and just put my cursor in the corner to see the plus sign after that i'm gonna just double click and or i can just drag it down there we go you can see uh i filled up the formula for the next cells without spending and wasting my time for that okay so now you can see this column by a specification column will show the amount of nutrients in my diet and i need to specify the dietary cost as well because we are going to use minimum price or least cost feed formulation that's why i need to have my dietary cost as well so i'm gonna choose costs and multiply it by the amount of each ingredient and add them up see here my corn price is 0.234 dollar per kilogram i'm just pretending so if i use 100 percent of my diet as corn my dietary cost will be the price of corn and it's the idea so after this i'm gonna formulate it that's it so now i'm gonna click on data tab and click on solver okay this part is the most important part in seed formulation because i'm gonna specify my feed formulation method and my feed formulation method here is this cost fit formulation in this window i've got objective cell it says set objective what's my objective here it is the dietary cost i'm gonna minimize it so i'm going to select this cell it is u3 or dietary cost that i can see in my directory cast here and what i'm going to do with objective cell am i going to maximize it or minimize it minimize it because i want to have a diet with least cost formulation and i'm gonna minimize the cost of the diet but in my next video that i will show you how to use excel to formulate the diet based on maximum profit feed formulation in that case you will see i will put profit cell here as objective cell and i will choose maximize it because i want to maximize my profit okay in this case it is list cost fit formulation so the next tab is by changing variable cells i'm going to formulate diet based on or by changing the values over here it means i'm going to choose ingredient inclusion as you can see it is cell b22 up to cell r22 okay it means that i'm saying the i'm telling the software by changing the values in this row actually meet the constraints i will specify the constraints here and formulate the diet okay subjects to constraint i need to have some constraints in my feed formulation i'm going to add so cell reference the first constraint is i need to have my inclusion rates less than less than or equal to the maximum amount that i have specified here see cell reference is actually p 22 until r 22 which means the formula feed formulation or my final um formula the inclusion rate of ingredients and they need to be less than or equal the maximum amount that i have specified in row 20. so add the next constraint is my inclusion rates should be greater or equal as you can see i changed it to greater or equal and what's the constraint minimum it means that they need to be um higher than minimum amounts so here i'm gonna actually okay i'm gonna change them a little bit because i've got 0.25 percent here for vitamin px and mineral premix and for some reason i have seen that if we specify because we want to use vitamin premix and mineral premix exactly at 0.25 percent if we put a constraint and say the reference cell should be equal exactly equal to this cell it will make it easier to formulate so i'm gonna just change this because here i specified um p22 to r22 i'm going to exclude mineral and vitamin premix for now and yeah that one this row until here and for this one it should be less than or equal to this amount as you can see i have selected from corn until throning and i i have excluded vitamin and mineral premixes here okay and for this one actually i'm gonna do same thing change it for reference cells i'm gonna choose these cells and they should be greater than the minimum values so as you can see i have excluded the vitamin and premix now i'm going to specify a vitamin and premix equals to this number see q22 this cell should be equal to uh q20 it means that i want the software to put the amount of vitamin premix exactly equal to 0.25 percent and i'm gonna add one more constraint and put the mineral premix equal to 0.25 okay i have done from um ingredients constraint and now i'm going to give constraints for diet specification so now i'm going to choose the weight of the diet that it is here it should be equal to this one nutrient constraint i mean weight of the diet that i have specified one it means that it needs to be formulated at 100 percent okay so now is better and just turned on my light here okay there we go i'm gonna add now i'm going to specify that these cells i mean my dietary nutrient concentrations and energy concentration should be greater or equal to the minimum values that i have specified over here and my energy and protein section should be less than or equal to the maximum amount that i have specified here so now i have got almost [Music] all constraints here and you need to check this box make unconstrained variables non-negative and then from the selecting select a solving method i'm gonna select simplex lp lp means linear programming because for nlp one non-linear we will use that in maximum profit fit formulation if i click solve okay we need to pay attention to this message it says solver found a solution all constraints and optimality conditions are satisfied great job if you get this message it means you are error free and you got the final formula but this is the computer it doesn't know anything about ingredient limitations it doesn't know anything about um nutrient requirements of animals or different scenarios that we might deal with in the real scenario i mean we need to double check the values that we got here for example for soybean oil i can see i've got 4.96 percent it's good it's not higher than the maximum amount of soybean oil if i want to for example here [Music] decrease the as you can see for soybean meal and fish meal both are zero why because the software has been trying to provide the dietary protein by using canola and i don't want to do that i want to restrict the amount of canola and use soybean meal and fish meal as well so i'm gonna just put um let's say 20 percent or even 15 percent for as a maximum value for canola and then i'm gonna formulate it again solve okay the message is good as you can see it decreased the amount of canola and it has added actually soybean meal and fish meal fish meal doesn't sound good to me i'm gonna decrease its amount to let's say three percent and solve it again again the message is good solver found the solution and you can see the usage or inclusion rate of fishmeal is three percent in my diet and all other um ingredients have been used in a good amount in an acceptable amount so by this actually i have formulated a diet to minimize the dietary cost now it's all we finished but if you want to make it user friendly for printing stuff you can just put something like um final formula over here and you can put ingredients and the person inclusion percentage i mean it's an extra thing you don't have to do that but i'm doing this just to um make it easier to read so ingredients i'm gonna say this cell should be equal to this cell and this one should be equal to wheat as you can see i'm trying to copy them down to have them in vertical position rather than having them in horizontal position because it looks better okay after limestone is calcium phosphate after that is sodium by carbonate after that is salt equals lysine equals pl methionine equals threonine and equals vitamin premix and mineral premix i think i've got all of them so now inclusion is actually this cell for corn and let me see if i can drag them down okay no i i think i need to actually put the formula one by one okay wheat middlings is this one and soybean oil is this value canola is this one for soybean meal i've got this one for fish meal i've got three percent over here and oyster and limestone and um by calcium phosphate and the other one is sodium bicarbonate the other one is salt the other one is lysine and the methionine and the throneing and vitamin premix and mineral premix okay if i want to um format them as table i'm gonna go to home tab and forum format as table let's say i'm gonna choose this one my tables has my table has headers yes okay see now uh if i formulate the diet let's say i'm going to for example change it to 20 percent for now and then formulate it again to see how it looks like okay you can see my final formula is here then i put it as a vertical now and i format it as table now i have a choice to sort it actually for example smallest to largest or sort them by largest i hope you enjoyed this video and you gained some experience about feed formulation and being independent if you have in a situation that you don't have access to um you don't have access to the feed formulation software you can use excel spreadsheet and just formulate your diet if you have any question please let me know down there in the comments and i'll see you in the next episode thank you very much