Application of Alternative Floor Space Prediction Equations using Microsoft Excel ®

The following information is related to the use of a spreadsheet tool designed to estimate the growth rate of finishing pigs provided varying floor space allowances. The spreadsheet is broken into four sections: 1) the adjustment observation section, 2) the estimate input section, 3) the database range information, and 4) the predicted performance output. Sections 1 and 2 allow users to input information to adjust the prediction equations to specific herd growth rates as well as information needed to calculate the growth rate estimates for different floor space allowances. This tool allows producers to utilize the prediction equations without having to convert standard to metric units and provides a simple output that is easy to evaluate and use as compared to the more complex prediction equations themselves. Three examples are included to show users the possible value of the spreadsheet tool. It is important for users to understand the potential pitfalls associated with inputting information into the spreadsheet and formatting “flags” that warn users of potential problems in the input information.


Introduction
It is known that reducing the floor space allowance of pigs reduces ADG and ADFI.Alternatively, when floor space per pig is reduced, production per unit of floor space increases.This leaves a challenge for producers to balance animal welfare and economic implications of floor space allowance with management practices.Also, with increases in market weights and the productivity of the breeding herd, more finishing pigs are fed for longer periods, suggesting that floor space may be more limited now than previously.Providing equations that accurately predict the impact of floor space allowance on growth could allow producers to establish value per unit of floor space to optimize growth rate while still efficiently reducing the fixed facility costs per pig.
Prediction equations developed by Flohr et al. (2015), when compared to previously developed equations, appear to be useful predictors of finishing growth performance.
Assembling the prediction equations in a user-friendly format that does not require conversion to metric units may appeal to producers for use in their own operations.The following material describes the use of an Excel spreadsheet that contains the prediction equations that allow producers to estimate the growth rate of finishing pigs based on floor space allowance and their own herd performance information.

Description
The specific equations developed to predict ADG, ADFI, and G:F are included in Table 1.These equations include the input terms of initial BW (kg), final BW (kg), and k (floor space, m 2 /final BW, kg 0.67 ).The equations for ADG and ADFI are quadratic polynomial equations where increasing k increases the response criteria, but at a decreasing rate.This suggests that eventually a plateau is reached where floor space allowance is optimal.After this point, continuing to increase floor space will reduce growth rate.Biologically, it would be expected that once the plateau is reached, then continuing to increase floor space would not affect growth rate; unfortunately, the statistical software to produce this type of complex nonlinear model is not available.
Two separate databases were used during the equation development process.The first database included information from studies examining the impact of floor space allowance on growth performance.The second database included the aforementioned studies along with studies examining the impact of floor space allowance after removal of pigs from the pen.This was an important distinction to evaluate in the database information, considering that many believe additional compensatory growth occurs following the removal of pigs from a pen.However, after comparing the fit of models from both databases to information from separate floor space allowance studies, equations from the second database appeared to better fit observed growth data; therefore, this discussion focuses information on equations developed from the second database.The equations from the second database are those available on the spreadsheet tool.
A screen shot of the Excel spreadsheet is present in Figure 1.The spreadsheet is broken down into four key sections (circled and numbered) which include: 1) the adjustment observation section, 2) the estimate input section, 3) the database range information, and 4) the predicted performance output.Each section is outlined below.

Section 1
The adjustment observation section is one of the two areas into which users enter information.Users input their finishing pig growth rates, along with the known floor space allowances (ft 2 ) used in the operation.The information then adjusts the intercepts of the prediction equations to levels similar to the user's individual performance.This is an important step in assessing the value of the equations because growth rates are very dynamic measurements affected by a variety of farm-or building-specific factors that are hard to categorize within prediction models.The user needs to provide: initial weight (lb), final weight (lb), floor space (ft 2 ), observed ADG (lb/d), and observed ADFI (lb/d).

Section 2
After entering information into the intercept adjustment section, the user can enter different weight ranges and space allowances to predict growth rates under various scenarios in section 2. Users can evaluate 5 different estimates.The information needed for this section includes an initial and final BW (lb) and a floor space allowance (ft 2 ).From that information, a k coefficient for each estimate is calculated, and the information is used to estimate the growth rates.

Section 3
Brief statistics that describe the information used to develop the prediction equations is located in section 3.This information includes the average, minimum, and maximum values observed for initial and final BW, floor space, and k.This allows users to see the range of information that was used in developing the prediction equations.It also provides the basis for some of the conditional formatting of section 2. If values in section 2 are entered either above or below the minimum or maximum values used in the database, then the cell in section 2 will format with a red background.This suggests that the prediction equation may not be as reliable when input values are outside the range of information used to develop them.Although k is calculated and not entered by the user, if a k is calculated outside the range of information used to develop the equations, then the k cell will also format with a red background.

