VIEWS: 212 PAGES: 23 CATEGORY: Other POSTED ON: 3/27/2008
A B C E F 1 Partial budgeting form (Profitability impacts) 2 Change being considered: 3 Litters per year: 141.6 4 Positive impacts Per Competing impacts 5 Increased incomes $ Increased costs $ 6 $0.00 litter $0.00 7 $0.00 8 $0.00 year 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Total increased incomes $0.00 Total increased costs $0.00 26 Reduced costs $ Reduced incomes $ 27 28 29 30 31 Total reduced costs $0.00 Total reduced incomes $0.00 32 Total positive impacts $0.00 Total competing impacts $0.00 A B C E F 33 Per year difference: $0.00 34 Per litter: $0.00 35 36 37 Calculating capital recovery charges 38 $0 Beginning market value 39 $0 Ending market value 40 5 Years of useful life 41 6.00% Interest rate 42 $0.00 Capital recovery charge G I J K L 1 2 3 4 Per 5 6 l 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 G I J K L 33 34 35 36 Calculating capital recovery charges 37 38 Beginning market value 39 Ending market value 40 Years of useful life 41 42 Capital recovery charge A B C D E 1 Partial budgeting form (Profitability impacts) 2 Change being considered: (baseline template; no changes) 3 Litters per year: 141.6 multiplier 4 Positive impacts Per Competing impacts 5 Increased incomes $ Increased costs 6 $0.00 litter 141.6 7 8 $0.00 year 1 9 10 The subtotal "Total increased incomes" (cell B25) is calculated 11 The multiplier values in D and H with this formula: =SUMPRODUCT(B6:B24,D6:D24) =IF(F6="","",IF(LEFT(G6,1)="l",$B$3, 12 13 This multiplies each value in column B with the value in column D If the first character of the “Per” 14 and then adds those products. Other subtotals are similar. multiplier will be the number of li 15 Tip: If your spreadsheet application doesn't support the SUMPRODUCT 16 columns are normally hidden. calculation, you can just use an extra column to first multiply columns B and 17 Tip: The "$" characters lock that part D (and F and H); then calculate the sum of this extra column for the 18 value at cell B3 so you can copy this subtotal. 19 20 21 22 23 24 25 Total increased incomes $0.00 Total increased costs 26 Reduced costs $ Reduced incomes 27 28 29 30 31 Total reduced costs $0.00 Total reduced incomes 32 Total positive impacts $0.00 Total competing impacts A B C D E 33 Per year difference: 34 =B25+B31 Per litter: 35 36 37 This area of the worksheet helps you calculate the Calculating capital recovery charges 38 capital recovery charge. The formula is: $0 39 =((E41*(1+E41)^E40)/((1+E41)^E40-1))*(E38- $0 40 E39)+(E41*E39) 5 41 6.00% 42 $0.00 43 F G H I J K L 1 2 3 multiplier Competing impacts 4 Per 5 $ 6 $0.00 l 141.6 7 $0.00 1 8 9 10 The multiplier values in D and H are selected with this formula: 11 =IF(F6="","",IF(LEFT(G6,1)="l",$B$3,1)) 12 If the first character of the “Per” cell in column C or G is the letter “ l ”, the 13 14 multiplier will be the number of litters per year; otherwise, it's just 1. These 15 columns are normally hidden. 16 Tip: The "$" characters lock that part of the formula to always use the litters per year 17 value at cell B3 so you can copy this formula anywhere and it will still work. 18 19 20 21 22 23 24 25 $0.00 26 $ 27 28 29 30 31 $0.00 32 $0.00 =F25+F31 F G H =F25+F31 I J K L 33 $0.00 =B32-F32 34 $0.00 35 =F33/B3 36 37 Calculating capital recovery charges 38 Beginning market value 39 Ending market value 40 Years of useful life 41 Interest rate 42 Capital recovery charge 43 A B C E 1 Partial budgeting form (Profitability impacts) 2 Change being considered: Increased idle time (+2 days) 3 Litters per year: 149.33 4 5 Positive impacts Per Competing impacts 6 Increased incomes $ Increased costs 7 Increased value of litter at market $21.53 litter Feed 8 0.2063 increased pigs per litter 36.3676 extra lb to market per litter 9 x 0.98 mortality in nursery at 2% x 3.33 feed:gain ratio 10 x 0.98 mortality in grower at 2% = 121.10 lb feed 11 x 0.98 mortality in finisher at 2% x $0.07 per lb feed 12 = 0.194168 increased pigs to market = $8.36 cost of feed per litter 13 x 241 lb weight at market 14 = 46.79447 increased lbs to market per litter 15 x $0.46 market price per lb 16 = $21.53 increased value of litter at market 17 Total increased incomes $3,215.07 Total increased costs 18 Reduced costs $ Reduced incomes 19 Pigs not fed $30.30 litter Decreased capacity 20 0.54721 pigs not fed 10.667 fewer litters 21 x 241 lb market weight x 8.14 pigs per litter 22 = 131.88 lb not produced x 0.98 x 0.98 x 0.98 mortality 23 x 3.33 feed:gain ratio = 81.717 total pigs lost 24 = 439.12 lb feed not fed 149.33 litters 25 x $0.07 per lb feed = 0.54721 pigs lost per litter 26 = $30.30 saved feed per litter x 241 lb weight at market 27 x $0.46 market price per lb 28 = $60.66 cost of lost pigs due to decreased capacity 29 30 Total reduced costs $4,524.70 Total reduced incomes 31 Total positive impacts $7,739.77 Total competing impacts 32 Per year difference: A B C E 33 Per litter: F G I J K L 1 2 3 4 Competing impacts 5 Per 6 $ 7 $8.36 litter 8 9 10 11 12 13 14 15 16 17 $1,248.40 18 $ 19 $60.66 litter 20 21 22 23 24 25 26 27 28 st of lost pigs due to decreased capacity 29 30 $9,058.36 31 $10,306.76 32 –$2,566.98 F G I J K L 33 –$17.19 A B C 1 Partial budgeting form (Profitability impacts) 2 Change being considered: Sow washing 3 Litters per year: 160 4 5 Positive impacts Per 6 Increased incomes $ 7 Increased value of litter at market $0.18 litter 8 0.00169 increased pigs per litter 9 x 0.98 mortality in nursery at 2% 10 x 0.98 mortality in grower at 2% 11 x 0.98 mortality in finisher at 2% 12 = 0.001591 increased pigs to market 13 x 241 lb weight at market 14 = 0.38333 increased lbs to market per litter 15 x $0.46 market price per lb 16 = $0.18 increased value of litter at market 17 18 Total increased incomes $28.80 19 Reduced costs $ 20 21 22 Total reduced costs $0.00 23 Total positive impacts $28.80 24 25 E F G I tability impacts) 1 2 3 4 5 Competing impacts Per 6 Increased costs $ 7 Labor $0.73 litter 8 0.1 hours per pig 9 x 15 pigs per farrowing 10 = 1.5 hours per farrowing 11 x 13 farrowings per year 12 = 19.5 hours per year 13 x $6.00 hourly rate 14 = $117 cost for 75-sow farm 15 160 litters per year 16 = $0.73 labor cost per litter 17 18 Total increased costs $116.80 19 Reduced incomes $ 20 21 22 Total reduced incomes $0.00 23 Total competing impacts $116.80 24 Per year difference: –$88.00 25 Per litter: –$0.55 J K L 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 A B C 1 Partial budgeting form (Profitability impacts) 2 Change being considered: AIAO 3 Litters per year: 156.8 4 5 Positive impacts Per 6 Increased incomes $ 7 Increased value of litter at market $23.72 litter 8 0.2273 increased pigs per litter 9 x 0.98 mortality in nursery at 2% 10 x 0.98 mortality in grower at 2% 11 x 0.98 mortality in finisher at 2% 12 = 0.21393 increased pigs to market 13 x 241 lb weight at market 14 = 51.558 increased lb to market per litter 15 x $0.46 market price per lb 16 = $23.72 increased value of litter at market 17 18 19 20 Total increased incomes $3,719.30 21 Reduced costs $ 22 Pigs not fed $8.66 litter 23 0.15635 pigs not fed 24 x 241 lb market weight 25 = 37.691 lb not produced 26 x 3.33 feed:gain ratio 27 = 125.48 lb feed not fed 28 x $0.07 per lb feed 29 = $8.66 saved feed per litter 30 31 Labor saved: monitoring $1,950.00 year 32 $6.00 cost of labor 33 x 50 hours required per farrowing with continuous flow 34 x 0.50 AIAO savings of 50% of monitoring time 35 = $150 saved per farrowing 36 x 13 farrowings per year 37 = $1950 saved per year 38 39 Labor saved: cleaning $175.50 year 40 6. hours labor with continuous flow 41 – 3.75 hours with AIAO 42 = 2.25 hours savings with AIAO 43 x $6.00 per hour labor cost 44 x 13 farrowings per year 45 = $175.50 labor saved per year 46 47 Total reduced costs $3,483.39 A B C 48 Total positive impacts $7,202.68 49 50 E F G I ability impacts) 1 2 3 4 5 Competing impacts Per 6 Increased costs $ 7 Feed $11.85 litter 8 51.558 extra lb to market per litter 9 3.33 feed:gain ratio 10 = 171.69 lb feed 11 x $0.07 per lb feed 12 = $11.85 cost of feed per litter 13 14 Capital recovery charge $482.78 year 15 • $4000 beginning market value 16 • $800 ending market value 17 • 10 years useful life 18 • 6.00% interest rate 19 (for one-time costs to convert facilities from continuous flow to AIAO) 20 Total increased costs $2,340.86 21 Reduced incomes $ 22 Decreased litters $2,717.84 year 23 3.2 fewer litters 24 x 8.14 pigs per litter 25 x 0.98 mortality in nursery at 2% 26 x 0.98 mortality in grower at 2% 27 x 0.98 mortality in finisher at 2% 28 = 24.516 fewer pigs to market 29 x 241 lb at market 30 x $0.46 market price per lb 31 = $2717.84 lost 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 Total reduced incomes $2,717.84 E F G I 48 Total competing impacts $5,058.70 49 Per year difference: $2,143.98 50 Per litter: $13.67 J K L 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 A B C E F 1 Partial budgeting form (Profitability impacts) 2 Change being considered: Pressure wash and disinfect (Cleaning) 3 Litters per year: 160 4 5 Positive impacts Per Competing impacts 6 Increased incomes $ Increased costs $ 7 Increased value of litter at market $35.99 litter Feed $18.23 8 0.3449 increased pigs per litter 78.233 extra lb. to market per litter 9 x 0.98 mortality in nursery at 2% x 3.33 feed:gain ratio 10 x 0.98 mortality in grower at 2% = 260.51 lb. feed 11 x 0.98 mortality in finisher at 2% x $0.07 per lb. feed 12 = 0.32462 increased pigs to market = $18.23 cost of feed per litter 13 x 241 lb weight at market 14 = 78.233 increased lbs to market per litter Labor $175.50 15 x $0.46 market price per lb. 0.15 hours per crate 16 = $35.99 increased value of litter at market x 15 crates for 75-sow farm 17 x 13 farrowings per year 18 = 29.25 hours per year 19 x $6.00 cost of labor per hour 20 = $175.50 yearly labor cost of cleaning 21 22 Capital recovery charge $712.19 23 • $3000 beginning market value 24 • $0 ending market value 25 • 5 years useful life 26 • 6.00% interest rate 27 (to purchase pressure washer) 28 Total increased incomes $5,758.40 Total increased costs $3,804.49 29 Reduced costs $ Reduced incomes $ 30 31 32 Total reduced costs $0.00 Total reduced incomes $0.00 A B C E F 33 Total positive impacts $5,758.40 Total competing impacts $3,804.49 34 Per year difference: $1,953.91 35 Per litter: $12.21 G I J K L 1 2 3 4 5 Per 6 7 litter 8 9 10 11 12 13 14 year 15 16 17 18 19 20 21 22 year 23 24 25 26 27 28 29 30 31 32 1 32