Download
1 / 52

WUFFDA Basics - PowerPoint PPT Presentation


  • 252 Views
  • Uploaded on

WUFFDA Basics. WUFFDA requires the MicroSoft Excel spreadsheet program with the Solver Option installed. Basic knowledge of spreadsheet usage is all that is required to use the program. WUFFDA can solve a problem with 25 ingredients and 34 nutrients. First Step in the Formulation Process.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'WUFFDA Basics' - Audrey


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Wuffda basics
WUFFDA Basics

  • WUFFDA requires the MicroSoft Excel spreadsheet program with the Solver Option installed.

  • Basic knowledge of spreadsheet usage is all that is required to use the program.

  • WUFFDA can solve a problem with 25 ingredients and 34 nutrients.


First step in the formulation process
First Step in the Formulation Process

  • Prepare an ingredient composition matrix.

    • WUFFDA contains nutrient composition data for a large number of ingredients.

    • You may enter data directly into the “Active” Ingredient Matrix

    • Or you may copy data from the “Storage” matrix to the Active matrix.


Ingredients
Ingredients

  • The Active Ingredient Composition Matrix Box is at the top of the “Ingredients” spreadsheet.

  • Ingredient composition information is stored at the bottom of the “Ingredients” spreadsheet.

  • The composition of any number of ingredients may be stored there.


Active ingredients
Active Ingredients

  • The ingredients in the Active Ingredient Composition Matrix Box will be used in the current formulation.

  • The next window shows a typical ingredient composition matrix for a broiler diet.



Preparing the active ingredient matrix
Preparing the Active Ingredient Matrix

  • Copy ingredient composition data from the storage area at the bottom of the spreadsheet to the active matrix area at the top

  • Always make any changes that you want to be permanent at the bottom of the sheet and copy them to the top.


Preparing the active ingredient matrix1
Preparing the Active Ingredient Matrix

  • Copying data from the Ingredient Composition Storage Matrix to the Active Matrix:


Click on the “38” to highlight the Canola line, then press the right mouse button to pull down the edit menu, and click “Copy”.



Click on the “15” to highlight the Alfalfa line, then press the right mouse button to pull down the edit menu, and click “Paste”.


Alfalfa Data Replaced by Canola Data press the right mouse button to pull down the edit menu, and click “Paste”.


Nutrient requirements
Nutrient Requirements press the right mouse button to pull down the edit menu, and click “Paste”.

  • Next, the nutritional requirements must be set:

    • Lists of nutritional requirements are contained on the “Nutrients” spreadsheet.

    • You can enter data directly into the “Current Specification” Area.

    • Or you may Cut and Paste from stored requirement lists.


Highlight the data to be copied by holding down the left mouse button while dragging the mouse across the block.


Press the right mouse button and click on “Copy”. mouse button while dragging the mouse across the block.


Place the cursor on cell C4 at the upper left corner of the Current Specification Box and choose “Paste” from the pull-down edit menu.



By Copying and Pasting, the Original Data Remains Unchanged. A New Nutrient Specification Can Always Be Copied and Stored for Future Use.


Ratios between nutrients
Ratios Between Nutrients A New Nutrient Specification Can Always Be Copied and Stored for Future Use.

  • It is easy to specify ratios between nutrient minimumrequirements in WUFFDA.

  • Simply insert a formula in the cell containing the requirement.

  • In the following example, the available phosphorus requirement in cell C14 is set equal to ~38.9 % of the calcium requirement in cell C12.


Ratios between nutrients1
Ratios Between Nutrients A New Nutrient Specification Can Always Be Copied and Stored for Future Use.

  • To assure that exact ratios will be achieved in the solutions is a more complex problem.

  • Another column or “nutrient” must be added to the ingredient matrix.

  • This causes the ratio information to be stored with the ingredient composition matrix and not with the nutritional requirements.


Ratios between nutrients2
Ratios Between Nutrients A New Nutrient Specification Can Always Be Copied and Stored for Future Use.

  • The new column is calculated by:

    • C=A-(P x B)

  • Where:

    • C = Coefficient

    • A = Value of First Nutrient

    • B = Value of Second Nutrient

    • P = Desired Proportion Between Nutrients