Section 4
The final section is the predicted performance output.Once input values are provided, the predicted performance will automatically update.Outputs include ADG, ADFI, G:F, and F/G.Additionally, below the response criteria, additional percentage change calculations are provided.For each response criterion, a percentage change from the first estimate is provided, along with the percentage change calculation from the previous estimate.The spreadsheet is designed to predict changes in growth when floor space allowance is increased.Predicted changes appear from left to right in the estimate section; therefore, if the values are not entered in that fashion, the percentage change calculations will not provide proper information.
As mentioned previously, conditional formatting rules alert the user if entered data are outside the bounds of the information used to build the database or if the user does not increase floor space allowance from left to right in the estimate input section.Additional formatting measures were also used in the output section to alert users when they reach or exceed the maximum growth that can be achieved by the prediction equation.If the user enters values that result in passing the plateau of the prediction equations, then the font of the predicted response criteria will be in red.

Examples
There are three examples provided to demonstrate the capabilities of the floor space prediction equation spreadsheet.The first will examine how growth changes when pigs are stocked at different floor space allowances throughout their time in the finisher.The second will examine how the marketing of pigs in late finishing can alter growth rate of pigs remaining in the pen, based on subsequent floor space allowance.The third exam-ple simulates multiple marketing events and the growth performance associated with the pigs remaining in the pen.

Example 1. Constant stocking density
This example examines how growth rate would change by stocking pigs in the finisher at 6, 7, 8, 9, and 10 ft 2 from 50 lb until the desired market weight of 280 lb.The first step is to enter the known information from the production system to adjust the intercepts of the prediction equations.For this example, the known ADG is 1.90 lb/d, and known ADFI is 5.70 lb/d for pigs from 50 to 280 lb when stocked at 7.0 ft 2 .The next step is to enter the initial and final weights into the estimate input section.All the initial weights in the five estimate values should be 50 lb and the final BW should be 280 lb, representing the expected entry weight and the target exit weight when marketing begins.Then, the floor space per pig for the estimate input section is entered at increasing levels, left to right, starting with 6 ft 2 and increasing to 10 ft 2 .
Once the information is entered into sections 1 and 2, the spreadsheet automatically updates with the output information.When the estimate information is entered, performance of estimate two equals the adjustment observation information, which means the intercepts were adjusted correctly.For ADG, reducing floor space from 7.0 to 6.0 ft 2 reduces the estimated ADG by 0.06 lb/d or approximately 3.0%.However, increasing floor space allowance from 7.0 to 10.0 ft 2 increases the estimated ADG from 1.90 to 1.98 lb/d.The estimated ADFI was reduced by 0.09 lb/d when floor space was reduced from 7.0 to 6.0 ft 2 .Meanwhile, ADFI increased from 5.70 to 5.82 lb/d when floor space was increased from 7.0 to 10.0 ft 2 .Feed efficiency, measured as G:F or F/G, was negatively affected with the reduction in floor space allowance from 7.0 to 6.0 ft 2 ; however, increasing floor space allowance from 7.0 to 10.0 ft 2 improved G:F and F/G by 2.2%.

Example 2. Removals per pen during marketing (topping)
The second example estimates the effect of marketing different numbers of heaviest pigs out of a pen on the growth rate of pigs remaining in the pen.The same adjustment observation information from Example 1 can be used.The weight range for the example is 240 to 280 lb.For this example, the same stocking density (7 ft 2 ) will be used for the first estimate, and then floor space allowance can be adjusted based on removing pigs from the pen.If each pen is assumed to contain 20 pigs (initially stocked at 7.0 ft 2 ) then the pen area is 140 ft 2 .The increases in floor space can represent the removal of pigs from the pen (2, 3, 4, or 5).To calculate the floor space per pig, the total pen area (140 ft 2 ) is divided by the remaining number of pigs in the pen (20, 18, 17, 16, or 15).The value is rounded to the nearest 0.1 ft 2 when entered into the spreadsheet.It is important to remember that this example does not address how much the initial BW of pigs in a pen is reduced with the removal of the heaviest pig or pigs.That change should be considered for more precise growth estimates.
The first estimate entered represents the growth rate of pigs from 240 to 280 lb when no pigs are removed from the pen before 280 lb.Estimates 2 through 5 represent removing 2, 3, 4, or 5 pigs from each pen.Removing 2 pigs from the pen and relieving floor space allowance to 7.8 ft 2 resulted in an increase in the predicted ADG (1.65 to 1.72 lb/d), an increase in estimated ADFI (6.03 to 6.18 lb/d), improved estimated G:F (0.274 vs. 0.279), and a reduced estimated F/G ratio (3.65 to 3.59).Removing an additional pig from the pen and going from 2 to 3 removals provided an approximately 8.2 ft 2 increase in the estimated ADG (1.72 to 1.76 lb/d), ADFI (6.18 to 6.26 lb/d), and G:F (0.279 to 0.281) and reduced the estimated F/G ratio (3.59 to 3.56).If the user removed a total of 5 pigs from the pen then estimated ADG, ADFI, G:F, and F/G would all improve.

