Homework answers / question archive / ANSC 221 HWEX2 Homework using Excel for Dairy Ration Balancing Problem The purpose of this exercise is to use the skills learned in the first Excel homework exercise to balance (or check) a ration, using the dairy cow as the species example

ANSC 221 HWEX2 Homework using Excel for Dairy Ration Balancing Problem The purpose of this exercise is to use the skills learned in the first Excel homework exercise to balance (or check) a ration, using the dairy cow as the species example

MS Excel

Share With

---

ANSC 221 HWEX2

Homework using Excel for Dairy Ration Balancing Problem

The purpose of this exercise is to use the skills learned in the first Excel homework exercise to

balance (or check) a ration, using the dairy cow as the species example. You need to first

balance the ration on a dry matter basis (column 1), then convert to as-fed (column 2), then

make each feed a percentage value in that formula (column 3) and then make that a ton of feed

(multiply each value by 20).

You could balance the ration as you did by hand for the swine ration, using the fixed amounts of

feeds specified for your problem, but you should create a spreadsheet for the calculations, put

in the fixed amounts of feeds specified, and then use algebra to get the correct corn and

soybean amounts, and then the correct Dical and limestone amounts. The potassium and

sulfur amounts will be calculated by Excel for you if you have created the spreadsheet properly,

and you can add KCl and/or NH4SO4 in the correct amounts as needed.

You can do this problem much as you did the first Excel exercise, except this one is more

extensive, and the directions for it are less explicit (so you might have to think just a little

more).

Create the spreadsheet and enter values to correspond with the homework exercise (called

#11). Add columns for calculating the content of each nutrient in each feed. Achieve your

answers and print out the spreadsheet page to turn in. Be sure to put your name on it. These

problems have answer pages with the correct solutions (like homework exercises 1, 2, 3 and 5

did), and you will be graded both for the correct answers and for successfully making the

spreadsheet to calculate it. Plan and lay out the spreadsheet in a way so that all columns fit on

one page (without the font becoming so tiny it cannot easily be read). There are options you

can change for margins, and for print options that can help with this, and it is possible to

change from portrait to landscape printing which you may use if you need to do so. You can

make your spreadsheet landscape, or place the calculation of nutrient content columns lower

on the page so it can be printed in portrait mode. (See the example page.)

On the back of this page are the directions for HW11 for doing it by hand (but

don’t do it by hand): Let Excel calculate the CP content of all your fixed feeds, and use that

to help calculate the corn and sbm amounts (use algebra). Enter those values and use the Excel

calculations for P content to help calculate the dical amount. Enter that and use the Excel

calculations for the Ca amount to find the Limestone amount, and so on. See the directions on

the back of this page for how to calculate the 4 columns on the homework page, and have Excel

make those calculations. Everything should be correct and Excel should show you that you

have it correct.

Directions for homework exercise #11 done by hand. Ration for Dairy Cow, %CP in ration method

• Balance the ration by the same basic method used for the “swine ration

method” that we learned, using the feeds listed, with 4 “fixed feeds”.

o Make it contain the level of CP listed for your problem

o Make it contain the level of corn silage and of alfalfa, Brewers grain

and whole cotton seed listed for your problem

o Leave initially the slack space listed for your problem

o Solve for corn and sbm

o Then solve for Dical, then limestone

o Put in the other ingredients as specified. Do the KCl and NH4SO4, if

needed, as you did copper sulfate in the swine problem, but first

make sure they are needed.

o Do everything so far on a DRY MATTER basis and put the answers in

the first column.

• Next, divide each feed amount by the %DM for that feed to get the as-fed

amount and put that in column 2.

• Total column 2 and then divide each feed amount by that total (times 100)

to get the as-fed percentage, and put that in column 3.

• For column 4, multiply each value in column 3 by 20, to get the amount in a

ton of feed of the as-fed amounts.

• Now, think about the logic of what you have just done, and see how it

makes sense.

Additional help:

To calculate the amount of corn (X), use: CP you want = X(CPx) + Fixed Feed CP + SBM(CPsbm), where

SBM is Y and Y=100-all the fixed feeds minus slack minus X. Solve for corn and then for sbm, enter those

to Excel and the sum of the CP column should show that you have the right amount of CP

Next you can use the Phosphorus content of your feeds that Excel has calculated and solve for Dical

needed. Follow with doing that for Calcium (from limestone).

If you have entered the Requirements, and subtracted what you have from what you need, you can see

that every need is met, and how far over any of them are