A new column to set the Calcium to Phosphorous Ratio equal to 2.0000 is created by inserting an equation in the new column:


When the equation is copied to all the cells in the column, values for ingredients with higher Ca:P ratios are more positive :


The Value for the Ca:P ratio should be fixed at zero, so the positive and negative coefficients balance each other out.


The Available Phosphorous in the solution is exactly half the Calcium level when the Ca:P=2 coefficients total to zero.


If only the minimum requirement for Calcium is changed, the amounts of Calcium and Available Phosphorous in the solution changes.


Formulating a diet
Formulating A Diet amounts of Calcium and Available Phosphorous in the solution changes.

  • When the ingredients have been chosen and the nutrient limits have been set, it is time to formulate the diet.

  • Go to the “Formulate” Spreadsheet:


Formulating a feed
Formulating a Feed amounts of Calcium and Available Phosphorous in the solution changes.

  • The Formulate Spreadsheet contains the same minimum and maximum requirement data as the Ingredient and Nutrient Spreadsheets.

  • Changes to requirement data should only be made on the Ingredients and Nutrient Spreadsheets.


Formulating a feed1
Formulating a Feed amounts of Calcium and Available Phosphorous in the solution changes.

  • You can enter data directly into the “Amounts” column on the left of the spreadsheet, and the nutrients supplied by those amounts will be displayed on the right in the “Supplied” column.

  • This is an excellent method of estimating what is in a feed that is already formulated.


Or You Can Get Excel to Formulate the Least-Cost Feed By Choosing “Solver…” From the Tools Menu.



Hopefully Solver will find a solution and you can just click on the “OK” Button to see what it is.


The Amount of Each Ingredient in the Formula and the Nutrients the Diet Supplies Will Be Displayed.


Output from formulations
Output From Formulations Nutrients the Diet Supplies Will Be Displayed.

  • The information from the Formulate Spreadsheet is Automatically transferred to the Feed Spec. Spreadsheet for printing on a black & white printer, on a single sheet of paper.


“Print Preview” of a Feed Specification. Nutrients the Diet Supplies Will Be Displayed.


Output from formulations1
Output From Formulations Nutrients the Diet Supplies Will Be Displayed.

  • Results are also graphed on the “Graph” spreadsheet for viewing in another perspective: as a proportion of the minimum requirement.


Scroll to See Both Graphs Nutrients the Diet Supplies Will Be Displayed.


Output from formulations2
Output From Formulations Nutrients the Diet Supplies Will Be Displayed.

  • A mixing sheet can also be printed from the “Mixing Sheet” Spreadsheet.

  • The <Ctrl> and <m> keys must be pressed together to sort the data from the largest ingredient to the smallest (the order they should be added to the mixer).


Sensitivity analyses
Sensitivity Analyses Nutrients the Diet Supplies Will Be Displayed.

  • Solver can give information on how feeds may change:

    • Changes in ingredient usage when ingredient prices change.

    • Changes in feed prices when nutrient levels change.


Sensitivity analyses1

Sensitivity Analyses Nutrients the Diet Supplies Will Be Displayed.

Choose “Sensitivity” from the list of possible Reports in the Solver Results Dialogue Box.


Sensitivity analyses2
Sensitivity Analyses Nutrients the Diet Supplies Will Be Displayed.

  • This creates the Sensitivity Report Spreadsheet, containing all the necessary information.

  • Each time the feed is formulated an additional Sensitivity Report is created if requested. It may be best to delete them as soon as they are no longer needed.


This feed should contain 52.09 % Corn unless the price increases by $0.989, or decreases by $0.129. Corn’s current value is $5.25 per 100 pounds.



The cost of the feed is expected to change by $0.4201 if the level of protein specified were to change by 1 unit up or down.


Time to give wuffda a try
Time to Give WUFFDA a Try level of protein specified were to change by 1 unit up or down.

  • If you don’t get a feasible solution:

  • Relax constraints one at a time until you get a feasible solution. This should help identify the problem.

  • Make sure you use the same units throughout the problem.

  • Make sure there is Methionine Plus Cystine activity in the DL-Methionine, etc.


Good luck
Good Luck level of protein specified were to change by 1 unit up or down.

WUFFDA


ad