Example 3. Multiple marketing (topping) events
Another option producers may want examine is the use of multiple marketing events per pen.In this case, the user will have to predict performance following each marketing event.For this example, the same adjustment observation information is used from the first example.Next, marketing either 0 or 2 pigs at an average BW of 240 lb, and then an additional 0, 2, 4, or 6 pigs at 260 lb, and then marketing the entire barn at an average weight of 280 lb can be modeled.The user must first determine the effect of removing 240-lb pigs on floor space allowance from 240 to 260 lb for the first marketing event, and then evaluate the effect of removing 260-lb pigs on floor space allowance from 260 to 280 lb.Afterwards, the growth periods must be added together to get the total growth performance for the pigs remaining in the pen from 240 to 280 lb.Again, this simple example does not address how much the initial BW of pigs in a pen is reduced as the heaviest pig or pigs are removed.That change should be considered for more precise growth estimates.
From 240 to 260 lb, removing 2 pigs per pen and providing 7.8 ft 2 improved predicted ADG by 0.08 lb/d (Table 2), increased ADFI by 0.15 lb/d, and reduced predicted F/G by 0.06.From 260 to 280 lb, it appeared that increasing the number of removals and providing additional floor space resulted in improved ADG, ADFI, and F/G.To capture the impact of the entire period of growth from 240 to 280 lb for the pigs remaining in the pen, the user must combine the growth performance of the pigs from 240 to 260 lb and from 260 to 280 lb.Table 2 illustrates the predicted growth rates based on the marketing strategies.

Potential errors in the use of the floor space spreadsheet
Problems with the spreadsheet may arise if the user enters values improperly.Conditional formatting steps have been used to warn users when problems may occur.The first errors are illustrated (Figure 4) when values outside the range of information used to develop the equations are entered in section 2. The blue circle identifies an initial BW outside the proper range, the red circle identifies a final BW outside the proper range, and the green circle identifies a floor space allowance outside the proper range.The orange circle highlights a k value (calculated from final BW and floor space) that is outside the proper range, although final BW and floor space do not show any error.That means the final BW and floor space combination are not within the range of those used in equation development, so the floor space allowance needs to be adjusted to resolve the error.This scenario often occurs when lighter final BW along with high floor space allowances are examined.
In Figure 5, the user has passed the plateau of the predicted quadratic curve, which results in a decrease in the growth rate.Biologically this does not make sense, but due to limitations in the current statistical software, a mixed linear model used for equation Swine Day 2015 development results in decreasing predicted response criteria after the plateau is passed.To warn the user that the plateau has been passed, the font of the output response criteria turns red.In this example, the user is examining the change in predicted growth when floor space is increased from 9.0 to 13.0 ft 2 .The predicted growth rates plateau in estimate 3 at 11 ft 2 .Since estimates 4 and 5 result in decreased response criteria, the fonts are turned red, signifying that the plateau has been passed.In addition, the percentage change information shows a negative change, suggesting the plateau has been passed.

Conclusion
This spreadsheet tool can be helpful for individual producers to estimate the changes in growth rate for finishing pigs provided different floor space allowances.It is important that the user enters information properly to reduce the chance of poor inference from the equation outputs.Conditional formatting of the spreadsheet helps the user realize when an input error may have occurred.

Figure 1 .
Figure 1.Screenshot of floor space prediction equation spreadsheet.Circled sections include: 1) the adjustment observation section, 2) the estimate input section, 3) the database range information, and 4) estimated performance section.Areas in yellow represent cells where the user can input information.

Figure 2 .
Figure 2. Screenshot of floor space prediction equation spreadsheet with example 1 inputs.

Figure 3 .
Figure 3. Screenshot of floor space prediction equation spreadsheet with example 2 inputs.

Figure 4 .
Figure 4. Potential input errors outside the range of information used to develop prediction equations.

Figure 5 .
Figure 5. Font change resulting from passing the predicted plateau of response criteria from the prediction equations.Corresponding percentage change values are also negative.

Table 1 .
Regression equations generated from existing data for ADG, ADFI, and G:F Prediction equations developed from the database with pig removal studies are those used in the spreadsheet tool.